Designing a Database Maintenance Plan | MCITP Administrator: Microsoft SQL Server 2005 Optimization and Maintenance (Exam 70-444) Study Guide

At the database level, the DBA has to design an appropriate database maintenance plan. Long gone are the days of a DBA being responsible for only database consistency checks and backups.

It is important to choose the appropriate tool when designing your database maintenance plan. SQL Server 2005 offers two main options via the SQL Server Management Studio (SSMS) environment.

The Maintenance Plan design surface provides a “free-form” environment where you can place maintenance objects on the design surface and subsequently link them through various types of evaluation operations and precedence.

The Database Maintenance Plan Wizard, as with all wizards other than Harry Potter, offers a number of choices through a series of steps that will produce a customized and sequenced database maintenance for one or more databases. Quick. Easy. Simple. Don’t forget that you can always modify the database maintenance plan in the Maintenance Plan design surface after you have initially created it in the Database Maintenance Plan Wizard.

Tip

The “Agent XPs” have to be turned on through the SQL Server Surface Area Configuration tool or through the sp_configure system stored procedure for you to be able to create database maintenance plans through the Maintenance Plan design surface or the Database Maintenance Plan Wizard.

When designing your database maintenance plan, you should perform the following actions:

Determine the maintenance tasks sequence. You will need to determine the sequence of steps in your database maintenance plan. Not only is the sequence important, but so is the priority potentially. Identify which steps of a task, upon failure, should report the failure of the database maintenance task.

Tip

We’ve seen people whose database maintenance plans include a nightly rebuild of all indexes, followed by a database shrink-which is a total waste of time! Why? Remember that shrinking a database will fragment it again!

Identify the hierarchy of different database maintenance plans. It is quite common for a SQL Server instance to have multiple database maintenance plans. Consequently, you will need to identify the order in which these different plans will be executed. The order could be based on the importance of the database to your organization, any existing service-level agreements (SLAs), or simply the amount of time that it will take.
Identify maintenance tasks not implemented in the built-in tools. You will need to identify any tasks that are not implemented by the built-in tools available in the SQL Server Management Studio environment.
Customize your backup tasks. There are very limited options for database backups through SSMS. Consequently, you need to consider whether you will perform your database backups through custom T-SQL scripts, which can leverage the various options discussed in Chapter 3, such as mirrors, checksums, and passwords.

Exercise 5.2 will demonstrate the Database Maintenance Plan Wizard and what happens in the background.

Exercise 5.2: Using the Database Maintenance Plan Wizard

You can create database maintenance plans through the wizard or manually. It’s easier to start with the wizard and then customize the plan if required.

Open SQL Server Management Studio, and connect to your SQL Server 2005 instance using Windows authentication.
Expand your SQL Server instance and then the Management folder.
Right-click the Maintenance Plans folder, and then click Maintenance Plan Wizard. You should see the Maintenance Plan Wizard Welcome screen. Click the Next button when you have finished.
Enter name and description for the database maintenance plan.
Click the Next button when you have finished.

Select the appropriate maintenance tasks that you want to perform. In this exercise, select the Check Database Integrity, Rebuild Index, Clean Up History, Back Up Database (Full), Back Up Database (Differential), and Back Up Database (Transaction Log) tasks. Usually you would not select all of these, but we want to highlight the options available in the more commonly used tasks. (You will not be running the database maintenance plan anyway.)
Click the Next button when you have finished.
Reorder the tasks as required. In this exercise, move the Clean Up History task so that it will be executed last.

Click the Next button when you have finished.
Click the Include Indexes check box if you require indexes to be checked during the database consistency check.
Click the Databases drop-down list, and select the databases for which you want to configure the database maintenance task.
Click the OK button when you have finished.
Select the appropriate options for the Rebuild Index task.
Choose the databases from the Databases drop-down list as you did in steps 11–12.

Click the Next button when you have finished.
Select the appropriate options for the Back Up Database (Full) task.
You need to make sure that the directory for the backup files exists.
Select the Check Database Integrity task.
Choose the databases from the Databases drop-down list as you did in steps 11–12.
Click the Next button when you have finished.
Select the appropriate options for the Back Up Database (Differential) task.
Make sure that the directory for the backup files exists.

Select the Back Up Database (Differential) task.
Choose the databases from the Databases drop-down list as you did in steps 11–12.
Click the Next button when you have finished.
Select the appropriate options for the Back Up Database (Transaction Log) task.
Make sure that the directory for the backup files exists.
Select the Back Up Database (Transaction Log) task.
Choose the databases from the Databases drop-down list as you did in steps 11–12.
Click the Next button when you have finished.
Select the appropriate options for the Clean Up History task.
In this exercise, change the Remove Historical Data Older Than option to 3 Months.
Click the Next button when you have finished.
To define a schedule for when this database maintenance plan is going to execute, click the Change button.

Define the schedule in the New Job Schedule dialog box, and click OK.
Click the Next button.

Check the Write a Report to a Text File check box.
Choose an existing directory location for where you want the report file to be generated.
Click the Next button.
Review the actions you want your database maintenance plan to perform.
You can get more information about a task by expanding its check box.
Click the Finish button.
SQL Server 2005 should create the database maintenance plan for you.

Click the Report button, and select View Report to see the report from the creation of the database maintenance plan.
To examine and potentially customize your maintenance plan, you can modify it in SSMS.
Right-click the Maintenance Plans folder, and choose Refresh.
Expand the Maintenance Plans folder.
Right-click your database maintenance plan, and choose Modify. This should display your database maintenance plan in the designer.
You can add new maintenance plan tasks from the Maintenance Plan Tasks toolbox. You can modify, delete, or group existing maintenance plan tasks. You can also modify the precedence between existing maintenance plan tasks.

Right-click the Back Up Database (Differential) task, and choose Edit. Examine the options in the Back Up Database Task dialog box.
To view the Transact-SQL script that will be used to execute the task, click the View T-SQL button.
Click the Close button when you have finished.
Click the Cancel button in the Back Up Database Task when you have finished.
Close the AdventureWorks Maintenance Plan database maintenance plan in the designer when you have finished.
SQL Server 2005 uses the SQL Server Agent to execute your database maintenance plans. You’ll now learn how the SQL Server Agent does this.
Expand the SQL Server Agent.
Expand the Jobs folder.
Right-click the AdventureWorks Maintenance Plan, and choose Properties.

Select the Steps page. The database maintenance plan consists of a single step titled Subplan.
Click the Edit button to see the properties of the Subplan job step.
Click the Command Line tab to see the actual command being executed.
Click the Cancel button to close the Job Step Properties window.
Click the Cancel button to close the Job Properties window.
To clean up, right-click the AdventureWorks Maintenance Plan in the Maintenance Plans folder, and choose Delete.
Click the OK button in the Delete Object window.

Warning

If the SQL Server Agent is not running, your database maintenance plan will not be executed, so make sure that the SQL Server Agent is configured to autostart with the Windows operating system and that it is running whenever you stop and start the SQL Server service.