You can forward all Microsoft® SQL Server™ event messages that meet or exceed a specific error severity level to one server running SQL Server. The forwarding server is a dedicated server that can also be a master server (MSX). You can use event-forwarding to enable centralized alert management for a group of servers. In this way, you can reduce the workload on heavily used servers. A SQL Server version 6.x server should not be configured to forward its events to a SQL Server 7.0 server. This configuration is not supported.
In a multiserver environment, it is recommended that you designate the master server as the alerts management server.
Advantages of setting up an alerts management server include:
Centralized control and a consolidated view of the events of several SQL Servers is possible from a single server.
Many physical servers can be administered as one logical server. You can add or remove servers to this physical server group as needed.
Configuration time is reduced because you need to define alerts and operators only once at one server.
Disadvantages of setting up an alerts management server include:
Forwarding events to an alerts management server can increase network traffic, although this can be moderated by restricting event-forwarding to severity events only above a designated level.
Handling alerts for the forwarded events causes an increased processing load at the alerts-forwarding server.
When configuring event forwarding, follow these guidelines:
The servers that are registered within SQL Server Enterprise Manager constitute the list of servers available to be chosen by that server as the alerts-forwarding server.
The alerts-forwarding server views all the servers forwarding to it as a logical whole. For example, an alerts-forwarding server responds in the same way to a 605 event from server A and a 605 event from server B.
Failure conditions encountered by the alerts engine are written to the local Windows NT application log with a source name of SQL Server Agent. For example, if SQL Server Agent cannot send an e-mail notification as it has been defined, an event is logged in the application log.
Note If a locally defined alert is disabled and an event occurs that would have caused the alert to fire, the event is forwarded to the alerts-forwarding server (if it satisfies the alert forwarding condition). This allows local overrides (alerts defined locally that are also defined at the alerts forwarding server) to be turned off and on as needed by the user at the local site. You can also request that events always be forwarded, even if they are handled by local alerts.
To designate an events forwarding server
To define the response to an alert