Click here to monitor SSC
  • Av rating:
  • Total votes: 35
  • Total comments: 7
Ernest Hwang

Database Build and Release with Jenkins

26 July 2012

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 Red Gate 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
    • Red Gate SQL Compare is used to deploy the schema
    • Red Gate 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 Red Gate 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 Red Gate SQL Compare since around 2006, and discovered SQL Source Control in May 2011.  By June 2011, we were using Red Gate 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 Red Gate 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.  Red Gate 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 Red Gate, or take a look at these Deployment and Continuous Intergration whitepapers for a technical overview.

Ernest Hwang

Author profile:

Ernest is a senior software engineer at Practice Fusion in San Francisco and has been working with SQL Server since 1999. For the past year, Ernest has been using Red Gate SQL Source Control, SQL Compare, SQL Data Compare, and SQL Test to automate Practice Fusion's Continuous Integration efforts and instrument database deployments.

Search for other articles by Ernest Hwang

Rate this article:   Avg rating: from a total of 35 votes.


Poor

OK

Good

Great

Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.


Subject: Say More about Jenkins/SubVersion/SQL Compare?
Posted by: William (not signed in)
Posted on: Wednesday, August 01, 2012 at 3:36 PM
Message: 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?

Subject: RE: Say More about Jenkins/SubVersion/SQL Compare
Posted by: Ernest.Hwang (view profile)
Posted on: Wednesday, August 01, 2012 at 6:44 PM
Message: 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 1.2.3.4 out in the wild and you wanted to upgrade it to version 2.3.4.5, 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).

Subject: Say More about Jenkins/SubVersion/SQL Compare?
Posted by: William (not signed in)
Posted on: Thursday, August 02, 2012 at 12:44 PM
Message: 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?

Subject: Excellent article and a couple questions
Posted by: Brad Wood (not signed in)
Posted on: Tuesday, August 07, 2012 at 12:44 PM
Message: 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?

Thanks!

~Brad

Subject: RE: Excellent article and a couple questions
Posted by: Ernest.Hwang (view profile)
Posted on: Thursday, August 09, 2012 at 7:19 PM
Message:
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.

Subject: RE: Excellent article and a couple questions
Posted by: Brad Wood (not signed in)
Posted on: Friday, August 10, 2012 at 8:57 AM
Message: Thank you for the answers. I appreciate the reply.

Subject: SSIS deployment using Jenkins
Posted by: SpeakSQL (view profile)
Posted on: Friday, September 20, 2013 at 5:01 PM
Message: Hi Ernest

Thank you for sharing nice article. How do you deploy SSIS? I posted my setup in the following link.
http://speaksql.wordpress.com/2013/09/04/ssis-2012-continuous-integration-using-jenkins-and-octopus-a-journey-to-db-deployment-automation/
Would you check when you have some time?

Thanks.

 

Phil Factor
Searching for Strings in SQL Server Databases

Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine like Google.... Read more...

 View the blog

Top Rated

Continuous Delivery and the Database
 Continuous Delivery is fairly generally understood to be an effective way of tackling the problems of... Read more...

The SQL Server Sqlio Utility
 If, before deployment, you need to push the limits of your disk subsystem in order to determine whether... Read more...

The PoSh DBA - Reading and Filtering Errors
 DBAs regularly need to keep an eye on the error logs of all their SQL Servers, and the event logs of... Read more...

MySQL Compare: The Manual That Time Forgot, Part 1
 Although SQL Compare, for SQL Server, is one of Red Gate's best-known products, there are also 'sister'... Read more...

Highway to Database Recovery
 Discover the best backup and recovery articles on Simple-Talk, all in one place. Read more...

Most Viewed

Beginning SQL Server 2005 Reporting Services Part 1
 Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

Ten Common Database Design Mistakes
 If database design is done right, then the development, deployment and subsequent performance in... Read more...

SQL Server Index Basics
 Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... Read more...

Reading and Writing Files in SQL Server using T-SQL
 SQL Server provides several "standard" techniques by which to read and write to files but, just... Read more...

Concatenating Row Values in Transact-SQL
 It is an interesting problem in Transact SQL, for which there are a number of solutions and... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.