SQL Server multiple servers management (MSSQL distributed job management)

The automated management of multiple instances of a range of SQL Server is called multi-server management. With multi-server administration, you can do the following:

Manage two or more servers.

Schedule the information flow for the data warehouse between Enterprise servers.

To take advantage of multi-server management, you must have at least one primary server and at least one target server. The primary server distributes the job to the target server and receives events from it. The primary server also stores a central copy of the job definition of the job that is running on the target server. The target server periodically connects to the master server to update their job schedules. If a new job exists on the primary server, the target server downloads the job. After the target server finishes the job, it will reconnect to the master server and report the job status. Note that when you execute any database, the job definition must be the same related activity.

The example shows the relationship between the primary server and the target server.

Write the backup job once to the primary server, and then enlist the divisional server as the destination server. From the time they are registered, all departmental servers will run the same backup job, and you only need to define the job once.

Start testing!! This test is a two-instance on a single server.

MSSQL 2014:hzc\mssqlserver (master server instance)

MSSQL:hzc\DOG (target server instance)

First, the registry is changed, and the encryption between the primary and destination servers is canceled. Open the registry and search for changes to two options.

msxencryptchanneloptions = 0

Allowdownloadedjobstomatchproxyname = 0

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL server\mssql10_50.mssqlserver\sqlserveragent\ Msxencryptchanneloptions

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL server\mssql10_50.mssqlserver\sqlserveragent\ Allowdownloadedjobstomatchproxyname

The description is as follows:

Value	Description
0	Disables encryption between the target server and the primary server. Select this option only if the channel between the destination server and the primary server has been protected with another method.
1	Encryption is enabled only between the target server and the primary server, but no certificate validation is required.
2	Enable full SSL encryption and certificate validation between the target server and the primary server. This is the default setting. Unless you choose a different value for a specific reason, it is recommended that you do not change it.

The agent startup account is added to the instance account, and the server role is granted to the sysadmin

Let's go straight ahead! Right-click the primary server proxy and set the current setting as the main server

In this step, the target instance is added on the registered server and the next step is completed!

(Open Registered Server: Menu Bar--"View--" registered server--"Add the server)

Or a command can be configured to complete! Add the current server to the list of available servers for the master server (target instance execution)

--Add the current server to the list of available servers for the master server (target instance execution) EXEC msdb.dbo.sp_msx_enlist @msx_server_name = n ' hzc ',--master server @location = N ' Location '- -Location of the target server to add go

OK, the name of the agent has changed, the configuration is complete! As you can see, the primary server instance agent distinguishes between local jobs and multi-server jobs.

Now simply create a job with the Step execution content: Select GETDATE (). The last option, "Target", which is to differentiate between a local or a multi-server job, check the target server as follows:

Soon, the discovery was synchronized.

Now open the target manager for synchronization management. Right-click the main Server Agent--"multi-server Management--" Management target server

The time of the most recent synchronization.

The job "Test2" is the primary server that was just created, and when created or modified, the records are recorded in the download instructions. The target server executes each of these records, and if the "Download Date" is not empty, it means that it has been synchronized.

--Download instruction (master instance execution) EXEC Msdb.dbo.sp_help_downloadlistSELECT * from Msdb.dbo.sysdownloadlistGO

If there is an error in the instruction, the wrong instruction can be deleted.

--delete from Msdb.dbo.sysdownloadlist where instance_id < 5--can be deleted directly

if a command resynchronization or new synchronization is required, you can insert the instruction into table sysdownloadlist.

--Insert an operation (row) into the system table sysdownloadlist for download and execution by the target server (primary instance execution)--https://msdn.microsoft.com/zh-cn/library/ Ms173737.aspxdeclare @jobid CHAR SELECT @jobid =job_id from msdb.dbo.sysjobs WHERE name = ' test2 ' EXEC msdb.dbo.sp_post _msx_operation @operation = ' INSERT ', @object_type =  ' JOB ', @job_id = @jobid, @specific_target_server = ' Hzc\dog ' GO

The job of the target server records each execution, and the job Activity Monitor in the master server can see the most recent status for all jobs.

--Job Activity Monitor (the target server job failed and will be reflected here (primary instance execution) EXEC Msdb.dbo.sp_help_jobSELECT * from Msdb.dbo.sysjobserversGO

Now change the local job to a multi-server job.

--Change the local job to a multi-server job (primary instance execution) EXEC msdb.dbo.sp_delete_jobserver@job_name=n ' Test ', @server_name = N ' hzc ' goexec msdb.dbo.sp_ Add_jobserver@job_name = n ' Test ', @server_name =  n ' hzc\dog ' GO

stored Procedures sp_delete_jobserver and sp_add_jobserver are just options for the server, and neither can be selected (only Sp_delete_jobserver execution). When you select one, you must first remove the other.

Let's talk about the management interface now!

See four options: Detach, set polling interval, synchronize clock, start job. The semantics are obvious, not much, and the following 3 settings scripts are as follows:

--Set the polling interval (primary instance execution) EXEC msdb.dbo.sp_post_msx_operation @operation = ' Set-poll ', @object_type = ' SERVER ', @specific_target_ Server = ' Hzc\dog ', @value = 60--polling interval in seconds (10 to 28800) go--set clock synchronization (primary instance execution) EXEC msdb.dbo.sp_post_msx_operation@operation = ' Sync-time ', @object_type = ' SERVER ', @specific_target_server = ' Hzc\dog ' go--set to start synchronization (master instance execution) EXEC Msdb.dbo.sp_post_msx_ operation@operation = ' re-enlist ', @object_type = ' SERVER ', @specific_target_server = ' Hzc\dog ' GO

out of the last word. Jobs may not be synchronized or changed much over time, and can be resynchronized in batches. The stored procedure sp_resync_targetserver first synchronizes a delete command, deletes all jobs, and then synchronizes all target server jobs.

--resynchronization of all multi-server jobs in the specified target server (primary instance execution) EXEC msdb.dbo.sp_resync_targetserver N ' all ' GO

after the synchronization instruction is released, it waits for the time to be polled to perform the synchronization. Polling time can be set, or it can be forced polling.

(Note: This stored procedure seems useless, it is best to click "Force polling" in the Operation Interface)

--Force polling, manual script seems invalid! (primary instance execution) DECLARE @localtime VARCHAR = CONVERT (varchar (), GETDATE (), +) EXEC Msdb.dbo.sp_sqlagent_probe_ Msx@server_name = N ' Hzc\dog ', @local_time = @localtime, @poll_interval =, @time_zone_adjustment = 480GO

For detachment, several methods:

Reference: Enterprise-wide automation management

SQL Server multiple servers management (MSSQL distributed job management)