When I wrote my most recent free eBook, Brad’s Sure Guide to SQL Server Maintenance Plans, some of the DBAs I know asked me why I wrote it. After all, “real” DBAs don’t use the Maintenance Plan Wizard/Designer built into SQL Server 2005/2008 Management Studio (SSMS) to maintain their databases, they write Transact-SQL or PowerShell scripts instead. The reason I wrote the book is because there are many DBAs, many more than you think, who use this tool.
In a recent survey on my website (www.bradmcgehee.com) I conducted the following poll:
I have recently updated and revised my DBA Best Practices Checklist, which is hosted on www.Simple-Talk.com. The goal of the list is not to cover every aspect of SQL Server administration, but to hit the highlights. In addition, not every item in the checklist will apply to every SQL Server instance, under every circumstance. So like any list of SQL Server best practices, don’t forget that they may or may not apply to your environment.
If you would like to add your own items to the checklist, or to provide other feedback on the checklist, please do so here, and I will try to include your feedback the next time I update the list.
Note: This is an in-depth article that exceeds 5,000 words, and provides a case-study of how a maintenance plan could be implemented on a single, plain vanilla, SQL Server instance.
As I have mentioned in previous parts of this article series (part 1, part 2), I act as a consultant DBA for the SQLServerCentral.com (SSC) and Simple-Talk.com (ST) backend databases, and I don’t perform any of the day-to-day tasks. But one of the things I was asked to do was to implement a database maintenance plan for the new, two-node, active/active cluster that has recently gone live. In this third part of this series, I am going to talk about how I created the maintenance plan, and why I made the choices I did.
I am currently in the early stages of writing an outline for a new book on indexing, targeted towards novice DBAs. While this topic has been done to death in hundreds of articles, presentations, and books, I think there is still room for a book that makes the topic easy to read and easy to understand for beginners. The focus on the book would be on the essential (not esoteric) indexing skills that DBAs need to perform their job on a day-to-day basis.
I would really appreciate your advice and feedback on what such a book should include. For example:
- What part(s) of indexing do you find very easy to understand?
- What part(s) of indexing do you find the most difficult to understand?
- What topics do you think I should include in the book that would be of the most help to you?
- What topics do you think I should not include in the book because they are not helpful for the typical DBA?
- Can you provide me URL’s of articles on indexing that you think are particularly good, or particularly bad?
- What’s the best book you have ever read on indexing? What’s the worst book? And why?
If you have any feedback on any of the above topics (you don’t have to answer all of the above question), please post them below. The more feedback I get, the better I will be able to write a book that will best fit the needs for novice DBAs. Once the book is done, it would become freely available as an eBook, just as all my recent books have been.
My new eBook, Brad’ Sure Guide to SQL Server Maintenance Plans is now available as a free, 269 page PDF eBook. The book is designed for part-time or novice DBAs who want to learn how to properly create Maintenance Plans using the tools that come with SQL Server Management Studio (SSMS) in SQL Server 2005/2008.
These two tools include:
- Maintenance Plan Wizard – a Wizard that steps the user through the process of setting up basic Maintenance Plans, with limited options.
- Maintenance Plan Designer – a drag-and-drop GUI interface in SSMS that facilitates the design and creation of more flexible, customizable maintenance plans.
Unfortunately, neither tool is especially easy to use or well documented. However, with the guidance I provide in this book, they can become powerful tools in helping the novice 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. I also tell you when not to use these tools, as there are many cases when it is more appropriate to use T-SQL or PowerShell scripts to perform database maintenance.
If you get a chance to read the book, please post your feedback here. While I spent a lot of time trying to include as much content as I could, I realize that I did not cover every possible way to use these tools, nor cover how they should be used under every possible SQL Server environment.
A paperback version of the book should become available at Amazon.com within the 60 days.
PS: If you haven’t read my blog on A Time and Place for the SQL Server Maintenance Plan Wizard, please do so. It explains why I decided to write this book.
When you create a SQL Server Maintenance Plan using the Maintenance Plan Wizard or the Maintenance Plan Designer, the SQL Server native backup command is used to perform full, differential, and transaction log backups. But what if you want to use backup compression to reduce the size of your backups and the amount of time it takes to perform them? Or what if you want to encrypt your backups to protect your organization’s data, or make the transfer of backup files across networks fault-tolerant? If you have the SQL Server 2008 Enterprise Edition, backup compression is built in, but if you have any other version or edition of SQL Server, or if you want encryption or network resilience, then you will need a third-party backup program, such as SQL Backup from Red Gate Software.
The Maintenance Plan Wizard and the Maintenance Plan Designer don’t have the ability to automatically include third-party backup software as part of a Maintenance Plan. Fortunately, there is an easy way around this, and in this article I will show you how you can use SQL Backup within a Maintenance Plan.