Inside VSTS

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
• 06/23/2008

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 script!

Extensibility
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.