By: Jeremy Kadlec | Comments (2) | Related: More > Professional Development Interview Questions DBA
Problem
At our organization many folks wear a variety of hats, because we have a small and talented team. Although we have a SQL Server DBA, our Network Administrator also takes care of some of the SQL Server related tasks in our environment. We are looking to grow our team and we are interested in finding out what a reasonable set of questions would be for Network Administrator candidates that also state that they can support SQL Server. Do you have any suggestions?
Solution
Trying to assess any technical person's skill set can be difficult. An interview process with appropriate questions for your environment and the correct skill set are key. In some respects understanding what a candidate knows and does not know, could be equally beneficial. What is meant by that is you want to know what knowledge someone has on day one and what you will need to teach them overtime. As such, here are a baseline set of questions with the answers intentionally hidden. Once you have formulated your answer go ahead and highlight the answer to see how you have done. Good luck!
Backup and Restore
- Question 1 - What are all of the backup options and their associated value?
- Full - Online operation to backup all objects and data in a single database
- Differential - Backup all extents with data changes since the last full backup
- Transaction log - Backup all transaction in the database transaction log since the last transaction log backup
- File - Backup of a single file to be included with the backup when a full backup is not possible due to the overall database size
- File group - Backup of a single file group to be included with the backup when a full backup is not possible due to the overall database size
- Cold backup - Offline file system backup of the databases
- Third party tools - A variety of third party tools are available to perform the operations above in addition to enterprise management, advanced features, etc.
- Additional information:
- Automating Transaction Log Backups for All SQL Server Databases
- Differential Database Backups for SQL Server
- Simple script to backup all SQL Server databases
- MSSQLTips Category: Backup and Recovery
- MSSQLTips Product Directory: Backup
- Additional information:
- Question 2 - What high availability options are available with SQL Server and what sort of functionality do they provide?
- Backup and Restore - Data centric solution with the ability to perform full, differential, transaction log, file or file group backups which can be restored in a manual manner or as the business requires i.e. automated.
- Additional information:
- Log Shipping - Data centric solution which is based on an automated process to perform backup and restore operations at a database level from a primary to secondary SQL Server instance to keep the data in sync.
- Additional information: SQL Server Log Shipping
- Clustering - Hardware centric solution based on multiple nodes to provide redundancy typically at an instance or server level.
- Additional information:
- Getting started with SQL Server clustering
- MSSQLTips Category: Clustering
- Additional information:
- Replication - Data centric solution to logically move data from one database to another either at a transactional, merge, snapshot, peer to peer, etc level which is performed by SQL Server Agent Jobs.
- Additional information:
- MSSQLTips Category: Replication
- Additional information:
- Database Mirroring - Data centric solution introduced in SQL Server 2005 based on end points between two databases to keep the data in sync.
- Additional information:
- Digging into Database Mirroring in SQL Server 2005
- MSSQLTips Category: Database Mirroring
- Additional information:
- Third party tools - Various vendors in the SQL Server community have tools with additional features.
- Additional information: SQL Server High Availability Products
- Backup and Restore - Data centric solution with the ability to perform full, differential, transaction log, file or file group backups which can be restored in a manual manner or as the business requires i.e. automated.
SQL Server Integration Services
- Question 1 - What are the different options to execute an SSIS Package?
- Management Studio
- Command line
- Visual Studio
- SQL Server Agent Job
- Question 2 - What is the dtutil and name some of the functionality it provides?
- Utility shipped with SQL Server 2005 Integration Services to copy, rename, move, delete and\or verify an SSIS package
- Additional information - Import, Export, Copy and Delete SQL Server Integration Services Packages SSIS
- Utility shipped with SQL Server 2005 Integration Services to copy, rename, move, delete and\or verify an SSIS package
Troubleshooting
- Question 1 - What are some of the tools that you use to troubleshoot a SQL Server issue?
- SQL Server Error Log
- SQL Server Agent Error Log
- Windows Application Event Log
- SQL Server Integration Services Logs
- Custom Application\Process Logs
- Question 2 - How do you troubleshoot a SQL Server performance problem?
- Review management software for the hardware, SAN, NAS, tape library, etc. for any hardware related failures
- Review System Monitor for CPU, Memory, IO, etc issues
- Coordinate with the DBAs on tools like Profiler, DMVs, etc.
Security
- Question 1 - As a Network Administrator, which components of SQL Server do you want to secure?
- File system
- File shares
- Backups
- Windows service accounts and associated rights
- Local administrator rights
- Windows Security log
- SQL Server 2008 audit logs
- Third party audit logs
- Additional information:
- MSSQLTips Category: Security
- Additional information:
- Question 2 - As a Network Administrator, how can you help to prevent a SQL Injection attack?
- Review firewall and web server logs for potential threats
- Prevent particular strings from passing through the firewall
- Prevent IP traffic from particular web sites
- Implement technology to scan your public facing web sites for potential issues
- Additional information: Recover from a SQL Injection Attack on SQL Server
Hardware
- Question 1 - In general, how would you design the drive layout for a SQL Server?
- Setup RAID arrays for the following sub systems:
- Windows and System Databases (Master, Model, MSDB TempDB)
- User Defined Databases
- Database File groups
- Primary
- Indexes
- Data - Read\Write
- Data - Read-only
- Data - Specific tables
- Database File groups
- Database Transaction Logs
- Backups
- Full Text Catalogs
- Batch Processing
- Additional information: Hard Drive Configurations for SQL Server
- Setup RAID arrays for the following sub systems:
- Question 2 - What are some of the hardware advances that can benefit SQL Server?
- 64 bit architectures
- Multi-core CPUs
- Solid state disks
- Hot swappable memory
- Virtualization
- Additional information - Hardware 101 for SQL Server DBAs
Next Steps
- To the interviewers:
- As you prepare for the interview, be sure to ask questions pertinent to your environment. The more you ask questions related to your environment the higher probability the candidate will be able to hit the ground running and benefit the organization.
- To the interviewees:
- As you prepare for the interview, be sure to understand what the organization needs and how you can be of assistance.
About the author
Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor's degree from SSU and master's from UMBC.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
