Resource: Brad's Sure Guide to SQL Server Maintenance Plans

On a few occasions, I've been asked to fill in as a temporary or emergency DBA for periods of time, usually during or after a company transition of some kind. While I am by no means an expert DBA, I can keep things running smoothly for a few months, thanks in part to this little ebook - Brad's Sure Guide to SQL Server Maintenance Plans.

Neither of the environments where I assumed this role had Maintenance Plans when I started - at least beyond the Full Backups. I'm not sure if this is because both were small companies with relatively small environments (< 200 databases), or if the previous admins just never got around to setting them up. In any case, after doing some reading online, I was fairly convinced that implementing Maintenance Plans was a best practice that we should have been doing for a while - I just wasn't quite sure how to do it.

I distinctly remember thinking this book was going to be exactly what I needed after reading the following two segments: Brad's creds on page xiii (obviously very experienced with the subject matter), and his introduction for the book on page 14:

"In many cases, organizations have SQL Server instances that are maintained by a part-time DBA, or an "accidental DBA," who may be a network administrator, developer, accountant, or even an office clerk. In the worst cases, nobody is looking after the health of the SQL Servers. ... with the guidance I hope to provide in this book, [SQL Maintenance Plans] can become powerful tools in helping the "accidental DBA" to perform critical maintenance tasks, and so help to ensure SQL Server's performance and availability. In addition to learning how to use these tools you will, along the way, pick up a lot of good, general advice on SQL Server database maintenance."

Sweet! Sounded like he had written it just for me!

So why should you take time to read this book and follow the advice within?  Why are Maintenance Plans important to your SQL Server environment? I'll let Brad explain:

"The goal of implementing [database maintenance plans] is to help prevent [all] kinds of problems ... If implemented correctly, [maintenance plans] can help ensure that a SQL Server's databases perform adequately and, if there should be a problem, provide the necessary backups to minimize the loss of any data. Another benefit of implementing a database maintenance plan is that it helps to prevent, or to catch early, many different kinds of database-related problems. By being proactive with a good maintenance plan, time spent troubleshooting problems after the fact is often reduced."

The first 4 chapters (about 80 pages) give an excellent overview of the basics of Maintenance Plans and how they work. Subsequent chapters go on to explain why you should or might want to consider implementing a certain type of Maintenance Plan, and how to do it. The "why" portion of each chapter was very valuable to me as a non-expert, and really helped me narrow down what I needed to implement (or avoid) to make improvements in my environments. The Database Mail configuration portion alone (Chapter 2) is a great process walk through. I had to do this for both of the environments I was working with, and probably would have spent a bunch of time on Google trying to piece together the correct steps from a plethora of articles.

One of the things I really liked about the book is that Brad takes the time to specifically explain all of the settings - with screenshots - and why or why not to use them. As you read it, you'll understand why this is valuable - not all of the fields mean what you think they'd mean. As an example, on page 181 while discussing the Cleanup Task, he states:

"The last option is to specify the age of the files beyond which they will be removed, using the Delete files based on the age of the file at task run time option ... This is another tricky option. First of all, don't deselect this option. If you do, the Maintenance Plan Wizard will assume that you want to delete all instances of the specified type of file, no matter how old they are. Of course, this is not what you will want to do. You will only want to delete files that are over a particular age."

I also really appreciated how the book is organized. It's really easy to go back and reference details on a specific Task. You can get to the information you need quickly and easily. Because of this, it was very easy to for me to read through his book and set up all of the Maintenance Plans I needed for both environments. It's not the kind of book you'd probably sit down and read cover to cover, and the structure reflects that well - it's a quick reference resource that you can easily go back to whenever you need to set up a new Plan.

There were just a couple of slight issues I ran into while following the examples:

• He states early in the book on page 16 that all of his screenshots (of which there are many) are from SQL Server 2008. As I was working with SQL Server 2005 in both cases, there were some differences for me in what he was describing. Overall though, almost everything worked as he explained it.
• Brad recommends setting up just a few Maintenance Plans with multiple Tasks, but I ended up having to create separate Plans for most major/daily tasks. This was because my maintenance windows were not large enough where I could pack multiple steps into a single Plan. Again, his instructions worked beautifully, it just took me a little longer to create the 7-8 Plans I needed.

In short, I highly recommend this resource, particularly if you find yourself in a situation like I did.You can get the book from Brad McGehee's site using the link I provided in the first paragraph. CodeProject