Database Build and Release with Jenkins

One of the frustrations of implementing a Continuous Integration (CI) system is understanding how the individual components of the workflow work together to contribute to the process. The software engineer for a successful system describes how his team designed and use a useful CI system based on Jenkins, Subversion and Red Gate Tools.

Practice Fusion is a web based Electronic Health Record (EHR) company that hosts over 35 million patient records (more than Kaiser Permanente and the Veterans Administration combined).  We are the fastest growing Electronic Health Record community in the US. Founded in 2005, we’re rapidly expanding and adding new users. Over 150,000 physicians and practice managers in 50 states currently use Practice Fusion’s Electronic Health Record.

We have a small but growing DBA team, with three DBA engineers and a Data Architect.  We also have a group of 10+ developers who create stored procedures, functions and triggers. I am a Senior Software Engineer and I work closely with the Build and Release team in all of our Continuous Integration (CI) efforts. 

Continuous integration: Our current workflow

We currently use Jenkins (Hudson) as our CI build server.  All database development is done using Redgate SQL Source Control and Subversion. SQL Source Control handles both the schema and the data changes. 

Our workflow is as follows:

  • Changes are committed to Subversion using SQL Source Control.
  • Jenkins polls Subversion for changes and triggers a build
  • Jenkins creates a brand new database and rebuilds everything from scratch and deploys all the schema objects to it via SQL Compare. 
  • If there are any problems caused by missing dependent tables, columns, procedures or functions, then the build will fail and the developer who committed the offending change will get emailed.
  • We also use the Jenkins “Promotions” plug-in to deploy database builds to our Integration, QA and Staging environments. In doing this
    • Redgate SQL Compare is used to deploy the schema
    • Redgate SQL Data Compare is used to synchronize the contents of lookup tables
    • We have custom scripts to Stop and Start replication in environments where replication is in place. The script dynamically loops through all entities in the database and attempts to drop the subscription of any replicated object
  • When we are preparing deployment to our Staging and Production environments, we do not blindly deploy the changes.  Instead, we use Redgate Compare to generate the alter script, which is then inspected manually by our DBA team to ensure we are making appropriate changes (e.g. make sure we are not needlessly creating a large index)
  • We use the Jenkins versioning module to create a build number for every build.  This build number gets baked into a user defined function and if you want to know what version is installed, you can just call SELECT dbo.DATABASE_VERSION() and you will see the formatted version number

The problem that we fixed

Inconsistent source

In the past, everything was managed by developers using hand crafted SQL scripts.  Developers were inconsistent in how they authored the scripts.  Some scripts could be run multiple times (e.g. It performed a CREATE if the object did not already exist or an ALTER if it did), some only allowed for forward moving updates.  Some developers would put everything into a single script that was hard to maintain and diff between updates; others would break apart the scripts in order to have one object defined in its own file, making the deployment task more difficult.  Sometimes the files were checked into our version control system, sometimes these files lived only on the developer’s workstation.

Versioning problems

Our previous attempts at versioning our databases were all clunky and were not scalable.

We had no way of determining what database version we had running in the various environments.  Every release involved a lot of finger-crossing because we could never be 100% sure what we were testing was representative of what the production environment would look like when we deployed.  Deploying database changes was a nightmare and relied on developers executing scripts that they usually stored locally on their hard drives.

Problems of a manual process

The team was getting too big.  A release would contain dozens of files that were written in an inconsistent manner and had to be opened up and run from SSMS during a release.  If there were changes to scripts, the QA team would have to holler out to the Developer teams when they wanted the scripts deployed.  Now it’s just a button click in Jenkins to get the changes they desire.

Lost database updates

We also did not have confidence that what we were testing in our development environments would resemble what we were releasing to production.  Smoke testing database updates often revealed missing objects that were deployed manually to our development database servers, but never captured in a deployment script.  This was probably our biggest headache that forced our hand in finding a solution.

Search for a solution

We tried using Visual Studio Database Project.  At one point, I got it working, but it was really fragile and it was a nightmare to manage.  It did not have the flexibility that we needed.  For example, we had no way to determine what changes were being made behind the scenes.  The deployment process was done blindly and it seemed to break very easily and without explanation.  Searching the internet was usually a fruitless effort since almost no one seems to be using Visual Studio Database Project.

Our company’s engineering architect gave me the task of finding a way to version control our database.  He was terrified because if we ever needed to roll back a release, we’d have no idea what state the database was previously in.  I’ve known about Redgate SQL Compare since around 2006, and discovered SQL Source Control in May 2011.  By June 2011, we were using Redgate to deploy our database changes to production, primarily using SQL Source Control, but also using SQL Compare and SQL Data Compare.  We use SQL Source Control to make Subversion the source of record for all database entities.  We also couple it tightly with our Continuous Integration server to validate builds and promote (deploy) changes to our many environments.

Introducing the new process

It’s always a challenge to introduce a new process to a development team.  Some developers were more enthusiastic than others. A few of the developers who were used to the status quo had a harder time understanding the “why” regarding the change. After an hour-long demonstration on how Redgate Source Control works the team seemed satisfied that checking in their changes would result in saving time as opposed to having a tax on their workload.

