SQL Server Agent Multi-Server Administration

In many enterprise installations there is a need to run jobs on multiple machines at the same time while controlling all executions from one central location. SQL Server Agent supports this scenario by allowing multi-server administration.

Master ServerTarget Server Distributed Job Execution Environment is also referred to as MSX/TSX. Here's how it works in principal. First, the administrator of the Target Server (TSX) needs to enlist in the Master Server (MSX). Enlistment has to be initiated from the TSX side because of security implications. Basically, TSX implicitly vouches to download and execute any job assigned to it by MSX. This means that the MSX server has full control over which jobs get executed on the TSX side. In the process of enlistment, a special account called the MSX Account gets created on the MSX server side and added to a special MSDB role called TargetServersRole.

SQL Server Management Studio makes the creation of a distributed environment very easy by wrapping it inside the Master Server Wizard.

Right-clicking on the SQL Server Agent node inside Object Explorer launches the menu shown in Figure 8-13. Choosing Make this a Master brings up the Master Server Wizard. The first step in the wizard is creation of a master server operator that will receive notifications for all distributed jobs associated with the current master server. The next step is to select target servers to be controlled by this master server, as shown in Figure 8-14. Keep in mind that even though the Wizard is run on the client side, it has to be able to connect to both servers as a member of the sysadmin fixed server role to execute this operation successfully.

Figure 8-13. Multi-Server Administration option.

Figure 8-14. Master Server WizardCreate enlistments.

After the administrator selects which target servers should be enlisted into the master, the Wizard provides the option of creating an MSX login account (see Figure 8-15).

Figure 8-15. Master Server WizardCreate an MSX account.

After you have created a master server you can later dynamically add or remove target servers to and from it. If you navigate from the TSX server side, the process is made even simpler if you use the Target Server Wizard, and involves a single step of picking a master server.

After enlistment, SQL Server Agent of the target server makes periodic connections to the master server, using MSX account credentials, and looks at the contents of the msdb.dbo.sysdownloadlist table. If there is a new or changed job posted for download, it is transferred over and entered into the system on the TSX side.

How do you assign a job to the target server? You can do so as part of the job creation process by following the "Targets" item in the tree view on the left, as shown in Figure 8-16.

Figure 8-16. Assigning a job to the target server.

Now, under the Jobs folder in Object Explorer, two subfolders have been created: Local Jobs and Multi-Server Jobs. Keep in mind that downloaded jobs cannot be modified on the TSX side; otherwise, master and target server configurations would get out of synch with each other.

Jobs get executed as scheduled, and their execution history gets transferred back to the master server and entered into msdb.dbo.sysjobservers table. Note this is different from all local jobs whose entries are stored in msdb.dbo.sysjobhistory table. The history contains only basic job status information.

The entire process continues until TSX explicitly defects from the MSX configuration.

Warning

TargetServersRole is a special role reserved for internal use by SQL Server Agent; therefore, regular users should not be added to it. Although members of the TargetServersRole have very similar privileges to members of the SQLAgentReaderRole, starting with the SQL Server 2005 release they cannot see local jobs, only the jobs scheduled for distributed execution.

One MSX server can control multiple TSX servers. There is no explicit limit defined in the system; however, for practical purposes and to reduce extra load on the master server due to frequent polling connections, it is recommended to keep the number of TSX servers under 100.

Tips and Tricks

If the MSX job being downloaded contains job steps that require proxy accounts, propagation does not happen automatically. Several things must happen first. First, the TSX administrator needs to configure the proxy accounts with the same names as the ones corresponding to the jobs being downloaded. Second, a special Registry flag, AllowDownloadedJobsToMatchProxyName, needs to be set to 1 (true).

When you upgrade MSX/TSX server clusters, it is recommended that you start by upgrading the target servers first. SQL Server 2005 level TSX recognizes and can communicate with a SQL Server 2000 post-SP3 MSX server. However, if a previous version of a SQL Server TSX server is enlisted and tries to download information from a higher-level MSX, the download will fail because of multiple metadata changes introduced in the new release of SQL Server 2005.