SQL Server: Maintenance Plans

Learn how to properly configure and control essential SQL Server database maintenance using Maintenance Plans. This course is applicable to anyone responsible for SQL Server databases, with any level of SQL Server experience.
Course info
Rating
(208)
Level
Beginner
Updated
Nov 22, 2013
Duration
2h 39m
Table of contents
Introduction
Approaches to Database Maintenance
Configuring SQL Server Agent Settings
Common Maintenance Tasks
Other Tasks
Creating Maintenance Plans
Maintenance Plan Challenges and Alternatives
Description
Course info
Rating
(208)
Level
Beginner
Updated
Nov 22, 2013
Duration
2h 39m
Description

Proper database maintenance is critical for making sure that database workloads perform as well as possible. One of the simplest ways to implement database maintenance is to use the built-in Maintenance Plan functionality to schedule the critical database maintenance tasks - backups, consistency checking, statistics maintenance and index maintenance. The course starts by explaining how to configure Database Mail, and Operator, and alerts, all within SQL Server Agent. It then describes all the commonly-required maintenance tasks and shows how to configure them, along with do's and don'ts, plus some of the less commonly used tasks. After that it shows how to pull these tasks together into Maintenance Plans using the Maintenance Plan Wizard, the Maintenance Plan Designer, and through Integration Services packages. Finally the course discusses some of the alternative methods of implementing database maintenance through freely-available scripts. This course is perfect for anyone who is responsible for SQL Server databases, with any level of experience, from SQL Server 2005 onward.

About the author
About the author

Jonathan Kehayias is a Principal Consultant with SQLskills. He is the youngest person ever to achieve the Microsoft Certified Master - SQL Server 2008 certification and has been a SQL Server MVP for many years.

More from the author
SQL Server: Change Data Capture
Intermediate
2h 10m
Mar 27, 2015
More courses by Jonathan Kehayias
Section Introduction Transcripts
Section Introduction Transcripts

Introduction
Hi this is Jonathan Kehayias from SQLskills. com and I'm recording this SQL Server Maintenance Plans course for Pluralsight. This is module 1, the introduction. So Microsoft really focused on positioning SQL Server as a leader in the relational database platform market. And SQL Server's low total costs of ownership and the ease of installation has really made it very popular for application data storage for small and large business needs. The result of this is that many businesses have what we refer to as accidental or involuntary DBAs whose primary task isn't necessarily managing SQL Server so a lot of times deploying databases becomes a set it and forget it type installation. At least until problems occur for small or medium businesses that don't have dedicated DBAs on staff for dealing with SQL Server. The truth is that regular maintenance tasks are necessary to avoid performance and availability issues from occurring with databases running on SQL Server and as a part of that you can implement and automate regular maintenance tasks very easily a number of different ways for SQL Server including the database maintenance plans feature that ships with the product out of the box.

Approaches to Database Maintenance
Hi, this is Jonathan Kehayias from SQLskills. com, and I am recording the SQL Server Maintenance Plans course for Pluralsight. This is module two, Approaches to Database Maintenance. So within SQL Server there are multiple approaches that we can use for performing our routine maintenance tasks. Each of these is going to have separate benefits and drawbacks associated with it. When we are selecting the best approach for our specific needs, you need to look at the way the application or database gets used, so that you can make the appropriate considerations for future supportability and reusability of your maintenance packages. Within this module we'll cover database maintenance plans, SSIS packages, using custom Transact-SQL scripts, and possibly even using PowerShell scripts, and each of the benefits and drawbacks that exist for each of these methods.

Configuring SQL Server Agent Settings
Hi, this is Jonathan Kehayias from SQLskills. com and I'm recording this SQL Server Maintenance Plans Course for Pluralsight. This is Module 3: Configuring SQL Server Agent Settings. So within SQL Server, the SQL Agent provides the ability to automate tasks that need to execute inside of the database or inside of the Windows operating system. And it does through jobs that will be configured that can contain one or more steps or one or more schedules associated with how they are supposed to execute automatically. Configuring the SQL Server Agent is an important task so that you can be certain that you will be able to monitor your job execution appropriately and that you will receive notification, should one of your maintenance jobs happen to fail. SQL Server Agent is not included in every version or edition of SQL Server, and a good example of one that does not include SQL Agent is SQL Express Edition. We will talk about how to configure maintenance in automated tasks for SQL Server Express Edition in Module 7 of this course. In this module, we will cover Configuring Database Mail, Configuring SQL Agent Mail settings so that it appropriately will use Database Mail for sending email notifications, Configuring the SQL Agent History retention, and Configuring SQL Agent Alerts, so that we get automated notification when certain error conditions happen inside of our server.

