Multiserver Administration

Multiserver administration is the process of automating administration across multiple instances of Microsoft® SQL Server™.

Use multiserver administration if you:

• Manage two or more servers.
  
  
• Schedule information flows between enterprise servers for data warehousing.

With multiserver administration, you must have at least one master server and at least one target server. A master server distributes jobs to and receives events from target servers. A master server stores the central copy of job definitions for jobs run on target servers. Target servers connect periodically to their master server to update their list of jobs to perform. If a new job exists, the target server downloads the job and disconnects from the master server. After the target server completes the job, it reconnects to the master server and reports the status of the job.

For example, if you administer departmental servers across a large corporation, you can define:

• One backup job with job steps.
  
  
• Operators to notify in case of failure.
  
  
• An execution schedule.

Write this backup job one time on the master server and then enlist each departmental server as a target server. In this way, all the departmental servers run the same backup job even though you defined it only one time.

Multiserver administration features are intended for members of the sysadmin role. However, a member of the sysadmin role on the target server cannot edit the operations performed on the target server by the master server. This security measure prevents job steps from being accidently deleted and operations on the target server from being interrupted.

Creating a Multiserver Environment

To create a multiserver environment, use the Make Master Server Wizard. 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 that will become target servers.

  It is recommended that both services be running in Microsoft Windows NT® 4.0 or Windows® 2000 domain accounts.

• Creating a master server operator (MSXOperator) on the master server.

  The 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, it is recommended that you define your master server on a nonproduction server, so production is not slowed by target server traffic. If you also forward events to this server, you can centralize administration on one server. For more information, see Managing Events.

When creating a multiserver environment, consider the following:

• Each target server reports to only one master server. You must defect a target server from one master before you can enlist it into a different one.
  
  
• The master and target servers must be running on the Windows NT 4.0 or Windows 2000 operating system.
  
  
• When changing the name of a target server, you must defect it before changing the name and reenlist it after the change.
  
  
• If you want to dismantle a multiserver configuration, you must defect all the target servers from the master server.

To make a master server