Microsoft SQL Server 7.0 System Administration Training Kit

This lesson describes how to establish a multiserver environment and automate administrative tasks within this environment. The multiserver administration model consists of a master server (MSX) and one or more target servers (TSX). Target servers do not need to be registered in SQL Server Enterprise Manager to set up multiserver administration, although it will make setup easier if they are.

After this lesson, you will be able to

• Automate administrative tasks within a multiserver environment

Estimated lesson time: 20 minutes

Grouping Multiple Servers

Having a multiserver administration configuration allows you to

• Group multiple servers into logical functioning business units
• Manage multiple servers from one location

For example, if a subset of your customer database is maintained at each branch office, you can create a job at the corporate headquarters to back up the customer databases in each branch office.

IMPORTANT

Defining a Master Server

A master server loosely manages all of the servers that have enlisted into it. The master server should be defined on a computer running Windows NT Server because of the higher connection load that it requires.

Use SQL Server Enterprise Manager or the Make MSX Wizard to define the master server. There are system stored procedures for managing a multiserver environment, but it is recommended that you use SQL Server Enterprise Manager. For details on using the system stored procedures, see Books Online.

Consider the following facts and guidelines about master servers:

• When you define a master server, you also enlist at least one target server. This inserts a row into the systargetservers system table on the master server. The existence of rows in this table designates the master server.
• The wizard creates an MSXOperator on the master server and on each target server.
• The master server usually represents a primary department or business unit server. In smaller organizations, one master server can serve the entire company.
• You should designate the master server as the event forwarding server.

If the master server is not performing database production functions, the load of managing events that are forwarded from target servers will not affect database application performance.

Defining Target Servers

You can use SQL Server Enterprise Manager or execute the sp_msx_enlist system stored procedure to define target servers. You can also use the Make Target Server wizard. The target server definition is stored in the msdb..systargetservers system table. A target server

• Is assigned to only one master server
• Must either reside in the same Windows NT domain as the master server or reside in a trusted Windows NT domain
• Cannot be a member of another master server until it defects from its current master server

Automating Jobs in a Multiserver Environment

You can create jobs on the master server to occur at a target server. SQL Server goes through the following steps to process jobs in a multiserver environment:

1. The master server posts jobs for the target servers in the msdb..sysdownloadlist system table.
2. The target servers periodically connect to the master server to determine whether any new or updated jobs have been posted for the target server to download. If a job exists, the target server downloads the job information.
3. The target server uploads the outcome status for any multiserver jobs that have completed since the last download and disconnects from the master server.

Figure 13.11 illustrates this process.

Figure 13.11 Job processing in a multiserver environment

Modifying Multiserver Job Definitions

The master server stores the master copy of job definitions and schedules. When you make any changes to jobs in a multiserver environment, consider the following facts and guidelines:

• Job definitions cannot be modified at the target servers.
• Any changes to the job must be made on the master server.
• SQL Server Enterprise Manager automatically posts the necessary instructions to the download list.

Reviewing Job History

The master server records the job outcome information from the target servers in the msdb..sysjobservers system table. This is in addition to the normal job history information recorded in the msdb..sysjobhistory system table of each local target server.

Lesson Summary

Multiserver administration makes it possible to centrally administer many SQL Servers with a minimum of effort. Set up a master server with target servers and you have to create jobs only once for all of the servers in your organization. Job histories of jobs created on the master server are available on the master server, making it possible to manage and monitor all jobs centrally.