方法：使用主服务器和目标SQL Agent作业进行多服务器管理

Have you ever been in a situation where you had to manage hundres of MSSQL Servers? Well, I am right on track here and believe me it does not get easier, even minor tasks take quite the time to do in large enterprise enviornments. One of the solutions I like and I’m using to ease the administration of standard maintenance jobs across my servers was ‘master – target’ jobs. Within the article to follow I will introduce you to the concept, the prerequisites and the limitations they have.

您是否曾经遇到过必须管理数百个MSSQL Server的情况？ 好吧，我在这里是对的，相信我并不会变得容易，即使在大型企业环境中，即使是次要的任务也需要花费很多时间。 我喜欢使用的解决方案之一是“主-目标”工作，该解决方案可简化服务器中标准维护工作的管理。 在接下来的文章中，我将向您介绍概念，前提条件和限制。

The lab we will be using today consists of three 2012 MSSQL Servers – LEO, ANTARES and ORION.

我们今天将使用的实验室包括三个2012 MSSQL服务器-LEO，ANTARES和ORION。

Multiserver administration with master and target jobs is not something new, actually it is available from some of the earliest SQL Server versons such as 7.0. It is hard to believe at the beggining but I have personally tested it from version 8.0 up to 13*.

具有主作业和目标作业的多服务器管理并不是什么新鲜事物，实际上它可以从7.0等最早SQL Server版本中获得。 难以置信，但我已经从8.0版开始测试了它，直到13 *。

Multiserver administration consist of one master server and one or more target servers.

多服务器管理由一台主服务器和一台或多台目标服务器组成。

We will use LEO as our target one, ANTARES and ORION will be our targets.

我们将以LEO为目标，ANTARES和ORION将为目标。

The master server will store the master copy of the jobs and it will distribute them to the targets. The execution status of the jobs – if they are successfull or not will also be send back to the master. Each target server can be assigned to only one master server, they use the master to periodically update their schedule of jobs and download new ones if such are created in the meantime. The period can be controlled in order to minimize the network traffic.

主服务器将存储作业的主副本，并将其分发到目标。 作业的执行状态（无论是否成功）也将被发送回主服务器。 每个目标服务器只能分配给一个主服务器，它们使用主服务器定期更新其作业计划并下载新的作业计划（如果在此同时创建）。 可以控制该时间段以最小化网络流量。

A good example of a simple implementation is to create a backup job on your master server, distribute it to all target servers in your enviornment and it will be automatically published – you wil be able to monitor the status of all jobs from the central master server.

一个简单实现的好例子是在主服务器上创建一个备份作业，将其分发到环境中的所有目标服务器，它将自动发布-您将能够从中央主服务器监视所有作业的状态。 。

Before starting with the implementation there are some considerations that you should keep in mind.

在开始实施之前，应牢记一些注意事项。

• The services of the SQL server and the SQL Agent should run under Windows domain accounts – they will need to communicate with different servers across the network.

  SQL Server和SQL Agent的服务应在Windows域帐户下运行-它们将需要与网络上的其他服务器进行通信。

• Consider using a server dedicated for similar administrative tasks instead of a highly loaded production or test server. The master server distributes jobs to other servers and along with this recieves back events from the targets. This additional traffic and load may impact an already busy server.

  考虑使用专用于类似管理任务的服务器，而不是高负载的生产或测试服务器。 主服务器将作业分发到其他服务器，并与此一起从目标接收回事件。 这种额外的流量和负载可能会影响已经繁忙的服务器。

• Target servers can be linked to only one master server. If you want to check the server who acts as master you have to disconnect all target servers and connect them to the new one. Same stands when you want to rename it but you can still use aliases to workaround this.

  目标服务器只能链接到一台主服务器。 如果要检查充当主服务器的服务器，则必须断开所有目标服务器的连接并将它们连接到新服务器。 当您想重命名它时，它仍然存在，但是您仍然可以使用别名来解决此问题。

Let’s start with the setup and create a master server, we will use the MSSQL Server LEO.

让我们从设置开始并创建一个主服务器，我们将使用MSSQL Server LEO。

The easiest way to setup a master server is using the graphical interface, of course you can automate the enlist for the target servers using T-SQL. Right click on the SQL Server agent, choose Multi server administration > Make this a Master.

设置主服务器的最简单方法是使用图形界面，当然，您可以使用T-SQL自动化目标服务器的登记。 右键单击SQL Server代理，选择“多服务器管理”>“使其成为主服务器”。

