How to Prepare for a SQL Server DBA Interview and Questions

Kevin Kline

Published On: October 13, 2021

Categories: 0

So, you have an interview lined up for a sweet new gig as a SQL Server database administrator (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.” However, you can count on at least two major components of your interview – a technical component and a non-technical component, often focusing on soft skills.

Technical Screening DBA Interview Questions

The technical screening interview almost always comes first. Why? The technical screen “filters” out candidates who may not have the technical skills needed for the role and thereby ensures the hiring company doesn’t invest too much time in candidates who would fail to meet the technical demands of the job. 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’s certainly no guarantee of that. At the same time, DBAs have a well-documented set of essential skills they must master. So, there are some areas you can brush up on to make sure you’re ready for a productive conversation:

Recovery (Backup/Restore)

  • This is the top priority job for any DBA. You should know the basics of backups, such as recovery modes and the differences between full, differential, and transaction log backups.
  • Make sure you know the business goals behind decisions about Recovery Time Objective (RTO) and Recovery Point Objective (RPO).
  • Think outside the box here. Be ready to talk about more than the default options. Think about enterprise tooling, VM failovers, 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.

High Availability

  • Be ready to cover current options like Availability Groups.
  • Be ready to support additional use cases like log shipping and
  • Understand availability options in Azure and AWS – think beyond how they work to consider the cost of various options.

Performance Monitoring and Tuning

  • Consider approaches to continuous SQL Server performance monitoring and how you troubleshoot using performance metrics. Think about getting ahead by performing proactive performance analysis and how you might prevent downtime.
  • Be ready to discuss tradeoffs regarding different performance telemetry, such as Extended Events versus Performance Monitor Counters, and other tracing options.
  • Approach the tuning conversation by preparing for some other interview topics like indexing, partitioning, memory optimization, query plan analysis, and statistics and how you can use dynamic management views (DMVs) to query important performance information.

SQL Server Everywhere

  • Where are 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?

T-SQL

  • This is how you tell SQL Server what to do, and you’ll undoubtedly need to demonstrate your knowledge of it
  • Data manipulation
    • CRUD type operations
  • Data definition
    • Designing and defining database schemas, tables, views, and other database objects
  • Data control
    • Granting and revoking permissions
  • Transaction control
    • Explicitly declaring transactions and committing or rolling them back
  • So much more…
    • Make sure you know how to read the basics of a query execution plan and the handful of operators that raise a yellow or red flag
    • 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

Connectivity

  • Will you know how to recognize a connection timeout versus 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.

Storage

  • Where should database files and transaction log files go?
  • How many of each should you have? What size? What growth settings?
  • What happens when they fill up?
  • So much more…
    • Storage is much more complicated than most people think on the surface, so be ready to cover the basics and some advanced topics – like VLFs

Scale

  • Are you ready to manage 100-plus SQL Server instances by yourself? What SQL Server monitoring 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 standardized new instances?
  • Brushed up on PowerShell lately?

The Non-Technical DBA Interview Questions

Once you get past the technical interview, you’ll normally be asked to meet with a wider array of staff at the hiring company. Some companies may introduce you only to the hiring manager, but others will introduce you to other members of the DBA team or even hold a panel interview. Since you’ve already been screened for technical skills, any technical question you get at this phase of the process is almost certainly designed to see how you approach problems, the kind of logic you might apply, and whether and how you might seek help when necessary. It’s also this phase of the hiring process that those interviewing you are interested in seeing how well you would fit in with the rest of the team.

Organizational Culture and Teamwork

Be ready to discuss your experiences supporting operations across departments. Have examples ready where your interpersonal skills in working with software developers, business analysts, executives, vendors, and external customers were key. Have stories ready to talk about your experiences with cross-functional teams and how you juggled multiple priorities.

DataOps

Ready or not, DevOps and the Agile methodology have come to the world of data. It’s called DataOps. The high value associated with reaching business decisions based on data is increasingly driving DataOps requirements. Luckily, DBAs have been automating things since before automating things was cool, so replacing “automation” in your vocabulary with “DataOps.” That’s half the battle. The other half has you learning about the basics of data science and machine learning (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.

Self-Improvement

Many organizations recognize constant learning as an important part of an IT career. Your interviewers want to make sure you’re not in a technology rut or aren’t curious about what’s next on the constant drumbeat of new product releases. Be ready to talk about things like:

  • What user groups or conferences do you know of or attend?
  • What blogs do you follow?
  • Read a good book lately?

Be prepared to discuss how, when, and why you learn new things.

Problem Solving and Adding Value

How effective are you at documenting, researching, and resolving problems with little to no help? Do you have a step-by-step process? (You should!) Incidents occurring off-hours with no one else around will sometimes require you to rely only on yourself to get back on track. You also want to demonstrate you’ll be a force multiplier rather than a potential drag on your team. Are you a good writer? That’s a great skill for a DBA. Do you take good meeting notes? Also worth extra points.

Summary

What I’ve covered here is nowhere close to the full scope of what you might encounter. But based on my experiences both as a job candidate and as a hiring manager, I hope it helps in some small way. Truly, the best advice I can give is to be confident in your experience, be authentic, and be candid. In the same way, take some time to review your accomplishments and technical skills. You should be able to point out key points in your career you’re proud of and which support the narrative that you’re a highly competent and professional IT team member. Also, remember interviews are a two-way conversation. You should be interviewing them as much as they are interviewing you. So make sure to have your own questions ready when you’re given the opportunity to ask them.

Have a great interview!

Kevin (@kekline) serves as Principal Program Manager at SentryOne. He is a founder and former president of PASS and the author of popular IT books like SQL in a Nutshell. Kevin is a renowned database expert, software industry veteran, Microsoft SQL Server MVP, and long-time blogger at SentryOne. As a noted leader in the SQL Server community, Kevin blogs about Microsoft Data Platform features and best practices, SQL Server trends, and professional development for data professionals.


Comments

Fighting Downtime and Slowdowns