Team Build 2010: Deploying a Database with MSBuild on Team Build 2010

The Problem

In this post, I’m going to discuss automating deployment of a database project to a database using Team Build 2010. Out of the box, you can build the database project, but that doesn’t actually deploy the database or create the deployment script. To do that, you’ll essentially need a custom step in your automated build. This can be especially important if your solution depends on the database project, and, if you’re automating everything else, why not automate this step too?

Assumptions

I’m assuming that you already have an automated build process and a database project that you want to deploy. I’m solely focusing here on automating the deployment of the database with Team Build 2010. I also assume that you’re comfortable with editing the build template. If you’re not, use my previous blog post as an example.

Solution

A fact that is not widely discussed is the fact that database projects have an additional target that normal projects don’t have: Deploy. This target can do one of two things:

• Create a deployment script (.sql) that can be run later, or
• Create a deployment script (.sql) and run the script on a target using a given connection string.

I’m going to use the second option here, and have an MSBuild step that will actually do the creation of the deployment script and deploying to the database. This is just easier and, from what I can tell, is no different than generating the deployment script with MSBuild and then using SQLCmd to deploy it.

Locate the “If Build and Tests Succeeded” Step

Open the build template and locate the If Build and Tests Succeeded step. Here is where you’ll be putting your logic to deploy the database project(s). Here’s what the finished process could look like (I have a deployment script for non-database projects you can ignore).

Here, we’re only focused on the Check for Database Deployment Target step in the process.

Add an “If” Step to the “If Build and Tests Succeed” Step

From the toolbox, drag an If step to the Then condition of the If Build and Tests Succeeded step and give it the name Check for Database Deployment Target.

The step will have an error warning, which you can ignore for now.

Add New Arguments to the Build Process

Revolutionize Your Business With Generative AI

From product design and software development to virtual agents, content creation, and reporting, GenAI is transforming business. Our AI experts help you unlock GenAI’s full potential and drive growth.

Let’s Get Started

To maximize configurability and reuse, we’re going to use a pair of arguments to the build process. These arguments are passed in through the build definition and allow us to configure each individual build without changing the definition template directly. To add a argument, select the Arguments tab at the bottom of the workflow window. Give the first argument the name TargetConnectionString and the second argument DatabaseProject. Both arguments should be String types.

Add New Arguments to Process Parameters Metadata

So now that you have your arguments, you need to get these arguments set when the process is queued. To do that, you need to find the Metadata argument in the Arguments lists. Once here, click the … in the default value section to open the Process Parameter Metadata Editor window. Here, you want to add the TargetConnectionString and DatabaseProject to the collection. Parameter Name must match the Argument’s name. Display Name, Category and Description can be whatever you want. Keep in mind that Category is used for grouping on the Process pane of the definition.

Add Condition to “Check for Database Deployment Target” Step

Now that you have the arguments added, you need to add the Condition to the step we created earlier. You want to add the following condition:

 1: Not String.IsNullOrWhiteSpace(TargetConnectionString) And

 2: Not String.IsNullOrWhiteSpace(DatabaseProject)

This is VBScript that you’re writing, and it includes the Team Foundation Object Model, if you ever needed access to that. This snippet checks whether our arguments are null. If they are, we don’t want to continue, thus the “If” step.
Your Check for Database Deployment Target step should now look like this:

Add Sequence to Then Conditional

Now we’re ready to add a Sequence to the Then conditional of our step. Give it the name Deploy to Database

Add a Variable to the Deploy to Database Step

Now that you have your deploy to database step, you need to add a variable to be used across two steps inside this sequence. To do this, open the Variables tab and click Create Variable at the bottom. Name the variable DatabaseLocation and leave the Variable Type as String and the Scope should be Deploy to Database.

Add the “Convert Database Project to Local” Step

With the variable created above, we need a step that will convert the DatabaseProject server location to a local location. Since the local location is set at runtime, there’s no way to know it. Thankfully, there’s a process step that will convert a server location to a local location for us. Drag a ConvertWorkspaceItem step from the toolbox to the Deploy To Database step. Give it the name Convert Database Project to Local and set the following values:

Now that you know the local location of the database project, you can use it in the final step: deployment

Add the “Deploy Database Project” Step

The final step in the sequence is an MSBuild step that does the deploying of the database project. Drag an MSBuild step from the toolbox below the Convert Database Project to Local step and give it the name Deploy Database Project.
There are quite a few properties you’ll need to set. The most important are Targets, Project, CommandLineArguments, and Configuration.

Command Line Arguments are extra arguments passed to the MSBuild process, which allow us to override any settings from the project file itself, such as deploying to the database and providing a target connection string. Configuration picks up the first configuration, if you’re using multiples, you may want to change this. OutDir just tells MSBuild were to put the files. Project is the name of the project to build. Targets tells MSBuild how to build. In this case, we want the project to be built to the Deploy target.

Test Your Solution

Now you just need to check in your new template and refresh your build process. Then, you can add the TargetConnectionString and DatabaseProject arguments and watch as Team Build deploys your database(s). As I said earlier, MSBuild uses SQLCmd to do the actual deployment and runs as a 64-bit process, so even large databases should deploy without a problem. One thing to take note of, the deployment can take a while, especially if you have a lot of data being inserted in the post-deployment script of your database project.