Configure Multiserver Automation
In this tutorial we will learn how to configure multi-server automation. However, automating administration across multiple instances of SQL Server is called multiserver administration. Use multiserver administration to do the following:
- Firstly, manage two or more servers.
- Secondly, schedule information flows between enterprise servers for data warehousing.
Further, 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. Moreover, it 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. And, 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:
If you administer departmental servers across a large corporation, you can define the following:
- Firstly, one backup job with job steps.
- Secondly, operators to notify in case of backup failure.
- Thirdly, 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.
Create a Multiserver Environment
Multiserver administration requires that you set up a master server (MSX) and one or more target servers (TSX). Jobs which process on all the target servers will get to define first on the master server and then downloaded to the target servers. However, by default, full Transport Layer Security (TLS), previously known as Secure Sockets Layer (SSL), encryption and certificate validation are enabled for connections between master servers and target servers. For more information, see Set Encryption Options on Target Servers.
Further, if you have a large number of target servers, avoid defining your master server on a production server that has significant performance requirements from other SQL Server functionality, because target server traffic can slow performance on your production server. If you also forward events to a dedicated master server, you can centralize administration on one server. For more information, see Manage Events.
Considerations for Multiserver Environments
Consider the following issues when creating a multiserver environment:
- Firstly, use the most recent version as the master server. This also supports current and two previous versions.
- Secondly, each target server reports to only one master server. You must defect a target server from one master server before you can enlist it into a different one.
- Thirdly, when changing the name of a target server, you must defect it before changing the name and re-enlist it after the change.
- Next, if you want to dismantle a multiserver configuration, you must defect all the target servers from the master server.
- Lastly, SQL Server Integration Services only supports target servers that are the same version or higher than the master server version.
Reference: Microsoft Documentation