Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

SCOME - Centralize Monitoring with ASP.NET - Part 1

By Drew Salem,

An Introduction

What to do when your hands are tied?

When they discovered VMware, my employers became overly excited. For every new application, they created a new Windows Server environment with (despite my recommendations) it's own SQL Server. And so SQL Servers were popping up like wild mushrooms in a field of cows with the runs (cows have 4 stomachs). Every couple of weeks, another 2 or 3 SQL Servers would appear in the network list, and I would have no idea where they came from. Additionally, SQL Servers are a free for all where I currently work, with network administrators and 2nd line support going on and fiddling with the Servers as and when they please. There is no change control. Passwords can change without me knowing, Servers are shut down at times during the night, out of hours, without a thought as to what it might affect. SQL Servers are installed by non-DBAs and no consideration is taken towards parameters outside the wizards default setup.

This is not a made up scenario, this is where I work. Now, I ask you from DBA to DBA, how do you manage 47 business critical servers in an environment like this? Forty seven SQL logs are 37 too many to check on a daily basis. As well as 47 x ? jobs (439 currently). Not only do failed jobs need to be checked, but their run times too. If a network fairy shut down a server during the night, SQL Server will not flag the job up as having failed. And then how do you manage disk space for this many servers when space is added and removed without any notice (due to another new toy, the SAN). And there are your test restores and log space and index rebuilds.... It's a lot to do in an uncontrolled environment for this many critical servers for one DBA. I can honestly say that during my first few months, I was losing sleep. I don't mean to go on about my own work experiences, but I'm going somewhere with this.

Despite the severity of the situation and even though little has changed, things are now very different, as through centralizing, I am one step ahead of everyone else in the department. With these scripts I am on top of every aspect of monitoring and regularly find myself informing colleagues in the department of errors and warnings in their areas well before they are even aware of them.

What are these articles about?

These articles are about centralizing the monitoring of all your SQL Servers (and Oracle ones too, if you have them) and packaging the results in an ASP.Net web application. I had read many good articles on the topic of centralizing and used some of the methods, but the majority of these returned results in the query window or used SQL Mail to deliver the results to your inbox. With a heap of different areas to check daily, this felt a little fiddley and messy. I wanted a cleaner way of monitoring all the servers. I wanted reports. Daily reports in a slick page, accessible from anywhere. And so I fired up Visual Studio, and over many months developed a suite of web applications that gave me a birds eye view of all the SQL Servers in the organization.

Through a series of articles, I'll demonstrate how I did this and will cover everything from the SQL Server side to the writing of the web application itself in ASP.Net/VB.Net. You don't need to know anything about .Net, as the format provided will be a step-by-step guide describing how to setup the Visual Studio environment, how to write the server side .Net code to display the results and to how to deliver the web pages across the network so that they are accessible from anywhere. To make this suitable for as many people as possible, I will assume you know nothing, so forgive me if I iterate over something that may seem obvious or something you already know. As there is so much to cover, I'll actually do very little explaining on how some things are the way they are in .Net, or the technology behind them, but feel free to Google these as, needless to say, there are very good web sites that do this. The aim here however is to get you up and running, so that you can systematically build your own .Net solutions in your own time, that will in-turn aid your day to day DBA duties. Whilst there are various ways of actually extracting data from databases in .Net, we will mostly use T-SQL to do this, purely so that you can combine your existing DBA SQL skills with web design abd development. If there is an ounce of creativity in you, you’ll find yourself reveling in doing so. It's great when it finally all comes together.

So enough faffing.

How does it work?

