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