Admin::DBA Technical Interview

Survive A SQL Server DBA Technical Interview

by Jeffrey Yao, 2006/07/10

 

Introduction

How to make a successful interview is always an interesting topic. A successful interview, in and of itself, needs attention from many perspectives, including behavioral, psychological, technical and even dressing details,  But in this article, I will focus on the technical part of a SQL Server DBA interview, using my personal experiences in the last 10 years, from small companies to big ones such as Microsoft, and IBM. I will only discuss preparation for technical interviews, which are usually held by your project manager or senior technical peers.

The article is mainly for a DBA with a few years' experience and will discuss the three phases of an interview, i.e. Preparation, Presentation, and Post-cleanup, which I call 3P phases.

About DBA type

Knowing what type of DBA your potential employer is looking for will better guide you to the right direction for preparation. Generally, DBAs are categorized into three types (of course, there are lots of overlaps between each other)

First type: Operation DBA. The major responsibility for this type of DBA is to ensure the high-availability of a system, to monitor and keep the well-being of the system, and to do daily DBA routine work, such as adding logins, database backup / restore, on-going database maintenance (reindex / db size adjustments / file defragmentation etc), performance tuning, database implementation and other ad-hoc requirements.

Skill requirements: Familiar with various performance counters (both system and SQL Server related) and their implications, security / performance related T-SQL statements, including DBCC statements, how to create / grant login, network protocol configuration, backup / restore T-SQLs and sufficient knowledge about log-shipping, replication, disaster recovery methodology etc.

Second type: Development DBA. The major responsibility for this type is to translate user requirements into database model, which takes into consideration entity relationship, data integrity, business rule conformation, and efficiency for the whole system (mainly index/view design). The DBA is also involved in the following tasks:

• developing exemplary code templates that embed "best practice" in terms of security, performance, and maintenance consideration.
• examining / performance-tuning other developers' code
• final database deployment

Skill requirements: familiar with at least one data modeling tool (ERWin, Visio or PowerDesigner etc), understanding index architecture, experts knowledge about T-SQL and SQL Profile tool, sufficient knowledge about SQL Server best practices.

Third type: Hybrid  DBA, who are required to be capable of every respect of database administration, but may not necessarily be an expert on everything. (Actually, I do not think one can be an expert on everything about database administration).

Skill requirements: broad understanding of SQL Server system, and strong capability to tackle any issues with independent research.

Preparation

The best approach to prepare for an interview is to treat the everyday work / study as if you would be asked a related question next day in an interview. For example, when you are restoring a database, try to understand the related restore statements, i.e. "restore filelistonly ", "restore headeronly", "restore verifyonly", and when/how these statements are used. Never take anything for granted, just act as if you were a kid, who dare to ask any question, to yourself. The advantage of this approach is you learn more effectively while you are working, with no pressure but sense of achievement every time you learn something that you know you will give "perfect" answers in your next interview.

Another practical approach is to act if you will be an interviewer who will ask a candidate SQL Server questions next day. The benefit here, is that this will no doubt drive you to pay more attentions to details. According to my experience, it is much harder to ask excellent questions than to answer them, esp. if the questions are targeted towards senior DBA level. For example, assuming you are a project manager and are responsible to design an SQL Server exam paper, people who pass the exam paper will work with you as your DBA, and your DBA's technical skills will finally decide the fate of your project. In this scenario, how to make out the exam paper is definitely no small task to you as a PM.

My final suggested approach, though a little bit  luxury but  really effective, is to get a senior DBA to be your mentor, and do a few rehearsals before the interview. Working together with the mentor, you can better understand your weakness through frank and honest opinions, thus improve your weakness quickly and efficiently..

Presentation

Treat an interview as an opportunity to present your technical capability and your affections for your profession. For senior position hiring opportunity, we will often be asked some open question, such as "what is your database admin strategy to ensure the high availability of your system?", "how do you troubleshoot a slow system, which originally run very well?"  and "how are you going to solve our discussed problem ? " etc. There is no right or wrong answer to this type of question, but there is definitely good or bad answer here in terms of logic, soundness, achievability and also the most important part, i.e. your vision and passion and ambition.

For development-oriented DBAs, the presentation should be centered around how you can make the data-driven solutions to solve / benefit the company's projects. Here data model technique, application layer methodology, DMO, XML or other data-related terms should appear at high frequency in your language. The point here is to convince the interviewer that you can contribute a lot with the technology you have talked about.

For operation-oriented DBAs, the presentation needs to be about security, performance, high-availability, efficiency and scalability, Demonstration of your proficiency of various management tools, deep understanding of the SQL Server architecture and innovative ideas in managing SQL Server system will prove your value. Personally, I believe operation-oriented DBAs need to be very strong in T-SQL and better in one of Window scripts (VBScript or JScript) to take advantage of DMO (SMO in SQL2K5) because without programming, you can hardly make an automated administration system, and thus you can hardly have any innovation in managing multiple SQL Server systems. If you can do jobs of two or three regular DBAs work, an employer will no doubt see the great value you can make.

For hybrid-type DBAs, the presentation is more about your knowledge base in various fields, and your capability in solving problems by research and learning. My personal suggestion here is to publish some articles, or to get some certifications to prove your capability and the broadness of your interests.

Post-fix

In this stage, the target is to give the interviewer/s the final pitch about your value. Sometime, if you cannot answer the question in the interview, it is still worthwhile to answer it in your "post-interview" time. This at least demonstrates your integrity and your keen interests in the interview.

I remember in one interview I did not do very well in a T-SQL question (a very complex query, and I used up my allowed time), the interviewer gave me the answer finally. But after I reviewed it, I decided this is not the one I pursued, but I cannot think out of the query immediately at the time. So after interview when I arrived home, I worked on the query again, and finally got what I wanted. I later sent to my interviewer the result, which I guess impressed him, because I got the offer letter the next day.

Another time, I believe I answered very well in the interview. So after interview, I sent a thank-you email, in which I commented about the technical interview questions, stating which question is a good one and why, and which is not because of what.

Summary

This article aims to provide some high-level guidelines to survive SQL Server DBA technical interview. Of the three (3P) stages, I'd say Preparation is the foundation stone that gives you strength to go to Presentation stage, while Presentation stage is when you will shine, and the Post-fix stage is the last try to solidify your value into the impression of your interviewers.

References

http://www.computerweekly.com/Article42138.htm

http://www.dyessconsulting.com/E-Books/InterviewBook/home.html