Discovery Wizard Plugin Query Packs

In my last post about the development process of the next version of Quest Discovery Wizard for SQL Server, I talked about how DBAs need to track what’s going on in their environment.  Companies with big bucks bring in a configuration management database (CMDB) to monitor this stuff, but for the rest of us, the next version of Discovery Wizard will be a framework that:

1. Finds SQL Servers in your environment
2. Track changes over time
3. Save the results into an easy-to-query database
4. Runs queries against them to find out what’s going on

When I looked at Discovery Wizard 2.0, we had #1 and #2 down pat.  You can see a video of how the discovery works below – if you’re reading this via RSS, you can click here to view the Discovery Wizard tutorial video.  The first one was done by me, and the rest were recorded in our St. Petersburg, Russia office by Nina Philippova and produced by Andrey Kviatkovsky.  No, geeks, you don’t get to see Nina, at least not in this series!

We could add #3 pretty quickly (it’s already in the development builds of v3), but #4 was going to be a problem for a free product.  There wasn’t a way we could add that in without pouring a lot of resources into the product.

Building inventory queries is a never-ending task.  I got a brief taste of this when I spent a little time at JPMorgan Chase: every time the security teams came out with a new guideline, we had to write a new set of queries to figure out whether or not our servers met that guideline.  Every time we found out about a new performance best practice, out came the T-SQL queries.  And of course we didn’t have every single server in the shop running the same version, so that meant writing queries for SQL Server 2000, 2005 and 2008.

If Quest was going to bundle a complete set of inventory queries into Discovery Wizard, it would cost a lot of money to build, test and maintain those queries.

The Solution: Plugin Query Packs

Just like Firefox is built to enable developers to write their own plugins, Discovery Wizard 3.0 is being designed to take plugins too.  Example plugins might include:

• Best Practices for Disaster Recovery – the number of databases not backed up in X hours, last date/time each database was backed up, exposure window (how long the database has gone in any given time window without a backup)
• Best Practices for Performance – autoshrink turned off, basic memory configuration checks
• Best Practices for Security – number of sysadmins on the instance, number of schema changes in the last X hours, policy-based management enabled, encryption set up, accounts with empty passwords

Notice that a lot of these are called “Best Practices” – keep in mind that they’re just starting points.  You can take these, adapt them for your own use, and take out the rules you’re not interested in.  We’ll ship the product with a few basic query packs to use as examples, and you can take these and run with them.

How’s This Different From Other Products?

There’s a ton of products out there that check your servers against a policy. The first problem is they’re not easy for database administrators to use.

We’re database administrators: we live in T-SQL.  We have a zillion utility T-SQL scripts that we’ve written over the years.  We just need a utility that will discover new SQL Servers in our environment, run our queries against them, and compile the results centrally.

Discovery Wizard is a simple, straightforward tool that handles the icky parts and lets us do what we do best: T-SQL.  We don’t have to learn SSIS, we don’t have to learn PowerShell, we don’t have to upgrade our servers, we don’t have to reverse engineer a complicated schema – we just have to write queries and save them in a text file.

The Discovery Wizard Plugin File Format

Right now, we’re thinking of using XML files as plugin sources.  At a minimum, each XML file would include:

• The name, version and source URL of the Plugin Query Pack. We’ll host a community repository for these on SQLServerPedia, but you don’t have to upload yours if you create a private one for your own company’s use.
• The T-SQL query that will be run for each server. The query will need to detect the right version of SQL Server, and run the appropriate T-SQL against it.  For example, you can’t query DMVs on a SQL Server 2000 instance.
• A URL where the user can click for help if they have questions about the results of the query.  We’ll have a dedicated section in SQLServerPedia where DBAs can write documentation for their Plugin Query Pack.  For example, if your queries check to make sure all user databases are in full recovery mode, then on your explanation page, you can simply link to SQLServerPedia wiki links about recovery modes to explain why it’s important.

We’ve got ideas for more fields, like a priorities on how important each individual field is.  For example, if I’m designing a Plugin Query Pack for security, and if it returns a result saying the server has a blank SA password, I want that to be priority numero uno.

But this leads into next week’s post: the more things we add to the Discovery Wizard, the longer development takes.  We have to make some compromises to get it shipped, and we’ll talk about those next week.