Last week, I talked about Discovery Wizard’s Plugin Query Pack concept: the ability for DBAs to write their own inventory queries to figure out what’s going on in their environment.  I talked about how we’d only deliver a few basic Plugin Query Packs with the product, because those types of queries are difficult (and expensive) for a company to deliver and support.

Even beyond the Plugin Query Packs, we’ve got some ambitious goals for what we want Discovery Wizard to accomplish.  Since we want it to remain free, we have to be smart about what we build into the product versus where we just build a framework for the community.  The more features we put into the product, the more bug testing we have to do, the more support people we have to add, the more developers we have to have, and it becomes tough to do as a freeware product.  Striking this balance means making some compromises.

Reports via SQL Server Reporting Services

Reports aren’t cheap.  Ask your BI team to build you a custom report, and if you listen closely, you can hear the cash register going ka-ching in the background.  And if you think that’s expensive, you should see how expensive it is for third party vendors like Quest to build, test and maintain reports in a product installed all over the world.

The core Discovery Wizard product needs to handle the plumbing: it needs to discover servers, poll servers and update the repository.  However, in order to keep it free, we have to avoid building reporting into the product.  Frankly, the reporting doesn’t belong in the product anyway: it needs to be web-based or email-based so that groups of DBAs can all get the same reports without installing Discovery Wizard on their workstations.  Reports will be done via SSRS reports, preferably client-side ones that we can view inside SQL Server Management Studio.

If we’re going to expect the community to write their own reports, though, we have to do our part by making the repository brain-dead-simple.

It Needs an Open, Stable, Easy-to-Understand Repository

I’ve seen a few tools that tout the ability to scan your environment to build a list of SQL Servers, but the data isn’t stored in a way that makes it easy for us to query.  When I was a DBA, I based my utility queries off the IBM Director database repository because I could easily reverse engineer it to figure out where the server names and IP addresses lived.  I didn’t care that the product wasn’t a database product at all – it just had a great repository.

Discovery Wizard needs that same thing: a simple repository with just a handful of tables that DBAs can extend to include any additional attributes.  The documentation for the repository, as well as the documentation for the community-built Plugin Query Packs, will need to live somewhere that the brightest minds in the SQL Server community can update it at the drop of a hat – the wiki at SQLServerPedia.com.

Documentation in the SQLServerPedia Wiki

Here’s our vision:

• Run Discovery Wizard to find SQL Server instances
• Load up your list of Plugin Query Packs
• Run the Performance Best Practices pack against your instances
• Find out that you’ve got 5 databases with highly fragmented indexes
• Click on a link to find out why this is an issue
• A web browser opens to SQLServerPedia showing several articles about index fragmentation, video webcasts talking about it, and scripts to fix it

This, for me, is where Discovery Wizard really starts to show off the power of the community. When a DBA runs a product like this for the first time, they’ll be blown away by the number of other DBAs out there who dedicate their time to writing, speaking and scripting SQL Server issues.

To prepare for it, I’ll be blogging about the kinds of Plugin Query Packs we’re building in the coming weeks.  I’ll show what kinds of things we’re trying to discover on servers, and what kinds of information we’d like to give to DBAs who wonder why these issues matter on their SQL Servers.

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

Website - Twitter - Facebook - More Posts

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.

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

Website - Twitter - Facebook - More Posts