Skip to main content

Cloud

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

If you missed my last post on automating deployments across domains with Team Build 2010, you can find it here.

 

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).
image
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.
image
The step will have an error warning, which you can ignore for now.

Add New Arguments to the Build Process

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

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

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:
image

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
image

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

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:

Property Value
Input DatabaseProject
Output DatabaseLocation
Workspace Workspace

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

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.

Property Value
CommandLineArguments String.Format(“/p:DeployToDatabase=true /p:””TargetConnectionString={0}”””, TargetConnectionString)
Configuration BuildSettings.PlatformConfigurations(0).Configuration
OutDir BinariesDirectory
Project DatabaseLocation
RunCodeAnalysis CodeAnalysisOption.AsConfigured
Targets New String() {“Deploy”}

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

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.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Andrew Schwenker

Andrew Schwenker is a Sr. Technical Consultant within Perficient’s Microsoft National Business Unit East's SharePoint practice. Andrew has nearly 2 years of experience in consulting and has participated in projects that have touched nearly every aspect of SharePoint 2010. Andrew earned his Bachelor’s degree in Computer Science as well as Master’s degrees in Computer Science and Information Systems from Indiana University. He’s interested in creating winning solutions to generate business innovation using SharePoint. Prior to starting at Perficient, Andrew completed internships with General Electric, ExactTarget, and Great American Financial Resources. During his studies, he actively participated in Alpha Phi Omega National Service Fraternity and competed in the first annual Cluster Challenge at SC07 in Reno, NV. Andrew was a part of the dynamic PointBridge team that was acquired by Perficient.

More from this Author

Follow Us
TwitterLinkedinFacebookYoutubeInstagram