How I set up a POC Database Deployment Pipeline for Continuous Integration, part II

Are your database deployments painful and are you looking for an example of how to do them safely and simply? Then read on.

In this post I’ll show you how I configured our Build server to enable Continuous Integration for Databases in our proof of concept (POC) database deployment pipeline and, more importantly, how easy it is. Implemented in a real deployment pipeline, this means that developers only have to do database development work from within their own local development environment. Builds will be created automatically when developers commit their changes to source control, and deployments can be done with ‘one click’ from within a deployment tool. No more manual scripting, and less manual coordination needed between developers working on the same database (thanks to the early detection of errors granted by the continuous integration process).


In the last post I showed the setup I used for the proof of concept (POC), how I configured a source control repository using SVN, and how I linked the Bookstore database to this repository using Redgate’s SQL Source Control (SOC). This was the first step to Continuous Integration (CI) – well actually, steps 1 and 2 in this high-resolution visualization.


The next step to CI was to configure our build server to make the magic of automated builds happen.

A build server is key for CI: it is continuously building packages triggered by the source code being checked in to the source control repository. Work of multiple developers working on the same code base integrates into one mainline which prevents integration problems.

Configuring the Build server

Before configuring anything else, I needed to install the Redgate’s Automation Pack on the Build server. This was really easy: I just downloaded the trial version from their website and installed it on our Build server following their instructions.

We use TeamCity as a Build Server, so next I created a TeamCity Project.

A project in TeamCity is a collection of build configurations. A TeamCity project can correspond to a  software project, a specific version/release of a project or any other logical group of build configurations. (Source: TeamCity Documentation)

I chose the name Database for the project I created, which is meant to be a logical group of database build configurations.

TC Project

Then I had to make my first TeamCity build configuration.

A Build Configuration is a “type of build” – a set of settings edited in the UI which are used to start a build and group the sequence of builds in the UI. The settings include VCS settings (from where to checkout sources for the build), what build procedure to run (build steps and environment parameters), triggers (when to start a new build) and others. (Source: TeamCity Documentation)

I gave the build configuration the name Bookstore and as a build number format I chose 1.0.0.{0}. For this POC the build number format was not that important, but when I set up CI for our production databases, it obviously was. More on that in another blog post.

TC Build Config General

Next I had to configure the Version Control System (VCS) settings. Until now I’ve talked about Source Control Systems, but they’re the same thing, and those terms can be interchanged.

TC Build Config VCS Settings

In the Version Control Settings step I had to ‘Create and attach new VCS root’, select Subversion as VCS Type, gave it the name SQL Bookstore, and entered the link to the repository, and the user name and password we use to connect to our source control system.

Next up was configuring the build steps. I selected Redgate SQL CI Build as the Runner type (which defines how to run the build and handle the output), gave the step a name, entered the package ID and selected SQL LocalDB as the Temporary database server. SQL CI uses this SQL instance to create a scratch database to test if the database code is valid. If not, the build will fail immediately, so the whole team can get fast feedback.

TC Build Config Build Step

By this stage, the SQL CI tool is now integrated with TeamCity and, after saving this, the initial build configuration was set.

One extra step had to be created to complete the start of the continuous integration pipeline, and that was the Publish step (i.e. to send packages further down the pipe once we’re certain they’re ready). I selected the Nuget Publish runner type, gave the step a name, selected the NuGet.exe version and entered the API key:

TC Build Config Publish Step

This step publishes the package to a NuGet feed so our release management tool can pick it up. So, the basic structure of the CI pipeline is now in place, but as you can see on the right side of the screenshot aboe, there are 5 more configuration steps which can be configured to refine the process. One of them is build Triggers, which you can use to configure how TeamCity will be triggered to create a build. (for example, based on a time, immediately after the previous build, or by some other external event)

TC Build Config Trigger

Here I have chosen the VCS Trigger as the Build Trigger, which means that every time someone checks code into the source control repository, a build will be triggered automatically. Time to test this and create the first build!

The first automatically created build

After having configured the build configuration, I went to my SSMS session, made a change to the bookstore database and committed this change to source control. Then I went back to our Team City configuration page, waited a few seconds and saw a build starting automatically!

TC Build in action

This build, as all builds, was automatically stored as a Nuget package on our Proget Package Management Server. Now the only thing left to do was to configure Octopus Deploy to be able to pull this package, create a release and deploy this release to the Build environment. How I did that will be described in the next and last part of this 3-part series on how I set up this basic POC.

Leave a Reply

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