SQL Server Multi Server Administration

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'.

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.

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.

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.

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

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.

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

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:

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.

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

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.

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.

Once finished, click the 'OK' button to 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.

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

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

Sources:

http://www.mssqltips.com/sqlservertip/2445/sql-server-multi-server-administration/