As a production database administrator, you’re responsible for making sure databases don’t have little icons next to them that say “Suspect”. Here’s what you want to know when I walk in the door:
What am I dealing with?
How many servers are out there? I don’t want to take somebody’s word for it, either, because I know that in any given environment, there’s all kinds of SQL Servers hiding under desks, on non-default ports, or using instance names. SQL Server is everywhere these days, and when one breaks, it’s going to be my responsibility whether I knew about that instance or not. I need to quickly get a handle on every instance out there, know what applications are on it, and get a very basic picture of that instance’s health.
Quest Discovery Wizard for SQL Server v2.0 will check your Active Directory domains or do TCP/IP scanning to find SQL Server instances and build an inventory. That’s a great start. Next, though, I want that data stored in a place that I can easily report off it. Since I’m a SQL Server database administrator, that location needs to be a SQL Server database. I want the table format to be relatively stable, because I’m going to write more and more queries over time against that repository.
In enterprises with multiple DBAs, a similar goal is gathering and maintaining an rigorous inventory including things like failed logins, the number of sysadmins, auditing status and more. Enterprises either have to buy a complicated Change Management Database (CMDB), or roll their own system. I’ve seen several approaches to building a SQL Server inventory, and they’re all pretty nifty – but they’re owned by the enterprise that did the building, and they’re not often interested in sharing their work with others. As a result, we all keep reinventing the wheel.
What’s changed recently?
I wish I had tight lockdown control over every server in the shop, but that’s just not realistic. I’ve got developers who talked management into letting them become sysadmins. I’ve got domain admins with nasty habits of clicking around in SSMS when I’m not looking. I’ve got project managers that install software that comes with SQL Server, and the dang vendors never pay any attention to best practices settings.
I want to look at a report that gives me a high-level overview of everything that changed. I may not be able to justify auditing software that’ll alert me the instant somebody makes a change and tell me who made it. I’d settle for just a report of what’s changed, and then I can go track down the culprit and beat him over the head with his keyboard. Sometimes simple – and free – is better, and I have to start controlling my environment somewhere.
Discovery Wizard 2.0 added baseline capabilities that will save snapshots of your environment. That’s a great start, and the next step will be storing that in the repository so that we can report against it.
What do I need to do next?
Database administration these days is about triage: which instances are bleeding, which servers are giving birth to new databases left and right, and which ones just have a bad cough.
When I stagger into my cube in the all-too-early hours of the morning, I want help with the triage. I want a basic picture of my patients. I may not need advanced performance monitoring or bad query detection – I just want to start with looking for the instances that haven’t ever been backed up.
Maybe more importantly, when my boss asks me what I’m doing, I want to be able to print out that list and say, “Here’s what I’ve got on my plate. The developers want me to fix their code to make it go faster, but in order to do that, I’m going to have to push this stuff back. Is that okay with you?”
This type of report will be easy when the inventory data is stored in the database. I’ll be able to run a stored procedure that will list all of my servers, the problems that they have (old versions, security holes, bad performance configurations) in a prioritized list.
At review time, what do I tell my boss that I’ve done lately?
When the big dog calls me into his office to ask me what I’ve been up to for the last few months, I don’t want to just say, “Making sure I don’t lose limbs.” I want to show a clear, concise report that lets me prove statements like this:
- Three months ago, we had 23 servers that needed urgent service packs and hotfixes. I patched 14 of those.
- We’ve added transaction log backups on 11 production servers, enabling us to recover within 15 minutes of data loss instead of losing up to a day of data.
- We’ve gone from having 56 instances to 81, a nearly 50% growth, without hiring more help. I can’t manage this growth by myself and stay on top of maintenance problems.
And I want to be able to spout off statistics like that without any work on my part other than clicking a few buttons. I’m a database administrator: I make a living off managing terabytes 0′s and 1′s. It should be easy to crank out metrics like this, but it’s not.
And I bet you’ve got more goals.
As you’re reading this, you’re probably thinking, “But what about ____? I absolutely gotta have it tell me ____.”
We’ve all got different opinions about what’s important to us as database administrators. No matter how many features we build into Discovery Wizard, it’ll never be enough. And furthermore, since it’s a free product, that restricts how many full time resources we can pour into it.
Fortunately, we’re not inventing the wheel here: there’s already a business model to follow. Think Mozilla Firefox: it’s a free product with minimal features, but it can be more things to more people because it has an extensible plugin framework. It gets the basics right, and then creative developers build add-ons to make it to all kinds of cool things.
That’s where we’re going, and in my next Discovery Wizard blog post, I’ll explain how.
