Daniel Calbimonte

How to execute jobs on multiple SQL Servers

February 18, 2016 by

Introduction

In earlier chapters, we explained how to administer multiple servers using the ApexSQL Backup and how to run queries in multiple SQL servers using the SQL Central Management Server. In this new chapter, we will show how to propagate a job from a SQL Server Master Agent Job to a target server.

This feature is called Multiserver Administration. In a multiserver administration, you need a Master Server and one or more target servers. In the master server, you create a copy of the job and then it is copied and executed in the target servers.

The jobs are scheduled to run and are executed in each Target Server.

Requirements

  • SQL Server Installed (2 SQL Servers or 2 SQL Server Instances).
  • 2 SQL Server Agents running.

Getting started

  1. Open the regedit, to edit the Windows registry and change the registry \HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<instance_name>\SQL Server Agent\AllowDownloadedJobsToMatchProxyName to 1 in the master and target Servers.


    Figure 1. Registry

  2. In the SQL Server Management Studio go to the SQL Server Agent, right click and select the Multi Server Administration and select the Make this a Master option.


    Figure 2. The Multi Server option

  3. The wizard to create the master server will be displayed. Press Next.


    Figure 3. The Master Server Wizard

  4. The next window is the Master Server Operator. The operator can receive notification using email, pager address or with a net send address. Specify the address of your preference and press next.


    Figure 4. The address of the operator

  5. In the Target Server window, we will select all the target servers. In this example, we only have one target server, but in the real life, we may have several. Press the Add connection button to add a new target server.


    Figure 5. Target Servers

  6. Connect to the SQL Server that will be used as a Target Server.


    Figure 6. Connection information

  7. If everything is OK, you will receive a success message related to the compability between the 2 servers.


    Figure 7. Server Compatibility Check

  8. You can view the report, save the report in a file, in the clipboard or send the report by email. In this example, we will select the first option.


    Figure 8. Report options

  9. In this option, the report will show the result about the version compatibility between the master and target servers.


    Figure 9. Compatibility Report

  10. When you finish the Wizard, the MXOperator (the operator of the master Server) will be created. The second step is the verification that the SQL Agent is running, and then the verification of the agent startup account of the target server. Finally, the target server is enlisted in the master server.


    Figure 10. Actions to enlist the target server and create the MSX Operator.

  11. You will notice that in the Master Server it says (MSX). It means that it is a Master Server. Right click and select the New Job option.


    Figure 11. Creating a new job.

  12. We will create a backup in the target server. If you have several target servers, the backup will be created in all of them.
  13. The creation of job will be similar than the local jobs. You just need a name and optionally a description.


    Figure 12. Creating a backup

  14. Go to the Steps page and click the New button to create a new Job.


    Figure 13. Creating new steps

  15. Specify the name and the command. In this example, we are backing up a database named db2 in the db2.bak file. You can modify the T-SQL sentences to your own needs:

    BACKUP DATABASE [db2] TO DISK = N’C:\Backup\db2.bak’ WITH NO FORMAT
    GO


    Figure 14. The step to create backups

  16. This is the key section, go to the Targets page and select the target multiple servers where you want to run the job. In this example, we have one target server. Select the option target to multiple servers and check the target server(s) where you want to run the job.


    Figure 15. Selecting Target servers

  17. As you can see, the job was created in a folder named multi-server jobs. Right click and select the start Job at Step.


    Figure 16. Start Job at Step

  18. If everything is OK, you will receive a Success message that the job was posted remotely for execution.


    Figure 17. Success execution message.

  19. A new backup will be created in the target server(s). As you can see, working with master and target servers is a straightforward process.


    Figure 18. The SQL Server backup created.

Some common problems

A very common problem when you are creating the target server (step 10) is the following message:


Figure 19. Error Message when the target server is created.

This error is related to the encryption between the master and target server.

If you have this error, verify that the registry
\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<instance_name>\SQL Server Agent\MsxEncryptChannelOptions matches in the master and the target servers. If not, modify the registries. This registry controls the encryption between the master and target server. The value 0 means no encryption. 1 means to encrypt without a certificate and 2 with a certificate.


Figure 20. The MsxEncryptChannelOptions registry.

Some administrative options

In the master server (MSX), it is posible to administer the Multi server configuration. Right click on the SQL Server Agent and in the Multi Server Administration, select Manage Target Servers.


Figure 21. Multi Server Administration

The target server status tab shows the list of target servers, the local time

(the data and time of the target server in the local time) and the last time that the target server polled the master.

The force poll button, forces the poll of the selected target server to the master server. The Force Defection button allows defecting the target server and the post instruction allows posting the instructions.


Figure 22. The target server status

The Download Instruction shows the operations sent to the target servers, specifying the Object Name, Date posted and the Date that it was downloaded.


Figure 23. The Download Instructions tab

When the proxy is required

If a proxy is necessary, you will need to create a credential and then associate with the proxy. Make sure that the proxy name is the same in the Master and Target servers.

How to remove the target server

The option to remove the target server from the Multi-Server. You can do that in the target server.

Go to the SQL Server Agent, right click and select Multi Server Administration>Defect


Figure 24. Defect the Multi-Server system

Conclusion

As you can see, creating a master server and target servers is a straightforward process. Once configured, you can send jobs to multiple servers at the same time.

References

For more information about Master and Target servers, refer to the following links:

Some useful stored procedures:

Some useful system views:

 
Daniel Calbimonte

Daniel Calbimonte

Daniel Calbimonte is a Microsoft Most Valuable Professional, Microsoft Certified Trainer and Microsoft Certified IT Professional for SQL Server. He is an accomplished SSIS author, teacher at IT Academies and has over 13 years of experience working with different databases.

He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs. He is also a writer for SQL Server training material for certification exams.

View all posts by Daniel Calbimonte
Daniel Calbimonte
SQL Server maintenance

About Daniel Calbimonte

Daniel Calbimonte is a Microsoft Most Valuable Professional, Microsoft Certified Trainer and Microsoft Certified IT Professional for SQL Server. He is an accomplished SSIS author, teacher at IT Academies and has over 13 years of experience working with different databases. He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs. He is also a writer for SQL Server training material for certification exams. View all posts by Daniel Calbimonte

2,212 Views