Creating a Multiserver Environment

Multiserver administration requires that you set up a master server and one or more target servers. Jobs that will be processed on all the target servers are first defined on the master server and then downloaded to the target servers.

To create a multiserver environment, use the Master Server Wizard. For information about using the Master Server Wizard, see How to: Make a Master Server (SQL Server Management Studio). Full Secure Sockets Layer (SSL) encryption and certificate validation are enabled for connections between master servers and target servers by default. For more information, see Setting Encryption Options on Target Servers.

The wizard takes you through the following steps:

  • Checking the security settings for the SQL Server Agent service and the SQL Server service on all servers intended to become target servers.
    We recommend that both services should be running in Microsoft Windows domain accounts. For more information, see Choosing the Right SQL Server Agent Service Account for Multiserver Environments.
  • Creating a master server operator (MSXOperator) on the master server.
    MSXOperator is the only operator that can receive notifications for multiserver jobs.
  • Starting the SQL Server Agent service on the master server.
  • Enlisting one or more servers as target servers.

If you have a large number of target servers, avoid defining your master server on a production server. Otherwise, target server traffic can slow performance on your production server. If you also forward events to a dedicated master server, you can centralize administration on one server. For more information, see Managing Events.

Note

To use multiserver job processing, the SQL Server Agent service account must be a member of the msdb database role TargetServersRole on the master server. The Master Server Wizard automatically adds the service account to this role as part of the enlistment process. For more information, see Selecting an Account for the SQL Server Agent Service.

Considerations for Multiserver Environments

Consider the following issues when creating a multiserver environment:

  • Each target server reports to only one master server. You must defect a target server from one master server before you can enlist it into a different one.
  • When changing the name of a target server, you must defect it before changing the name and re-enlist it after the change.
  • If you want to dismantle a multiserver configuration, you must defect all the target servers from the master server.
  • If you want to distribute maintenance plans additional configuration is required. For more information, see Maintenance Plans.

The following are common tasks for creating a multiserver environment:

To create a master server
To create a target server
To enlist a target server into a master server
To defect a target server from a master server
To defect multiple target servers from a master server
To check the status of a target server

See Also

Concepts

Troubleshooting Multiserver Jobs That Use Proxies

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

12 December 2006

New content:
  • Added information about the connection between master servers and target servers being configured for SSL by default.

14 April 2006

Changed content:
New content:
  • Added a note indicating that the SQL Server Agent service account must be a member of the TargetServersRole fixed database role in the msdb database.

5 December 2005

New content:
  • Added "Behavior of SQL Server Agent Service Account in a Multiserver Environment" section.