SQL Server DBA Interview

The SQL Server DBA Interview Questions & Answers is for anyone who is applying for SQL Server Entry or Mid level Database Administrator, Database Developer or Involuntary SQL DBA. This is a comprehensive list of all possible SQL questions that might be asked during an Interview. I strongly recommend reading “50 database terminologies that every Developer should know” and SQL Interview Questions and Answers  before you begin this. The answers presented here are version independent unless other wise mentioned.. You can also do a search within our site for any questions that you need a detail information on.

DBA Responsibilities:

A Database Administrator aka DBA is an IT professional who is solely responsible for Installation, Configuration, Upgrading, Administration, Monitoring, Maintenance and Security of database in an organization. Below is the detail list of responsibility of DBA and these list may varies with companies.

1. Database Availability: The DBA needs to ensure the database is available 24X7 for mission critical application to support.
2. Database Backup: Backup of database is done and stored multiple copies at multiple locations everyday and DBA is responsible for making sure the daily backup are complete and the backup copies are good.
3. Database Restore: Restoring is a process of building a database from a backup copy.  During a catastrophes like hardware or disk failure, DBA are required to restore the database and make it available as soon as possible. Restoring is also a solid way of testing a backup.
4. Database Design: A good database design makes the application faster and run the database with less overhead. DBA are required to work with developers during their design phase to help design the right database.
5. Data Migration: Organizations have various need for moving date from once database instance to another. The most frequent request for DBA is to move data from development to test and from test to production of vice versa. 
6. Database Upgrade :  The never version of SQL Server is available in every couple years. These new versions are the bug fix of earlier version plus more features and DBA are required to upgrade with the latest version.
7. Database Patch: There is no software that is perfect and database is also a software and it isn't perfect. Therefore Database vendor release a patch to resolve a know bug or to tighten the security. DBA constantly monitors these pact and apply them to the database as needed.
8. Query Optimization: Query optimization is a process of making query run faster without adding extra overhead to the database. DBA should identify the worst performing query and should be able to advice the developer to re-write or design to perform it better.
9. Database Tuning: It is a ongoing process done by DBA to optimize the performance of a database. This requires the advanced knowledge on database, data and hardware.
10. Database Security: Security is a top priority of any company and database stores the most important data of any organization. DBA creates users with minimum privilege to perform his her job and stays on top of role and privileges.
11. Capacity Planning: Capacity planning accommodates the future growth of data and bandwidth of a company. The growth can be database users, app users or data itself. DBA works with hardware and system administrator to ensure they have enough disk space, memory processor and bandwidth to support the growth.
12. Database Monitoring: Monitory is done to ensure the database is running without a problem. Monitoring is done to find out the problem queries, jobs that are taking more I/O or memory.
13. And Much More...  A DBA is responsible for everything we listed here in a smaller companies where you are just the DBA whereas in bigger organization, you are responsible for one of few responsibilities. 

SQL Server Editions & Version:

Express:  Microsoft SQL Server Express is a version of SQL Server RDBMS which is absolutely free to download, distribute and use.  This version of SQL is target to small scale business, student, and developer. Express provides most of the features of the paid version of MS Server with some restrictions.