Common Maintenance Tasks
Hi. This is Jonathan Kehayias from SQLskill. com, and I'm recording this SQL Server: Maintenance Plans course for Pluralsight. This is Module 4: Common Maintenance Tasks. So, within the Database Maintenance Plans features there are multiple tasks that can be configured to perform various types of database maintenance operations inside of SQL Server. Understanding the most common tasks that need to be implemented for a solid Maintenance Plan in SQL Server, how they work, and then how to go about configuring each of those tasks is important when designing an overall Maintenance Plan strategy for SQL Server. In this module we'll take a look at the Back Up Database Tasks, the Check Database Integrity Task, the Rebuild Index Task, the Reorganize Index Task, the Update Statistics Task, the History Cleanup Task, and the Maintenance Cleanup Task and how you'll go about configuring each of those to design a Maintenance Plan for your SQL Server instance's needs.

Other Tasks
Hi. This is Jonathan Kehayias from SQLskills. com, and I'm recording this SQL Server: Maintenance Plans course for Pluralsight. This is Module 5: Other Tasks in Maintenance Plans. Now, as a part of implementing database Maintenance Plans, there are a number of Maintenance Plan tasks that shouldn't be used in regular maintenance or will only apply to very specific scenarios where you need to implement those particular tasks. Other less common Maintenance Plan tasks cannot be configured within the Maintenance Plan Wizard and are only available through the Maintenance Plan Designer or within SSIS Packages. In this module we'll cover the Shrink Database Task and how you might use it and why you shouldn't use it in routine maintenance, the Execute SQL Server Agent Job Task in scenarios where you might use it, the Execute T-SQL Statement Task and where you might use it, and the Notify Operator Task, which can be used for sending an email notification to an operator that is configured within SQL Server Agent.

Creating Maintenance Plans
Hi. This is Jonathan Kehayias from SQLskills. com, and I'm recording this SQL Server: Maintenance Plans course for Pluralsight. This is Module 6: Creating Maintenance Plans. So, within SQL Server Management Studio there are multiple ways that we can go about configuring Maintenance Plans, and each of those has different limitations and/or benefits for the features that they provide or the limitations that exist. The Maintenance Plan Wizard does simplify configuring the core maintenance tasks within SQL Server Management Studio, and one of the benefits of using the Maintenance Plan Wizard is that it will also set up and maintain the SQL Agent Job schedules associated with the Maintenance Plan Tasks that we're configuring. The Maintenance Plan Designer will allow us to do more complex configurations for our individual maintenance tasks all within SQL Server Management Studio, and it also provides the benefit of creating our SQL Agent jobs appropriately and maintaining those as we make changes to our Maintenance Plan configuration. SQL Server Integration Services or SSIS packages will allow us to create Maintenance Plan packages that can be reused, but it doesn't create scheduled jobs in SQL Server Agent automatically, so we'll have to do management of those jobs manually, but this does allow us to use a simple configuration that can be redeployed across multiple servers. In this module we'll cover Maintenance Plan Wizard introduction, a Maintenance Plan Designer introduction, SQL Server Integration Services packages specifically for database maintenance plans introduction, and the options to go about simplifying our Maintenance Plan reuse.

Maintenance Plan Challenges and Alternatives
Hi. This is Jonathan Kehayias from SQLskill. com, and I'm recording this SQL Server Maintenance Plans course for Pluralsight. This is Module 7: Maintenance Plan Challenges and Alternatives. So within SQL Server, Database Maintenance Plans, regardless of how you actually go about implementing them, will have some significant drawbacks that limit their usage on larger databases. If you understand what these limitations are and then understand the available alternatives that might exist, you can actually build better maintenance configurations for larger databases. In this module we'll cover index maintenance limitations, fragmentation-based analysis approaches for index maintenance, Michelle Ufford's reindex script for performing index maintenance outside of database Maintenance Plans, and Ola Hallengren's maintenance scripts for performing not only index- based maintenance, but also consistency checks against your databases, as well as updating statistics where necessary.