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.