We have seen a lot of benefits from using SQL Source Control. 

  • We now have a source of authority on the state of a database build — We can be confident that what we are developing and testing is what is going to be deployed to production.  That by itself is priceless.
  • Developers don’t need to waste time hand-crafting database alter scripts
  • Deployment is now automated — Developers don’t need to be bugged by QA every time QA is ready to accept a new version of the application.
  • We have identified holes in our process.  In many cases, engineers were making changes directly to production, which is a big no-no.  Redgate helps identify these unauthorized changes.

Database Source Control has given us the following benefits

  • We now have a definitive build of our database in source control
  • Database deployment is now automated — We can deploy any database version to any development environment with a few mouse clicks and by both developers *and* QA engineers
  • Developers have more time to develop, since they no longer need to manage deployment scripts and they no longer need to deploy them
  • We were able to identify differences between instances of our databases in different environments.  In some cases the changes were minor (e.g. rarely used indexes), in other cases the changes were quite large (e.g. missing columns and different stored procedure parameters).

We now have fewer issues when we deploy changes to production.  We have the ability to roll back changes.  We can now perform a simple SELECT statement to determine what version the database is running.

If you would like more information about how to set this up in your environment, or if you would like a demo, please get in touch with Redgate, or take a look at this Continuous Integration whitepaper for a technical overview.

Tags: , , , , ,


  • Rate
    [Total: 42    Average: 4.4/5]
  • William

    Say More about Jenkins/SubVersion/SQL Compare?
    Can you say more about how you do database schema source control and builds? Do you keep parallel SubVersion projects, 1) with just create object script(s) to create a fresh build at whatever version and 2) with some base create object script(s) plus a history of alter scripts for migration and upgrades?
    Maybe the above is moot as you only have one live database? i.e. you don’t install the product at clients’ data centers?

  • Ernest.Hwang

    RE: Say More about Jenkins/SubVersion/SQL Compare
    We provide a Web-Based SAAS appliction and we host the database. The application is multitenanted, so we only have one version of the database “in the wild”.

    If you wanted to support multiple versions of your database in the wild, you’d have to create different versions of the update scripts, but the underlying process would be identical no matter what source and destination versions were (although it might be more difficult to automate).

    For example, if you had version out in the wild and you wanted to upgrade it to version, then you could check out the tagged versions and use SQL Compare & SQL Data Compare to generate the upgrade scripts. You could also use SQL Packager to create executables that you could distribute to clients (I’m not all that familiar with SQL Packager, so I can’t offer too much direction there).

  • William

    Say More about Jenkins/SubVersion/SQL Compare?
    Can you say more about how you do database schema source control and builds? Do you keep parallel SubVersion projects, 1) with just create object script(s) to create a fresh build at whatever version and 2) with some base create object script(s) plus a history of alter scripts for migration and upgrades?
    Maybe the above is moot as you only have one live database? i.e. you don’t install the product at clients’ data centers?

  • Brad Wood

    Excellent article and a couple questions
    Thanks for the writeup. We’ve had Jenkins set up to automate our stage and production web code deployments for a while. We’ve been using SQL Source Control for several month, but have yet to actually automate the migration of SQL code. I have a couple questions:

    1) The database you build from scratch, is that a database in use by one of your environments, or just a test database to see if the build passes?
    2) Are your DB developers using local databases when they develop, or a shared development server?
    3) How do you handle branching in your source control system in regards to SQL? More specifically, when you switch do you just synchronize the local copy with the target revision, or do you have a script to rebuild a development DB from scratch with all data necessary to run the app?
    4) How do you handle ad-hoc scripts such as a data backfill to a table which is not static data, so there’s nothing to commmit, but it needs to be run on the target environment when code is deployed?



  • Ernest.Hwang

    RE: Excellent article and a couple questions

    1) The database is built from scratch on our integration server. The build script suffixes the database with "_CI", so if the automated build of the "MyDatabase" database would be named "MyDatabase_CI". It is immediately destroyed after it is created.

    2) Most developers have local copies of the database on their own machines (this is strongly encouraged). We also have a server that they can use as their source control baseline if they have hard drive capacity issues. On this server, there is one copy of the database per developer, so my copy of the database would be named "MyDatabase_ehwang".

    3) Branching is a touchy subject. We typically don’t branch the database, although we do have feature/team branches of our .NET code. If you want to branch your database builds, I would suggest having a copy of the database with separate Jenkins/CI jobs that build against those branches.

    4) The trunk of our repository has a /RedGateScripts folder which holds all of the SQL Source Control created content. There is also a /DeploymentScripts folder under trunk that has a folder for each release. We commit deployment related migration scripts in those folders. My understanding is that SQL Source Control 3.x has better support for migration scripts (we’re in the process of upgrading), but I haven’t used it personally.

  • Brad Wood

    RE: Excellent article and a couple questions
    Thank you for the answers. I appreciate the reply.

  • SpeakSQL

    SSIS deployment using Jenkins
    Hi Ernest

    Thank you for sharing nice article. How do you deploy SSIS? I posted my setup in the following link.
    Would you check when you have some time?