Multiserver Jobs
Administration can be drastically simplified when you use multiserver administration. In
a multiserver environment, you can configure one instance as a master server (MSX) and
then other servers as target servers (TSX). You can then create a set of maintenance jobs
on the MSX and configure them to run on the TSXs, or a subset of the TSXs.
Configuring the MSX and TSX Servers
Before creating multiserver jobs, you must first prepare the environment.
The first step is to edit the Registry on the MSX and set the value of the
AllowDownloadedJobsToMatchProxyName REG_DWORD to 1, which allows jobs to match
the proxy name. You can find this value under the SQL Server Agent key, which is located
under the Software\Microsoft\Microsoft SQL Server\[YOUR INSTANCE NAME] key in
the Registry. You also need to ensure that the TSXs have a proxy account configured with
the same name as the proxy account on the MSX that will be running the job.
Figure 22-28.  The Options page

We also need to configure how the TSXs encrypt the data when they communicate
with the MSX. We achieve this using the MsxEncryptChannelOptions Registry key for
the TSX. You can find this key in the SQL Server Agent key, which is located under
the Software\Microsoft\Microsoft SQL Server\[YOUR INSTANCE NAME] key in the
Registry. A value of 0 means that encryption is not used. 1 indicates that encryption
is used, but the certificate is not validated, and an option of 2 indicates that full SSL
encryption and certificate validation is used. In our environment, since all instances are
on the same physical box, we disable encryption.
Therefore, to prepare our SQLSERVER\MASTERSERVER instance to be an MSX and to
prepare our SQLSERVER\TARGETSERVER1 and SQLSERVER\TARGETSERVER2 instances to be
TSXs, we run the script in Listing 22-9 to update the Registry.
Note  The demonstrations in this section use three instances named
SQLSERVER\MASTERSERVER, which we configure as an MSX, and SQLSERVERTARGETSERVER1 and SQLSERVER\TARGETSERVER2, both of which we configure
as TSXs.
Listing 22-9.  Updating the Registry
USE Master
GO
EXEC xp_regwrite
  @rootkey = N'HKEY_LOCAL_MACHINE'
 ,@key = N'Software\Microsoft\Microsoft SQL Server\MasterServer\SQL Server
Agent'
 ,@value_name = N'AllowDownloadedJobsToMatchProxyName'
 ,@type = N'REG_DWORD'
 ,@value = 1 ;
EXEC xp_regwrite
  @rootkey='HKEY_LOCAL_MACHINE',
  @key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL15.TARGETSERVER1SQLServerAgent',
  @value_name='MsxEncryptChannelOptions',

@type='REG_DWORD',
  @value=0 ;
EXEC xp_regwrite
  @rootkey='HKEY_LOCAL_MACHINE',
  @key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL15.TARGETSERVER2SQLServerAgent',
  @value_name='MsxEncryptChannelOptions',
  @type='REG_DWORD',
  @value=0 ;
GO
Tip  Because all of our instances reside on the same server, this script can be
run from any of the three instances. If your instances are on different servers, then
the first command will run on the MSX and the other two commands should run
against their corresponding TSX. You should also note that the service account
running the database engine will require permissions to the registry keys, for the
script to succeed.
We now use the SQLCMD script in Listing 22-10 to create the PowerShell proxy account
on TARGETSERVER1 and TARGETSERVER2. The script must be run in SQLCMD mode to
work because it connects to multiple instances.
Listing 22-10.  Creating a Proxy
:connect sqlserver\targetserver1
CREATE CREDENTIAL WinUserCredential
        WITH IDENTITY = N'PROSQLADMIN\WinUser', SECRET = N'Pa$$w0rd' ;
GO
EXEC msdb.dbo.sp_add_proxy
                @proxy_name=N'PowerShellProxy',
                @credential_name=N'WinUserCredential',
                @enabled=1,
                @description=N'Proxy to check Browser Service status' ;
GO

EXEC msdb.dbo.sp_grant_proxy_to_subsystem
                @proxy_name=N'PowerShellProxy',
                @subsystem_id=12 ;
GO
:connect sqlserver\targetserver2
CREATE CREDENTIAL WinUserCredential
        WITH IDENTITY = N'PROSQLADMIN\WinUser', SECRET = N'Pa$$w0rd' ;
