Implementing Continuous Integration for databases I: The Stage

You’ve convinced Management and your colleagues your company should implement Continuous Integration for databases, and now you are ready to actually set this up for your production databases. Where to start? How do you tackle this? In this series I’ll explain how I did it. 

The Stage

Our set of environments

We have two DTAP (Development, Test, Acceptance and Production) streets, one for our backend databases and one for our manufacturing database. They are in two different locations and serve different purposes. For both, shared development was done on databases running in our development environment (a SQL Server instance we call D-DTAP) and change and rollback scripts were manually crafted by developers. These changes were promoted to our test environment (a SQL Server instance we call T-DTAP) for testing and then were run in our (user) acceptance environment (you guessed it – A-DTAP) by our Release Manager before getting deployed to production.

DTAP HL Overview

Each DTAP street is used by one team who have different approaches and processes in developing their software and their databases. I started implementing Continuous Integration for databases for one of the teams.

Our Source Control System

We use SVN and GitHub as source control systems for our applications and for our database change scripts, but we decided to use just SVN as the source control system for our databases. For now. Since we can also source control static data, which can be sensitive, we’d rather have that data not in a public cloud. And, as we already source controlled our change scripts in SVN, we figured the transition to source controlling our databases using Redgate’s SQL Source Control would go more smoothly.

Our Build Server a.k.a. CI Server

Our Build Server, which from now on I am going to call our CI Server, is a TeamCity server with 8 build agents running on their own servers. The reason I am now going to name this a CI Server instead of a Build Server is that it is going to do much more besides just building in the future (for example, automated testing.)

Our Deployment Server

We use Octopus Deploy as our deployment tool for our (automated) deployments. With this tool we can deploy releases to the different environments with the push of a button. We already used this tool for deploying releases of our applications, but now we are also able to use the same method for databases with Redgate’s DLM Automation Suite.

Where to start

My developer colleagues and I agreed to first source control the database schemas and set up Continuous Integration for them to make the transition go more smoothly. Source controlling static data would be done at a later stage, as well as providing sample data, taking care of environment-specific data and how to handle the same database in different geographical locations. We also agreed to start with the smallest and least complex databases to get us started and build up experience as quickly as possible.

Now the stage is set, it has to be prepared to make Continuous Integration for databases possible. In the next post I’ll show you how I did that.

Key takeaways

  • Build on your existing environment (if you have one)
  • Start simple, only source control the database schema the first time (if you haven’t already source controlled your databases)
  • Start simple, pick the least complex database to source control and to setup for Continuous Integration


2 thoughts on “Implementing Continuous Integration for databases I: The Stage”

  1. Hey Sjors,

    Thanks for putting this series of articles together as it’s been very helpful for me. Couple of questions I have I’m hoping you might be able to answer.
    1. Do you start the initial source control against production or the dev environment?
    2. How do you have CI for databases that might have different project steams going at the same time with potentially diff release cycles?

    1. Hey Jimmy,

      1. If you haven’t source controlled your database yet, your production database is your single source of truth. I recommend restoring the most recent backup of it on a different (staging) server and source control it from there.
      2. I’m assuming you’re talking about a shared database used by more than one application developed by different streams. That can be a challenge and there is no one size fits all solution. You can look for example to apply a branching strategy or consider splitting up the database into multiple databases: one for each API. Whatever you do, you need to talk with streams about possible solutions. In the end, they need to change their processes.
      Let me know it works out.

Leave a Reply

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