How to: Create Multiserver Maintenance Plans

New: 12 December 2006

You can create a multiserver maintenance plan using the Maintenance Plan Wizard, or you can use the design surface. The Wizard is best for creating basic maintenance plans, while creating a plan using the design surface allows you to utilize enhanced workflow.

To create a multiserver maintenance plan, a multiserver environment containing one master server and one or more target servers must be configured. Multiserver maintenance plans must be created and maintained on the master server. These plans can be viewed, but not maintained, on target servers.

To create or manage maintenance plans, you must be a member of the sysadmin fixed server role. Note that Object Explorer only displays maintenance plans if the user is a member of the sysadmin fixed server role.

To create or manage maintenance plans in a multiserver environment, additional configuration is required. For more information, see Maintenance Plans and Automating Administration Across an Enterprise.

To create a multiserver maintenance plan using the Maintenance Plan Wizard

  1. In Object Explorer, expand a server, and then expand Management.

  2. Right-click Maintenance Plans and select Maintenance Plan Wizard.

  3. Follow the steps of the wizard to create a multiserver maintenance plan.

To create multiserver maintenance plans using the designer

  1. In Object Explorer, expand a server, and then expand Management.

  2. Right-click Maintenance Plans and select New Maintenance Plan.

  3. In the New Maintenance Plan dialog box, type a name for the plan.

  4. The Toolbox opens, and the <Maintenance Plan Name> [Design] surface opens with the default Subplan_1created. Optionally enter a description for the entire plan on the Design tab.

  5. Double-click Subplan_1, and enter a name and description for the subplan in the Subplan Properties dialog box. Click the Subplan Schedule icon to enter schedule details in the Job Schedule Properties dialog box.

  6. To build a subplan, drag and drop task flow elements from the Toolbox to the plan design surface to define the tasks that will be performed. Double-click tasks to open dialog boxes to configure the task options.

  7. To define the workflow between tasks, click the task you want to occur first, hold the CTRL key down, and click the task you want to occur second. Right-click either task, and then click Add Precedence Constraint.

  8. To select the target servers where you want the subplan to run, click Servers. This option is disabled in the maintenance plan designer when the local server is not configured as a master server.

  9. To add another subplan that contains tasks run on a different schedule, click Add Subplan on the toolbar.

  10. To specify reporting options, click the Reporting and Logging icon. To save more detailed information, select Log extended information. To write maintenance plan results information to another server, select Log to remote server. To view the results in the log file viewer, right-click the Maintenance Plans node or the specific maintenance plan, and then click View History.

See Also

Tasks

How to: Change Maintenance Tasks in the Maintenance Plan Wizard
How to: Create a Maintenance Plan
How to: Add or Modify Maintenance Plan Subplan Schedules
How to: Make a Master Server (SQL Server Management Studio)
How to: Make a Target Server (SQL Server Management Studio)

Concepts

Maintenance Plans
Automating Administration Across an Enterprise

Help and Information

Getting SQL Server 2005 Assistance