SQL Server DBA Interview Questions and Topics
So, you’ve got an interview lined up for a sweet new gig as a SQL Server DBA. What interview questions will you be asked? How can you make sure you ace the interview? What will make you stand out from the other candidates?
There are no concrete answers, because… “it depends.” This resource will help you prepare for a SQL Server DBA interview in 2020.
There will be a technical screen. I can’t tell you what you’ll be asked or how. I hope you’ll have an interview experience that tries to measure your potential more than your memorization skills, but there is certainly no guarantee of that.
At the same time, there are some areas you can brush up on to make sure you’re ready for a productive conversation:
- Think outside the box here. Be ready to talk about more than the default options. Think about enterprise tooling and SAN snapshots, for example.
- Brush up on syntax, but if someone expects you to have every command option memorized, you might consider failing the interview to be a blessing—unless, of course, the entire role depends primarily on continuity and recovery.
- Consider the newer landscape. For example, you know Azure SQL DB handles backups for you. Can you explain how you get a restore done?
- Be ready to cover newer options like Availability Groups.
- Be ready to support additional use cases like log shipping and replication.
- Understand availability options in Azure and AWS—think beyond how they work to consider the cost of various options.
- Consider approaches to continuous monitoring as well as how you troubleshoot using performance metrics. Think about getting ahead by performing proactive performance analysis and how you might prevent downtime vs reacting to it.
- Be ready to discuss trade offs regarding different tracing options.
- Approach tuning by preparing for some other key interview topics like indexing, partitioning, memory optimization, query plan analysis, and statistics.
Where are all the places SQL Server can live? Are you ready to speak intelligently about each one?
- Installed on hardware
- Installed on VMs
- Installed on IaaS in the cloud
- PaaS on Azure
- PaaS on Amazon
- On Linux
- In Containers
How would you plan to migrate one option from the above list to another?
This is how you tell SQL Server what to do, and you will undoubtedly need to demonstrate your knowledge of it.
- Data manipulation
- CRUD type operations
- Data definition
- Designing and defining schema
- Data control
- Granting and revoking permissions
- Transaction control
- Enlisting in transactions and committing or rolling them back
- So much more…
- T-SQL is a big topic, and this could go anywhere.
- The best preparation tool is experience. Make sure you have plenty, even if it has to come from tutorials.
- Will you know how to recognize a connection timeout vs. a client-side command timeout?
- How can you make sure connections are over a secure protocol?
- What are firewall and authentication considerations for SQL Server PaaS in the cloud?
- You’ll be expected to help developers, analysts, and non-humans like applications and DevOps pipelines get and stay connected. Be prepared to talk about it.
- Where should files and transaction logs go?
- How many of each should you have?
- How should you size them?
- How should they grow?
- What happens when they fill up?
- So much more…
- Storage is much more complicated than a lot of people think on the surface, so be ready to cover the basics as well as some advanced topics—like VLFs.
- Are you ready to manage 100+ SQL Server instances by yourself? What tools will you or have you used to help?
- How will maintenance impact performance when databases exceed hundreds of Terabytes in size?
- How quickly can you roll out new instances?
- Brushed up on PowerShell lately?
Be ready to discuss your experiences supporting operations across departments. Interpersonal skills in working with software developers, business analysts, executives, vendors, and external customers will be key. Have some stories ready to tell about your experiences with cross-functional teams.
Ready or not—DevOps has come to the world of data. It’s called DataOps.
The high value associated with reaching business decisions based on data will increasingly drive DataOps requirements. Luckily, DBAs have been automating things since before automating things was cool, so replacing “automation” in your vocabulary with “DataOps” is half the battle. The other half will have you learning all about data science, AI, and ML, so this topic is a win all around. You may not be ready to dive into the deep end with Agile yet but be prepared to speak the language.
Be prepared to discuss how, when, and why you learn new things.
- What user groups or conferences do you know of or attend?
- What blogs do you follow?
- Read a good book lately?
How effective are you at documenting, researching, and resolving problems with little to no help? Incidents that catch you off-hours with no one else around will sometimes require you to rely only on #1 to get back on track. You also want to demonstrate that you’ll be a force multiplier rather than a potential drag on your team.
What I’ve covered here is nowhere close to the full scope of what you might encounter, but I hope it helps in some small way. Truly, the best advice I can offer is to be confident in your experience, be authentic, and be candid. At the same time, take some time to review your accomplishments and technical skills.
Remember that interviews are two-way affairs, and you should be interviewing them as much as they are interviewing you. Make sure to have your server DBA interview questions ready when you’re given the opportunity to ask them. Complete research on the company and role to make sure it's what you want. If it is, complete more research to demonstrate your high level of interest.
Have a great interview!