Learning the Tricks of the Trade - Microsoft SQL Server 2008 R2 Administration - page 408

16. If, in case, a scheduled job is designed for multiserver jobs, which have been
designed outside of SSMS, we must post the changes to the master server download
list, so that target servers can download the updated job again.
17. To ensure the target servers have current job definitions, we must post an instruction
on the master server using the following TSQL statement: EXECUTE sp_post_msx_
operation 'INSERT', 'JOB', '<job id>'
18. The job ID context for that scheduled job can be obtained by the execution of the
sp_help_job statement.
19. To resynchronize all multiserver jobs in the specified target server, execute the
TSQL statement using msdb system database context: EXEC dbo.sp_resync_
targetserver N'<ServerName>';
This completes the steps to implement automated administration across an enterprise using
the Multiserver administration concept.
How it works...
The Master Server wizard plays an essential part in setting up automated administration on
a Multiserver environment. Multiserver administration requires a master server and one or
more target servers, so that the jobs will be processed on defined target servers. The wizard
will perform the process to check the security settings for SQL Server Agent service and SQL
Server service on all servers intended to become target servers. The process will create a
master server operator ( MSXOperator ), which is required for Multiserver scheduled jobs to
receive notifications for Multiserver jobs. Then the SQL Server Agent service will be started
on the master server. Finally, the target servers will be enlisted based on the relevant service
account permissions.
The concept of a Multiserver environment ensures that each target reports to only one master
server and we must defect a target server from one master server by using the msdb.dbo.
sp_delete_targetserver @server_name = 'tsx-server' , @post_defection
= 0 statement . The default value for @post_defection will be 0 , which indicates that
no forced defection should occur and if the value is 1 then the forced defection of the target
server will be implemented. When performing a change in the name of a target server, ensure
to defect the TSX before changing the name and re-enlist it after the change. Finally, in order
to remove all of the Multiserver configuration, we must defect all the target servers from the
master server.