Bradley Beard

Practical Maintenance Plans in SQL Server

Automation for the DBA

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

Distributed to the book trade worldwide by Springer Science+Business Media New York, 233 Spring Street, 6th Floor, New York, NY 10013. Phone 1-800-SPRINGER, fax (201) 348-4505, e-mail [email protected], or visit www.springer.com. Apress Media, LLC is a California LLC and the sole member (owner) is Springer Science + Business Media Finance Inc (SSBM Finance Inc). SSBM Finance Inc is a Delaware corporation.

For you, my dearest Jessica

Introduction

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.

What Is the Scope of this Book?

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.

Who Is this Book Written For?

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.

Acknowledgments

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.

Contents

  1. Chapter 1:​ Introduction to Maintenance Plans
    1. Before We Start
    2. What Is a Maintenance Plan?​
    3. Maintenance Plan Wizard Task Options
    4. Maintenance Plan Design Surface Options
    5. Summary
  2. Chapter 2:​ Backing Up a Database
    1. Recovery Models
      1. Full
      2. Bulk Logged
      3. Simple
    2. Backup Types
      1. Full Backups
      2. Differential Backups
      3. Transaction Log Backups
    3. Setting Up the Maintenance Plan
      1. Full Backup Configuration
      2. Differential Backup Configuration
      3. Transaction Log Backup Configuration
      4. Configuring the Jobs
    4. Summary
  3. Chapter 3:​ Checking Database Integrity
    1. What Is Database Integrity?​
    2. Practical Application of Database Integrity Principles
    3. Setting Up the Maintenance Plan
    4. Summary
  4. Chapter 4:​ Executing SQL Server Agent Jobs
    1. E-mail from the Database
      1. Configuring E-mail
      2. Sending a Test E-mail
      3. Enabling the Mail Profile
    2. SQL Agent Job Creation
      1. Creating an Example Table
      2. Writing a Query for the Job
      3. Creating a SQL Agent Job
      4. SQL Server Agent Options
      5. Steps Tab
      6. Schedules Tab
      7. Alerts Tab
      8. Notifications
      9. Targets
    3. Gmail’s SMTP
      1. Setting Up a Profile
      2. Testing E-mail Configuration
      3. Allowing Access to Google’s SMTP Server
      4. Enabling POP E-mail
    4. Summary
  5. Chapter 5:​ Cleaning Up SQL Server Agent History
    1. Setting Up the Maintenance Plan
    2. Choosing Tasks
    3. Define What to Clean
    4. Review
    5. Summary
  6. Chapter 6:​ Cleaning Up Maintenance Files
    1. Separation of History from Maintenance
    2. Setting Up the Maintenance Plan
    3. Backups Cleanup
      1. Deleting Backup Files
      2. Deleting a Specific File
      3. Searching and Deleting Based on Extension
    4. Logs Cleanup
    5. Text Files Cleanup
    6. Summary
  7. Chapter 7:​ Rebuilding Indexes
    1. Indexes Explained
      1. Beginning Indexes
      2. B-Tree Structures
    2. Rebuilding vs.​ Reorganizing
    3. Setting Up the Maintenance Plan
    4. Summary
  8. Chapter 8:​ Reorganizing Indexes
    1. Reorganizing vs.​ Rebuilding
    2. Setting Up the Maintenance Plan
    3. Summary
  9. Chapter 9:​ Shrinking the Database and Files
    1. Disk Usage Reporting
    2. Disk Space Considerations
    3. The Transaction Log
    4. Setting Up the Maintenance Plan
    5. Summary
  10. Chapter 10:​ Updating Object Statistics
    1. Distribution Statistics Explained
    2. Setting Up the Maintenance Task
    3. Summary
  11. Chapter 11:​ Executing T-SQL Statements
    1. Setting Up the Maintenance Plan
    2. Implementing the Maintenance Plan
    3. Executing the Maintenance Plan
    4. Summary
  12. Chapter 12:​ Notifying Database Operators
    1. Setting Up the Maintenance Plan
    2. Creating an Operator Profile
    3. Summary
  13. Chapter 13:​ Tying It All Together
    1. Checking Your Environment
    2. Ordering of the Maintenance Tasks
    3. Determining Complexity of the Maintenance Plan
    4. Planning the Maintenance Plan
      1. Scenario 1
      2. Scenario 2
    5. Creating the Maintenance Plan
      1. Editing the Jobs
      2. Finalizing the Jobs
      3. Saving the Changes
      4. Reviewing Your Schedule Needs
      5. Adding the Tasks to the Plan
    6. Full Backup Maintenance Activities
      1. Adding the Check Integrity Task
      2. Adding the Rebuild Index Task
      3. Adding the Shrink Database Task
      4. Adding the Update Statistics Task
      5. Adding the Cleanup History Task
      6. Adding the bak Files Task
      7. Adding the txt Files Task
      8. Adding the trn Files Task
    7. Differential Backup Maintenance Activities
      1. Adding the Check Integrity Task
      2. Adding the Reorganize Index Task
      3. Adding the Shrink Database Task
      4. Adding the Update Statistics Task
    8. Transaction Log Backup Maintenance Activities
      1. Adding the Check Integrity Task
      2. Adding the Reorganize Index Task
      3. Adding the Shrink Database Task
      4. Adding the Update Statistics Task
    9. Precedence Constraints
      1. Constraint Options
      2. Multiple Constraints
    10. Testing the Maintenance Plan
    11. Starting a Job from a T-SQL Script
    12. Summary
  14. Index

About the Author and About the Technical Reviewer

About the Author

A396023_1_En_BookFrontmatter_Figb_HTML.jpg

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] .

About the Technical Reviewer

A396023_1_En_BookFrontmatter_Figc_HTML.jpg

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).

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.222.94.153