The Master Server Wizard will open on its welcome screen:

主服务器向导将在其欢迎屏幕上打开：

On the next screen you can configure an operator to be used on the master server and each of the target servers. Note that the multiserver jobs can only notify this opeator.

在下一个屏幕上，您可以配置要在主服务器和每个目标服务器上使用的操作员。 请注意，多服务器作业只能通知此操作者。

Here you can add your target servers. If you already have them registered in “Local Server group” or in “Central Managament Servers” you can add them, if not – then add as a new connection:

在这里您可以添加目标服务器。 如果已经在“本地服务器组”或“中央管理服务器”中注册了它们，则可以添加它们（如果没有）–然后添加为新连接：

Moving forward the wizard will check the compatability and add the server.

继续前进向导将检查兼容性并添加服务器。

You also have the option to create a new login on the master server if necessary.

如果需要，您还可以选择在主服务器上创建新的登录名。

Clicking Next and Finish the wizard will process all required operations. Note that you may recieve errors similar to “Create failed for Login …” this is expected if the service accounts are used instead.

单击下一步并完成向导将处理所有必需的操作。 请注意，您可能会收到类似于“为登录创建失败...”的错误，如果改用服务帐户，则会出现此错误。

To add a new target server using TSQL we can use the following:

要使用TSQL添加新的目标服务器，我们可以使用以下命令：

 
--- SysAdmin privileges are required
USE msdb;  
GO 
EXEC dbo.sp_msx_enlist N'KOSEVK5\LEO', N'Description for the master server, default is NULL' ;
GO
 

Once a server is added as a target you can notice a change in the graphical user interface that a note is added next to the SQL Server agent, in our case the note is “TSX: KOSEVK5\LEO” showing that the current server acts as a target and its master server is KOSEVK5\LEO.

将服务器添加为目标后，您会注意到图形用户界面中的更改是在SQL Server代理旁边添加了一个注释，在本例中，注释为“ TSX：KOSEVK5 \ LEO”，表明当前服务器充当目标及其主服务器是KOSEVK5 \ LEO。

Let us now create one SQL Server agent job that will execute a stored procedure “dbo.DatabaseBackup” that is creating backups of the user databases. To automate the task, we will create the job on our master server (MSX) LEO and publish it on the target servers (TSX) as follows:

现在让我们创建一个SQL Server代理作业，该作业将执行存储过程“ dbo.DatabaseBackup”，该过程正在创建用户数据库的备份。 为了使任务自动化，我们将在主服务器（MSX）LEO上创建作业，并将其发布到目标服务器（TSX）上，如下所示：

 
USE [msdb]
GO
-- We are adding a new SQL Server agent job named 'BackupSystemDatabase'
EXEC msdb.dbo.sp_add_job
@job_name=N'BackupSystemDatabases', 
@enabled=1, 
@notify_level_eventlog=0, 
@notify_level_email=0, 
@notify_level_netsend=0, 
@notify_level_page=0, 
@delete_level=0, 
@description=N'No description available.', 
@category_name=N'[Uncategorized (Multi-Server)]', 
@owner_login_name=N'sa'
 
