Take control of "building" your database! (2/3)

Published 09 July 07 11:57 AM | dmckinstry

Part II – Build, Deploy and Database Connections

I actually wrote this post immediately after the previous one, but wanted to hold off until I QA’ed it. It is truly embarrassing to post part two of a series ten weeks after part one but at least it is better than when I first released it.

As I described in my previous post, using Microsoft Visual Studio 2005 Team Edition for Database Professionals can significantly evolve the approach for database team development; for the MSDN version of the story refer to http://msdn2.microsoft.com/en-us/library/aa833404(VS.80).aspx. For this post I’ll describe how to automate building and deploying an instance of your database based on the definition you have in version control. I’m going to assume that you’ve already figured out how to capture your database objects in a Visual Studio project and checked them into version control. I’ll also assume that you have a properly configured build machine and that you’re familiar with Team Build. If you need more information on any of these prerequisites, here are some good resources from Microsoft:

· How do I in Team Edition for Database Professionals (Note: This is the big one with references to the following and more!)

· Walkthrough: Creating an Isolated Database Development Environment

· Walkthrough: Establishing a Baseline for the Isolated Development Environment

· Walkthrough: Performing Iterative Database Development in an Isolated Environment

· How to: Create a Database Connection

There is other good documentation and blog postings so if you don’t find what you’re looking for in these links, your favorite search engine can help as well.

Database Builds within Visual Studio

Visual Studio 2005 Team Edition for Database Professionals includes extensions for MSBuild to enable building change or create scripts and deploying them to a target database. To do this interactively, you can access the project properties of your database project and set the connection string and target database as shown below:

If you’ve configured your database project appropriately, you can right-click on the project in Solution Explorer and select Build to create a script that can be used to create the target database or update the definition of the target database, depending upon your options in the project. Likewise, you can right-click and select Deploy to not only create the script but also to deploy it to the target database.

In itself, this manual build mechanism within Visual Studio can be very important. This, in part, enables you to easily create databases based on a point-in-time in version control. You can get a n old version of the database project and deploy it to your development box for troubleshooting, or get the latest version and deploy it to your local development database to incorporate changes from other team members.

This interactive mode of building and deploying databases is a great developer tool but it isn’t appropriate for automated builds. Luckily, Microsoft realized that this would be helpful so they’ve included custom targets and tasks to support automating database builds using MSBuild.

Additional Settings and Considerations

A couple of other quick notes on the build configuration screen. The build will, by default, break if you do something that will cause data loss. This can result from inserting a new column in the middle of the table, rename refactoring, reducing the width of a VARxxxx column or a few others that you may have discovered. If you’re early in the database design cycle and don’t care about the data, you can tell MSBuild to continue regardless of data loss by unchecking the “Block incremental deployment if data loss might occur” checkbox.

If that checkbox doesn’t fix it for you and/or if you simply want to always start with a fresh database build you can select “Always re-create database”. This will result in a faster database build as the system can easily generate a create script without having to inspect the existing database as required for an alter-based script. If you plan to use the resulting scripts later in a production environment, you’ll want to go with a variation of the alter scripts and leave this box unchecked.

Database Builds using MSBuild

These are already good documents on how to modify the database project files to enable MSBuild-based database builds (reference: How to: Prepare Database Build Scripts). I’m not going to duplicate the details but will provide a brief summary review as follows:

  1. This will enable us to simply copy the connection string information instead of trying to manually create it.
  2. This will be named something like MyDbProject.dbproj.user. Note that ‘user’ files are not checked into version control; this means that the interactive builds settings are per-user. This also means that the target database information is managed through Visual Studio 2005 and not currently through MSBuild.
  3. This file will be named similar to MyDbProject.dbproj. If you double-click on this file in Windows Explorer, Visual Studio will open the project as usual. This file is just a standard XML file so you can edit it using your favorite text editor. Alternatively, with your database project open in Visual Studio 2005, you can right-click on the project in Solution Explorer and “Unload Project”, then right-click and “Edit Project” and finally right-click again “Reload Project”.

With the connectivity information properly in your database project file, MSBuild is now able to build the database scripts and deploy to your chosen database. If you want to give it a shot, open a Visual Studio 2005 Command Prompt, move to the directory containing your project and run MSBuild (e.g., “MSBBUILD /t:build MyDbProject.dbproj”).

Now this approach is suitable if you want to create a command script to call MSBuild and always deploy to the same database. It is distinctly possible, however, that you’ll want to use the same database project file to build and deploy to one or more different databases. If so, read on…

Database Builds using Team Build

Team Build is the build engine included with Team Foundation Server. It leverages MSBuild but also includes additional functionality to integrate with Version Control, Work Item Tracking and Reporting. When you install Team Edition for Database Professionals and a SQL Server 2005 or SQL Express instance on the build server, it can also support database build and deployment. In fact, you can get it to work with only minor modifications the majority of which are described above. Microsoft has provided the details on MSDN at “How to: Deploy Changes using Team Foundation Build”. However, this will only work if you are always deploying to the same database server.

I propose that a more common usage in mature development shops will include the ability to deploy to different environments depending upon the specific team build. That is, you may want to build and deploy to an integration database server for one build type and then use a different build type to take the same database project and deploy it to a Test environment. Modifying the DBProj file as described on MSDN essentially hard-codes the target database server.

Implementing database builds in Team Edition for Database Professionals can be performed through the strategic use of an MSBuild task in your TFSBuild.proj. The official documentation proposes placing this in the AfterDropBuild target but I propose the AfterCompile target is a better place. AfterCompile will allow you to deploy a database for testing purposes before running the unit tests.

The key to specifying the specific database to deploy to within TFSBuild.proj is to add a property for the TargetDatabase. If you still use the ‘hard-coded’ connection string as described by MSDN, you can use different database names at that server. At this point, the overridden target will be similar to the following:

<Target Name="AfterBuildDrop">
    Targets="Build;Deploy" />

There is also a parameter for the target connection string which can be passed into the MSBuild task, but there is a catch. Unfortunately semicolon delimited strings have a special meaning in MSBuild and semicolon delimited strings are required as connection strings. Luckily you can handle this be quoting the semicolons using “%3b”. So you can completely avoid the hard-coded connection strings by updating your MSBuild task as shown below:

  Properties="Configuration=Default;OutDir=$(SolutionRoot)\..\binaries\Default\;TargetDatabase=MyDB_TestEnvironment;TargetConnectionString=Data Source=MyTestDbServer%3bIntegrated Security=True%3bPooling=False%3b"
  Targets="Build;Deploy" />

Note that this process results in a very long property string that should stay on a single line (regardless of how it was rendered in your browser).

By putting all of these pieces together you have the essentials to create your own automated build and deploy of a single source database project to multiple different database servers using the same database project file and different Team Build Types.

Assuming database builds are something you’ll see more than once, you may want to parameterize your database names and connection strings and expose their usage through a custom targets file. Consider using an ItemGroup to wrap your database connection information so that you can build multiple databases in a single Team Build Type.

But there is still more… Stay tuned if you want to learn how to automate testing and data generation in the data tier. And this time I promise it won’t take another 10 weeks until the next post J!


No Comments

This Blog

Microsoft VSTS Blogs

MSDN Forums

VSTS Community Blogs