Manage Change, Mitigate Risk: Part One

  • Article

We’re here, TechEd Boston! I truly can’t believe how fast this conference has snuck up on us, as we have been planing on making our initial public CTP available here for a number of months now. But it is upon us, we are ready ( Yeah! ), and the conference is certain to be chalk full of great content, not the least of which is VSTEforDBPros!

At TechEd ( and in various press and analyst briefings ), a key message that you will hear is how Visual Studio Team Edition for Database Professionals helps mitigate the risks inherent in making database schema changes. We do this by supporting the following activities:

1) Directly support an isolated or “sandbox” development environment

2) Directly support a test or staging area where database tests can be established in the “sandbox”.

3) Directly support the rich generation of data that can be used to test the database

4) Directly support the creation of unit tests that can be leveraged as a regression mechanism

5) Directly support the ability to automate common changes throughout the database meta data

This post will be one of many to start better articulating how our solution is actually doing all this. ( It’s also import to point out that the steps I’ll outline are not the only ways to achieve the proper results with this tool! Different organizations have different ways of doing things, so as you read on, think about how you can leverage this new feature set in whatever process your org follows! )

Let’s dig in!

SIDE NOTE: By the way, if you are going to be at TechEd, we have a number of break out sessions ( see Matt Nunn’s blog for details ) that you can get more information at. But I will also be showing the bits in Dave Campbell’s Monday morning talk in a 10–15 demo, so hope to see you there!.

First Step: Isolation

The first thing we do to help get the complexities of database schema change management under wraps is to fully support an off-line development and testing environment. The lynch pin in that support is our new Database Project. The database project system allows you to import existing schema ( such as the schema of your production database ) into Visual Studio’s solution explorer. Once done, you have an offline, equivalent representation of that database schema, represented in fine-grained sql scripts, that can then be versioned ( you can also create a db project and start creating new schema, but we figure most folks will start by importing existing schema, so I want to focus on that for now ). I can’t stress how important this is, so let me make sure you understand what I mean by offline schema representation: Literally, once you have finished importing the schema from your production environment, there is no longer a connection to that environment in any way. You can now change, replace, update, and delete ( CRUD ) that meta data to your heart’s content and not be concerned in any way that you are disrupting the production environment ( or any of your team mates for that matter! ).

So the first step to establishing an isolated environment ( from here out I’ll be referring to this environment as your “sandbox” ), is go to the File->New->Project… menu item. You’ll see the following dialog:

 

You’ll notice right away ( if you are familiar with Visual Studio 2005 ) that there are some new Project types. In our first version of the product, we will have full support for SQL Server 2000 and SQL Server 2005. ( please note that the TechEd CTP has focused on 2000 since it is largely a subset of 2005, so support is better in that CTP for 2000. Clearly, this will not remain that way for long! J ) The rest of this walkthrough will assume you’ve selected the “SQL Server 2000” project type.

Once you hit the OK button on the New Project dialog, your new database project will appear in the Solution Explorer, as seen below:

 

At this point, you’re ready to import existing database schema, by right-clicking on the project node and selecting “Import Database Schema…”, as seen below:

 

The following dialog appears, asking you to specify what database contains the schema that you want to import. I’ll import the Northwind sample database in this example:

 

Once done importing, you can see that the schema has been imported by drilling into the nodes of the project, as seen below:

 

This is all well and good, and this is also where the interesting part of the import is seen. If you select a node in the project, say the “dbo.Categories” node, and then view the properties window ( hit F4 if not visible ), you’ll notice that a simple .sql file is the artifact that actually represents that node, as seen below:

 

And in fact, if you double click the dbo.Categories node, a TSQL editor is displayed, showing you the contents of the DDL for that table:

 

Why is this all important? Because now, the schema is represented in files in your “sandbox”, not connected to the database where that schema was imported from! Changes to the database project ( i.e., the schema! ) DO NOT affect anything but the database project and the schema in it!

But this is just the beginning of the benefits. Now that you have a database project inside Solution Explorer, you can now do the following:

  • Version control this project ( i.e., your schema! ) right along side the application code sitting on top of that database. You can now version the database meta data and the application that uses that meta data ( and the data in it! ) as one unit.
  • Now that you can version the app code and db code as one, you can benefit from labeling the two in your source configuration management provider of choice ( any provider that supports MSCCI, which all the biggies do ), thereby establishing baselines at any point in time. This will allow you to better control evolution of your database AND application holistically, so that never the twain shall diverge. Version 1.1.4344 of the application and the database meta data that application depends on are managed together.

How do you version the database project? Just like you would ANY project inside Visual Studio, as seen below:

Summary

So where are we at? We've successfully imported the Northwind schema into an offline representation ( i.e., pulled it into a database project ), and are now prepared to check all this stuff into Team Foundation Server.

In my next post, I'll talk about the next step in creating a fully functioning offline environment: creating the testing database from the database project! Stay tuned!

Cameron