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

To convince management, development, operations and quality assurance (QA) that we needed CI Continuous Integration for databases, I had to set up a proof of concept (POC). For this proof of concept I used the Bookstore database, which is the sample database used in Redgate’s SQL Source Control Basics book. I could have used any database, but since I was already using this database for learning SQL Source Control, I had it to hand. In this post, I’ll give a bit of context to the POC I built, explain a little about how I got buy-in, and then walk through the details of how I got the test database into source control.

Later on, the setup with this database became more useful for demoing, and for colleagues who used this database for trying and testing stuff. It became a database for anyone who wanted to play and learn.

The flow for the POC was very simple: Have the Bookstore database in our source control system, deploy the database on my workstation from our source control system using Redgate’s SQL Source Control, make a change to the database and then commit the change back to our source control system.

SQL CI POC Flow

Then I showed that, due to this commit, our CI server was triggered to automatically create a new build of the database. Finally, I demonstrated how to deploy this new version of the database to our shared development environment using our deployment tool (we use Octopus Deploy, but there are plenty of other options out there). Continuous Integration for databases in its simplest form.

For DBAs New To Continuous Integration

Now, as a DBA, these systems were completely new to me and I can imagine that there are DBA’s in the same position as I was – wanting to set up a database deployment pipeline, but not being sure how to start. If that sounds like you or someone you know, then my advice is to get out of the cubicles and talk to the developers. Ask them what source control system they use for their software, if they use a build server (and which brand), ask how they deploy their software (do they use a deployment tool?). Ask for a demo.

Let them show you how they commit their software to the source control system, let them show you how they create builds and deploy them to the various environments. Most importantly, ask them how they would like to be able to deploy database changes in a smooth, pain-free way. Is it similar to the database deployment pipeline I prototyped, or something completely different? I’ll bet that they’ll really appreciate your interest and your intentions to make things better for them, and they’ll be willing to work with you to make everyone’s life better.

Also talk to the administrators of the servers which run the source control system, the build server(s) and the deployment server(s) – they are probably members of your operations team. Let them know you want to set up a proof of concept for a database deployment pipeline, and explain to them the advantages for them.

If you haven’t realized it already, all this talking, listening, and collaborating is a key part of DevOps!

Our Setup

For setting up the proof of concept, I used:

VisualSVN Server (our source control system)
– Redgate’s SQL Source Control plugin
– JetBrains TeamCity (our build/CI server)
– Redgate’s SQL Automation Pack
Octopus Deploy (our deployment tool)

It could well be that you don’t use SVN as your source control system, in which case don’t worry – you can configure a similar setup with GIT, Team Foundation Server, Vault, Mercurial or Perforce. The same goes for your build/CI server – if it’s not Team City, you can also set up Continuous Integration for databases with Jenkins, Go or Bamboo.

Configuring the SVN Repository

Configuring a repository in SVN is easy. To prepare for this POC, I logged on to our SVN server, started the VisualSVN Server Manager and created a repository called DB_VCS_POC. In this repository I created the folder Bookstore and the subfolder Trunk.

VisualSVN

Installing Redgate’s SQL Source Control

Next I installed Redgate’s SQL Source Control (SOC). When I started SSMS, the SOC plugin is immediately visible. I created the (still empty) Bookstore database, selected it in the Object Explorer and in the setup tab for SQL Source Control, I clicked Link to source control.

SOC-link-to-SVN

On the left of the window I selected our source control system (SVN), specified the details for the location in source control (link-to-our-source-control-system/DB_VCS_POC/Bookstore/Trunk) and selected Dedicated database. I don’t recommend it, but it is also possible to link to a database that is used by multiple developers. If you want to know more about why, Redgate have some documentation talking about different database development models. To do the initial commit I went to the Commit changes tab, typed a meaningful commit comment (“Initial source control commit”) and that was it: I had source controlled our first database!

Conclusion So Far

As you can see, it is easy to set up a database repository and to source control a database using Redgate’s SQL Source Control. Next, we’ll see how easy it is to setup Continuous Integration for databases with Redgate’s Automation Pack!

Leave a Reply

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