Defining Multiserver Jobs

The Make Master Server Wizard assists you in creating your multiserver environment. It takes you through the steps to:

• Check the security settings for the SQLServerAgent service and the Microsoft® SQL Server™ service on all servers that will become target servers (TSX). It is recommended that both services be running in Microsoft Windows NT® domain accounts.
• Check that all servers in the multiserver configuration are running SQL Server version 7.0.
• Create a master server operator (MSXOperator) on the master server (MSX).
• Start the SQLServerAgent service on the MSX.
• Enlist one or more servers as targets of the MSX.
• Define a job and select one or more target servers.

You can define a Windows NT server running SQLServerAgent to be the master server. You can enlist other servers running SQLServerAgent to be the target servers. Each target server reports to only one master server, which is recommended to be running Windows NT Server. The target server must be running on Windows NT.

Multiserver Administration Components

You must define the components of multiserver administration to create multiserver jobs.

Master Server

It is suggested that, if you have many target servers, 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 nonproduction master server, you can centralize administration on one server.

To set up a master server, you must define a master server operator (MSXOperator) on the master server.

The MSXOperator is the only operator that can receive notifications for multiserver jobs.

If you want to dismantle a multiserver configuration, you must defect all the target servers from the MSX master server.

Target Server

You can enlist target servers into and defect target servers from the master server.

A target server can be enlisted into only one master server. You must defect a target server from one master before you can enlist it into a different one.

If you change a target server’s computer name, you must defect it before changing the name and reenlist it after the change.

Multiserver Jobs

You create jobs for multiple servers in much the same way that you create jobs for stand-alone servers. For a multiserver job, you must also specify one or more nonlocal target servers that will run the job.

You can create a job from the Multiserver Jobs node on the master server. You then assign the job to one or more target servers.

Controlling Target Servers

When a target server polls (periodically connects to) the master server, it reads the sysdownloadlist table in the msdb database. The sysdownloadlist table contains operations assigned to the target server. These operations control multiserver jobs and various aspects of the target server’s behavior. Examples of operations are: deleting a job, inserting a job, starting a job, and updating the target server’s polling interval. The polling interval, which has a default of one minute, controls how frequently the target server downloads operations and how frequently the target server uploads job outcome status. Consequently, if a multiserver job’s execution frequency (for example, every minute) is greater than the polling interval (for example, every five minutes), then not all job execution outcomes will be uploaded. In this case it is necessary to view the job history on the target server in order to see all job execution outcomes.

Operations are posted to the sysdownloadlist table in one of two ways:

• Explicitly, by using sp_post_msx_operation.
• Implicitly, by using other job stored procedures.

If you use job stored procedures to modify multiserver job schedules or job steps, or SQL-DMO objects to control multiserver jobs, issue this command after modifying a multiserver job’s steps or schedules to keep the target servers synchronized with the current job definition:

EXECUTE msdb.dbo.sp_post_msx_operation 'INSERT', 'JOB', '<job id>'

Note You do not have to post operations explicitly if you use:

• SQL Server Enterprise Manager to control multiserver jobs.
• Job stored procedures that do not modify job schedules or job steps.

To make a master server

To make a target server

To enlist a target server from a master server

To defect a target server from a master server

To defect multiple target servers from a master server

To create a multiserver job

To view a master SQL Server Agent error log

To check the status of a target server

See Also