Using TeamCity to Automate Building and Deployment of Database Projects

So far we have seen how to setup TeamCity and connect to TFS. In this blog we will be looking at how to automate building and deployment of database projects, other BI project types are a little more complicated and will be covered soon in a subsequent post.

The idea is that upon check-in of the solution TeamCity will automatically build all database projects it contains and deploy these to a predefined development environment.

Prerequisites

Prior to commencing this tutorial you should have installed TeamCity and setup a connection to your TFS project as detailed in previous blog posts:

Continuous Integration with TeamCity – Automating SSIS Build and Deployment
Configuring TeamCity to Connect to Visual Studio

Visual Studio must be installed on the build server (the one running TeamCity) in order to use MSBuild. For the building and deployment of database projects you must also install the Data Tools extension for Visual Studio as this contains the dll’s required to enable MSBuild to build the projects.

At the time of writing the latest versions of data tools can be downloaded here.

For automated deployment you also need to have either SQL Server installed on the server or alternately the Data-Tier Application Framework, part of the (very handy) SQL Server feature pack available from: http://www.microsoft.com/en-us/download/details.aspx?id=29065 for 2012 or http://www.microsoft.com/en-gb/download/details.aspx?id=42295 for 2014.

Part 1: Enabling The Automated Build

To automate the building of the project you will need to create a build configuration and VCS root as detailed in the previous blog posts.

Following this navigate to: Administration > Root Project > [YourProjectName] > Build Configuration Settings

Click ‘Add build step’ and configure the build configuration as per the below.

Save the changes and navigate to the Triggers section below.

Add a VCS trigger and set it to trigger a build on each check in as below.

You should now be ready to go. To test this is working check in your solution in TFS and you should be able to view the project build (or fail) by clicking the projects icon in the top left corner which will give an overview of current build history.

You can also download the team city tray notifier (recommended) which you can set to pop up a notification when a build fails.

If you browse to the directory C:TeamCitybuildAgentwork[UniqueProjectId]you should be able to view the output of the built solution. Note that only projects which can be built by MSBuild will have been built, we will look at other project types (eg SSIS) in a later blog post.

If you only want to enable an automated build without deployment then you do need to follow the remaining steps.

Part 2: Automating the Deployment

Now that the build is automated we can add a second build step to deploy the databases to SQL Server.

First we need a way of telling Team City where to deploy the databases. I am going to deploy them to a local instance of SQL server in this example, however you can use this method to deploy to any compatible SQL instance on your network. In order for this to work you will need to make sre that the Service account which Team City is running under has the required access to the target database in order to deploy/update the database.

First you need to add a publish script to the database(s) you wish to deploy. To do this right click the Database, Select publish

Set the connection string appropriately, chose a name for the deployed database. you can also set the advanced options if required.

This will create an xml file within the database project.

As this is a BI Solution and is likely to contain many database projects I have been using an xml file which details each of the database projects which need to be built in the top level of the project. Using this approach we only have to maintain this top level xml file and the publish xml file for each database and we can avoid creating additional build steps for each database project added.

The template xml file that I use is in the format:

<?xml version="1.0" encoding="utf-8"?>
<Project >="https://schemas.microsoft.com/developer/msbuild/2003" DefaultTargets="Publish">
  <PropertyGroup>
    <SqlServerRedistPath Condition="'$(SqlServerRedistPath)' == ''">$(MSBuildProgramFiles32)Microsoft SQL Server110DACBin</SqlServerRedistPath>
  </PropertyGroup>
  <ItemGroup>
    <DBProj Include="SSDB">
      <DBName>CI.SSDB</DBName>
      <DBRoot>CI.SSDB</DBRoot>
    </DBProj>
  </ItemGroup>
  <Target Name="Publish">
    <Exec Command=""$(SqlServerRedistPath)sqlpackage.exe" /Action:Publish /sf:"./%(DBProj.DBRoot)/bin/Debug/%(DBProj.DBName).dacpac" /pr:"./%(DBProj.DBRoot)/%(DBProj.DBName).Dev.publish.xml"" />
  </Target>
</Project>

Create a new (second) build step in the project with the following configuration:

The completed build steps configuration should look like this:

Now, if all has gone to plan we should see that when we check in the project both build steps will be run in order and the database will be deployed to the instance specified in the xml configuration script.

The build should run as per the below, if we click to view the build log (very useful) as highlighted in red

Then you should see something which looks roughly like this:

That’s it for now, in the next post,  I detail the process of setting up CI builds in TeamCity for SSIS.