Multiserver administration allows you use one server to manage alerts and job scheduling for other servers from a central location. You centrally manage alerts through event forwarding. You centrally manage job scheduling by designating master servers and target servers.
If you have multiple instances of SQL Server running on multiple systems throughout the network, event forwarding is a time and resource saver. With event forwarding, you can forward application log events to a central server and then process those events on this server. Thus, rather than having to configure alerts on 12 different server instances, you configure event forwarding on 11 servers and have one server handle all the incoming events. You could then use the application log’s Computer field to determine the system on which the event occurred and take the appropriate corrective actions using scripts or remote procedure calls.
To configure event forwarding, complete the following steps:
In SQL Server Management Studio, access the Management folder on the server running SQL Server Agent.
Right-click the SQL Server Agent entry and select Properties.
Access the Advanced page of the SQL Server Agent Properties dialog box, as shown in Figure 15-14.
Select Forward Events To A Different Server.
Use the Server selection list to choose a registered server that will handle the events. If the server you want to use is not listed, you will need to register it. Then access the SQL Server Agent Properties dialog box again.
Set the type of events to forward by selecting Unhandled Events or All Events. An unhandled event is one that you have not configured alerts for on the current server.
From the If Event Has Severity At Or Above drop-down list, select the severity threshold for events that are forwarded.
Click OK.
When you want to centrally manage job scheduling, you will need to create a master server and one or more target servers. The SQL Server Agent running on the master server can:
Centrally manage jobs for the target servers. Then you create jobs on the master server that run on the targets. For details, see the subsection titled "Assigning or Changing Job Definitions" earlier in this chapter.
Download jobs to a target. For details, see the subsection titled "Managing Existing Jobs" earlier in this chapter.
For the master/target relationship to work correctly, you must:
Make sure that the master server and all target servers are running SQL Server 2005.
Use domain accounts, not local accounts, when configuring the master and target.
Make sure that SQL Server Agent is running on the master server and all target servers.
To create a master server, complete the following steps:
In SQL Server Management Studio, access the Management folder on the server running SQL Server Agent.
Right-click the SQL Server Agent entry, point to Multi Server Administration, and then select Make This A Master. This starts the Master Server Wizard.
Read the welcome dialog box, and then click Next.
As shown in Figure 15-15, create a special operator to handle multiserver job notifications. This operator, called the Master Server Operator, is created on the master and on all target servers that use this master. Set an e-mail, pager, and Net Send address, as appropriate. You can change this information later by editing the Master Server Operator properties on the master server.
Select the target servers to associate with this master server. If a server is not registered, you can add a connection for it by clicking Add Connection. The process of associating target servers with a master is called enlisting. Later, you can remove the association by right-clicking SQL Server Agent in SQL Server Management Studio, selecting Multi Server Administration, and then selecting Manage Target Servers.
When you click Next, the wizard will check to make sure that the versions of SQL Server running on the master and target servers are compatible. If target servers are running different versions of SQL Server, note any compatibility issues listed. Click Close.
Specify the account that the target server will use to connect to the master server and download jobs. If Windows Authentication is allowed on the master server, the new login will be created automatically.
Click Next, and then click Finish. The wizard performs the necessary tasks and reports its progress. You will be notified of any errors.
Click Close when the configuration is finished.
You can configure one or more target servers for each master server. You create target servers by completing the following steps:
In SQL Server Management Studio, access the Management folder on the server running SQL Server Agent.
Right-click the SQL Server Agent entry, point to Multi Server Administration, and then select Make This A Target. This starts the Make TSX Wizard.
Read the welcome dialog box, and then click Next.
Click Pick Server to select a master server for this target server. Use the Connect To Server dialog box to connect to the master server. The master server is the source server from which SQL Server Agent jobs will be downloaded.
When you click Next, the wizard will check to make sure that the versions of SQL Server running on the master and target servers are compatible. If target servers are running different versions of SQL Server, note any compatibility issues listed. Click Close.
Specify the account that the target server will use to connect to the master server and download jobs. If Windows Authentication is allowed on the master server, the new login will be created automatically.
Click Next, and then click Finish. The wizard performs the necessary tasks and reports its progress. You will be notified of any errors.
Click Close when the configuration is finished.