Welcome back to our series, Server Review Essentials for Accidental and Junior DBAs, where we’ve been taking an in-depth look at how to get ready to perform regular SQL server reviews for your company or clients. So far, we’ve covered how to get your working environment set up (Part 1 | Part 2) setting up biographical information about your environments, collecting server information, building a maintenance job list, and backup frequency list, as well as providing sample biographies and server reports.
Now it’s time to move forward and put all your preparation to use and perform regular server checks…almost. We’ve got one more round of prep work to do before your environment is completely ready to go.
Verify RPO
Before you go any deeper into this process, now is the time to ensure all of the servers you’ve been entrusted with are meeting the stated Recovery Point Objectives. My advice at this point is to not worry over the Recovery Time Objective until you’ve ensured you’re meeting RPO. Your company or clients won’t care how fast their data can be recovered if they can’t recover the data they actually need.
Use the backup frequency list you created in an earlier step and take the time to look through each server to see if the backup schedule meets the stated RPO in terms of FULL and LOG backup frequency (and DIFFs if relevant). We’re not confirming the existence of the backups yet, we’ll do that in a later step. For now, just make sure the backup jobs are scheduled properly to meet your RPO requirements.
If you find that your backup jobs are not scheduled properly, get with your senior DBA or the relevant person in authority and correct the backup job schedules accordingly.
Create Your Own DBA Tools Database
Though not necessary, I’d suggest creating your own DBA Tools database on each of the servers for which you are responsible. Most companies and clients won’t have any problem adding such a database since it is lightweight, provides tools beneficial to your server health, and creates a sandbox in which those tools can reside separately from the data.
This is a common method for us here at Dallas DBAs. Whenever we’re given the responsibility for administrating a new server or environment, one of the very first things we do is evaluate the current state of the server, implement some best practices, and create a new database like this to install our preferred scripts.
We’re not going to cover the steps to creating a new database for your tools. If you don’t know how to do that, check out the Microsoft Docs to learn how to make a new database. Having said that, we feel it’s always best to put the database in SIMPLE recovery model and make sure the database owner is “sa”. Otherwise, you can pretty much use the defaults in creating your new DBA Tools database.
What scripts do we install and use regularly? In the following posts in this series we’ll showcase some of the scripts we use, but below you’ll find a list of scripts and stored procedures we like to implement on the servers we touch.
Keep in mind the purpose of this post is not to explain how to install or use any of the scripts and tools listed below, only to show you some of the tools we like to use.
- Ola Hallengren’s SQL Server Maintenance Solution is our preferred tool for all database backups, integrity checks, and index optimization. His scripts are far superior to the built-in Maintenance Plans in SQL Server.
- Adam Machanic’s sp_WhoIsActive.
- Brent Ozar’s First Responder Kit, which includes fantastic tools such at sp_Blitz, sp_BlitzCache, sp_BlitzIndex, and more.
Get Ready for Proactivity
Now your servers are primed for regular reviews, which means you will often know about potential issues long before they become catastrophic failures. Having done all of this work doesn’t guarantee your servers will be problem-free, but it does mean your servers will be less likely to suffer common issues and you’ll be better equipped to handle issues as they arise.
If you’d like to know more about SQL Server Maintenance, check out Kevin’s Getting Started with SQL Server Maintenance course at Pluralsight.
Follow me on Twitter at @SQLandMTB, and if you’re into mountain bikes come over and check out my site NTX Trails.
