• Article

Automating Administration Across an Enterprise

Automating administration across multiple instances of SQL Server is called multiserver administration. Use multiserver administration to do the following:

  • Manage two or more servers.

  • Schedule information flows between enterprise servers for data warehousing.

Note

As part of Microsoft ongoing efforts to reduce the total cost of ownership, SQL Server 2008 introduces two features: a method of managing servers that is called Policy-Based Management, and multiserver queries that use configuration servers and server groups. These features can be used with, or instead of, some of the features that are described in this topic. For more information, see Administering Servers by Using Policy-Based Management and Administering Multiple Servers Using Central Management Servers.

To take advantage of 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 also stores the central copy of job definitions for jobs that are run on target servers. Target servers connect periodically to the master server to update their schedule of jobs. If a new job exists on the master server, the target server downloads the job. After the target server completes the job, it reconnects to the master server and reports the status of the job. The following illustration shows the relationship between master and target servers:

Multiserver administration configuration

If you administer departmental servers across a large corporation, you can define the following:

  • One backup job with job steps.

  • Operators to notify in case of backup failure.

  • An execution schedule for the backup job.

Write this backup job one time on the master server and then enlist each departmental server as a target server. From the time of their enlistment, all the departmental servers run the same backup job, yet you defined the job only once.

Note

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 that are performed on the target server by the master server. This security measure prevents job steps from being accidentally deleted and operations on the target server from being interrupted.

In This Section