Database Administrator Interview Questions
Database administrators evaluate database software purchases and supervise the modifications done to existing database software. They maintain the integrity and performance of company databases and ensure the data is stored securely.
Database administrators secure company data through controlled access. To do this, they create databases with certain security clearance, depending on the role of the user within the company.
Database administrator responsibilities may include:
- Monitoring user access and security
- Writing database documentation
- Performing capacity planning
- Mapping out conceptual architecture designs for databases
- Assigning user roles according to needs and security clearance
Databases are the platform on which businesses operate. In order to maintain an effective database, a skilled database administrators will:
- Possess an eye for detail in order to monitor user access
- Think conceptually to effectively manage the database
- Follow security procedures to protect sensitive information
- Communicate clearly with staff to explain the importance of data policy
- Maintain a solid work ethic
An entry-level position is possible to obtain without a degree as long as the candidate possesses general IT skills. However, a bachelor’s degree in computer science or mathematics is preferred. Additionally, preference is given to candidates with prior IT experience, especially experience with managing databases.
If you’re getting ready to interview for a position as a database administrator, you can prepare by researching the company as much as possible. Learn about the 9 things you should research before an interview.
Salaries for database administrator range between $86K and $143K with the median being $114K.
Factors impacting the salary you receive as a database administrator include:
- Degrees (bachelor's, master's, PhD or equivalent)
- Years of experience
- Reporting structure (seniority of the manager you report to, number of direct reports)
- Level of performance - exceeding expectations
DATABASE ADMINISTRATOR INTERVIEW QUESTIONS
Question: What are the different SQL server versions you have worked on?
Explanation: This is an example of an opening or general question. The interviewer will ask this type of question early in the interview to begin to explore your background, get you talking, and learn more about you so they can ask additional questions.
Example: “I have direct experience working in SQL Server 7, SQL Server 2000, 2005, and 2008. My most recent experience has been with 2008. I found it relatively easy to transition from earlier versions of SQL to the more recent ones.”
Question: What were some of the new features released in SQL Server 2005 compared to 2000?
Explanation: The interviewer asked you a follow-up question based on your answer to the previous question. This is typical when you provide brief answers to general or technical questions. You should be prepared for follow-up questions to any information you provide to the interviewer.
Example: “There’ve been quite a few changes implemented in SQL Server 2005. Some of the more significant ones include database partitioning, dynamic management views, a resource database, SQL server integration services, and support for analysis services on a failover cluster. Of course, there are others if you’re interested in me describing them.”
Question: Can you describe the differences between a system database and a user database?
Explanation: This is an operational question which the interviewer is using to begin to qualify you as a database administrator. You can anticipate that most of the questions will be either operational or technical. In order to prepare for this, you should reread the job description to understand the specific duties you are expected to perform and the knowledge they require you to have. Review this information before the interview so it will be fresh in your mind.
Example: "System databases are the default databases that are part of the installation for the SQL Server. These include the Master, MSDB, TempDB, and Model. Normally these databases are not modified or customized as doing so may impact the functions of the SQL System. A user database, on the other hand, is a database that is created to store data and manipulate the user data.
Question: What is the purpose of the Model database?
Explanation: Again, the interviewer is following up on your previous answer to delve deeper into your qualifications and make sure you fully understand the information you are providing them. The best way to answer a follow-up question is to address it straightforwardly and concisely.
Example: “The model database serves as the template for all databases created in the SQL System. If the initial model database is modified, all subsequent databases created in the systems will reflect the changes. However, databases created earlier will not. Additionally, the TEMPDB is created from the model every time SQL Server starts up.”
Question: How do you trace the transaction traffic accessing a SQL Server?
Explanation: This is another operational question in which the interviewer is attempting to learn how you manage an SQL server and the related databases. Operational questions indicate that the interviewer is looking for an answer that describes a process rather than a definition of a term. Make sure you step them through the process you use to perform this function.
Example: to trace traffic, I use the SQL Server utility known as the SQL profiler. Usually, I filter the traces to narrow down the transactions that are captured to the ones that I am interested in and thereby reduce the overhead for the trace. The trace files can then be searched, saved, and replayed to help with troubleshooting and to optimize the server.
Question: What types of data replication are supported in SQL server?
Explanation: This is a technical question. The difference between a technical question and an operational question is that a technical question addresses a specific piece of knowledge or a skill you need to perform the job. Operational questions address the processes you use while performing the job. Technical questions should be answered by first providing the information the interviewer is requesting and then giving an example of how it is used in the job for which you’re interviewing.
Example: “There are three types of replication supported in an SQL server. These include Snapshot, Merge, and Transaction. The first of these creates a snapshot of the data at a specific point in time, as the name implies, to deliver to the clients. Merge replication uses a snapshot to seed the replication. Changes on both sides of the publication are tracked so the subscriber can synchronize with the publisher when connected. A typical use for this type of replication is in a client and server scenario. The clients update their copies of the data until they're reconnected with the server when all the data is merged. The transaction replication also begins with a snapshot, but the changes are tracked as transactions and merged with the central database as they occur.”
Question: What is an SQL agent, and how would you use it?
Explanation: This is another technical question that also has an operational component to it. You are first asked to identify the term and then describe how you would use it to perform your job. You should always answer an interviewer’s questions by providing the information they specifically asked for. You can always embellish it but never provide less than the initial request.
Example: “An SQL agent is the job scheduling function in an SQL server. You can use it to schedule jobs to run at a set time or when a specific event occurs. You can also execute jobs on demand. An SQL agent is most often used to schedule administrative jobs such as backups and restores.”
Question: What happens when the database encounters a checkpoint?
Explanation: Here is another technical question in which the interviewer is exploring your qualifications. As you progress through the interview, these technical questions will become more difficult. This is a good sign because it indicates the interviewer is gaining confidence in your qualifications and digging deeper. If you encounter a question you can’t answer at some point, simply state that and then describe how you would go about finding the information you need.
Example: “Checkpoints can be scheduled or manually executed. They cause the transaction log to be curtailed up to the beginning of the oldest open transaction. While storing completed transactions in the cache helps boost the performance of an SQL server, the cache needs to be flushed and moved to permanent storage to prevent data loss in the case of a failure. It also prevents the transaction log from getting too big and consuming too many resources.”
Question: What are DBCC statements, and can you name the different types?
Explanation: This is another technical question where you are being asked to define a term. You should be recognizing these by now and know how to answer them.
Example: “DBCC stands for Database Console Commands. There are four types of DBCCs. They include Maintenance, Informational, Validation, and Miscellaneous. Maintenance commands, as you may guess, are commands that enable the database administrator to perform maintenance on the database. Informational commands provide feedback regarding the database. Validation commands include commands that validate the database such as CHECKDB. Finally, miscellaneous commands are everything else and include statements like DBCC HELP.”
Question: What is the purpose of using the update statistics command?
Explanation: This is another technical question seeking an explanation of a specific command used by database administrators. If you choose to, you can provide additional information to any technical question. Just make sure you understand the interviewer’s level of technology so they understand the information you are providing them. Going too deeply into a topic is just as bad as not providing enough detail.
Example: “The update statistics command is used to force a recalculation of query optimization statistics for a table or indexed view. Although query optimization statistics are automatically recomputed frequently, a query may benefit from updating those statistics. You need to keep in mind that re-computing the query statistics causes the queries to be recompiled which may negate all the performance gains you achieved by calling update statistics.”
ADDITIONAL DATABASE ADMINISTRATOR INTERVIEW QUESTIONS
How would you improve our 15-hour database backup?
How would you handle a difference of opinion between you and a senior technical member?
If there was a database incident, what steps would you take to resolve the issue?
What role should a manager play for a database administrator?
What is a trigger in a database?
Can you name the five different database objects?