1. 10 GB Maximum Database Size.
2. No SQL Server Agent Service ( You can't schedule a job using Express edition)
3. Hardware utilization limits ( Single CPU and  1 GB of RAM)

Developer:  Microsoft SQL Server Developer is a version that lest developer build almost any kind of database application  using SQL Server. It's licensed for sue as development and test systems, not as a production server, and is and ideal choice for people who build and test applications. This is not free and costs around US $59.95 to download and install per license.

Developer Editions is licensed per developer at $50 therefore multiple developers can connect to single instance of the SQL server. This editions has all the features that Standard and Enterprise Editions but your are only allowed for development and testing only.

Standard: Microsoft SQL Server Standard Edition is an entry level database system for commercial use. This edition has everything that is included in Developer editions without any restrictions on users. This can use unto 64 GB of memory and 16 processor. It offers database high availability options not found with Express Edition like compression, fail over clustering, mirroring etc

Enterprise: Microsoft SQL Server Enterprise Edition is a high transaction, high availability database system for commercial use after Standard edition. This edition supports more then 524 petabytes and address 2 terabyte of memory. It has all the features that MS came with like advanced compression, and most of the DDL operations can be done online like Indexing, Schema Change etc.

Versions:  SQL Server 7.0,  SQL Server 2000, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012 and SQL Server 2014. There were several version of SQL Server prior to 7.0 and I don't think It is necessary to know them.

Q: Which Edition and Version of SQL Server do support?  If this is your first time applying for DBA position and do not have any prior industry experience, you should definitely be taking about the Express or Developer Edition and don't forget the version you practice your database administration work.Anyone who is looking for opportunity outside of your company should know the Version and Edition. Also be prepared to talk on why you are on those version and editions and any plan to upgrade soon. You may use "Select @@version" on your SSMS to find your current Edition and Version of your database.Your output will be something like this:

Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64)
Jun 28 2012 08:36:30
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.1  (Build 7601: Service Pack 1) (Hypervisor)

Q: Which TCP/IP port does SQL Server run on? It runs on port 1433. The port can be configured from Network Utility TCP/IP properties on SQL Server Configuration Manger.

System Databases:

In order for SQL Sever to provide a  database service, SQL server relies on SQL Server system. There are four system databases that are shipped with SQL Severs to functions and administer the database. The solid understanding of these system database helps you troubleshoot and issue and better administer the database. SA owns system database and never try changing the owner.

master:  Master database records all the system-level information for SQL Instance. The system-level records include logon accounts, endpoints, linked server and system configuration settings. System objects are no longer stored in the master database, they are stored in Resource Database. This system database records the existence of all other databases and the location of those database files and records the initialization information for SQL server. This is a heart of SQL Sever and know which databases are available. If this is corrupt, you will not see any other databases. This database purely control how SQL Server react.

SQL server can't start with out master database presence.  You are required to regularly backup the backup of master database and restore it when problem arises. Any operations that you normally do with user database are prohibited with system databases. This stores all the system views, procedures and functions commands that you use in SQL Server.

model: This database act as a model database for creating a user database in a SQL Instance. The model database must exist because this is used to create a tempdb database every time the server restarts. You are required to regularly backup the model database. Any operations that you normally do with user database are prohibited with system databases. Any user database created uses the same recovery model as the model database. Any objects you create on this model database gets created to user databases, same thing applies to the configuration and the settings.

msdb:  The msdb database also known as Microsoft database is used by SQL server Agent for scheduling alerts and jobs, SSIS package, database mail, and Service broker. This database also stores the history and metadata information on the  transactions that happens within SQL Instance. For example, SQL server automatically maintains a complete online backup and restore history within tables in msdb. This information includes the name of the party that performed the backup, the time of the backup and the devices or files where the backup is stored.

This database can grow out of control, therefore you are required to make backup and restore when needed. It uses simple recovery mode by default therefore experts recommend that you switch to full recovery model for msdb.

tempdb:  According to Microsoft, tempdb is a system database which is a global resource to all users connected to the SQL Server Instance and holds:

1. Temporary database objects both system and user database objects like global or local database objects like table, stored procedures or cursors.
2. Internal objects that are created by SQL Instances like work tables to store intermediate result for spooling or sorting.
3. Row versions that are generated by data modifications transactions in a database that uses read-committed using row version isolation or snapshot isolation transactions
4. Row versions that are generated by data modification transactions for features such an online Index operations, multiple active result sets and after trigger.

The operations within tempdb is minimally logged  which enables transactions to be rollback. Tempdb is re-created every time SQL Server is started or that the system always starts with a clean copy of the database. Nothing is stored permanently in tempdb  therefore you are not required to make a backup of tempdb.

Recovery Models:

