Introduction

Multi server administration allows you to create jobs and maintenance plans once, on a server designated as your master server. All jobs and maintenance plans created on the master server can then be executed on any server enlisted as a target server to your master server. Job statuses can be checked from the master server. In effect you are centralizing your administration of automatic processes onto one SQL Server.

The usefulness of multi server administration to you will depend largely on how many SQL Servers you have and how many jobs and/or maintenance plans are the same on more than one of them.

This article is designed to give you a good understanding of multi server administration so that you can implement it on your SQL Servers. Everything described in this article was tested extensively on SQL Server 7.0 with Service Pack 3. It is likely that most of what I explain here can be used on SQL Server 2000, but you would have to test this yourself.

Configuring Multi Server Administration

You must have at least 2 SQL Servers to configure multi server administration. One server will be designated a master or MSX and the other will be designated a target or TSX. I am not aware of a limit to the number of target servers one master server can have. A SQL Server designated as a target server can only be enlisted to one master server.

To configure multi server administration double click the Management folder, right click SQL Server Agent, select Multi Server Administration, and choose Make this a master. This will activate the wizard that will create your master server and enlist the target server(s) you designate. Complete the steps in the wizard to configure multi server administration. Once complete you are ready to add jobs and maintenance plans.

The first screen of the wizard (called Make MSX Wizard) requests contact info for an operator called MSXOperator. This will be the only operator that can receive notifications from multi server jobs. Also, if you plan to be notified by e-mail then each target server that you want to send you an e-mail must have a mail profile for the SQL Server Agent. The subject of each e-mail will identify which target server is sending the message to you.

I don’t know if clustered servers can be included in multi server administration. I have seen one distinct difference when trying to include a clustered server. The list of tasks the wizard will perform includes starting the physical SQL Server Agent which doesn’t run in a clustered environment; the clustered server uses a virtual SQL Server Agent. Since the only clustered servers we have are production servers I decided not to complete the set up and exited the wizard.

Managing Multi Server Administration

Now to manage multi server administration you follow the same directions as I gave above to launch the wizard to configure it. Except that now you won’t see the option Make this a master. Double click the Management folder, right click SQL Server Agent, select Multi Server Administration, and Manage Target Servers. You’ll see a window open up with a list of all your target servers. You’ll notice there are two tabs. The current one is Target Server Status and the other one is Download Instructions.

Target Server Status

There are three buttons on the Target Server Status tab. The Force Poll button can be used to force the highlighted target server to poll for pending instructions. Doing this also resets the time until the next poll will take place (I’ll discuss more about this in the Set Polling Interval section).

The Force Defection button can be used to remove a target server from multi server administration. Doing this from the master server will leave the multi server jobs on the ex-target server. You can then remove them from the ex-target server by right clicking its SQL Server Agent, selecting Multi Server Administration, and Defect From MSX. When I forced the defection at the target server all reference to it was removed from the master. It is best to not force defection when you can issue a defect instruction that will automatically clean up most everything associated with multi server administration (the few things a normal defection doesn’t clean up will be discussed in other parts of this article).

The Post Instructions button allows you to instruct a target server to defect, change the polling interval, synchronize clock with the master server, and instruct a job to start. These instructions are available to choose from after clicking this button.

Instructing a target server to defect is much cleaner than forcing it to defect. The instruction will be downloaded and executed the next time that target server polls the master. Upon receiving the defect instruction the server will remove itself from the list of target servers and clean up almost everything associated with multi server administration. By this I mean it will delete all multi server jobs from itself, however if a multi server maintenance plan created those jobs, you will still find the name of the maintenance plan when you view your maintenance plans. Upon opening the plan you’ll find that none of the tabs have anything checked off. The following two queries will delete the remains of these multi server maintenance plans:

USE msdb
 
