Managing Events
You can forward all Microsoft® SQL Server™ event messages that meet or exceed a specific error severity level to one instance of SQL Server. The forwarding server is a dedicated server that can also be a master server. 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.
In a multiserver environment, it is recommended that you designate the master server as the alerts management server.
Advantages
Advantages of setting up an alerts management server include:
- Centralization.
Centralized control and a consolidated view of the events of several instances of SQL Server is possible from a single server.
- Scalability.
Many physical servers can be administered as one logical server. You can add or remove servers to this physical server group as needed.
- Efficiency.
Configuration time is reduced, because you need to define alerts and operators only once on one server.
Disadvantages
Disadvantages of setting up an alerts management server include:
- Increased traffic.
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.
- Single point of failure.
- Server load.
Handling alerts for the forwarded events causes an increased processing load at the alerts-forwarding server.
Guidelines
When configuring event forwarding, follow these guidelines:
- Avoid running critical or heavily used applications on the alerts-forwarding server.
- Avoid configuring many servers to share the same forwarding server. If congestion results, reduce the number of servers that use a particular alerts management server.
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.
- Define alerts that require a server-specific response on the local instance of SQL Server instead of forwarding them.
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.
- After configuring your alert system, periodically check the Microsoft Windows® application log for SQL Server Agent events.
Failure conditions encountered by the alerts engine are written to the local Windows 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.
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
SQL-DMOTo define the response to an alert
Running Event-Triggered Jobs
You can define a job to be executed in response to an alert. For example, you can execute a job that corrects or further diagnoses a problem detected by the alert.
Note Given that a job can raise an event, be careful not to create a recursive alert-job loop.