TFS 2010, VS2010 Database Projects, and CI

Tom Opgenorth
Programming

I’m currently working on a project where there are some functional tests that require a SQL Server database. Before in the past I’ve always handled this by using Redgate’s excellent SQL Server tools to create a monolithic script that would deploy the DB Schema, and then another set of scripts to set up the data.  Then it’s pretty trivial to use OSQL.EXE to run the scripts and setup the database.

However, in this case, I’m constrained to use the VS2010 database project and TFS Build.  So, the trick for me became how to use TFS 2010 Team Build to deploy a fresh copy of the database before the functional tests are run.  After a bit of jiggery-pokery, here is what I ended up doing.  I’m sure that at some point in my future, I will have to do this again, and nothing helps my failing memory like writing it down.

First a rough overview:

  1. Declare some variables to hold the physical path of my .dbproj and the default data path for SQL Server.
  2. Convert the source code control path of my .dbproj to the physical path on disk.
  3. To help with debugging and diagnostics, write a build message with the location of the physical path of the .dbproj
  4. Add an MSBuild task to my workflow that would deploy the .dbproj.

Without further adieu, here is more details breakdown of the steps involved.

Declare Variables

So, first things first – declare the two variables I need to hold the physical path to the .dbproj file, and the directory for my SQL Server databases.  This should be pretty simple and straight forward (assuming that the POS that is the “Workflow Designer” isn’t crashing VS2010 constantly).

image

Once that is out of the way I scrolled down the pretty, crashy, workflow designer until I came across the Compile and Test sequence.  At the very start of it I added a sequence that I called Deploy Database.  Inside this sequence I added the following Team Foundation Build Activities:

ConvertWorkspaceItem

The point to this BuildActivity is to figure where the hell on the file system TFS put one of the files.  Pretty straight forward:

image

WriteBuildMessage

Always nice to have a message in your log file to help with troubleshooting.  Here’s what my WriteBuildMessage activity looks like.  Notice that the message makes use of the “DbProjectPath” variable that we set above in the ConvertWorkspaceItemActivity.

image

MSBuild

This the working part of the the sequence.  In here we use MSBuild to call the .dbproj and deploy a fresh copy of the database to SQL Server. Key properties to set:

  • CommandLineArguments : this contains the properties to pass on the command line when deploying.  You’ll want to provide these properties
    • /p:TargetDatabase=YOUR_DATABASE_NAME
    • /p:”DefaultDataPath=DIRECTORY_OF_DATABASE_FILES”
    • /p:”TargetConnectionString=YOUR_CONNECTION_STRING_FOR_THE_TARGET_DATABASE”
    • /p:DeployToDatabase=True
  • Configuration : just specify which configuration in the solution to use.
  • DisplayName : what ever you want, this is how the MSBuild activity will be displayed in your sequence
  • LogFile : the name of the log file for the deploy
  • OutDir : the output directory
  • Project : Notice that this is the value of DbProjectPath, which we set above in our ConvertWorkItem
  • RunCodeAnalysis : Set this to CodeAnalysisOption.Never.  Doesn’t make much sense to do code analysis on a database project.
  • Targets :

Here is what the properties look like for this particuar Build Activity:

image

Now all of this has to live somewhere.  You might want to have this live somewhere else depending on when or how you want the database to deploy.  In my case, as I wanted to deploy the database BEFORE my tests ran, I hunted through the workflow and found the sequence called Run Tests.  I modified one side of the If condition to include a new sequence call Deploy DB and Run Tests:image

Here is an overview of what my Deploy DB and Run Tests sequence looks like

image

 

After Thoughts

To be honest, I found the whole process annoying and awkward.  Sure, I didn’t have to edit a bunch of XML by hand, but the Workflow Designer in Visual Studio 2010 wasn’t exactly a joy to work with either.  I don’t know exactly what the problem was, but it kept crashing while I was trying to edit this Build Process Template. It was always the same error, an Out of Memory Exception.  On a Dell Latitude E6510 with 4GB of memory, this shouldn’t be happening.  As well, the whole editing process for the work flow was awkward at best.

As much as I dislike XML based build tools, at least text editors don’t get all crashy and such.  As well, I found the overall experience of trying to create and piece together the workflow for Team Build to be sluggish and tedious.  It’s great to have a GUI editor to hide the crummy XML, but honestly, I think the way FinalBuilder works is far superior to how VS2010 in terms of easy of use and readability(application crashes aside).

Next is to setup my Release build definition, and to tackle the issue of updated the version number in AssemblyInfo.cs and creating a zip file of all the deployment artifacts.  But first I’ve got to go and buy a bottle or two of Talisker to help numb the pain that will follow as I go done down that path.