Here’s a run down on how the system works (I'll provide scripts and step-by-step instructions in articles to come). Let's say that we want to monitor for failed jobs on two SQL Servers called Payroll_Server and CRM_Server. 

1. First thing's first, you ask the boss for your own SQL Server "SQL_Admin" to run all your monitoring and auditing scripts from. You want your own server because later you'll be performing test restores on to it.

2. You chase the request for your server.

3. Six months later you've got your server. You now create your own database "DBA_Admin" that will hold all tables that will store the data regarding the SQL Servers that are being monitored.

4. You create two linked server connections from SQL_Admin to Payroll_Server and CRM_Server.

5. You create a table in DBA_Admin called "Linked_Server_Details" that holds details about all servers to be monitored (i.e. all linked servers), in this instance Payroll_Server and CRM_Server.

6. You create a table in DBA_Admin called "Failed_Job". This table will hold data regarding all failed jobs on all linked servers being monitored.

7. Now we setup the mechanism that will collect the data from all the linked servers. It's what I call the SCOME technique (Servers Coming Outa My Ears). We create a stored procedure called usp_GetFailedJob. This stored procedure accepts an input parameter; the name of any linked server i.e. usp_GetFailedJob 'Payroll_Server'. The stored procedure then fetches data from Payroll_Server's Dynamic Management Views and/or system tables and puts it into the Failed_Job.

8. But we want SQL_Admin to run this procedure on all our linked servers so that the table Failed_Job holds data about failed jobs on all servers being monitored. We have data about Payroll_Server's failed jobs but not CRM_Server's failed jobs. So we create another stored procedure call usp_iterate_thru_servers. This proc goes through all servers in the Linked_Server_Details table one row at a time, collects the name of the server being monitored and sticks it in front of usp_GetFailedJob. Usp_GetFailedJob then does it's bit. We end up with a table of data of all failed jobs for all linked servers.

9. We create a job on SQL_Admin called Failed_Jobs and schedule this to run however often we want it to. We give this job a new category called "Monitoring_Jobs" (this is because we'll later write a web report that will tell us if any of our monitoring jobs themselves have failed!).

10. Now the fun part, we fire up Visual Studio and create a colorful web application that collects data from the table and displays it for us. Additionally, from this one page we can provide links to other pages that display the errors logs to see why it failed (saves us firing up SSMS), have a graphical representation of the number of failed jobs in the last month, the date the data was last collected etc.

11. We setup Internet Information Services to display the application as a web site so that we can access it from anywhere and then we publish the site.

12. We can now check anytime during the day if any of the hundreds of jobs across all our servers have failed.

That's it. The job is not resource heavy and completes within seconds. Now the beauty of the SCOME technique, is that this system can be applied to any type of DBA monitoring i.e. Disk Space, Unrun backups, Log size etc. Just replace the job's specific DMVs with those that hold data on backups or disk space or Log space. And replace the Failed_Job table with a table such as Disk_Space. You can have a whole suite of reports. In fact, if you can think of any that could be added to the suite, please share your idea with us in the article forum. I am genuinely open to suggestions on this and will be happy to write new articles based on these suggestions (as well as use them at work!).

Here's a figure representing the SCOME technique.

We use ASP.Net to read the results from the table and display them in a web page...

Now substitute the failed jobs system tables and dynamic management views for any other,
and you can collect info about anything SQL Server, on all your linked servers.

In the next article we'll setup the SQL Server side of things; the tables, linked servers, security settings and any necessary MSDTC settings that needs changing.

See you then,


Total article views: 10045 | Views in the last 30 days: 3
Related Articles

SQLServerCentral Article on Backup Monitoring and Reporting

The article, Backup Monitoring and Reporting, demonstrates a SQL Server backup reporting solution I ...


SQL Server 2014 Buffer Pool Extension part 3: system monitoring

In this article I’d like to tell about monitoring tools available in SQL Server 2014 to evaluate the...


Use Operations Manager to Monitor Your SQL Agent Jobs

Monitoring your SQL Server Agent jobs is important to ensuring that all of the automation you have c...


SQL Server Blocking Monitoring

Monitoring blocking can be problematic when you cannot catch it in the act. This article will show ...


SQL Server 2005 Activity Monitor

SQL Server 2005 Activity Monitor