Multiserver Job Management

Job management can be centralized on a single SQL Server, with other servers polling this server for the jobs they should run. In this situation, you configure a master server and define the jobs on this server. The next step is to enlist target servers, which periodically poll the master to see if they have jobs scheduled to run. This works if the servers are identical or similar in configuration. A job set to run on multiple servers to back up the sales database to drive F: will obviously fail if no sales database or drive F: exist on that server! By their nature, multiserver jobs must be generic.

Creating a Master SQL Server Agent

To create the Master Agent, right-click the SQL Server Agent folder from the server that will be the master. Select Multiserver Administration and then select Make This a Master. This will activate the Make MSX Wizard, which will prompt you for an operator to notify for multiserver jobs and for the servers that should be the target servers. It will also allow you to provide a description for each target server. Click Finish and the master and target servers will be registered.

Enlisting Target Servers

If you want to enlist another target server after a master server has been created, select Make This a Target from the Multiserver Management menu of that server. The wizard will prompt you for the name of the master server and then register the target with the master. This can also be done from the master server by selecting Add Target Servers from the Multiserver Management menu. Remember that a server can be the target of only one master.

Multiserver Jobs

After the master and target servers are set up, jobs are created on the master server and specified as to which target servers they should run on. Periodically, the target servers poll the master server. If any jobs defined for them have been scheduled to run since the last polling interval (which is 5 minutes), the target server downloads the job and runs it. When the job completes, it uploads the job outcome status to the master server.