-- The first and only step will be to execute a stored procedure named 'dbo.DatabaseBackup' (shoutout to Ola Hallengren)
EXEC msdb.dbo.sp_add_jobstep 
@job_name=N'BackupSystemDatabases', 
@step_name=N'BackupSystemDatabasesStep1', 
@step_id=1, 
@cmdexec_success_code=0, 
@on_success_action=1, 
@on_success_step_id=0, 
@on_fail_action=2, 
@on_fail_step_id=0, 
@retry_attempts=0, 
@retry_interval=0, 
@os_run_priority=0,
@subsystem=N'TSQL', 
@command=N'EXECUTE dbo.DatabaseBackup
@Databases = ''SYSTEM_DATABASES'',
@Directory = ''C:\Program Files\Microsoft SQL Server\MSSQL12.LEO\MSSQL\Backup'',
@BackupType = ''FULL'',
@Verify = ''Y''', 
@database_name=N'master', 
@flags=0
 
--- Creating a sample schedule to work with
EXEC msdb.dbo.sp_add_jobschedule @job_name=N'BackupSystemDatabases', 
@name=N'BackupSystemDatabases', 
@enabled=1, 
@freq_type=4, 
@freq_interval=1, 
@freq_subday_type=8, 
@freq_subday_interval=1, 
@freq_relative_interval=0, 
@freq_recurrence_factor=0, 
@active_start_date=20160707, 
@active_end_date=99991231, 
@active_start_time=0, 
@active_end_time=235959
 
-- This one is important
-- We are saying to which target servers the job should be published, in our cases thats ANTARES and ORION
EXEC msdb.dbo.sp_add_jobserver @job_id=N'47881bca-50b1-4d79-92bd-a749bfae0df0', @server_name = N'KOSEVK5\ANTARES'
EXEC msdb.dbo.sp_add_jobserver @job_id=N'47881bca-50b1-4d79-92bd-a749bfae0df0', @server_name = N'KOSEVK5\ORION'
GO
 

The same can be achieved by using the graphical interface – just create the job as a standard one, but pay attention on the tab “Targets” – there you should specify that a job is targeting multiple servers and to choose which one exactly.

使用图形界面可以实现相同目的–只需将作业创建为标准作业，但要注意“目标”选项卡–您应指定作业针对多个服务器并准确选择哪个服务器。

Once the jobs have completed at least one execution, the outcome, no matter successful or not – the history will be send back to the master (MSX) server and can be reviewed directly from there: Right Click on the job ➜ View History, and you will get an overview of the executions of all servers. For more detailed information, you will need to connect directly to your target server and review it from there in case of errors;

作业完成至少一项执行后，无论成功与否，结果都会将历史记录发送回主（MSX）服务器，并可以从那里直接进行查看：右键单击作业➜查看历史记录，然后您将获得所有服务器执行情况的概述。 有关更多详细信息，您将需要直接连接到目标服务器并在出现错误的情况下从那里对其进行检查。

Example: We are seeing that our job has completed successfully on both of the servers, along with some other helpful information:

示例：我们看到我们的工作已经在两个服务器上成功完成，以及其他一些有用的信息：

Using Extended Events we can capture the communication between the master and the target servers. As an example, once we modify the master job from the graphical user interface we can see the TSQL running on the background as follows:

使用扩展事件，我们可以捕获主服务器和目标服务器之间的通信。 例如，一旦我们从图形用户界面修改了主作业，我们可以看到TSQL在后台运行，如下所示：

 
EXEC msdb.dbo.sp_update_jobstep @job_id=N'47881bca-50b1-4d79-92bd-a749bfae0df0', 
@step_id=1 , 
@command=N'EXECUTE dbo.DatabaseBackup
@Databases = ''SYSTEM_DATABASES'',
@Directory = ''C:\Program Files\Microsoft SQL Server\MSSQL12.LEO\MSSQL\Backup'',
@BackupType = ''FULL'',
@Verify = ''Y''' 
 

Immediately after that the SQL Server triggers a POST operation to distribute the changes to the target servers:

此后，SQL Server立即触发POST操作，以将更改分发到目标服务器：

 
EXECUTE msdb.dbo.sp_post_msx_operation  N'INSERT', N'JOB', @job_id = '47881bca-50b1-4d79-92bd-a749bfae0df0' 
 

Along with a probe operation towards the targets:

连同对目标的探测操作：

 
EXECUTE msdb.dbo.sp_sqlagent_probe_msx @server_name = N'KOSEVK5\ORION', @local_time = N'2016/07/07 17:18:14', @poll_interval = 60, @time_zone_adjustment = 120 
 

And an interesting connection test 🙂

还有一个有趣的连接测试🙂

 
SELECT N'Testing Connection...'
 

Update in the local tables to log a successful download of the jobs from the target servers:

更新本地表以记录从目标服务器成功下载作业：

 
UPDATE msdb.dbo.sysdownloadlist SET date_downloaded = GETDATE(), status = 1 WHERE (instance_id = 86) 
 

The table sysdownloadlist contains history for the last downloads and if they are successful or not.

sysdownloadlist表包含上次下载的历史记录以及它们是否成功。

Housekeeping is done for sysdownloadlist table as follows (This trigger controls how many downloaded (status = 1) sysdownloadlist rows exist for any given server. It does NOT control the absolute number of rows in the table.)

如下完成sysdownloadlist表的内务处理（此触发器控制任何给定服务器的sysdownloadlist行下载数量（状态= 1）。它不控制表中的绝对行数。）

 
exec sp_executesql N'SELECT TOP (@P1) instance_id, operation_code, object_type, object_id FROM msdb.dbo.sysdownloadlist WHERE (target_server = @P2) AND (status = 0) ORDER BY instance_id',N'@P1 int,@P2 nvarchar(128)',50,N'KOSEVK5\ORION'
 

