Brad's Sure Guide to SQL Server Maintenance Plans [Kindle Edition]

Brad spends a lot of time talking about the Wizard, much of that time warning against it! An exhaustive haul through the various options that Microsoft provide for maintenance. Brad is wary of the wizard, in part because it could lead the unwary to ticking every box and thus doing unnecessary and even damaging stuff. Ticking Rebuild and Reorganise, for example - it just makes your plan take twice as long. If you Rebuild, then do an unnecessary Update Statistics, the update can screw up the statistics that the rebuild created and hit performance as well as wasting time.

I've never used the wizard myself - the SSIS-style drag and drop interface in the plan designer is so easy to use that I don't see the need. Brad also recommends using T-SQL or Powershell scripts for more complex maintenance. In fact he recommends this on average once per chapter, so it tends to get a little tedious.

To my mind, this leaves a lot of middle ground where the simplicity of the graphic maintenance plan designer does the job neatly and effectively. Want to take a backup? Just pick the backup task, spend a few moments telling it what to backup and where to, and then schedule it to run at a convenient time. Job done! Why write the code when the plan can do it for you?

Here's why - you can rebuild indexes, or reorganise indexes - but best practice is probably to read the fragmentation level of each one and then decide whether to rebuild, reorganise or leave alone. Especially on large databases, especially if you have a narrow maintenance window, you don't want to do an unnecessary rebuild. For this your best bet is a custom script.

But for most stuff the maintenance plan is fine. Brad reminds me of all the things I should be doing - like the history clean up task. Have I scheduled this? Probably not everywhere - note to self - go and check. And he explains very well the point of multiple sub-plans which I hadn't grasped before

This book is aimed at what the author calls "accidental database administrators". I certainly qualify in this classification. Having used SQL Server for many years and having been coerced into being responsibile for DB maintenance I wish the book had been available many years ago. The book contains very simple to following instructions (with diagrams) on the configuration tasks necessary to create very usable maintenance plans. The author is very clear about the dos and don'ts of using the tools.

The book starts with a very important discussion on why DB maintenance is important before explaining the two different approaches described in the book - those being the Maintenance Plan Wizard (MPW) and the Maintenance Plan Designer (MPD)

Chapter 3 starts with an introductory (simple) example of the use of the MPW which results in a very usable maintenance plan. This is followed by 12 chapters that give details of the various components that can be configured using the (MPW). Very few stones are left unturned related to the available options.

Chapter 16 then introduces the MPD. The MPD is graphical tool that enables maintenance plans to be created in a dynamic fashion. The final few chapters then cover the detail on the MPD including amending exists plans created by using the MPW.

This book is a very welcome aid to help the amateur become a more fully rounded professional. The explanations are clear the reading style is very easy. The clear guidelines on the pros and cons of each tool on top of the recommendations on things to avoid will save much time both in one's learning curve and on the eventually performance and effectiveness of created maintenance plans.

This book will enable any person responsible for data stored in a SQL Server database to ensure it is being managed to a required minimum with a small amount of effort and possibly limited experience. This is not a book long time DBAs who have scripts already in place to maintain their databases will get a lot out of, it's for the people who are becoming known as `Accidental DBAs'. Maybe you were closest to the server when the last DBA quit, or you are a system administrator with a new system based on SQL Server and you don't have a DBA in-house. Whichever way you have happened to find yourself looking after SQL Server databases, this book is for you.

It explains very clearly what a maintenance plan is designed to do, what it can and can't do and in some cases why you don't want it to do some things. Once the intent of each feature is established Brad then walks you through configuring the task options to your requirements and how it can be linked to or affects other tasks you need carried out on your databases. A clear format of what the task does, why you want it done (or not) and how to make it happen is followed in each chapter. Pitfalls and limitations of the various tasks are explained clearly and where there are options to avoid or work around these they are explained so that they can be implemented. The tasks are described with enough background information that someone unsure about SQL Server will clearly understand what the task is attempting to achieve and will then be able to decide whether that is something they want to apply to their systems.

While the majority of the book refers to the process followed by the Maintenance Plan Wizard Brad also explains how to make your own custom Maintenance Plan or alter existing plans with the Maintenance Plan Designer. Implementing schedules and operator notifications into your plan is explained so that a user can keep up to date with the status of any executions without having to read through the detailed reports along with using logic to control flow from one step to another within a plan and adding multiple copies of tasks in order to get the exact results needed.

If you have any databases in your remit and you are wondering how to look after them, or want a guide on how to make your existing maintenance plans more effective, then this book will be a valuable asset to you.

It could be as simple as reading this book for a couple of hours on Monday, Tuesday and Wednesday and then over a similar time on Thursday and Friday you could have your SQL databases fully covered by a set of maintenance plans to ensure your data is well maintained and backed up.