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.
End of Article
Prev. page
1
2
[3]
next page -->
