Dear Junior DBA…

Congratulations on getting your first SQL Server DBA job!

Presumably you have a tech background or education, and have been through some basic training in SQL Server administration.  I also assume you intended to be a DBA and want to be really good at it so you can advance your career and get mad raises/bonuses.

With any luck at all, you are in an environment where there is at least one other DBA there that knows more than you do.  Ideally a Senior that is really into mentoring that can guide your path.

If not, here are some of the basic things that you may already know how to do in SQL Server Management Studio, but don’t really know the inner workings or the T-SQL to make them happen.

Also, almost everything you can do in current SSMS versions can be scripted.  Look for the script button and click it after you make all of your selections so you can start learning the code behind the GUI.  In time you’ll prefer going straight to the Query Window for some functions of your job.

The list I want my juniors to get intimately familiar with:

• Backup and Restore…beyond the Maintenance Plans
• Creating/Deleting databases
• Creating Logins and Users (and knowing the difference)
• Creating and maintaining indexes
• Other database maintenance items
• Basics of whatever HA/DR may be in place (Clustering, Log Shipping, Availability Groups)
• Basic performance monitoring via SQL Trace or Extended Events in modern SQL versions

All of these can be setup/monitored in the GUI…so make sure you know all of the options there, and then start working on knowing them deeper. Start with Books Online/MSDN and go from there.

More on each list item:

Backup and Restore – I want you to be able to regurgitate exactly what the difference is between Full, Differential and Transaction Log backups.  I want you to know when you would use each.  You need to know how to restore to a point in time, to another server or as a new database name. Backwards and forwards…this is DBA 101 and the first question I ask if I interview you.  You need to be able to throw down the basic Backup Database syntax on the fly.  Also, recovery models…memorize and understand them (including Bulk-Logged)

Creating/Deleting databases – There are many ways to create a database…SSMS, T-SQL, Restore from a backup, deploy from a .dacpac/.bacpac, etc.  Know how to do each, when you would use each, what options are available and how they affect behavior.  Know about filegroups and best practices for laying out your .mdf, .ldf and .ndf files…for your environment.   Know what to do before you delete a database.  Does the requester mean Delete, Detach or just take offline?  What’s the difference?  Know your RPO and RTO by heart.

Creating Logins and Users (and knowing the difference) – I’m amazed at the number of experienced people and non-sql people that use the terms interchangeably.  Know the difference. Also learn what server and database roles are. These days, the more you know about the various pieces of the security model in SQL Server the stronger a DBA you are.

Creating and maintaining indexes – If your databases are only a few GB, you can almost ignore indexes…but don’t.  Know and be able to explain the difference between clustered and non-clustered indexes.  Understand how to determine what indexes are needed and which existing ones are not. What is an included column?  What is a covering index?  Memorize the ins and outs of Reorganize vs Rebuild and Online vs. Offline maintenance…this matters.  A surprising number of application developers will rely heavily on you for help in this area.

Other database maintenance items – DBCC CheckDB and its impact on tempdb.  How to respond to CheckDB errors. Statistics…what are they, do they matter and how do you handle them?  When?