SQL Server diagnostics are a mess. To monitor the well-being of a server, you need to be able to inspect all logged errors, have a whole raft of performance information, WMI data, and be able to study the history of backups and scheduled jobs. You have to monitor server downtime, low disk space, low physical memory, job failures, jobs failing to start, jobs taking too long to finish, blocks, locks and long running queries. There will be DBCC checks too. Because the beast we think of as SQL Server is actually built as a collection of services, you need to look for information in several places, including the host operating system. As well as that, you need to check periodically on the top cached query plans just to ensure that there is nothing awry with indexes or application design. You need also to check for long-running SPIDs, intrusion attempts, and patterns of usage that indicate fraud.
The DBA with around a hundred production servers may rightfully start to believe that her/his workload is excessive, especially as there are a whole raft of other Database administrative functions to perform as well. Around two years ago, the last time we got reliable information, Google admitted to half a million PCs serving information. It must be a lot more now, and it would be interesting to know how they are administered.
The past twenty years have seen an explosion of techniques for distributing the holding, analysis and processing of data. The monolithic systems of a previous generation have, in some cases, been replaced by container-loads of commodity PCs. Relational databases haven’t always kept pace with the change.
A group of SQL Servers requires an approach that is more organic. One has to think of syndromes, where one deduces a cause from a variety of symptoms or indications. It is one thing to collect all the indications of trouble, but quite another to work out the underlying cause. What then are the key symptoms; The twinges of pain the spots, the rashes, the depressions? What, even, is the catalogue of illnesses?
There have been many fascinating approaches to this problem, and a number of software tools that seek to provide the panacea. SQL Response Beta really is a Beta, in that it provides a framework, in order to find out from real users what the key requirements are. It acknowledges that the DBA really knows best. Even this early Beta is the result of much reflection on the predicament of DBAs in administering large numbers of Servers. It doesn’t pretend to provide all the answers. Dan and the other developers really want to know whether they are on the right track; what changes or improvements are necessary to make it more useful; and anything else that would be important to professional DBAs with heavy workloads.
One speaks of DBAs as though they were a uniform breed with similar requirements. The reverse is truer, and it would be fascinating to learn of some of the extremes of the tasks facing working DBAs, and whether a software tool could really help the workload significantly. Do you have a vast number of servers, a huge jelly-mass of inscrutable scripts? Post your headaches as a comment to my blog. All entries will go into a draw for a $50 Amazon voucher!
Cheers,
Tony.