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.

On the master server, expand the Enterprise Manager hierarchy so that you can see SQL Server Agent, Jobs. After you configure the server as a master server, the Jobs item splits into Local Server Jobs and Multi Server Jobs, as Figure 2 shows. The SQL Server Agent now has (MSX) next to it, meaning that this SQL Server Agent is running on the master server. You don't need to do anything further to set up the target server; setting up the master server automatically configures the target server. On the target server, SQL Server Agent now has (TSX) next to it and the master server's name. The SQL Server Agent (TSX) entry also shows the Jobs item split into Local Server Jobs and Multi Server Jobs.

Adding and Removing Target Servers

As your network grows, you might need to add more target servers to provide the additional resources that a growing network needs. To add target servers from the master server, follow the same steps you used to set up the master server initially. Note that when you right-click SQL Server Agent and select Multi Server Administration, you now have the options of managing or adding target servers. If you're connected to the new target server in Enterprise Manager, you can right-click the SQL Server Agent, select Multi Server Administration, then choose the option Make this a Target.

Keep in mind that when I talk about making changes on any SQL Server system, you don't have to be physically present at the server. As long as you can register the system in Enterprise Manager and you're a member of the system admins role, you can administer the system and make changes remotely. So, when I talk about making changes at the master server or at the target server, I'm referring to the server you're connected to in Enterprise Manager.

Removing a target server is called defecting a target server. You can remove a target server from the master server's list of target servers through the Multi Server Administration, Manage Target Servers option, then choosing the Force Defections button. Or you can remove it from the target server's Enterprise Manager, again through Multi Server Administration. On the target server, the only administrative option you have is to defect.

Setting Up a Multiserver Task

Adding a new job or task in a multiserver environment is done in much the same way that you would typically add a job. Right-click Multi Server Jobs and select New Job to get the New Job Properties dialog box. The difference is that with multiserver tasks, you must pay attention to the options on the General tab, which Figure 3 shows, on which you can specify whether this job is to Target local server or Target multiple servers. (For a discussion of how to set up jobs and the associated operators and alerts, see Certifiably SQL, "Lab Files: Defining Jobs," May 2000 and Certifiably SQL, "Lab Files: Alerts and Operators," April 2000.)

To select the servers you want this job to run on, click Change to get a list of target servers. You can send the job to all the target servers or to a subset of target servers, as Figure 3 shows. You might need to select a subset of target servers because each server could have its own set of unique databases, so you must create jobs to target each database individually. Another reason you might pick a subset is that a job's schedule applies to all the servers to which you assign the job, and you might want the job to run at different times on different groups of servers. Keep in mind that the target server downloads the scheduling instructions and executes them locally. So, for example, if you schedule a backup of the master database for 1:00 a.m. on Sunday, each target server would run the job at 1:00 a.m. local time. That's why you can check the time on each target server from within the Multi Server Administration dialog boxes.

While the Job Properties dialog box is open, click the Notifications tab. When you select a box to email, page, or Net Send for this job, note that the operator to notify automatically becomes the MSXOperator. Even if you've defined other operators, you can't select them.

The target servers actually drive the downloading of instructions to run multiserver tasks, polling the master server every minute (by default). This constant polling increases the master server's load, so you wouldn't typically want to use one of your essential production servers as the master server. When a target server finds a new set of instructions assigned to it, the target server downloads the instructions and schedules the job for the specified time. Possible instructions include setting up a job, deleting a job, or changing the polling interval. The job runs on the target server at the scheduled time, then the target server sends a message to the master server that reports the job's success or failure.

Checking Job Status on Multiple Servers

One of the major benefits of multiserver tasks is that you don't have to check each job on each server to see whether a particular task ran. The target servers all report in to the master server, so you can quickly see which jobs have run, on which servers, and whether they succeeded or failed. A DBA can check a job's status either by examining a target server's results to see which jobs ran on that server or by using the Job Execution Status dialog box, which Figure 4 shows, on the master server to look at which servers ran which jobs. To reach the Job Execution Status dialog box, select Jobs, right-click the desired job, and select Job Status, as you would for a local server job. The Job Execution Status dialog box displays a list of servers, the time at which the job last ran on each server, and whether the job was successful.

The Job Execution Status dialog box helps you easily spot a server on which the job hasn't yet run or has failed. If a particular server seems to have a problem, you can switch the view from Show status by Job to Show status by Server to see whether all the jobs sent to that server are running into problems. If so, the problem might be with the server, not the job.

You can click View Remote Job History to view the same information as examining the job's history directly on the target server through Enterprise Manager. For more information about the target servers, click Target Servers Status to see a list of servers, as Figure 5 shows. The Target Servers Status tab includes for each target server the current local time, when it last polled the master server, whether it's offline, and whether any unread instructions are still waiting on the master server. The Download Instructions tab shows a list of jobs posted to the target servers, including each job's posting date and the date and time that the target server downloaded each job. When a job shows up on a target server as having been pushed from the master server, you can view the job's history at the target server if you're physically at the server or connected to it through Enterprise Manager.

When to Use Multiserver Jobs

Multiserver jobs work well when you need to run the same job on multiple servers—for example, when you need to back up the system's databases or multiple replicated copies of a database on different servers. As federated databases grow in popularity, you might find even more uses for multiserver jobs. In a federated database, a table's data is partitioned among multiple databases on multiple servers. If a federated database has the same name on each server—but you can't assume that it does—a multiserver job is a great way to back up an entire distributed table. If you have any influence on design, make sure that the database uses the same name on each server instead of, for example, Customers1, Customers2, and so on.

Multiserver jobs aren't necessarily as useful when you have several servers, each with a different set of databases. You couldn't set up the same job for different databases unless you wrote a script and set up a job to cycle through the user databases one by one on each server.

Because Microsoft decided not to let SQL Server Personal Edition be a master server or a target server, the company has eliminated a couple of possible scenarios in which multiserver jobs could be very useful. For example, you can't set up a computer with SQL Server and Windows 2000 Professional to act as an administrative center and push jobs to the production servers. Also, you can't use the multiserver job functionality to control jobs for a group of users who have SQL Server Personal Edition on their desktop machines, although these users are probably least likely to be able to set up their own maintenance tasks. Once again, marketing decisions, rather than technical issues, affect how you administer your SQL Server network.

If It Fits, Wear It

Multiserver task administration is a useful tool, and as SQL Server expands into larger databases, distributed or federated across groups of servers, multiserver task administration will become even more useful. Centralized reporting, one of its most valuable features, lets the DBA quickly focus on a server on which jobs aren't running correctly. If multiserver task administration looks like a good fit for your SQL Server environment, try it.