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
Having a multiserver administration configuration allows you to
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
Multiserver administration requires SQL Server version 7 on all servers. Your servers cannot use older versions of SQL 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:
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.
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
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:
Figure 13.11 illustrates this process.
Figure 13.11 Job processing in a multiserver environment
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:
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.
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.