SQL Server Multiserver Administration: Master and Target Servers

SQL Server enables you to set up a master server (MSX). The master server can send jobs to be run on one or more target servers (TSX), but the master server may not also be a target server that receives jobs from another master server. The target servers receive and run jobs from a single master server, in addition to their own local jobs. You may have multiple master servers in your environment, but a target server is associated with a single master server. This is a simple two-level hierarchy; a server is a master server, a target server, or neither. The language used to describe the process is military in character: You enlist target servers to add them, and they defect to go away.

Setting up servers is easy. Simply follow these steps:

1. In SSMS, right-click the SQL Server Agent node, select Multiserver Administration, and choose Make This a Master.
2. After the initial dialog box, you see a box where you can provide the e-mail address, pager address, and Net Send location to set up a master server operator. Fill in these fields appropriately. This operator will be set up on the master server and all target servers. This is the only operator who can be notified from multiserver jobs.
3. The next dialog box enables you to choose all the target servers. The list includes the servers that you have registered in SSMS. Choose the servers that you want to be targets of this master, and click Next. You may add additional registrations by clicking the Add Connection button.
4. Close this dialog box. SQL checks to ensure that the SQL versions of the master and targets are compatible. If the versions are not compatible, drop the target from the list and then continue. Later, you can upgrade the target or master, so the versions are the same.
5. Go to the next dialog box and use the wizard to create a login on the target, if necessary, and grant it login rights to the master server. Target servers must connect to the master server to share job status information. After you complete the setup, refresh your SQL Server Agent nodes and see the change. There will be a note on the master server (MSX) and a note on the target server.

Now you can create jobs to be used at multiple target servers. Notice on the MSX that the Jobs node is divided into two sections: local jobs and multiserver jobs. To create a job, follow these steps.

1. Right-click multiserver jobs, and select New Job to create a simple job.
2. Create a simple job on the MSX server and have it run at one or many TSX servers. While doing this, be sure to go to the notifications page. The only operator you can notify is MSXOperator.

Creating multiserver jobs is a nice way to manage a larger implementation without having to buy additional third-party products. No one on the TSX box can mess up your jobs. Use SSMS to connect to the target server as an administrator and look at the job properties for the job you just created and downloaded from the MSX. You can see the job, you can see the job history, and you can even run the job. You cannot delete the job, change the schedule, change the steps, or anything else. This job does not belong to you; it belongs to the MSX.

As you begin to think about how you might use this, be sure you consider the implications of a single job running on multiple servers. Any reference to directories, databases, and so on must be valid for all the TSXs where this job runs. You can create a single backup share that all the backups can use, for instance.

Because a job can start another job, you could also create a master job that has a single step that starts another job. This other job is created on each TSX and is specific to each TSX. This enables you to perform some customization, if necessary. To create a master job, perform the following steps:

1. Back in SSMS, right-click the SQL Server Agent node on the master server.
2. Choose Multi Server Administration. Here you can add target servers and manage target servers.
3. Choose Manage Target Servers. In this dialog box, you can monitor the status of everything. When you create a job for a target server, the job is automatically downloaded to the target server. If the unread instructions count does not go down to 0, poll the target server. This wakes it up to accept the instructions.
4. Click the relevant tab to see the details of downloaded instructions. This shows you details of when jobs are downloaded and updated.
5. Using the Post Instructions button in the Target Server Status dialog, you can synchronize clocks between the servers, defect target servers, set polling intervals, and start jobs. You can also start the job directly from the Jobs node on the MSX or the TSX.
6. Job histories can be viewed on the MSX for the job, just like any other job, but you cannot see job-step details. To get the step details, view the job history from the TSX.
7. You can defect TSXs from the TSX SQL Server Agent node or from the Manage Target Servers dialog on the MSX. When all the TSXs have been defected, the MSX is no longer an MSX.