SQL Server Multi Server Administration

By:   |   Comments (8)   |   Related: > SQL Server Agent


Problem

I have multiple SQL Servers that need the exact same SQL Server Agent Job created. I know I can script a SQL Server Agent Job out and run the T-SQL script on multiple servers or bounce from server to server creating the SQL Server Agent Jobs, but isn't there an easier way? Check out this tip to learn more.

Solution

I seem to notice a lot of people do not use or even know about Multi Server Administration for their SQL Server Agent Jobs and Maintenance Plans. Multi Server Administration can be really useful when you need to create and run the same jobs or maintenance plans across numerous SQL Server instances. Let's walk through setting up Multi Server Administration for SQL Server.

Here's a basic look at how to setup Multi Server Administration. My environment has two separate VM's running in VM Workstation with Windows 2008 R2 Datacenter Edition and SQL Server 2008 R2 Enterprise named Principal and Mirror.
First thing I'm going to do is register both servers in SQL Server Management Studio (SSMS). If you do not see the Registered Servers tab in SSMS navigate to 'View' | 'Registered Servers' or press 'Ctrl + Alt + G'.

RegisteredServers tab in ssms

Once both servers are registered I can start configuring Multi Server Administration. To do this right click on 'SQL Server Agent' on your master server and choose 'Multi Server Administration' | 'Make this a Master...' to begin the process.

sql server agent

Click Next on the Welcome for the Master Server Wizard.

The next screen is where we can configure an operator. Just like when we create an operator for local jobs, we can create one for our Multi Server jobs. You can also leave this section blank if you prefer not to have an operator although I would not recommended this practice. Once completed, press the 'Next' button to continue.

master server wizard

On the Target Servers screen is where we specify our target servers. You can have multiple targets, but for this example I'm only using one. You can select target servers using your 'Registered Servers' from your SSMS session or you can add a connection by clicking the 'Add Connection...' button. I'm going to select Mirror as my target and click the 'Next' button to continue the process.

target servers using registered servers from your ssms session

Next, SQL Server will check that the versions of the master and target server are compatible as shown on the screen shot below.

sql server will check that the versions of the master and target servers are compatible

On the Master Server Login Credentials screen security is checked between the master and target servers. If needed, the check box can be selected to create a login and assign it rights to the master server. Click the 'Next' button to continue the process.

master server login credentials

Once you click the 'Finish' button on the Complete the Wizard screen it will start the configuring the servers.

enlist tsx progress

Be aware that a common problem exists during configuration and the following error (The enlist operation failed (reason: SQLServerAgent Error: The target server cannot establish an encrypted connection to the master server 'Server Name'. Make sure that the MsxEncryptChannelOptions registry subkey is set correctly on the target server.) (Microsoft SQL Server Error: 22026)) may be thrown as shown on the screen shot below:

microsoft sql server error 22026

As the error indicates, we need to make a registry change on the target server. To accomplish this, logon to the target server and navigate to 'Start' | 'Run' | type 'Regedit'. As a best practice, be sure to backup your registry before making changes. From the Registry Editor browse to 'HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\\SQLServerAgent and change the MsxEncryptChannelOptions' value to '0'. Save the change and close the Registry editor.

microsoft sql server\\sql server agent

Once this is complete, run the wizard from the master server again and it should finish successfully as shown below.

run the wizard from the master server again

To verify everything was configured correctly, connect to both servers and beside SQL Server Agent it should have either '(MSX)' or 'TSX:' as shown below.

beside sql server agent it should have (msx) or tsx

To set up a Multi Server Job under SQL Server Agent you must connect to the master server in SSMS and navigate to 'SQL Server Agent' | 'Jobs' | 'Multi-Server Jobs". Configure the job as needed and on the 'Targets tab' select the target servers you want this job to execute on.

in ssms navigate to sql server agent/jobs

Once finished, click the 'OK' button to save job to both servers.

save job to both servers

The same general process applies to Maintenance Plans, connect to the master server and navigate to 'Management' | 'Maintenance Plans' | right click and choose 'New Maintenance Plan...'. This process starts by naming the job as shown below.

