In part one of this series, I described why and how I use event forwarding to implement a cheap and effective monitoring feature for my large SQL Server environment. Here I'll describe how to set up a central server and highlight issues you should be aware of at the central point of control.
As I mentioned previously, SQL Server 2000 ships with a native monitoring feature called "event forwarding." Setting up this feature requires that you dig deep into some of the lesser-used areas of SQL Enterprise Manager.
The first step in implementing SQL Server's event forwarding system is to designate an events forwarding server. This is the server that will act as the central clearinghouse for events from all your other SQL servers. I'll call this the central server from here on out.
Remember that the central server will be receiving data from all your remote servers. Consequently, it should be a fast network connection, or you might even use multiple network interface cards to ensure that you don't experience any bottlenecks.
Follow these steps to tell a remote server to send events to the central server:
- In SQL Enterprise Manager, navigate to a "server group" and then a "server."
- Open the "management" folder under the server.
- Right-click on the SQL Server Agent icon and choose "properties." You will see a properties dialog like that shown in Figure 1. SQL Server Agent must be running on both the central server and the remote server for events to forward properly.
Figure 1: The Advanced Tab of the SQL Server Agent Properties Dialog
- Click the "advanced" tab.
- Click the "forward events to a different server" check box under the SQL Server event forwarding section of the dialog.
- Select a registered SQL Server from the "server" list. Pick a central server, and forward events from all other remote servers to this one. Keep in mind that the central server will sustain added overhead (mostly networking overhead) because it is processing incoming events.
- Choose one of the two mutually exclusive options:
A. Select the "unhandled events" radio button to forward only those events that have not been handled by local alerts.
B. Select the "all events" radio button to forward all events regardless of whether they have been handled by local alerts. This is my preference.
- Choose a value from the "if event has severity of or above" list to define a minimum error severity level at which events are forwarded to the central server. My preferred value is 010 (informational) when we need to monitor all activity on a server, or more often 018 (nonfatal internal error) when we want to monitor only errors and trouble on the server. Plus, a value of 018 greatly reduces the amount of networking traffic flowing to the central server.
Note that you'll have to follow these eight steps on all remote servers that you want to monitor. In our case, that means we must follow those eight steps on all 140+ SQL Servers so they will forward events to our central management server. The events from the remote servers then appear in both the SQL Server error log of the central server and the Windows Event logs.
The final step to make your event forwarding system complete is to install SQL Mail on the central server. SQL Mail is a means of enabling Outlook mail on your SQL Server. Because it's complex and is already described in the SQL Server books online, look up the topic "Configuring SQL Mail" to figure out how to install and set up SQL Mail.
In the next installment of this series, I'll tell you how to correlate events to SQL Server alerts -- in essence, how to make events actionable. I'll also discuss how to extend your event forwarding system into a business-enabling technology by using it to notify business application owners of important business events.
About the author: Kevin Kline is the director of SQL Server solutions at Quest Software Inc. He also presides as president of the international Professional Association for SQL Server (PASS) and frequently contributes to database technology magazines, Web sites and discussion forums. He is the author of "SQL in a Nutshell" published by O'Reilly & Associates. Kline welcomes your questions as SearchSQLServer.com's Monitoring/Administration expert.
More information from SearchSQLServer.com