I really need to come up with shorter titles. But I like long Waltzes, what can I do.

While working on a mini (and I emphasize the mini) multi-scenario (also emphasize the multi) performance testing structure (so a multi-mini kind of) using the SQL Server Data Collector (SSDC), I found myself wanting to extend the Management Data Warehouse with new functionality, but not really wanting to create new stuff in the MDW itself. The thing is, the MDW itself is not your code. It can be changed by Microsoft at any time (even though they haven’t touched it in years), so it’s really out of your project’s control. However, to make the MDW dance to a new Tango, you do have to get some T-SQL code of your own in there, playing new tunes for it to enjoy.

So you have some hard thinking to do here:

• Will I develop stuff straight in the MDW?…And risk a SQL Server upgrade sweeping me on the dance floor?
• Or will I duplicate the MDW as a database of my own somehow?… And possibly make it harder to upgrade my dance shoes when better ones come (well, if they come at all)?
• Or is there a way to develop on my own database, while pretending to play my tunes on the MDW?
• And what about source control? How to I keep my files safe?

Fortunately, there is (at least) one way to sort this out, and it’s called SSDT Database Projects.

Step 1: Use SqlPackage To Snapshot the MDW

And why not SSMS? Basically, because of this:

The banana peel here is that the MDW’s model, as it stands, references some outside stuff, which SSMS doesn’t like at all. The smarter alternative to SSMS, is to use the SqlPackage utility.

You can extract a DacPac from the MDW like so:

SqlPackage /Action:Extract /SourceServerName:YourServerNameHere /SourceDatabaseName:MDW /TargetFile:MDW.dacpac

This will net you a shiny new model of the MDW database:

Step 2: Create an SSDT Database Project for your own Monitoring stuff

Now that we have the MDW dacpac in place, we can create something to make use of it. So the next step, is to create your own Monitoring project, like so:

Now copy the MDW dacpac into the new project’s folder, so you can keep it safe in your source control.

Step 3: Reference the MDW dacpac in your own Monitoring project

Fortunately, with SSDT, this is easier than doing a spin in double time. Then again, most things are.

So first thing is to add the MDW dacpac file to your project, so you tuck it away in your source control, along with your project.

Now you just have to add a database reference:

And then configure it appropriately:

On the Database Name box, you can put the name of the actual MDW database you’ll be talking with, if you know it. Also pick a sensible name for the reference variable. If you don’t know the name yet, or it changes, don’t worry, just put something helpful to yourself, as you can always specify this during deployment.

Also note the option Suppress errors caused by unresolved references in the referenced project. This will tell SSDT to ignore de MDW’s dependencies on the master and msdb system databases and allow you to build your code.

You solution should build now, so pat yourself on the back, and go get a drink too.

Step 4: Make the MDW play to your tune

And this is where it all wraps up. Because we’re now using a model of the MDW as a reference, we can call upon its objects from SSDT, as we would do with any other reference. With Intellisense and all. Now isn’t that better than a flying boleo? Well, perhaps not, but it’s almost just as neat.

Step 5: Celebrate

With this endeavor, we’ve achieved:

• Separation of concerns: Let the MDW do its tango while you do your own.
• Separation of code: Deploying your code is now a also a separate concern from configuring the MDW.
• Befriended Sourced Control. You own code now lives in the declarative safety of an SSDT Database Project, best friends with your repository of choice.

Now keep on dancing and see you next time.