May 12, 2015 by Kenneth Fisher
If a user is going to call me about problem, I’d much rather know about it ahead of time. In fact my favorite problems are the ones I’ve already fixed before the users ever notice it. In order to manage this amazing feat you have to monitor your instances, your servers, your network, your applications, etc. At every level of IT someone should be keeping an eye on things. As DBAs we typically only have to worry about the databases (and maybe a little bit into the server/application areas). Fortunately for us there are some really amazing software solutions out there. Among others Redgate and Idera both make top end monitoring software, and like most top end pieces of software they tend to be a bit expensive. Not nearly as expensive as your SQL Server license however, so an easy case can be made for purchasing enough to cover all of your instances. On the other hand if your company is like mine telling your manager “Hey, it’s not even 5% of our SQL licensing cost” doesn’t really hold a whole lot of weight. Certainly not when you have hundreds of instances.
So what can you do? We took a three tiered approach.
- First we keep enough licenses to cover the vast majority of our production instances. The high profile ones and the problem ones.
- Then we added 2-3 additional floating licenses. We move these particular licenses around when we have an issue on a server. We don’t have the long term history but we can spotlight a server for a few days or weeks to get a feel for what’s going on.
- Last but not least we have a light weight, home grown, monitoring system. This system is pretty simple and that’s by design. We aren’t trying to be perfect here, just better than nothing. Remember we have the professional system for anything really important.
In case anyone is interested we took an SSIS package that loops through a list of instances and used it to collect some information. Here is an example of one of the queries we use.
SELECT CAST(@@ServerName AS VarChar) AS Server_Name, CAST(sysdatabases.name AS VarChar) AS Database_Name, CAST(sysdatabases.cmptlevel AS Int) CtmpLevel, CAST(ISNULL(DatabasePropertyEx(sysdatabases.name,'Status'),'Removed') AS VarChar) AS Status, CAST(DatabasePropertyEx(sysdatabases.name,'Updateability') AS VarChar) AS Updateability, CAST(DatabasePropertyEx(sysdatabases.name,'UserAccess') AS VarChar) AS UserAccess, CAST(DatabasePropertyEx(sysdatabases.name,'Recovery') AS VarChar) AS Recovery, MAX(CASE WHEN type = 'D' THEN backup_finish_date ELSE NULL END) AS LastFull, MAX(CASE WHEN type = 'I' THEN backup_finish_date ELSE NULL END) AS LastDifferential, MAX(CASE WHEN type = 'L' THEN backup_finish_date ELSE NULL END) AS LastLog, -- I'm not worrying about the following types of backups although that could -- obviously be pulled just as easily. -- F = File or filegroup -- G = Differential file -- P = Partial -- Q = Differential partial SUSER_SNAME(sid) AS DBOwner FROM master.dbo.sysdatabases sysdatabases LEFT OUTER JOIN msdb.dbo.backupset backupset ON backupset.database_name = sysdatabases.name -- AND server_name = ? WHERE sysdatabases.name <> 'tempdb' GROUP BY sysdatabases.name, sysdatabases.cmptlevel, DatabasePropertyEx(sysdatabases.name,'Status'), DatabasePropertyEx(sysdatabases.name,'Updateability'), DatabasePropertyEx(sysdatabases.name,'UserAccess'), DatabasePropertyEx(sysdatabases.name,'Recovery'), SUSER_SNAME(sid)
This returns a list of databases on the instance, some various settings and when the most recent backups were taken. We can then report on this information across all instances. This means that we get a single report with either an all clear, or a list of problems. Some of the reports we have include lists of any DBs that are marked READ_ONLY, RESTRICTED, OFFLINE, have no valid owner, have no FULL backup in the last day or week (depending on the backup schedule), no log backup in the last 12 hours (again depending on the schedule), etc. Some of the reporting can get a bit complicated and we collect quite a bit more information than this but this does give you a general overview of what’s we are doing. It takes a bit of effort to set up and build your report but it’s highly worth it. I think I spent about a month building the current system and it’s probably saved me 10 times that much over the last few years.