But the ease of administration Microsoft provides for us is a two-edged sword in my experience. Why, you ask? Because the sheer number of servers that a single, skilled database administrator can manage is much greater for SQL Server than it is for most other database platforms. I'm not sure exactly how many servers the average SQL Server DBA supports, but after talking to a lot of people at conferences, webinars and user group get-togethers, I would guess that the average enterprise DBA supports between 16 and 30 servers each.
In my former life as a SQL Server DBA at one of the Big 4 accounting firms, we supported around 140 servers with just three DBAs. How did we scale up to such a high number of servers per DBA? In a nutshell, there were two secret ingredients in our administrative recipe. First, we automated every process that we could. Second, we standardized every aspect of our servers and kept them rigorously similar with a strong change-management system.
One of the key processes we implemented in those days involved both automation and standardization by using a simple monitoring system that told us about problems before users called in to report the issues themselves. In this
Requires Free Membership to View
multi-part series, I'd like to tell you about the free monitoring tools that ship in the native SQL Server toolkit. (You can buy a third-party tool that gives you a lot more features, but the native tool provides a moderate degree of alerting and awareness of your far-flung servers without you paying a dime.)
This native monitoring feature, called event forwarding, is not used very often in the field, from my experience. In fact, it's rather hard to find within the SQL Server 2000 Enterprise Manager interface. Yet, it's quite handy and effective for many monitoring situations.
On the positive side, event forwarding has the following advantages:
- Centralization: It gives a centralized and consolidated view of the events of many SQL Server instance. A single point of control.
- Scalability: It allows administering many physical servers as one logical server. Add or remove servers to this physical server group as needed.
- Efficiency: It saves time spent configuring new servers because alerts and operators are defined only once on the central server. Configure SQL Mail only once and maintain it at only one location.
On the negative side, event forwarding has the following disadvantages:
- Increased traffic: Forwarded events increase network traffic to the central server.
- Single point of failure: If the central server fails for any reason, the central record of events fails, too. (Remote servers continue to log their own local events.)
- Increased server load: Processing the forwarded events of other servers causes a bigger processing load on the central server.
In the next part of this series of articles, I'll describe how to set up your central event-forwarding server. Plus, I'll provide a number of tips about how to maximize event forwarding on the remote, monitored servers.
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.
This was first published in June 2005
Join the conversationComment
Share
Comments
Results
Contribute to the conversation