SQL Server Magazine

Web infrastructure monitoring. I subdivided the Web monitoring part of the solution into three categories: hardware load balancers, Web servers, and SQL Server back end. APC's six hardware load balancers maintain a workload balance for several servers in a farm (or group of servers) that have a unique farm name. Each of these servers has its own IP address, and at any time, users might be connected to servers that are active in a farm. Servers around the world are divided into two regions: EMEA (Europe) and NAM (North America). I could get all the information our DBAs needed about a hardware load balancer by using the specific Management Information Base (MIB) commands. However, the information is in separate text files. I used T-SQL to combine the text-file information into one table, which I then published on the central monitoring Web site. The Web page shows DBAs each farm, the servers in the farm (servers can be in more than one farm), and each server's status, IP address, number of attached users, and region.

The second part of Web monitoring was getting information about Web servers. I set up a Windows Service that polls all Web servers every 2 minutes. The service gathers data about CPU utilization, memory utilization, Microsoft IIS connections, and the status of Web Services and Cold Fusion Services and stores this data in a SQL Server table. The Windows Service then uses T-SQL to aggregate the information and publish it on the monitoring Web site. DBAs use the published information to calculate resource availability and usage. I also created another Windows Service that gathers similar information from the Web back end for reporting purposes. I used Reporting Services to compile the information the Windows Service collects into graphs and charts for trending. Reporting Services automatically publishes the charts on the monitoring Web site every month so that management can access them.

OS scheduled tasks. Monitoring OS tasks—such as scheduled batch-file executions and custom data-transfer jobs—was the last piece I added to APC's monitoring solution. I gathered task-status information from Windows NT scheduler, Macromedia Cold Fusion Task Scheduler, 24X7 WinPro Scheduler, and NSI Double-Take scheduler, primarily in the form of log files and COM-based access. Then, I used T-SQL to parse the information I'd gathered and compile it into scheduled DTS packages and store the information in SQL Server. The monitoring Web site gets OS information that the DBAs need from SQL Server.

Benefits of the Solution
Once we'd successfully deployed the complete monitoring solution, APC saw the benefits immediately. Support calls logged at our Help desks for problems related to Siebel, SQL Server, or the Web dropped by 80 percent because we were alerted to problems well before end users noticed them. APC could regularly meet its service level agreements (SLAs). Availability of the SQL Server and Siebel platforms increased to 99 percent. Before this tool was in place, jobs sometimes failed repeatedly, and the failures would often go unnoticed because of the sheer number of servers DBAs had to monitor. Now, failed jobs are detected and fixed immediately. Our Web commerce revenue is directly tied to the availability of our commerce Web infrastructure, so this solution has helped increase the overall revenue for the company—which, in the end, is what really matters. Overall, we had happy end users, increased revenue, and increased platform availability.

End of Article

Prev. page     1 [2]     next page -->