connect to ssms and navigate to management/maintenance plans

Configure the Maintenance Plan as needed and on the Servers button select the servers you want this Maintenance Plan to execute on.

select servers you want maitenance plan to execute on

Once finished, click 'Save and Close' to save both Maintenance Plans to both servers as shown below.

save both plans to both servers
Next Steps
  • Here are a few other notes about Multi Server Administration
    • Once the master server is configured you can monitor the target servers or add more target servers by going back into the Multi Server Administration on the master server and selecting 'Manage Target Servers' or 'Add Target Servers'.
    • When monitoring the target server status, you can view the target servers, 'Force Poll' the target server, 'Force Defection' against the target server, Post Instructions which include 'Defect', 'Set Polling Intervals', 'Sync Clocks' and 'Start Jobs'.
    • From the target server's Multi Server Administration screen in SSMS you can only 'Defect' the target server.
  • As you deploy the same SQL Server Agent Jobs or Maintenance Plans across multiple servers, be sure to consider the automation options outlined in this tip.
  • Check out all of the SQL Server Agent tips.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Brady Upton Brady Upton is a Database Administrator and SharePoint superstar in Nashville, TN.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Monday, October 10, 2016 - 4:40:39 AM - praveen Back To Top (43522)

Hi  Ashutosh Verma,

 

well while configuring master server please read the description carefully, there we have to add our target server instead of that you are giving master server as a target server so that't the reason you are not able to configure.

 

 

 

Tuesday, February 2, 2016 - 2:49:47 AM - Ashutosh Verma Back To Top (40569)

 

 Hi , 

i did the changes as told above , MsxEncryptChannelOptions' value to '0'. (1) in all target servers (2) in all target and master server , but still getting the same error please help.

 

Tuesday, December 1, 2015 - 5:36:12 AM - Sammo Back To Top (40168)

to give each target server its own job scdedule, during the creation of a new SQL Agent job, at the Schedules tab, click 'Pick...' instead of 'New...', and select the desired pre-defined schedule.

 

to define a schedule, please right click on 'Jobs' folder under the the SQL Server Agent node icon in Object Explorer in SSMS, and select 'Manage Schedules'

for TSQL approach, check out https://msdn.microsoft.com/en-us/library/ms173737.aspx 

Wednesday, June 17, 2015 - 9:12:17 AM - Earl Cooper Back To Top (37942)

Really good article - Thanks for the screen shots... One question - How do I give each target server its own job schedule? We don't want all the backup jobs firing at the same time.

Tuesday, September 16, 2014 - 7:29:54 PM - Kathi Back To Top (34553)

Is the multi-server administration only available on the Enterprise edition and if so, can the Master server be Enterprise but the Target servers be Standard edition or must the Target servers be Enterprise as well?  I noticed in your example both servers seem to be the same version.  I would really like to implement this functionality but if all target servers need to be Enterprise edition that would be too costly.   Thanks for you help.  

 

Monday, April 8, 2013 - 9:26:43 AM - Suzanne Back To Top (23228)

Isn't it dangerous to change this registry key? Microsoft does not recommend changing it. Any thoughts?

http://msdn.microsoft.com/en-us/library/ms365379.aspx

This post was very helpful. I was looking into multi job administration for a while now.

Friday, February 1, 2013 - 5:41:43 AM - Suzanne Back To Top (21859)

This was exactly what I was looking for! Thanx a lot! No idea why this multiserver administration isn't used more.

Tuesday, September 20, 2011 - 9:22:59 AM - Emily Back To Top (14696)

Great article!  I have a question I've been searching for and haven't found ANY information on it (surely I can't be the only one..)

Do you know any way to create multiple schedules on a master server that apply only to designated targets? For example, you would have one generic MSX full backup job- then schedule it for Mondays on TSX A, Tuesdays on TSX B, and so on. Let me know if you have any insight on how (or even if) this can be done.. Thanks!

 

 














get free sql tips
agree to terms