Introducing the VSTS Database Edition GDR
The VSTS Database team announced a slew of new improvements at Tech-Ed. Jeff walks through some of the key changes.
- By Jeff Levinson
For some time now, the Visual Studio Team System (VSTS) Database team has been
releasing major functionality out-of-band.
The new community technology preview (CTP) of the VSTS 2008 Database Edition
General Distribution Release (GDR), announced at the recent Tech-Ed developer
conference, is no exception. It provides some much-needed performance and extensibility
improvements -- not to mention a new build and deploy system!
In this article, I'll discuss the changes to the build and deploy system as
well as the extensibility system. A complete list of the changes is available
on Gert Drapers' blog here.
Also, note that because the current release is a CTP, it's subject to change.
Build and Deploy
In the current version of Database Edition, the build and deploy process is
essentially a one-step operation; when you perform a build, the tool generates
a script that can be run against the database. This presented a number of issues,
not the least of which were issues with renames and not being able to handle
changes to the target database at the last minute.
Welcome to the new model, where these issues have been fixed!
By separating the build from the deployment, the Database team has introduced
a much-needed flexibility -- and it did it in a system that's simple to use
and understand. Take the following example:
I reverse-engineered the AdventureWorks sample database and renamed the HumanResources.Department
table to HumanResources.Departments table. It's a simple change, but I like
to pluralize table names where it makes sense. In the previous release, you
would have to run the Refactor Command Generator and pre-run the resulting script
in order for the deployment to work correctly.
With the GDR release, when you perform a build, there are a number of new files
created in the output folder (by default, this is the /sql folder under the
project folder). For the purposes of this discussion I'll concentrate on the
.dbschema file and the Transaction.refactorlog file.
The dbschema file contains a complete representation of the database project
(including a reference to the "Departments" table and not "Department").
However, in the Transaction.refactorlog file, you'll find that there is a refactoring
entry for the Department table with a property attribute called "NewName."
This allows the deployment process to know that you meant to rename the table
and not drop the Department table and add a new table called Departments. This
simplifies the process of deploying your projects greatly.
It also provides another benefit: It handles changes to the database schema
easily. In the current version, you have to select the target that you wanted
to update to match your database project (the source). But what if the database
changed before your deployment, or you ran the build against an updated Dev
or Test environment but those environments weren't updated correctly? The deploy
would flat-out fail.
With the new model, the deployment script is created on the fly based on an
examination of the schema's final state versus the existing schema. While not
available in the first CTP, there will be a command line tool (vsdbcmd.exe)
which performs the actual deployment of the schema and can also generate the
SQL script on the fly so a DBA can examine the changes before deploying the
The other announced new feature of the GDR that I'll talk about here is the
extensibility of all of the different features. The first piece -- which isn't
available to the public at the moment and won't be in the GDR release -- is
the addition of a database provider model. This means that the Database Edition
will no longer support just SQL Server 2000, 2005 and 2008 (this is included
with the GDR release), but also any other databases for which someone wants
to create an interface. IBM announced support for DB2 at Tech-Ed, and we can
hope that other database vendors will come on board very shortly.
As before, the Data Generators and Test Conditions are still extensible --
no point in going backward, after all -- but the team has also added the ability
to extend the built-in Static Code Analysis rules (previously, these were available
only as part of the power tools, but the rules are now included in the base
product) and refactoring types. This means that using the Database Edition will
allow you to examine your SQL code to ensure it meets your organizational
standards. If it doesn't, you can write your own refactoring code to make it
meet those standards. (OK, maybe not, but you can create your own refactorings
to make common mistakes easier to fix.)
In short, the Database team has done an incredible job of bringing us the functionality
that we as a community asked for (the team members really do read the e-mails
and suggestions), and it's done it in a well-thought-out manner that will make
the process of developing and deploying databases even easier than it was with
the first version of DB Pro.
Jeff Levinson is the Application Lifecycle Management practice lead for Northwest Cadence specializing in process and methodology. He is the co-author of "Pro Visual Studio Team System with Database Professionals" (Apress 2007), the author of "Building Client/Server Applications with VB.NET" (Apress 2003) and has written numerous articles. He is an MCAD, MCSD, MCDBA, MCT and is a Team System MVP. He has a Masters in Software Engineering from Carnegie Mellon University and is a former Solutions Design and Integration Architect for The Boeing Company. You can reach him at Jeff.Levinson@nwcadence.com.