Maintenance Plans

Using various management utilities of SQL Server, such as Backup/Restore, combined with various scripts, procedures, schedules, and processes, you can set up so-called maintenance plans to automate a collection of management procedures in SQL Server. The maintenance plan feature lets you collect the activities into a package that is then executed using the components of SSIS. The SQL Server Agent is then enlisted to run the jobs and provide the necessary logging and alert system. SQL Server 2005 Integration Services must be installed on the instance of the server or server nodes in a cluster you are managing. SQL Server 2005 lets you create the following when you are creating maintenance plans:

• Workflows that combine a variety of maintenance procedures.

• Custom Transact-SQL scripts and various objects (including CLR-based objects introduced in Chapter 11).

• Conceptual hierarchy for maintenance plans, including error checking and switching.

• Plans that are editable in a graphical environment.

• Plan that can also be scheduled to run at different times.

Due to the various integrated services used by the maintenance plans, you need to log on to SQL Server using Windows Authentication. Maintenance plans are only supported using Windows Authentication. You also need to be a member of the sysadmin fixed server role to create and work with maintenance plans.

To create a maintenance plan, you can use the Maintenance Plan Wizard, or you can create a plan using the design palette. Maintenance plans can be used to create a workflow of the maintenance tasks required to make sure that your database performs well, is regularly backed up in case of system failure, and is checked for inconsistencies. Although the Maintenance Plan Wizard can be used for creating core maintenance plans, creating these plans manually gives you much more flexibility. In SQL Server 2005 Database Engine, maintenance plans create a job that performs these maintenance tasks automatically at scheduled intervals. You must be a member of the sysadmin role to create and manage maintenance tasks.

For basic or base maintenance plans you should start with the wizard. Later you can use the designer tools’ enhanced workflow and expand the plan. Note that maintenance plans are only displayed if the user is connected using Windows Authentication. Object Explorer does not display maintenance plans if the user is connected using SQL Server Authentication. To understand how the maintenance plans work, turn to Chapter 7, where I create a maintenance plan for backing up a collection of databases.