Q: What is a Recovery model? Recovery models are designed to control transaction log maintenance. A recovery model is a database property that controls how transactions are logged in SQL Server, whether the transaction log requires (and allows) backing up, and what kinds of restore operations are available. Three recovery models exist: simple, full, and bulk-logged. Typically, a database uses the full recovery model or simple recovery model. A database can be switched to another recovery model at any time.

Simple: This recovery model does not backups the log. It reclaims the log space automatically which eliminating the need to manage the transactions log space. Operations which requires transactions log backups are not supported. There are some restrictions that comes with simple recover model. Changes since the most recent backup are unprotected and those changes most be re-done with the event of a disaster.  This recovery can only recover to the end of the backup and following operations are not supported.

1. Log Shipping
2. Always on or Database mirroring
3. Media recover without data loss
4. Point-in-time restores

Full: This recovery model requires log backups. No work is lost due to the lost or damaged data file. Can recover to an arbitrary point in time. Normally the work loss exposure is none. If the tail of the log is damaged, changes since the more recent log backup must be re-done. You can recover to a specific point in time, assuming that your backups are complete up to that point in time.

Bulk Logged: This recovery model also requires log backups. An adjunct of the full recovery model that permits high-performance bulk copy operations. Reduces log space by using minimum logging for most bulk operations. If the log is damaged or bulk-logged operations occurred since the most recent log backup, changes since that last backup must be done otherwise, no work is lost. You can recover to the end of any backup. Point-in-time recovery is not supported.

Backup Database

There are 3 main database backup options available.

Full Backup:  As the name says, this backup copies the entire copy of data from a database. Full backup brings a peace of mind, but at the same time, it is resource intensive to execute with huge database. You will also need bigger source to store which might be costly to both store and restore if you  are to run full backup daily.

Incremental Backup: Running full backup daily is an expensive operations. Incremental backup copies only data that got added or update after a full backup. This backup prevents the need to store duplicate copies of unchanged data. If you run full backup every Sunday after midnight then you will need to run increment backup rest of the week to capture all the changes that happened after a full backup. This is much faster which runs without much overhead to database and also requires less storage space. To restore data, you will need to restore full backup and all the incremental backups.

Differential backup: This backup copies all the file that have changed since the last full backup. The data restore from this backup is much faster compared to incremental backup. The only disadvantage is that the size of differential database grows bigger if you run this backup. You will need full backup and latest differential backup copy to restore.

SQL Server Backup Types:  SQL Server will let you take Full, Differential and Transaction log backup. You can run these backups using T-SQL or by using the wizard from SSMS

Full: A full backup contains all the data in a specific database or set of groups or files and also enough log to allow for recovering that data. It is the base for both Differential and Transaction log backup. Full backup is available on all three recovery models Simple, Bulk-Logged and Full.

Differential Backup: A differential backup depends on a Full backup. You have to have a full backup before you can create a differential backup. This backup copies all the files that have changed since the last full backup. This is must faster and requires less space compared to full backup. To restore you will need a full backup and the latest differential backup file.

Transaction Log Backup: Only a Full or Bulk-Logged recovery model will let you take the Transaction log backups. The full backup along with transaction log backup will let you do the Point In Time (PIT) recovery of the database. Transaction log backup truncates the transaction log after a backup which prevents log filling the disk space. Transaction log backup is not available on Simple recovery model but the transaction log files are under control. How does that happen? Transaction logs truncation occurs atomically after a checkpoint run.

Q: What is hot backup? Backup taken while the database is running. This will be your daily backup which you set up using maintenance plan.

Q: What is a cold backup? Database backup taken while the database is shutdown. This backup runs faster compared to hot backup. This provides a complete copy of the database which can be restored exactly. Is this options available in SQL Server?

Q: What is backup compress?  Backup compression is a new feature which Microsoft added on version 2008 and later. This feature compresses the backup files up to three times depending on the data types. Compressed files takes less disk space. Therefore, the time to restore the database is much faster compared to uncompressed data. It saves both recovery time and disk space. Of course, this great feature isn't free, it comes with a cost of additional CPU overhead therefore the database should be tuned correctly.

Q: How do you verify the backup you have taken are valid and not corrupt? There are two ways to verify the backup copies of a database.

