Event Forwarding

Similar to multiserver job management, SQL Server can also be configured to forward error messages. All or any unhandled event messages that meet or exceed a specified severity level can be sent to another server for processing. For example, you can configure all your servers to forward level 17 or greater messages to an underutilized departmental server. The advantage here is that the alerts, jobs, and operators that respond to the error need to only be configured on the one server. The disadvantages are increased network traffic, and, perhaps most importantly, a single point of failure has been introduced. If the server that is handling the events or its network segment is down, then you are in peril of missing events from the other servers. The downside might outweigh the advantages here. Using Enterprise Manager or scripting, it is simple to create the alerts, jobs, and operators on each server. It's a conundrum really; if I had a few servers, I would let each handle its own affairs. If I had dozens of servers, I wouldn't want one server responsible for all their messaging!

If you want to configure event forwarding, it is done through the Advanced tab of the SQL Server Agent properties. Figure 18.16 illustrates configuring event forwarding.