GO
EXEC msdb.dbo.sp_add_proxy
                @proxy_name=N'PowerShellProxy',
                @credential_name=N'WinUserCredential',
                @enabled=1,
                @description=N'Proxy to check Browser Service status' ;
GO
EXEC msdb.dbo.sp_grant_proxy_to_subsystem
                @proxy_name=N'PowerShellProxy',
                @subsystem_id=12 ;
GO
We can now begin to configure our SQLSERVER\MASTERSERVER instance as an MSX. To
do this through SQL Server Management Studio, we invoke the Master Server Wizard
by opening the SQL Server Agent context menu in Object Explorer and selecting Multi
Server Administration ➤ Make This A Master.
After passing through the Welcome page of the wizard, we find the Master Server
Operator page (see Figure 22-29). On this page, we enter the details of an operator who
will be notified of the status of multiserver jobs.

On the Target Servers page of the wizard, shown in Figure 22-30, we select our target
servers from the list of registered servers in the Registered Servers pane and move them
to the Target Servers pane using the arrows. After highlighting a server in the Target
Servers pane, we can use the Connection button to ensure connectivity.
Figure 22-29.  The Master Server Operator page

Tip  All of our instances appear in the Local Server Groups node of the Registered
Servers pane because they are all on the same server. If the instances that you
wish to be target servers are not local, you can register servers by using the
Registered Servers window, which you can access from the View menu in SQL
Server Management Studio.
On the Master Server Login Credentials page of the wizard, we are asked if a New
Login should be created if required. This is the login that the TSXs use to connect to the
MSX and download the jobs that they should run. If the instances of SQL Server Agent
share the same service account as the MSX, then this is not required.
Now we see a summary of the actions that will be performed on the Completion
page of the wizard before we are presented with a progress window, which informs us of
the success or failure of each task.
Figure 22-30.  The Target Servers page

Creating Master Jobs
You can create a master job in the same way as a local job, with the exception of
specifying the target servers on which it should run. However, a limitation of using
multiserver jobs is that T-SQL job steps cannot run under the context of another user;
they must run under the context of the service account. Therefore, before we convert our
BackupAndCheckBrowser job to be a multiserver job, we must edit it to remove the Run
As Account. We can do this by using the sp_update_jobstep procedure, as demonstrated
in Listing 22-11.
Listing 22-11.  Updating Job Step
USE MSDB
GO
EXEC msdb.dbo.sp_update_jobstep
                @job_name=N'BackupAndCheckBrowser',
                @step_id=1 ,
                @database_user_name=N" ;
GO
Another limitation of multiserver jobs is that the only allowable operator is the
MSXOperator, who receives all notifications for multiserver jobs. Therefore, we also need
to change the DBATeam operator to the MSXOperator operator before continuing. We
can use the sp_update_job procedure to achieve this with the script in Listing 22-12.
Listing 22-12.  Updating a Job
USE msdb
GO
EXEC msdb.dbo.sp_update_job
                @job_name=N'BackupAndCheckBrowser',
                @notify_email_operator_name=N'MSXOperator' ;
GO
We can now proceed to convert our BackupAndCheckBrowser job to a multiserver
job from Management Studio by opening the Job Properties dialog box and navigating
to the Targets page. As illustrated in Figure 22-31, we can use this page to change the

job to a multiserver job and specify the target servers that it should run against from a
list of target servers that have been enlisted using the sp_msx_enlist stored procedure.
After closing the properties dialog box, the job runs against the TargetServer1 and
TargetServer2 instances instead of the MASTERSERVER instance.
Figure 22-31.  Convert to multiserver job
To achieve the same results via T-SQL, we use the sp_delete_jobserver system
stored procedure to stop the job from running against the MSX and the sp_add_
jobserver system stored procedure to configure the job to run against the TSXs. Both of
these procedures accept the parameters detailed in Table 22-3.

In our scenario, we can use the script in Listing 22-13 to convert the job.
Listing 22-13.  Converting to a Multiserver Job
EXEC msdb.dbo.sp_delete_jobserver
        @job_name=N'BackupAndCheckBrowser',
        @server_name = N'SQLSERVER\MASTERSERVER' ;
GO
EXEC msdb.dbo.sp_add_jobserver
        @job_name=N'BackupAndCheckBrowser',
        @server_name = N'SQLSERVER\TARGETSERVER1' ;