And let us check how the target server receives the updates when a job is modified.

让我们检查一下修改作业后目标服务器如何接收更新。

First we receive an instruction to delete the job with this ID without deleting the history:

首先，我们收到一条删除该ID的作业而不删除历史记录的指令：

 
DECLARE @ret INT DECLARE @schedule_id INT IF (EXISTS (SELECT 1 FROM msdb.dbo.sysjobs WHERE (job_id = 0xCA1B8847B150794D92BDA749BFAE0DF0))) 
EXECUTE msdb.dbo.sp_delete_job 
@job_id = 0xCA1B8847B150794D92BDA749BFAE0DF0, @delete_history = 0 
 

Then we add a job with the same parameters for name and ID, we create the steps and the schedules again as well:

然后，我们添加一个具有相同名称和ID参数的作业，再一次创建步骤和时间表：

 
EXECUTE @ret = msdb.dbo.sp_add_job 
@originating_server = N'KOSEVK5\LEO', 
@job_id = 0xCA1B8847B150794D92BDA749BFAE0DF0, 
@job_name = N'BackupSystemDatabases', 
@enabled = 1, 
@etc .....
--- some code removed for visibility 
 
EXECUTE @ret = msdb.dbo.sp_add_jobstep 
@job_id = 0xCA1B8847B150794D92BDA749BFAE0DF0, 
@step_id = 1, 
@step_name = N'BackupSystemDatabasesStep1', 
@subsystem = N'TSQL', 
@command = N'EXECUTE dbo.DatabaseBackup
@Databases = ''SYSTEM_DATABASES'',
@Directory = ''C:\Program Files\Microsoft SQL Server\MSSQL12.LEO\MSSQL\Backup'',
@BackupType = ''FULL'',
@Verify = ''N''', 
@etc .....
--- some code removed for visibility 
 

And we update the metadata for the job as well:

我们也更新了工作的元数据：

 
UPDATE msdb.dbo.sysjobs SET 
date_created = N'20160707 16:40:28:870', 
date_modified = N'20160707 17:39:02:723', 
version_number = 0 
WHERE (job_id = 0xCA1B8847B150794D92BDA749BFAE0DF0
 

We can see that the target and master server are communicating and exchanging data for every single job, sometimes we have numerous target SQL servers running not so few jobs, if the traffic that is generated is not something we can live with we can control it by changing the pooling interval between the master and the target servers, to do this we modify the poll_interval parameter.

我们可以看到目标服务器和主服务器正在为每个作业通信和交换数据，有时我们有许多目标SQL服务器运行的作业不是那么少，如果生成的流量不是我们可以忍受的，我们可以通过以下方式控制它：更改主服务器和目标服务器之间的缓冲间隔，为此，我们修改了poll_interval参数。

We can use the graphical interface: Right click on SQL Server agent, Multi server administration, Manage target servers, Post instruction, from the drop down menu ‘Instruction type’ choose ‘Set polling interval’ and modify the ‘Pooling interval field for all or just some target servers;

我们可以使用图形界面：右键单击SQL Server代理，多服务器管理，管理目标服务器，发布指令，从下拉菜单“指令类型”中选择“设置轮询间隔”，然后为所有或所有修改“缓冲间隔”字段。只是一些目标服务器；

Alternatively, you can use the stored procedure ‘sp_post_msx_operation’ with Operation type ‘SET-POLL’

另外，您可以使用操作类型为“ SET-POLL”的存储过程“ sp_post_msx_operation”

 
msdb.dbo.sp_post_msx_operation @operation = 'SET-POLL', @object_type = 'SERVER', @specific_target_server = 'KOSEVK5\ANTARES', @value = 121
 

Last but not least, a cheap trick when the target servers go out of sync is to re-sync them manually, so far I was not able to find out what causes this issue, but a quick way to fix it is to force the re-sync:

最后但并非最不重要的一点是，当目标服务器不同步时，一个便宜的技巧是手动重新同步它们，到目前为止，我仍无法找出导致此问题的原因，但是解决此问题的快速方法是强制重新同步。 -同步：

 
EXEC msdb.dbo.sp_resync_targetserver N'KOSEVK5\ANTARES'
 

翻译自: https://www.sqlshack.com/multiserver-administration-master-target-sql-agent-jobs/