When I first started as a SQL Server DBA / Developer in 1995 working with SQL Server 6.x one of the key steps was to set up alerting and monitoring.
I was working for a very rapidly growing mortgage provider and DR, monitoring and performance were critical to the ongoing success of the company.
I was very lucky to be working with some very clever and gifted people to whom SQL Server was something new, myself included as my prior experience had been Ingres and a little Oracle,
this meant exciting times with requests for functionality to be discovered and explored.
One of the things I implemented was log shipping, this didn't exist as a Microsoft option back then so it had to be hand written, I still to this day use a variation of that original method over the Microsoft offering - yes it's only a robocopy
but back then even robocopy was new, and we needed to know the whole thing was working as expected.
This was also the start of my customised monitoring but that story doesn't belong here.
So this was a fundamental part of DR and business continuity as well as safeguarding the company data from loss or damage, back then it wasn't unusual for thieves to break into companies to steal server hardware.
Although it all seems very strange now a 9GB scsi disk or a ( 4 MB ? ) memory chip was very costly.
Anyway how do you monitor or alert? I remember the sometimes heated discussions regarding what was a reasonable backup strategy and why alerting on failure was a really bad idea and so on.
Q. Why would I say that alerting on failure is a bad idea?
( Answers on a postcard to ........ )
A. If you don't arrive at something similar to this and you're a Production DBA I'd suggest you're probably not quite paranoid enough !
The problem with only delivering alerts on failure is when there is a failure around your mail server .. sadly I have seen this situation fully demonstrated where teams have sat oblivious to an ongoing issue just because there were no alerts.
How many times over the year? Quite a number to be honest.
So way back then there were a continuous string of alerts/emails to tell us all was well, a sudden lack of emails was an indication of a problem.
I've a presentation I gave to the SSUG of which the main theme was "Don't let the users tell you your server is down" and I think this is a key way to look at supporting production systems so if the first you know of a problem is when a user, or in a few cases a company director appears next to your desk to ask you what's wrong you really do have a problem.
I use customised real time dashboards to monitor key systems, the dashboards are designed in such a way that any issues should show before they become serious, so at least I know before that phone call or visit to my desk.
The loss of a dashboard is obviously a symptom. I use alerts too, some I have written myself but most come through Idera SQL Diagnostic Manager which I also use as part of my monitoring. I've used this product since the late 1990s when it was known as SQL Probe.
This post is sponsored by .. an email issue .. surprise surprise! Now I wasn't having any system issues but I was monitoring a key business process .. I watch this using some reports and a dashboard. Whilst watching I received some alerts ..but the alerts didn't match what I was seeing visually.
At this point I logged into SQL Diagnostic Manager as this product allows me to step through historical performance data .. e.g. step back 60 mins and then advance one minute at a time.
( there's a wealth of information that can be viewed; Locks, deadlocks, blocking, sessions, cpu, users, io and so on )
I wasn't able to see the indicated issues from the alerts .. hmmmm .. A check then showed that the alert had actually been raised an hour earlier, a handful of checks later it was clear there was a hefty delay to the email alerts I was receiving.
And that is the problem with alerts vs monitoring, if you get an issue with the delivery you'll maybe never know until it's too late .. then you will be reactive and having to play catch up rather than being proactive and hopefully ahead of the game.
A SQL Server note here, I raise smtp alerts from SQL Server, I don't use database mail or any of the built in mail versions - force of habit I suppose.
However I don't raise mailed alerts direct, I have a "send message procedure" that alerts are sent to. This proc reads from a configuration table which flags where an alert is sent, there are two flags, "mail" and "file" , both can be toggled, any email issues I toggle the "file" setting and the alerts get stored in a table.
Because that's another issue with failed email alerts, you probably won't have an alert history.
The option I don't use is to write the alert to the SQL Error Log .. no particular reason for that but it's an option which could be used too.
As a footnote I do recommend a look at SQL Diagnostic Manager from Idera . I’ve used this product for very many years.