How-to: Multiserver administration with master and target SQL Agent jobs

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.

The lab we will be using today consists of three 2012 MSSQL Servers – LEO, ANTARES and 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*.

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.

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.
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.

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.

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:

--- SysAdmin privileges are required

USE msdb;

EXEC dbo.sp_msx_enlist N'KOSEVK5\LEO', N'Description for the master server, default is NULL' ;

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.

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:

USE [msdb]

-- 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'

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;

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:

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:

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.

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.)

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:

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:

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.

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;

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

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'

Author
Recent Posts

Kaloyan Kosev

Kaloyan Kosev is a SQL Server expert working with enterprise companies for the last 8 years. His daily work routine is a mixture of troubleshooting bugs and issues, consulting and advising clients and presenting to students.

View all posts by Kaloyan Kosev

Related posts: