SQL Administrator Interview Preparation Guide
Download PDF

SQL Administrator based Frequently Asked Questions in various SQL Administrator job interviews by interviewer. These professional questions are here to ensures that you offer a perfect answers posed to you. So get preparation for your new job hunting

84 SQL Administrator Questions and Answers:

1 :: Tell me why would you use SQL Agent?

SQL Agent is the job scheduling mechanism in SQL Server. 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.

2 :: Please explain why would you call Update Statistics?

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.

3 :: How to start SQL Server in different modes?

Single User Mode (-m): sqlcmd –m –d master –S PAXT3DEVSQL11 –c –U sa –P *******
DAC (-A): sqlcmd –A –d master –S PAXT3DEVSQL11 –c –U sa –P *******

4 :: Tell me what is SQL Server VSS Writer?

The SQL Writer Service provides added functionality for backup and restore of SQL Server through the Volume Shadow Copy Service framework. When running, Database Engine locks and has exclusive access to the data files. When the SQL Writer Service is not running, backup programs running in Windows do not have access to the data files, and backups must be performed using SQL Server backup.

Use the SQL Writer Service to permit Windows backup programs to copy SQL Server data files while SQL Server is running. It must be running when the Volume Shadow Copy Service (VSS) application requests a backup or restore. To configure the service, use the Microsoft Windows Services applet. The SQL Writer Service installs on all operating systems.

5 :: Do you know what is SQL Server service and its importance?

SQL Server service is core of SQL Server instance. It runs the Database Engine and executes the client requests related to data processing. If this service is not running, no users can connect to the any of the database, hence users will not be able to fetch, insert, update or delete the data.

6 :: Do you know what is a primary key?

A primary key is usually used as the index for a particular table — a value that the table can depend upon to be a reliable unique value in every row. When trying to pull data for a particular row, the primary key will normally be used to pull that information, usually a numeric value. For example, if you are trying to pull up data on a specific person, and that database is using their unencrypted ssn as the primary key (naughty), then that could be used in the query to identify that particular row since there could be other people present in the database with that specific name or other identifying characteristics.

7 :: Tell us do you have experience working with Hadoop?

Big data technology is another rapidly growing area. Hadoop helps organizations work with massive data sets by splitting them into smaller sets and then consolidating the results. A willingness to learn Hadoop or existing experience may help your company use your data more efficiently. What to look for:

☛ Subject matter knowledge
☛ Overall experience
☛ Desire to learn about trends and new solutions

8 :: Tell us how do you open a Cluster Administrator?

From Start -> Run and type CluAdmin (case insensitive) and the Cluster Administrator console is displayed OR you can also go to Start -> All Programs -> Administrative Tools -> Cluster Administrator.

9 :: Tell me what is Transparent Data Encryption?

Introduced in SQL Server 2008 Transparent Data Encryption (TDE) is a mechanism through which you can protect the SQL Server Database files from unauthorized access through encryption. Also, TDE can protect the database backups of the instance on which TDE was setup.

10 :: Tell us what the different components of Replication and what is their use?

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. The subscriber is the destination where the publisher's data is copied/replicated.