SQL Interview Questions For SQL DBA

In the last couple of jobs that I applied for I was subjected to technical SQL interview, by people that were not technical SQL people.

This is not ideal, and I would hope that if you are interviewing a SQL DBA for a role, particularly a senior one that you find someone with the technical knowledge and background to be able to really grasp if the candidate knows what they are talking about.

Additionally at a previous role I was asked to prepare some questions for my replacement (I was leaving). I tried to make them as hands on as possible.

Instead of the normal “What are three types of index” I created them in a way where they would have to describe how to do something.

That way the interviewer could get a sense of whether they actually had hands on experience – unlike someone that had just googled the most common SQL questions and learned the answers.

The problem with this though was the interviewer let them take the questions away and answer them in their own time, of course the answers came back perfect – but they could have just googled them.

From these experiences I wanted to create some good basic questions to get an idea of hands on experience – however make sure you are present and the answers are not able to be taken away and researched.

Feel free to move these around and add more along a similar vein in areas you are wanting focus on.

SQL DBA Interview Questions (SQL 2008 Onwards – Enterprise version of SQL)

Basic Backup and Restore Skills

1./ Describe how you would take a basic backup of a database to a network drive, talk me through how you would do it via SSMS and how you would make sure the backup file was compressed.

2./ Walk me through now restoring that database onto another server

3./ What kinds of Backup recovery options are available and what would you recommend for my server (Think of a role of a SQL server in your organization, let them know what the DR requirements are and see if they can advise you appropriately)

DR knowledge and skills

1./ Provide a scenario and requirement and ask for a recommendation of whether to use Logshipping, Replication, clustering or mirroring/high availbility groups.

2./ Once they have given you a recommendation – ask how they would go about actually setting that up – task by task.

T-SQL Knowledge

1./ Provide 5 real life SQL problems and get them to write out the answer – not ones that are found on Google, get someone in your organization to create them in varying levels of difficulty with number 1 being very simple and number 5 quite difficult.

Performance Troubleshooting

1./ Describe a problem scenario where a SQL Server is performing poorly and ask how they go about troubleshooting it. There are many answers to this type of question, it is more around the way they think and a practical approach to troubleshooting.

2./ Provide them with an image of an execution plan with some issues for example a recommended index and some table scans, ask them to explain what they would be looking for and why.

3./ Ask them how they would go about troubleshooting a deadlock problem on a server and what kinds of situations could cause a deadlock.

SSRS

1./ Describe how you would go replacing/deploying a report file on your SSRS server that one of your developers has updated.

2./ If your users had a report that kept timing out – what would you do to troubleshoot and resolve? (Again many answers, based on their experience you should get a practical approach and recommendations)

SSIS

1./ Describe how you would create a simple SSIS package to move data from 3 tables on server a to server b

2./ How would you deploy it?

3./ How would you ensure that it ran each day at 12PM and notified someone if it failed?

SSAS

Note: I have not had much experience in this area so if you are looking for someone with analysis services experience make sure you ask them actual process questions along the line of the previous items.

DMV’s

1./ Ask them what they would do in regards to a health check on a SQL Server instance in regards to indexing and general performance and what if any DMVs they would use in that process.

Data files

1./ Your SQL Server is running out of space for one of the databases which has one main data file. Your Server admin has presented a new disk to the sql instance, describe how you would go about getting the data file moved from the old disk to the new.

Service Packs

1./ Describe the process to service pack a 3 node SQL Cluster?

2./ How would you service pack a mirrored setup with two servers in the mirroring partnership?

Other Areas

1./ Security – ask for thier feedback on the best ways to secure sql servers and the objects within them.

2./ Triggers, stored procedures, functions – you can create some basic tests around these if you like.

There are many other areas you can ask questions on, what I have tried to do here is highlight a way of questioning, rather than the actual questions yourself.

I am keen for feedback though, have I missed any crucial areas?

What can I add to make this a comprehensive list that is of value to those attemption to employ experienced DBA’s?

Rob StGeorge
Senior SQL Server Database Administrator residing in Auckland, NZ

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.