Edit

Automated Administration Across an Enterprise

  • Article

Applies to: SQL Server

Important

On Azure SQL Managed Instance, most, but not all SQL Server Agent features are currently supported. See Azure SQL Managed Instance T-SQL differences from SQL Server for details.

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.

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. Note that your job definition must be same when performing any database related activities.

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

Create a Multiserver Environment
Contains information about how to create and manage master and target servers.

Choose the Right SQL Server Agent Service Account for Multiserver Environments
Contains information about how using nonadministrative Windows accounts or the Local System account for the SQL Server Agent service can affect multiserver environments.

Set Encryption Options on Target Servers
Contains information about setting the MsxEncryptChannelOptions SQL Server Agent registry subkey on target servers.

Manage Jobs Across an Enterprise
Contains information about checking job status, changing target servers for jobs, synchronizing target server clocks, and polling master servers for their current job status.

Troubleshoot Multiserver Jobs That Use Proxies
Contains information about troubleshooting multiserver jobs that use proxies which fail.

Poll Servers
Contains information about how to implicitly and explicitly make target servers poll master servers to synchronize jobs information.

Manage Events
Contains information about event forwarding from target servers to master servers.

Tune Automated Administration Across an Enterprise
Contains information about how automated administration in a multiserver environment takes advantage of the self-tuning features of SQL Server.

See Also

Backward Compatibility Topics for Installing the SQL Server Database Engine
Registering Servers
sp_add_targetservergroup
sp_delete_targetserver
sp_delete_targetservergroup
sp_help_downloadlist
sp_help_jobserver
sp_help_targetservergroup
sp_resync_targetserver
sp_update_targetservergroup
sysjobservers
syslogins
systargetservers