22 06 2014
An (A)DBA and SQL Monitor
In my professional life I am a C# .net Developer and the accidental dba (ADBA) at my company. I call my self an ADBA because we didn’t originally have a Database Administrator and I sort of fell into the role. The company grew and although it’s databases were already important they were now under greater strain and needed to be run on better hardware.
So we centralised the physical tin into hosted machines (some physical and some virtual) in our data center. As we started to have more users hitting the same disk-arrays/cpu/ram we started to notice a few performance hits. Using the various SQL tools that are our there I was able to identify some of them; but it was an active job that required a lot of time (and I still have my actual development tasks to perform!). So like any good tech-guy I consulted the interwebs (Stack Overflow, Brent Ozar, DBA Exchange); I downloaded a trial of Redgate’s SQL Monitor and set about configuring it.
For those who are not already aware of the SQL Monitor tool that Redgate provides; it is an active program that can monitor multiple SQL instances for a large range of problems. Those problems could be as simple as job overrun (granted not always simple!) all the way to a run away transaction log gorging itself on your storage. The best thing about it is that you don’t have to do anything to actively monitor the situation; Redgate will email/text/notify you as and when potential new problems start to develop.
So what can you do?
On install you need to have a monitoring box for the Monitor Service and your SQL Server connection details. You can run the monitor on the SQL Server but hopefully I do not need to tell you why that is a bad idea. The install is painless; it will install it’s own web server (or you can opt to use IIS) and then ask you for connection details and credentials to a SQL server that you wish to house the monitor’s own database (this can be your monitored server if you wish).
Once installed all you need to do is tell it where your targeted servers are (and provide credentials); then you can sit and watch the alerts role in.
The likelihood is that the monitor will locate potential problems in your severs that have passed you buy such as:
- Collation issues between the tempdb and user databases.
- User Databases with Page Verification settings that are not optimal.
- A database that has not been backed up recently.
- I could go on!
Once you have cleared those out the way you can relax and wait for SQL Monitor to detect something else; for the next few mornings you are likely to get job duration alerts as SQL Server starts to understand your nightly MI or maintenance jobs. For a full list of the types of alert that SQL Monitor can provide check out its listing on Redgate’s website.
Not just alerts!
One of the best features of SQL Monitor is its active performance metrics and base-lining ability. The monitor will keep an eye on everything from perfmon style stats such as Disk I/O to the number of active transactions on each database. You can then view this information in a rather fancy graphing tool that allows you to show baselines from hours/days/weeks in the past. It can be invaluable in the early detection of bottle-necks in your systems.SQL Monitors Analysis Tool – Not from my systems, it’s the Redgate demo site!
The analysis/alert engine allows you to define your own metrics (perhaps you want to use SQL to bring back the number of visitors to your site; or logins to the members area). I have used this feature to keep an eye on TRN files larger than a set value, or index fragmentation beyond a set percentage. These results can also be pushed onto the Analysis Graphs shown above.
Download the trial! You can’t lose anything by trying this tool and you may just find it indispensable. Use the analysis of perfmon stats to convince your IT Head to buy it; you would be surprised how much they love those numbers!