Multiserver Task Administration

One of the many features introduced with SQL Server 7.0 is the multiserver task capability. With it, you can set up a task, such as a backup or database maintenance, on one server and have that task copied to, and run on, multiple servers. Not only is this feature a big time-saver but it conveniently puts all reports in one place. Let's look at multiserver tasks, how to set them up, and when they can help a DBA. (Note that this information applies equally to SQL Server 2000 and 7.0.)

Defining the Roles
To use multiserver tasks, the first step is to set up one of your servers as the master server, on which you build the tasks and from which you distribute task instructions to the target servers. The target servers run the jobs and report their status back to the master server.

To set up a master server, you need at least one other SQL Server (7.0 or later) system on the network to act as a target server. You can add more target servers later as your network grows or when it's convenient. You need to register in Enterprise Manager all servers that you plan to use as target servers. If Enterprise Manager hasn't registered any systems that you want to use as target systems, you can register them as part of the process of enlisting those systems as target servers.

A target server can only be a target for, and receive task instructions from, one master server at a time. So, because the target server can't modify tasks, a minimal level of security exists. For example, if you have a branch office with no onsite DBA, you can remotely control that branch's administrative tasks from the master server, but people at the branch can't change the tasks from the target server.

Before a server can become the master server—or a target server, for that matter—it must be running either SQL Server Standard Edition or SQL Server Enterprise Edition. If you try to make a server that's running SQL Server Personal Edition the master server, you won't get an error message, but that server won't be able to enlist a target server. If you try to enlist a target server that's running SQL Server Personal Edition, instead of SQL Server Standard Edition or SQL Server Enterprise Edition, you'll get an error message.

Setting Up the Master Server
From Enterprise Manager, expand the hierarchy so that you can see the Management, SQL Server Agent item. Right-click SQL Server Agent and select the option for Multi Server Administration. Select Make this a Master to start the Make MSX Wizard. Note that the wizard's welcome screen, which Figure 1 shows, states that the wizard will create on the server an operator called MSXOperator, which is the only operator that multiserver jobs notify. So, you should set up MSXOperator with the email, pager, and Net Send addresses of someone who can fix any problems on the master server, such as a senior DBA.

The wizard shows you a list of all the registered servers on your network and lets you select, or enlist, one or more of them as target servers. You have to enlist at least one target server to make the current server a master server. The wizard also prompts you for each target server's description, which might include that server's department or function, such as finance, engineering, or customer accounts.

SQL Server creates a login account on the master server—although the Multi Server Setup Wizard doesn't mention this account—that the target server uses to connect to the master server and get instructions. The new account is servername_msx_probe, where servername is the name of your target server. Note that the wizard says it can set SQL Server security to mixed mode. In fact, the wizard will set security to mixed mode, if that isn't already the chosen mode. So if you haven't yet changed the systems administrator (sa) password because you're using Windows-only security, you should do so now. The login account that the wizard creates is a SQL Server login account, not a Windows NT account; that's why the security must be set to mixed mode. The servername_msx_probe account has permission to connect to only the msdb database, which contains all job information.

When you set up multiserver administration, the wizard adds a user-defined role, the TargetServersRole, to the msdb database on both the master server and the target servers. The TargetServersRole has limited permissions to access system tables and stored procedures relating to the multiserver tasks.