Free SQL Server monitoring with event forwarding

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 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...

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT Microsoft SQL Server Performance Monitoring and Tuning SQL Server Mailbag: CALs, witnesses and unwanted changes SQL Server Mailbag: Data restoration and DB property management Working with IntelliSense in SQL Server 2008 Management Studio SQL Server Mailbag: Stored procedures, triggers and SSRS reports Troubleshooting Distributed Transaction Coordinator errors in SQL Server Clearing the Windows page file and its effect on server performance Optimizing SQL Server indexes –- even when they're not your indexes Performance implications of transaction log autogrowth in SQL Server The short course on how SQL Server really works Determining the source of full transaction logs in SQL Server Database Management and Administration Meet compliance requirements with improved database security practices Hardening the network and OS for SQL Server security Securing the server and database in SQL Server How SQL Server 2008 components impact SharePoint implementations Troubleshooting Distributed Transaction Coordinator errors in SQL Server Achieving high availability and disaster recovery with SharePoint databases Clearing the Windows page file and its effect on server performance Deploying a SQL Server virtual appliance for Microsoft Hyper-V How to create SQL Server virtual appliances for Hyper-V Push vs. pull: Configuring SQL Server replication

RELATED GLOSSARY TERMS Terms from Whatis.com − the technology online dictionary contiguity  (SearchSQLServer.com) contiguous  (SearchSQLServer.com) drilldown  (SearchSQLServer.com) hashing  (SearchSQLServer.com) hybrid online analytical processing  (SearchSQLServer.com)

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

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.

Rate this Tip To rate tips, you must be a member of SearchSQLServer.com. Register now to start rating these tips. Log in if you are already a member. DISCLAIMER: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.