I have just spent the last few days trying to get a SQL Project to build, deploy, run unit tests and create a dacpac on our build server which uses TeamCity. It wasn’t a simple process and I had to accept a few conditions that I wasn’t overly keen on but the upshot is that it works now and it works well.

Here is the outline of how it all hangs together, I will do a few more detailed posts to follow up.

The solution itself consists of three projects. Two database projects and a unit test project.  They are checked into GIT (using BitBucket) and TeamCity watches the repository to trigger a build.  The build creates a dacpac for each database, publishes the changes to the appropriate server, runs the unit tests and saves the dacpacs into a nuget package that is published by TeamCity itself.

Out of the box, TeamCity can handle the build but you need MSTest and SSDT installed on the build server.  That is where your troubles begin.  You can download an MSTest agent as I wrote about here.  You can even install the SSDT tools for VS2012 (though that was a bit hit and miss for me). The real problem is that with VS2013 the SSDT tools are integrated and no longer available as a separate download.  In the end I had to settle for installing VS2013 on the build server.  Microsoft allows your developer license to be used on the build server and at least I can easily update the SSDT version – I’m not particularly happy about having to do it though.

In order to publish your database to the correct server you just need to include appropriate publish profile files and Microsoft have provided the ability in database unit tests to switch config files according to the build server machine name. The publishing part worked fine for me but the config switching seems flaky and if you put a whitespace in the wrong place your config will be ignored.  My workaround here was to resort to Slow Cheetah – a plugin from NuGet that allows config switching for app.config in the same way that you can achieve it with web.config. This works well but remember to restore your nuget packages as the first build step in TeamCity. It’s not so restrictive as a config that switches based on machine name (we only have a single build server so would be unable to test on multiple platforms).

The result is that on every check in, the dacpacs are built, deployed to a test environment, tested and if they pass, made available via a nuget feed for other projects.

It feels good when it all just starts working!