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
Transact-SQLTo 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 check the status of a target server
Server Polling
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 the results of job execution. Consequently, if the execution frequency of a multiserver job is greater than that of the polling interval, then not all job execution outcomes will be uploaded. In that case, it is necessary to view the job history on the target server in order to see all the results of job execution.
When a target server polls the master server, it reads the operations assigned to the target server from the sysdownloadlist table in the msdb database. These operations control multiserver jobs and various aspects of the behavior of a target server. Examples of operations include deleting a job, inserting a job, starting a job, and updating the polling interval of a target server.
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>'
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.
See Also
SQL Server 2000 and SQL Server version 7.0