Centralized Administration

In most large distributed SQL Server environments, there are several SQL Servers working in tandem. They may have pieces of data spread over each server. Nothing wastes a DBA's time more than duplicating work, creating the same jobs on each server. It's also frustrating to have to view logs on each server. In this section, I'll discuss SQL Server's method for consolidating jobs.

You can use the Multi Server Administration feature in SQL Server 2000 to centralize all of your common tasks. There are two components to the feature: a master server (MSX) and any number of target servers (TSX). Once established, you can use this feature to centralize all of your jobs and maintenance plans. As you add a job, it is downloaded to the target servers. Then, when it executes, its status is sent back to the master.

Tip 

If you have many SQL Servers in your multiserver environment, consider making a non-production server the master server (MSX). The traffic from synchronizing the jobs and logs can harm the performance of a heavily used production server.

Managing Multiserver Jobs

To enable Multi Server Administration, open Enterprise Manager and connect to the server you want to assign the role of central server. Right-click SQL Server Agent under the Management group, and choose Multi Server Administration | Make This A Master. This launches the Make MSX Wizard, which performs the following functions:

• Starts SQL Server Agent, if it's stopped

• Creates an operator called MSXOperator

• Enlists target servers (called TSX)

• Where applicable, changes the account that starts SQL Server and SQL Server Agent to a valid Windows account on target servers

The first step the wizard performs is to create an operator called MSXOperator on each of the target servers. This operator will be alerted upon completion of jobs.

Next, the wizard asks which servers you want to enlist as target servers. Think of a target server as the child of a parent server. The jobs originate with the parent and are transferred to each of the children. In the wizard window where you define the target servers, the wizard displays a list of registered servers. To enlist a server as a target, simply check the corresponding checkbox.

The list of servers you see in this screen is the list of registered servers on the workstation from which you're connecting. For example, if you're connecting from your desk, you'll see the servers that are registered on your server, not the servers that are registered on the actual server. It is for that reason that I recommend you do this while you're physically at the server. That way, you can ensure that you have proper network connectivity between the servers. Even though your efforts may be successful on your workstation, that doesn't mean things are going to work properly once the servers try to synchronize.

The wizard verifies your connectivity and ensures that it can connect to each server. After it has determined it can connect to each TSX, the wizard asks you to correct any problems with the servers. Once any problems are fixed, the master server enlists each of the target servers. If any problems occur, you can double-click on the red text error to see full details about it.

Note 

To remove a target server, you can log on to the target server and right-click the SQL Server Agent, then select Multi Server Administration | Defect From MSX. Once you've defected, you must remove the multiserver jobs that you've created.

Creating a Multiserver Job

After you've enabled Multi Server Administration, you can log on to the MSX with Enterprise Manager, and manage the jobs or maintenance plans centrally. You'll notice that your Jobs group is separated into two new groups: Local Server Jobs and Multi Server Jobs. Local Server Jobs only execute locally on the MSX, while Multi Server Jobs can execute on one or multiple TSX.

To add a new multiserver job, right-click Multi Server Jobs and select New Job. You can perform the same steps as you did in the Jobs section earlier in this chapter, with the exception of the Target Multiple Servers option. This radio box is now selected by default, and you can click the Change button to select the servers on which you want this job to execute (double-click the servers).

You can also create and manage groups under the All Server Groups tab. In a large environment, you can have the job execute on every server in a group to simplify your administration.

By default, SQL Server Agent polls the target servers every minute, in order to determine if jobs need to be transferred or status reports need to be transmitted. To view the status of a job, after it has executed and transmitted its status, right-click the multiserver job and select Job Status. The status that SQL Agent downloads is the job status, not the step-level status that is needed to debug. To see the step-level status, select the server, then click the View Remote Job History button. You can also manually synchronize the jobs by clicking the Synchronize Jobs button.

Click the Target Server Status button to see the current status of the TSX. You can also access this screen by right-clicking SQL Server Agent, and choosing Multi Server Administration | Manage Target Servers. In this screen, you can force your SQL Server to poll for new logs, or force the TSX to defect from the pool of servers.