Bradley Beard
Bradley Beard
Palm Bay, Florida, USA
Any source code or other supplementary material referenced by the author in this text is available to readers at www.apress.com . For detailed information about how to locate your book’s source code, go to www.apress.com/source-code/ .
ISBN 978-1-4842-1894-5
e-ISBN 978-1-4842-1895-2
DOI 10.1007/978-1-4842-1895-2
Library of Congress Control Number: 2016938532
© Bradley Beard 2016
Practical Maintenance Plans in SQL Server
Managing Director: Welmoed Spahr
Lead Editor: Jonathan Gennick
Development Editor: Douglas Pundick
Technical Reviewer: Mike McQuillan
Editorial Board: Steve Anglin, Pramila Balen, Louise Corrigan, Jim DeWolf, Jonathan Gennick, Robert Hutchinson, Celestin Suresh John, Michelle Lowman, James Markham, Susan McDermott, Matthew Moodie, Jeffrey Pepper, Douglas Pundick, Ben Renow-Clarke, Gwenan Spearing
Coordinating Editor: Jill Balzano
Copy Editor: Kim Burton-Weisman
Compositor: SPi Global
Indexer: SPi Global
Artist: SPi Global
Cover Designer: Anna Ishchenko
For information on translations, please e-mail [email protected] , or visit www.apress.com .
Apress and friends of ED books may be purchased in bulk for academic, corporate, or promotional use. eBook versions and licenses are also available for most titles. For more information, reference our Special Bulk Sales–eBook Licensing web page at www.apress.com/bulk-sales .
Standard Apress
Trademarked names, logos, and images may appear in this book. Rather than use a trademark symbol with every occurrence of a trademarked name, logo, or image we use the names, logos, and images only in an editorial fashion and to the benefit of the trademark owner, with no intention of infringement of the trademark. The use in this publication of trade names, trademarks, service marks, and similar terms, even if they are not identified as such, is not to be taken as an expression of opinion as to whether or not they are subject to proprietary rights.
While the advice and information in this book are believed to be true and accurate at the date of publication, neither the authors nor the editors nor the publisher can accept any legal responsibility for any errors or omissions that may be made. The publisher makes no warranty, express or implied, with respect to the material contained herein.
Printed on acid-free paper
For you, my dearest Jessica
Have you ever wondered about what keeps your database running smoothly? We all hope the database keeps running as well as it did as when it was first installed, but what really is happening under the covers? More importantly, can we control what is happening? As it turns out, we certainly can control these events, and they are extremely powerful tools that we can use to make sure that our databases stay in good shape.
This book will introduce you to the core concepts of creating a maintenance plan that will handle all of the tasks necessary to keep your database functioning at 100%. There are exercises that you can run in your own installation of SQL Server to ensure heightened data security and integrity by harnessing the power of SQL Server Agent. With the proper amount of foresight and planning, database administrators of any experience level will be confidently using the tools available in SQL Server to create extremely versatile maintenance plans in a short amount of time.
In the scope of this book, I am going to concentrate on using the SQL Server 2012 Management Studio interface as much as possible. I am not going to get into a lot of scripting by hand; it will be point-and-click as much as possible. There will be some areas where we will venture into the void, but I’ll keep that to a minimum. To be clear, I will be working with the Maintenance Plan Wizard, and not from the Maintenance Plan Design Surface. There will be times when I will focus directly on the design surface, but for the most part, we will work with the wizard. Most of what I detail and outline here is transferable between the two interfaces anyway, as you will soon see.
We are going to make a single maintenance plan that will automatically execute every day on a set schedule. I am going to show you how we can manipulate the database engine into performing the maintenance activities we choose on the schedule we choose. I am also going to show you how to expand on the concept of reporting and logging, from the database point of view, to keep us aware of any possible issues. In the end, we are going to have a maintenance plan that does everything we need it to do, period. This will give us an assistant, so to speak, that runs these menial although important tasks without our supervision. When I think of maintenance plans, I immediately think of the old Ron Popeil commercials with “set it… aaaaaaand forget it!” That’s exactly what we do here. We are going to take the time to set it up correctly, and examine the why and not just the how. We are then going to ensure that our reporting and logging is set up correctly, so that we are aware of any issues. After that, it will be smooth sailing and you can enjoy your newly augmented rock star DBA status.
This book was written for you, of course! It should be a refresher course for the majority of readers. My hope is that this book will augment what you already know, and perhaps shed some light on some concepts that you didn’t know. At a minimum, you should come away from this book with a clear understanding of what a maintenance plan is, how it benefits a database, how to structure it correctly, how it runs, and how to possibly make it run more efficiently. I would like to think that this isn’t the first you have read about maintenance plans, as there is a certain level of assumed knowledge about the interface and mechanics of SQL Server. If it is the first you’ve heard of it, that’s fine; I keep everything as simple as possible so that anyone can read along and understand… even my 12-year-old daughter! I try to keep things “light”, meaning that I tend to try and put some element of humor into my writing to keep things interesting, but I will cover some pretty heavy topics also, so sometimes it gets serious.
Chapter 1 provides introduction into the separate components of the maintenance plans, and concise examples of what you can do to make the maintenance plan work for you. Chapters 2 through 12 will detail each of the tasks available within the maintenance plan, and Chapter 13 will tie everything together so that you can create a complete maintenance plan based on your specific needs. Along the way, I will examine the multitude of rabbit holes that pop up, because there are actually quite a few little bits of interesting things that need to be discussed. In the end, you will have a complete set of tools and the knowledge to use those tools to achieve great things for your database.
Having said all that, let’s dive into this adventure! You can use any installation of SQL Server you would like, provided that you have SSIS installed. That implies any version after and including SQL Server 2005, and it must be Standard, Enterprise, or Business Intelligence versions. Although this book was specifically written with SQL Server 2012 in mind, I also ran everything through SQL Server 2014 and had absolutely no issues beyond slight interface differences.
First of all, a huge thanks to Jonathan Gennick for all the guidance and tips along the way.
A big thanks to my wife and kids for understanding.
Big, huge, gigantic thanks to my good friend and mentor John Wysocki, without whom I would not know half as much as I do about anything related to databases (or chemistry, or plumbing, or nuclear physics… seriously, he knows everything).
Thanks to my other database nerd friends, including Cam, Eric (phpfreak), Leisha, Kyle, Isabelle, and everyone else in the MCSA class, and the guru herself, Suzy Moore. You guys are awesome.
To my friends, brothers, and sisters. I told you I was writing a book and none of y’all believed me.
To my brother Brian and my niece Holly, thanks for being you… even when I was too dumb to appreciate it.
And finally, thank you, Lord, for second chances. I could never deserve your mercy and love.
Bradley Beard is a software engineer with more than 15 years’ experience writing dynamic, interactive web sites using ColdFusion and SQL Server. He graduated from Florida Institute of Technology in 2007 with a master of science in computer information systems, and studied for his undergraduate degrees in CIS and technology management at Herzing University. In 2013, he earned the MCSA: SQL Server 2012 certification from Microsoft, and in 2016, he earned the MCSE: Business Intelligence certification as well. His continual quest for learning has earned him shelves full of books at home and at work, most of which are about SQL Server, ColdFusion, or general web architectures or frameworks.
Bradley lives in Palm Bay, Florida, with his wife, Jessica, and children, Josh, Kaylee, Matthew, and Emma. He also apparently runs an animal shelter made up of his dogs, Lady and Bella, and cats, Spice, Simba, Mercury, and Dobby. In his free time, he enjoys fishing and spending time with his wife and kids.
Bradley is available for consultation and third-shift remote employment on ColdFusion and SQL Server by contacting [email protected] .
Mike McQuillan is a software and database specialist who lives with his wife and daughter in the United Kingdom. Mike is a polyglot programmer who began messing around with computers in the 1980s, first with an Atari 800XL and then a Sinclair Spectrum. He took up databases in the 1990s, and quickly fell in love with SQL. He’s been working with SQL Server since version 7 and he is an SQL Server MCSA.
When he’s not tinkering with computers, Mike and his family enjoy lengthy walks around Cheshire with the family pups, Dolly and Bertie (who keep his feet warm when he’s writing).
18.191.186.219