GO
EXEC msdb.dbo.sp_add_jobserver
        @job_name=N'BackupAndCheckBrowser',
        @server_name = N'SQLSERVER\TARGETSERVER2' ;
GO
Managing Target Servers
When you configure your MSX, make sure you consider various maintenance activities
against the TSXs. These include polling the TSXs, synchronizing time across the servers,
running ad hoc jobs, and defecting (delisting) TSXs.
We can achieve these tasks in the Target Server Status dialog box, which we can
invoke from the context menu of SQL Server Agent on the MSX by selecting Multi Server
Administration ➤ Manage Target Servers. The Target Server Status tab of this dialog box
is shown in Figure 22-32.
Table 22-3.  sp_delete_jobserver and sp_add_jobserver Parameters
Parameter
Description
@job_id
The GUID of the job that you are converting to a multiserver job. If NULL, then
the @job_name parameter must be specified.
@job_name
The name of the job that you are converting to a multiserver job. If NULL, then
the @job_id parameter must be specified.
@server_name
The server\instance name that you want the job to run against.

On this tab, we can use the Force Poll button to make the Target Servers Poll the
MSX. When a TSX polls the MSX, we are forcing it to download the latest copy of the jobs
that it is configured to run. This is useful if you have updated the master job.
The Force Defection button causes the highlighted TSX to be delisted from the
MSX. After it is delisted, the selected TSX no longer polls for or runs multiserver jobs.
The Post Instructions button invokes the Post Download Instructions dialog box,
where you are able to send one of the following instructions to TSXs:
•
Defect
•
Set Polling Interval
•
Synchronize Clocks
•
Start Job
To synchronize the time on all servers, you would choose the Synchronize Clocks
instruction type and ensure that All Target Servers is selected in the Recipients section,
as illustrated in Figure 22-33. The clocks are then synchronized when the targets next
poll the master.
Figure 22-32.  The Target Server Status tab

In another scenario, there may be a time when we wish to perform an ad hoc run of
our BackupAndCheckBrowser job against TARGETSERVER1. We can do this by selecting
Start Job as the Instruction Type and then choosing our job from the Job Name drop-­
down list. We then use the Recipients section of the screen to select TARGETSERVER1. This
is illustrated in Figure 22-34.
Figure 22-33.  Synchronize Clocks

On the Download Instructions tab of the Target Server Status dialog box, which is
illustrated in Figure 22-35, we see a list of instructions that have been sent to targets. We
can use the drop-down lists at the top of the screen to filter the instructions by job or by
target server.
Figure 22-34.  Start a job on TARGETSERVER1

Figure 22-35.  The Download Instructions tab
Summary
SQL Server Agent is a scheduling engine for SQL Server that allows you to create
powerful maintenance jobs, with decision-based logic, on a variety of schedules. A job
is the container for the tasks that should be performed, and each of these tasks is known
as a step. Each job step can run under the context of a different account and can run
tasks under different subsystems, or types, such as T-SQL, PowerShell, operating system
command, or SSIS package.
A schedule is attached to a job and can be triggered at a specific date and time,
when the CPU is idle, or on a reoccurring schedule, such as daily, weekly, or monthly. A
schedule can also reoccur on an intraday basis, such as hourly, every minute, or even as
frequently as every 10 seconds.
An operator is an individual or team who is notified of the success or failure of jobs
and if an alert fires. Operators can be notified of job status via e-mail, pager, or NET SEND;
however, support for NET SEND and pager are deprecated. For an operator to be notified
by e-mail, Database Mail must be configured so that e-mails can be sent via your SMTP
Replay server.

By default, jobs run under the context of the SQL Server Agent service account.
However, for good security practice, you should consider using proxy accounts to run
the job steps. Proxy accounts map to credentials at the instance level, which in turn map
to a Windows-level security principle. Proxies can be used for all subsystems, except
T-SQL. T-SQL job steps use EXECUTE AS to execute the commands under the context of a
database user. This is configured using the Run As property.
Alerts can be triggered when an error or warning is fired within the Database Engine,
when a WMI event occurs, or in response to a performance condition being met. When
an alert fires, responses include notifying an operator or running a job to resolve an
issue.
Multiserver jobs allow DBAs to run jobs consistently across their enterprise. In a
multiserver scenario, there is a master server (MSX), in which jobs are created and
modified, and multiple target servers (TSXs). The TSXs periodically poll the MSX and
retrieve a list of jobs that they should be running.