TeamCity - Branching Databases and Continuous Integration

This week there have been lots of build failures, thanks to our HipChat and TeamCity integration I’m notified constantly. As a “senior developer”, this matters to me. The problem comes down to multiple feature branches using a single CI database, which means that it isn’t always “up-to date”, hence the failing tests.

In this post I’m going to show you how I fixed this by setting up TeamCity to publish a database for each of our feature branches.

There are a two bits that need to be done to achieve this end goal:

1. TeamCity needs to publish the SQL Server Database Project into a separate database for each branch
2. All connection strings throughout the solution need to be updated to point to this new database

Publishing the SQL Database Project

To accomplish this we will be using the SqlPackage.exe command line utility (MSDN docs) which is shipped with SQL Server Data Tools (you will have probably already installed this on your build server to get it to build the sqlproj). This command line tool does the same thing as when you right click in Visual Studio on your project and select publish.

We can use SqlPackage.exe to publish a new database by using the following command:

sqlpackage.exe /Action:Publish /SourceFile:"c:\path\to\my.dacpac" /TargetDatabaseName:targetdb /TargetServerName:sqlserver1 /Profile:"mydatabase.ci.publish.xml" The arguments are pretty self-explanatory, except for perhaps the last one. The profile as you will see in a minute allows you to define different parameters in an XML file which are then used during deployment.

Creating the Continuous Deployment Profile

For our CI environment deployments I always want the database to be dropped and recreated, this can be configured in the deployment profile.

In Visual Studio, right click on your database project and click Publish. This will bring up the Publish Database window.

Then click Advanced, this will bring up the Advanced Publish Settings. Tick “Always re-create database”. The options you can select are extensive and worth a look through.

Click OK to close this and go back to the Publish Settings window.

To create the Profile simply click the “Create Profile” button, this will add a new {projname}.publish.xml file to your project.

I’ve renamed mine to Database.CI.publish.xml

Setting up TeamCity

The next thing to do is to have TeamCity actually publish the database project. In TeamCity add a new Build step:

1. Runner type: Command Line
2. Step name: Publish database
3. Run: Executable with parameters
4. Command executable: C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\SqlPackage.exe
5. Command parameters:

1
2
3
4
5

/Action:Publish
/SourceFile:src/Database/bin/Output/MyDatabase.dacpac
/TargetDatabaseName:MyDatabase.%teamcity.build.branch%
/TargetServerName:[server]
/Profile:src/Database/Database.CI.publish.xml

Then click Save.

Updating Connection Strings

Once TeamCity has built the solution and published the latest version of the database I needed a way to update all the connection strings in the various app.config and web.config files.

To accomplish this I put together the following PowerShell script:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

$connectionStringName = $args[0];
$newConnectionString = $args[1];
 
# get the directory of this script file
$currentDirectory = [IO.Path]::GetDirectoryName($MyInvocation.MyCommand.Path)
 
$fileList = gci $currentDirectory -Recurse -Filter *.config
 
foreach($file in $fileList)
{
    $appConfig = New-Object XML
    # load the config file as an xml object
    $appConfig.Load($file.FullName)
 
    foreach($connectionString in $appConfig.configuration.connectionStrings.add)
    {
 
        if ($connectionString.name -eq $connectionStringName)
        {
            'file: ' + $file.FullName
            # write the name to the console
            'name: ' + $connectionString.name
            # write the connection string to the console
            'connectionString: ' + $connectionString.connectionString
            # change the connection string
            $connectionString.connectionString = $newConnectionString
        }
    }
    # save the updated config file
    $appConfig.Save($file.FullName)
}

What this does is it will find all *.config files files recursively from the directory that the PowerShell script lives in, it will then load each config file as an XML file and find all the connection strings, any that have a name that matches the first argument will have the ConnectionString property updated to the value of the second argument.

To configure TeamCity add a new build step:

1. Runner type: Powershell
2. Step name: Update connection strings
3. Powershell run mode: Version 2.0; Bitness: x86
4. Script: File
5. Script file: ConnectionStringUpdate.ps1
6. Script execution mode: Execute .ps1 script with “-File” argument
7. Script arguments: "MyDatabaseConnString" "Datebase=MyDatabase.%teamcity.build.branch%;Server=..."

Back into the green

Now when code is pushed up to BitBucket TeamCity will run through the following steps:

1. Build the solution
2. Publish the database project(s)
3. Update connection strings
4. Run unit test / integration tests - currently one step

TeamCity is back to being happy

and more importantly, when the build completes HipChat notifies me that the build has been successful.