File Name: ms sql server dba interview questions and answers .zip
In this article, we will discuss a number of questions that you may be asked when applying to a senior SQL Server database administrator position.
Explore Now! These questions and answers are suitable for both freshers and experienced professionals at any level. The source data will be copied to destination through replication agents jobs. Object level technology.
There are a lot of opportunities from many reputed companies in the world. What purpose does the Model Database Server? The model database , as its name implies, serves as the model or template for all databases created on the same instance. If the model database is modified, all subsequent databases created on that instance will pick up those changes, but earlier created databases will not. Traces can be filtered to narrow down the transactions that are captured and reducing the overhead incurred for the trace.
The trace files can be searched, saved off, and even replayed to facilitate troubleshooting. Snapshot replication creates a snapshot of the data point-in-time picture of the data to deliver to the subscribers. This is a good type to use when the data changes infrequently, there is a small amount of data to replicate, or large changes occur over a small period of time. Merge replication uses a snapshot to seed the replication.
Changes on both sides of the publication are tracked so the subscriber can synchronize with the publisher when connected. A typical use for this type of replication is in a client and server scenario. A server would act as a central repository and multiple clients would independently update their copies of the data until connected. At which time, they would all send up their modifications to the central store.
Transaction replication also begins with a snapshot only this time changes are tracked as transactions as the name implies. Changes are replicated from publisher to subscriber the same as they occurred on the publisher, in the same order as they occurred, and in near real-time. This type of replication is useful when the subscriber needs to know every change that occurred to the data not point-in-time , when the change volume is high, and when the subscriber needs near real-time access to the changes.
Jobs can be scheduled to run at a set time or when a specific event occurs. Jobs can also be executed on demand. SQL Agent is most often used to schedule administrative jobs such as backups.
Checkpoints, whether scheduled or manually executed, cause the transaction log to be truncated up to the beginning of the oldest open transaction the active portion of the log. That is, the dirty pages from the buffer cache are written to disk. Storing committed transactions in the cache provides a performance gain for SQL Server. However, you do not want the transaction log to get too big because it might consume too many resources and, should your database fail, take too long to process to recover the database.
One important thing to note here is that SQL Server can only truncate up to the oldest open transaction. Therefore, if you are not seeing the expected relief from a checkpoint, it could very well be that someone forgot to commit or rollback their transaction. It is very important to finalize all transactions as soon as possible. Maintenance commands are those commands that allow the DBA to perform maintenance activities on the database such as shrinking a file.
Informational commands provide feedback regarding the database such as providing information about the procedure cache. You can set the fill factor on your indexes. This tells SQL Server how much free space to leave in the index pages when re-indexing. The performance benefit here is fewer page splits where SQL Server has to copy rows from one index page to another to make room for an inserted row because there is room for growth built into the index.
Update Statistics is used to force a recalculation of query optimization statistics for a table or indexed view. Query optimization statistics are automatically recomputed, but in some cases, a query may benefit from updating those statistics more frequently.
Beware though that re-computing the query statistics causes queries to be recompiled. This may or may not negate all performance gains you might have achieved by calling update statistics. In fact, it could have a negative impact on performance depending on the characteristics of the system. A correlated sub-query is a nested query that is linked to the outer query. For instance, say I wanted to find all the employees who have not entered their time for the week.
Notice that the inner query relates to the outer query on the employee ID, thus making it a correlated sub-query. The inner query will be evaluated once per outer query row. This is a generic question often asked by many interviewers. Explain what are the different SQL Server Versions you have worked on, what kind of administration of those instances has been done by you. Your role and responsibilities carried out in your earlier projects would be of significance to the potential employer.
This is the answer that lets the interviewer know how suitable are you for the position to which you are being interviewed. If you have worked only some version be honest in saying that, remember, no one would be working on all versions, it varies from individual to individual.
More on these Indexes later. In a clustered index, the leaf level pages are the actual data pages of the table. When a clustered index is created on a table, the data pages are arranged accordingly based on the clustered index key. There can only be one Clustered index on a table. In a Non-Clustered index, the leaf level pages do not contain data pages instead it contains pointers to the data pages. There can multiple non-clustered indexes on a single table. There are quite a lot of changes and enhancements in SQL Server A few of them are listed here:.
Peer-to peer Replication 3. Database Mirroring. I would recommend reading this blog of mine which explains the differences between these 4 features. The job activity monitor displays the current status of all the jobs on the instance. Choose a particular job that failed, right-click, and choose view history from the drop-down menu.
The execution history of the job is displayed and you may choose the execution time if the job failed multiple times during the same day. There would information such as the time it took to execute that Job and details about the error that occurred. A Database can contain a maximum of 32, files. There are Primarily 2 types of data files Primary data file and Secondary data file s 3. There can be only one Primary data file and multiple secondary data files as long as the total of files is less than 32, files.
By default, the fill factor value is set to 0. The easiest way to find and change the default fill factor value is from Management Studio, right-click the SQL Server, and choose properties. In the Server Properties, choose Database Settings, you should see the default fill factor value in the top section. You can change to the desired value there and click OK to save the changes. System databases are the default databases that are installed when the SQL Server is installed. It is highly recommended that these databases are not modified or altered for the smooth functioning of the SQL System.
A user database is a database that we create to store data and start working with the data. There are 3 recovery models available for a database. Full, Bulk-Logged, and Simple are the three recovery models available. Primarily, the recovery model is chosen keeping in view the amount of data loss one can afford. If one expects to have minimal or no data loss, choosing the Full recovery model is a good choice.
Depending on the recovery model of a database, the behavior of the database log file changes. I would recommend you read more material on log backups and log file behavior and so on to understand in depth.
Replication is a feature in SQL Server that helps us publish database objects and data and copy replicate it to one or more destinations. It is often considered as one of the High-Availability options. One of the advantages of Replication is that it can be configured on databases that are in a simple recovery model. There are basically 3 types of replication: Snapshot, Transactional and Merge Replication.
For example, Snapshot Replication is useful only when the data inside the tables does not change frequently and the amount of data is not too large, such as a monthly summary table or a product list table, etc. Transactional Replication would useful when maintaining a copy of a transactional table such as sales order tables etc.
The 3 main components in Replication are Publisher, Distributor, and Subscriber. The publisher is the data source of a publication. The distributor is responsible for distributing the database objects to one or more destinations. Replication can be configured in any topology depending on keeping in view of the complexity and the workload of the entire Replication. It can be any of the following:. That is, in SQL Server , the Installation process itself installs on all of the nodes be it 2 nodes or 3 nodes.
In or above this has changed, we would need to install separately on all the nodes. An Active-Passive cluster is a failover cluster configured in a way that only one cluster node is active at any given time.
An Active-Active cluster is a failover cluster configured in a way that both the cluster nodes are active at any given point in time. That is, one Instance of SQL Server is running on each of the nodes always; when one of the nodes has a failure, both the Instances run on the only one node until the failed node is brought up after fixing the issue that caused the node failure. The instance is then failed over back to its designated node. High-Performance Mode is to ensure that the Principal database runs faster, by not waiting for the Mirrored database to commit the transactions.
There is a slight chance of data loss and also the Mirrored database can be lagging behind in terms of being up to date with the Principal database if there is a heavy load on the Mirrored Server.
Yes, it is possible to have various configurations in a Replication environment. Drop database deletes the database along with the physical files, it is not possible to bring back the database unless you have a backup of the database. When you take a database offline, the database is not available for users, it is not deleted physically, it can be brought back online. Row compression and Page Compression.
How can it be changed? Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages. A non clustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a non clustered index does not consist of the data pages. Instead, the leaf nodes contain index rows. Kana Sensitivity — When Japanese kana characters Hiragana and Katakana are treated differently, it is called Kana sensitive.
Ans: Yes! Adding CPUs can occur physically by adding new hardware, logically by online hardware partitioning, or virtually through a virtualization layer. Requires hardware that supports hot-add CPU. How can we check whether the port number is connecting or not on a Server? What is the port numbers used for SQL Server services?
What are the different types of backups? Answer: The SQL server offers 4 types of backups to suit the need of the administrator. Complete backup :The complete back up is just zipping the content of the entire database in terms of the different tables and procedures etc. This back up can server as an independent entity that can be restored in different systems with just the base SQL server installed. Transaction log backup: This is the mechanism of backing up the transaction logs that have been maintained in the server.
There are a lot of opportunities from many reputed companies in the world. What purpose does the Model Database Server? The model database , as its name implies, serves as the model or template for all databases created on the same instance.
Download PDF 1. What are the two authentication modes in SQL Server? There are two authentication modes — Windows Mode Mixed Mode Modes can be changed by selecting the tools menu of SQL Server configuration properties and choose security page. This is mainly used to capture and save data about each event of a file or a table for analysis. What is recursive stored procedure?
You are at accurate place. SVR Technologies give SQL Server training online at your registered time, you can choose classes where, when, and how it accommodates you the best.
Я не видела смысла впутывать моего спутника, да и самой впутываться в дела, связанные с полицией. Беккер рассеянно кивнул, стараясь осмыслить этот жестокий поворот судьбы. Она отдала это чертово кольцо. - Я пыталась помочь умирающему, - объясняла Росио. - Но сам он, похоже, этого не. Он… это кольцо… он совал его нам в лицо, тыкал своими изуродованными пальцами. Он все протягивал к нам руку - чтобы мы взяли кольцо.
В Третьем узле виднелось голубоватое сияние: терминалы по-прежнему работали; они обеспечивали функционирование ТРАНСТЕКСТА, поэтому на них поступало аварийное питание. Сьюзан просунула в щель ногу в туфле Феррагамо и усилила нажим. Дверь подалась. Стратмор сменил положение. Вцепившись в левую створку, он тянул ее на себя, Сьюзан толкала правую створку в противоположном направлении. Через некоторое время им с огромным трудом удалось расширить щель до одного фута.
Люди, знающие толк в компьютерах, пришли в неистовство. Фонд электронных границ, воспользовавшись вспыхнувшим скандалом, поносил конгресс за проявленную наивность и назвал АНБ величайшей угрозой свободному миру со времен Гитлера. Новый стандарт шифрования приказал долго жить. Никому не показалось удивительным, что два дня спустя АНБ приняло Грега Хейла на работу. Стратмор решил, что лучше взять его к себе и заставить трудиться на благо АНБ, чем позволить противодействовать агентству извне. Стратмор мужественно перенес разразившийся скандал, горячо защищая свои действия перед конгрессом.
Но когда ТРАНСТЕКСТ расшифровал эти потоки информации, аналитики тут же увидели в них синхронизированный через Интернет отсчет времени. Устройства были обнаружены и удалены за целых три часа до намеченного срока взрыва. Сьюзан знала, что без ТРАНСТЕКСТА агентство беспомощно перед современным электронным терроризмом. Она взглянула на работающий монитор.
S…U…Z…A…N И в то же мгновение дверца лифта открылась. ГЛАВА 108 Лифт Стратмора начал стремительно спускаться. В кабине Сьюзан жадно вдохнула свежий прохладный воздух и, почувствовав головокружение, прижалась к стенке лифта. Вскоре спуск закончился, переключились какие-то шестеренки, и лифт снова начал движение, на этот раз горизонтальное. Сьюзан чувствовала, как кабина набирает скорость, двигаясь в сторону главного здания АНБ.
УМБРА, что было высшим уровнем секретности в стране. Никогда еще государственные секреты США не были так хорошо защищены. В этой недоступной для посторонних базе данных хранились чертежи ультрасовременного оружия, списки подлежащих охране свидетелей, данные полевых агентов, подробные предложения по разработке тайных операций. Перечень этой бесценной информации был нескончаем.
Ну, на самом деле. Все было совсем не. - Да вы не стесняйтесь, сеньор.
Он протягивал свою изуродованную руку… пытаясь что-то сообщить.
What is BCP? When would you use it? How to implement one-to-one, one-to-many and many-to-many relationships while designing tables? Explain primary key in Sql Server? Explain foreign key in Sql Server? What are the difference between “Where” and “Having” clause in Sql Server? What are Magic Tables in SQL Server?GigГ M. 10.05.2021 at 05:40
Curriculum foundations principles and issues 7th edition pdf directing the story pdf free