1. Restore the backup files on test database.
2. Use of RESTORE VERIFYONLY  statement during backup

Q: What are some of the common post restore processes? After a successful restore of database you will need to perform:

1. Sync the Logins and Users
2. Verify database objects and data
3. Verify and fix privilege
4. Change database recovery model.

Q: What are some of the third party tools for backup MS SQL Server?  There are tons of  third party backup tools that are to efficiently manage enterprise databases backups:

1. Cloud Berry:  It provides fast and easy-to-use backup for SQL Server database to Amazon S3, Amazon Glacier, Windows Azure and over 15 other clouds.
2. SQL Safe Backup: This is a backup tool from Idera which is popular, efficient and robust backup solutions.
3. and more ....

The Idea is to make you familiar with tools other than what  MS SQL Server comes with and know why they are out there.

 Q: What is COPY ONLY backup option? A SQL Server backup option that is required to take a backup for special purpose without affecting the overall backup and restore procedures which is already in place. This options preserves the existing log archive point and therefore does not affect the sequence of regular backup. Failure to use this options will break the chain of log sequence which  then breaks the recovery process. 

INDEX:

Q:What is an Index? Index is a database object which contains an entry of each record that appears in the indexed column to provide faster access of data.  Indexes are used in a View or Table. Knowing the concept of  Index in detail  is very crucial to database administrators, database developers, and anyone who likes to work with database. The proper use of Index makes your query and database perform faster. But it might hurt your query performance and add overhead to database if the Index is not designed  correctly. 

Q: When do you add Index?  When you have one or more following scenario(s)

1. Frequently searched column
2. If you are selecting 10% or less data
3. If recommended by execution plan or SQL tuning adviser
4. Query with Join and Where Clause

Q: When do you Not add Index? When you have one or more following scenario(s)

1. Frequently updated or deleted column
2. When selecting more than 10% of data
3. When the database engine ignores the Index use

Clustered vs Non Clustered Index

Clustered: An index that sort and store the data row in the table or view on their key values.You can have one clustered index per table as the data rows sort in only one order. A table with clustered index sort the data based on the column where there is clustered index. The table with clustered indexed is also called clustered table. A table without a clustered index is a heap table because  it is not sorted.  A clustered index is created while creating a primary key or can be created later after the table creation.

Non Clustered: A regular Index which most database system comes with. The non clustered Index contains key values where each key value entry has a pointer to the data work that contains the key value. You can have multiple non clustered Index as you are not sorting data on that record like in clustered Index.

Both clustered and non-clustered can be composite or non composite Index.

Picking the right Index type: Picking the right Index is necessary to get optimum performance of a query. If your column is unique and there is a pattern of increasing or decreasing of data, then clustered Index is a way to go. For everything else you may use non clustered only if you truly need it. Let's consider you have an employee table that stores employee number along with their details. If you frequently search for employee detail with his employee number, a clustered Index on employee number is the way to go because it is unique and has a pattern. The decision on picking right Index should be done while the code in stage. You can add the index later but it might be hard or you may need to take the table offline which may require more work than you think.

Rebuild or Reorganize: Index needs a routine maintenance just like any other objects. Index Rebuild drops and re-creates an Index. This is a costly process that may impact the performance of a query while the rebuild is process. The Index stats gets updated with Rebuild and this maintenance option is preferred when there is 30% of more Index fragmentation.  Reorganize Index  is a light weight process which organizes the leaf of an Index. Since the Index is not  recreated, the stats are stall. This maintenance strategy is picked when the fragmentation is below 30%.

Q: What are the disadvantages of having Indexes? Index may improve the SELECT operation but impacts INSERT, UPDATE and DELETE operations. It requires frequent maintenance and become costly.

Q: Explain fill factor? The fill factor values determines the percentage of space on each leaf-level page to be filled with data. The fill factor 90 means that 10 percent of each leaf-level page is left empty, providing space for index expansion as data is added to the underlying table.

Work in Progress, Please check back for More >>>>>>>>>>>