For our front office alone, I manage about 15-20 SQL database servers. Add to that other database servers that I coordinate with other sister companies under umbrella company, I say, total development database servers would run up to total of 25-30 servers.
You would ask, how do I manage the scheduled jobs and server maintenance plans? Easy. Or should I say SQL Server made this task easy for thousands of DBAs out there. I use the MSX and TSX feature in SQL Agent. I was very happy that this feature came out in SQL Server 2008. I am not sure though how far back it came out. I just started using it in SQL Server 2008.
The MSX and TSX, or sometimes called also as master and target servers, is surprisingly also easy to setup. You will just need an additional SQL database server to serve as your Master server. As the name implies, this Master server will contain all the scheduled jobs and maintenance plans that you need scale out to your Target servers. The Target server maybe tens or more SQL database servers that will be targeted by the Master server to run the jobs.
As general rule, you should setup a service account for SQL Agent in your servers and it would be great if you can use a single account across all servers for the SQL Agent service. Then you have to manage permission for just a single account. As a good practice make sure also that this account will be granted the least previleges required for the service to run.
Another thing that you will need to do is to bypass the level of security between the master/target communication during validation of the MSX server. On the server that you want to enlist as a target server, run regedit to open registry editor. Then go to expand HKEY_LOCAL_MACHINE > expand SOFTWARE > expand Microsoft > expand Microsoft SQL Server > expand MSSQL10_50.MSSQLSERVER (It means i’m running SQL Server 2008 R2 and installed MSSQLSERVER instance, it could be different in your case) > expand SQLServerAgent. On the right pane for the values, look for MsxEncryptChannelOptions and right click. Modify and change the value to 0. Usually this value is set default 2. The value of 0 means we are disabling this encryption on the TSX.
For more information on Encryption Options in the target servers, click here.
Creating a MSX Server
To make a database server the Master or the MSX server, go to SQL Agent right click on Multi Server Administration and click on Make this Master. This will launch the wizard as below,
Enter the email for alerts to the MSX operator, enter the target servers you want to be enlisted in this MSX server. After the wizard is completed and it is successful, you should be able to see (MSX) beside your SQL Agent, and from your Jobs menu, there is menu for Local Jobs and Multi-Server Jobs.
Enlisting Target Servers
Now that we have prepared a server as our Master server, next we start adding target servers to this Master. To enlist a database server to the MSX server, right click on the SQL Agent> Multi Server Administration > Make this a Target. This will launch the wizard for enlisting the server. See below screen shot of the wizard.
Click Next, enter your MSX Server, SQL Server will check compatibility then if it passes then you’re good to go.
Once you are successful in enlisting your database to target servers, refresh the SQL Agent menu in your SSMS. You should be able to see beside it this (TSX: <MSX servername here>) noting that the server is now enlisted. See below.
Scaling Out Your Maintenance Plans
As we have successfully setup a MSX and TSX servers, we are now ready to create maintenance plans in the MSX server and scale this out to the TSX servers.
To start scaling out your maintenance plans to your target servers, create a maintenance plan in your MSX server. Then on the part of the editor window click on ‘Servers’, see similar screen below
Then add all your target servers you wish this maintenance plan will run. Save the maintenance plan. On your target server, refresh the SQL Agent and go to Jobs menu. The scheduled jobs created from the MSX server should appear in your TSX server jobs with (Multi-Server) note beside it. See below.
All maintenance plans from the MSX server cannot be updated or deleted in TSX server.
If you wish to remove the target server from being enlisted, right click on the Multi Server Administration under the SQL Agent menu and click on ‘Defect’. You will need to confirm that you are sure you want to defect this target server.
Click Yes to confirm and refresh the SQL Agent in your target server.
Every time there is a new database server, I just enlist this new server, go to MSX server update the maintenance plans to scale out my maintenance plans to this new server and done! To make this more effective in your database administration, I suggest you add email notifications for any failed maintenance plans or other alerts in your MSX server.