DELETE mpd
FROM sysdbmaintplan_databases mpd
WHERE mpd.plan_id IN
(
SELECT mp.plan_id
FROM sysdbmaintplans mp
LEFT JOIN sysdbMaintplan_jobs mpj ON mpj.plan_id = mp.plan_id
LEFT JOIN sysjobs j ON SUBSTRING(name,CHARINDEX('''',name,1) + 1, LEN(name) - 1 - CHARINDEX('''',name,1)) = mp.plan_name
WHERE mpj.plan_id IS NULL AND mp.plan_name <> 'All ad-hoc plans' AND j.job_id IS NULL
)
 
DELETE mp
FROM sysdbmaintplans mp
LEFT JOIN sysdbMaintplan_jobs mpj ON mpj.plan_id = mp.plan_id
LEFT JOIN sysjobs j ON SUBSTRING(name,CHARINDEX('''',name,1) + 1, LEN(name) - 1 - CHARINDEX('''',name,1)) = mp.plan_name
WHERE mpj.plan_id IS NULL AND mp.plan_name <> 'All ad-hoc plans' AND j.job_id IS NULL

The Set Polling Interval instruction allows you to change the polling interval from the default, which is 60 seconds. Polling interval can be set different for each target server. The interval is set in seconds and can be between 10 and 28800 (8 hours). To make the new polling interval take effect immediately you can click on the Force Poll button after giving the new polling intervals. Any time you force a target server to poll for new instructions you reset the poll interval to start counting from the time you forced that target server to poll. For example, if you have a target server with a polling interval of 10 seconds and it polls for new instructions at 10 seconds past the minute and then 20 seconds past and so on. If you force it to poll for instructions at 15 seconds past the minute then it will continue polling every 10 seconds from that time so that the next time it polls now will be 25 seconds past the minute and then 35 seconds past and so on. This can be useful if you want to make sure no target server polls at the same time as any other target server and thus minimize network traffic to only one target server at a time.

I have seen that when you defect a target server and then enlist it again to the same master or a different master the polling interval will be the number of seconds you set it to last before you defected the server. So, if you set the polling interval to 10 seconds, defect the server, and then enlist it as a target again it will still have a polling interval of 10 seconds. This value doesn’t reset to the default of 60 seconds.

Now let me discuss network traffic. I have only been able to test multi server administration on a 100 MB Ethernet LAN using switches to connect all the servers to the network. I used 1 master server and 3 target servers and set all of them to poll the master every 10 seconds. Then I watched how much network traffic was generated. The bandwidth used was so small as to be negligible. I believe there would be little or no problem running this on any network as long as you increase your polling interval for smaller bandwidth. For example, I believe that polling every hour or every two hours across a T1 line would be no problem as the polling only lasts a second or less.

The last instruction you can issue is to have a multi server job start. Multi server jobs will be discussed below in another section of this article.

All these instructions can be sent to one or more target servers or all target servers.

Download Instructions

The Download Instructions tab allows you to review the instructions you have issued for each target server. In the target server drop down you can select an individual target server or all target servers. For the job drop down you can select all jobs or an individual job. Upon making selections the display area changes to show the instructions associated with the selected target server(s) and selected job(s).

In the display you will see the target server name, operation, object name, date posted (which is the date you issued the instruction), and the date downloaded (which is when the instruction was polled by the target server and executed).

Adding Target Servers

It is a simple matter to add more target servers to a master server after multi server administration is set up. Simply right click the SQL Server Agent of the master server where you plan to add a target server, select Multi Server Administration, and Add Target Server. The list you see will be the SQL Servers registered with Enterprise Manager on the computer you are logged into. The list will include servers that are target servers to other servers and other master servers. However, you can’t enlist master servers nor can you enlist servers that are targets to a different master. You must first defect the target server from the other master before enlisting it with another master server. Each target server can have only one master server.

You can also enlist a target server by going to the server you plan to make a target server. Right click its SQL Server Agent, select Multi Server Administration, and Make this a Target. This will launch the Make TSX Wizard. When prompted enter the name of the master server you want this target to be enlisted to.

Multi Server Maintenance Plans

After you have set up multi server administration you can create multi server maintenance plans. This must be done from the master server. The creation process is identical to how you would do it on a server not part of multi server administration except that the first screen of the wizard will allow you to designate which servers this plan will run on. You can select one, multiple, or all target servers and/or local. After creation you’ll have to wait until each target server polls the master for it to get the maintenance plan or you can force each one to poll. Whenever you make a change to the maintenance plan instructions will be posted and polled by the target servers. All changes made to this plan will also be reflected in the jobs on the local server if you included the local (master) server in the maintenance plan.

I have noticed that if I only make a change on the optimization tab for the change free space percentage it doesn’t keep this change nor post it for the target servers. However if I change the Shrink database when it grows beyond size and the percentage mentioned above then both changes will be posted for the target servers. The best way to make sure the change you made will be posted is to reopen the maintenance plan and see if the changes you made are there. If they are then you can go see the posted instructions on the target server tab for multi server administration.

Multi Server Jobs

Upon successful creation of a master server you will notice a plus sign next to the jobs icon in the left window of Enterprise Manager. When you click on this plus sign you will see to areas for jobs. One is labeled Local Server Jobs and the other Multi Server Jobs. Jobs you want to run on the master must be created in the Local Server Jobs area.

Multi server jobs created on the master can’t run on the local server. If you created a multi server maintenance plan (or create one now) that runs on the targets and the local (master) server you can open the Local Server Jobs folder and see the jobs for that maintenance plan that will run on the master server. Now look in the Multi Server Jobs folder and you will see the same jobs for the same maintenance plan. However, these will only run on the target servers.

Creating jobs is the same as you would for a SQL Server that is not part of multi server administration. The only difference here is that when you create a job in the Multi Server Jobs folder you can select one or more target servers on which you want this job to run. When you open the job properties you’ll notice in the lower right corner a button labeled Change (normally grayed out for local jobs). Click it to assign target servers to the multi server job.

You can right click any multi server job (on the master server) and select job status to see the last execution status for the job on each target server. To see the entire history for a job on a particular target server, simply click on the target server and then click the button called view remote job history. Now you will be looking at the job history as if you were looking at it on the server itself. This way you can review the job history for multiple servers without having to open each server and the various folders to see each job on each server. The more servers you have the more useful this becomes as you have to click around the screen less and connect to one server instead of however many servers you have to view the status and history for each server. There is a radio button that allows you to view status by job or by server.

You can start a particular multi server job on all or selected target servers by right clicking the job, selecting start job, and then deciding if you want to start it on all target servers or only a few.

The target server status button takes you to the same window as described above in managing multi server administration.

The Synchronize Jobs button is only needed if you must restore the msdb on the master server and there were changes to multi server jobs that occurred after the last backup. This button will resynchronize those changes.

Security

One nice thing about multi server administration is that the multi server jobs and MSXOperator on each target server can’t be modified from the target server. All changes must be done on the master server. This ensures that you or any other administrator doesn’t accidentally (or intentionally) change or delete those jobs. This feature also allows you to know that all the multi server jobs are identical.

Removing Multi Server Administration

To remove multi server administration completely you must defect all target servers. When the last target server successfully defects the master server is uninstalled. Unfortunately, a number of multi server jobs and the MSXOperator get left behind on the ex-master and an empty shell for multi server maintenance plans gets left on each ex-target server. I provided the SQL commands to remove those orphaned plans from the ex-target servers earlier in this article. Here are the SQL commands you must execute on the ex-master server to remove what got left behind on it:

USE msdb
 
DELETE j
FROM sysjobs j
WHERE j.category_id = 2  --This is the ID for multi server jobs
 
IF (EXISTS (SELECT name FROM msdb.dbo.sysoperators WHERE name = N'MSXOperator'))
 ---- Delete operator with the same name.
  EXECUTE msdb.dbo.sp_delete_operator @name = N'MSXOperator'

On the other hand if you leave the multi server jobs there and make the ex-master server a master server again then the jobs will automatically move to the multi server jobs folder and you can then simply add target servers to them and they’ll be ready to use again. Jobs in the category [Uncategorized (multi-server] can not be run on an ex-master server until the category is changed to a local category. So you must either run the above delete statement to get rid of them or change all of them to local jobs if you plan to keep using them.

Summary

In this article you have learned how to set up multi server administration, multi server maintenance plans, and multi server jobs. You have seen how useful or not useful multi server administration will be for you with your current environment. You will also have a good working knowledge of how to manage multi server administration. I welcome your comments on this article.