SQL Server Multi Server Administration


By:   |   Updated: 2011-07-26   |   Comments (8)   |   Related: More > 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.


Last Updated: 2011-07-26


get scripts

next tip button



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

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





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

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 02, 2016 - 2:49:47 AM - Ashutosh Verma Back To Top

 

 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 01, 2015 - 5:36:12 AM - Sammo Back To Top

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

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

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 08, 2013 - 9:26:43 AM - Suzanne Back To Top

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 01, 2013 - 5:41:43 AM - Suzanne Back To Top

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

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!

 

 



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools