We talk a lot about continuous integration here on the Atlassian Dev Tools blog, and many readers are bonafide CI gurus.  Now that you are integrating your application code, test code, config files and deploy scripts, are you ready to take it to the next level?   An increasing number of engineering shops are starting to bring the continuous integration discipline into their database development.  

If your first thought is “Say what?!”, then read on.  David Atkinson, product manager at Red Gate Software and Atlassian fanboy, was kind enough to take a few minutes with me to explain the basics, talk tools, and (bonus!) offer a free Red Gate license to you, our esteemed reader.  

The idea of “database continuous integration” is fairly new for most of us. What are some of the problems in software development DBCI addresses?

Yes, it’s a totally new concept to all but the most bleeding edge database developers. Continuous integration is driven by code changes checked into source control, and database source control itself is a recently emerging trend. However, we’ve seen extraordinary growth in our SQL Server Management Studio (SSMS) plug-in SQL Source Control. It connects SQL Server to your existing source control system – SVN, TFS, Git, Mercurial, Vault and all others with a command line. Recently we’ve been inundated with questions about how to integrate databases into both the continuous integration and the wider continuous delivery process.

A continuous delivery environment tries to simulate production conditions as much as possible. The problem for databases is that production has existing mission critical data that must be persisted. You can’t just delete the binaries and replace them with new ones. Therefore to test the deployment of a new database version in an automated process, you need to automate a deployment process that preserves the state of the data. This is hard but we now have a solution, which means that throughout the project lifecycle, the development team can have confidence that they have a fully working and validated upgrade script.

Currently there are seven Red Gate tools that can take part in the continuous integration story: SQL Source Control, SQL Compare, SQL Data Compare, SQL Packager, SQL Test, SQL Data Generator and SQL Doc.  We believe that our tools are very cost-effective and will provide a return on investment very quickly.

Sounds like a good way to help DBAs spend less time manually executing scripts in the various environments, and focus on more interesting things like improving DB performance, failover & risk mitigation, etc.

Exactly. The beauty of automation is that once it is up and running, it provides daily value with no additional effort required.

When moving toward DBCI, what components need to go into SCM? Schema definitions? Deployment scripts? Copies of the data itself?

Committing a change with SQL Source Control

To maintain an object-level history, SQL Source Control scripts out objects into source control as their SQL creation scripts. The SQL Compare command line is in charge of generating the upgrade script. It does this by diffing the latest schema in source control with the version that corresponds to what is deployed in production. However, there are a number of cases where this doesn’t work, such as adding new NOT NULL columns, renaming tables or any other refactoring that requires data migration to be considered. We have introduce a ‘migration scripts’ feature that allows developers to supplement the SQL Compare engine with the developer intent. This is the key to making database continuous integration work. The full upgrade script is now built up from a combination of auto-generated scripts and developer-contributed scripts.

So let’s say you’re building an application that operates on demographic data, and must add a table to track customers’ zip code, birth date, etc. A developer or DBA commits some SQL containing the table definition, the continuous integration server kicks into gear, and it invokes SQL Compare somewhere in the build process?

Yes, we ship SQL Compare Pro with a command line interface, sqlcompare.exe, which gets called either directly by the CI tool, or indirectly via a NAnt, MSBuild or Powershell script. This command line interface is used not only to generate the scripts, but also to generate difference reports, and to validate the outcome of applying the scripts.

Can you use the same workflow to add new values to tables with static data (eg., enum tables or a country-to-currency mapping table)?

Indeed. In SQL Source Control you can designate certain tables as ‘static data’. These are saved in source control in the same way as the schema definitions. SQL Data Compare has its own command line, sqldatacompare.exe which can be used to deploy static data.

Linking static data in SQL Source Control

How do you feel DBCI impacts the great debate over whether developers should have personal dev DBs vs. sharing a common dev DB?

That’s a big question and frankly there are good arguments for both methodologies. The main advantage of having a personal database is to benefit from the sandbox, knowing that potentially breaking changes can be made without affecting the rest of the team. However, the majority of shops have shared development databases, mainly for practical reasons such as the size of the ‘test’ dataset. Most development teams develop on restored production backups to ensure that their modifications are compatible with real production data. Red Gate tools don’t try to prescribe which one the user should adopt, and will support both.

Right on.  Any closing thoughts for us?

Like for source control, continuous integration is increasingly considered a best practice with well-understood benefits. If management isn’t convinced, it’s usually a simple question of setting this up on a pilot project as a proof of concept, and the value of the automation quickly becomes clear.


Get Started with Red Gate for DBCI (on the cheap!)

To get started with database continuous integration you’ll need a tool to connect your database to your existing source control system. To achieve this quickly and easily Red Gate is kindly offering Atlassian users a free license of the SQL Server Management Studio plug-in SQL Source Control or the Visual Studio plug-in SQL Connect.  How cool is that?!?  To claim, send an email to michael.francis@red-gate.com and quote code ‘atlassianmay2012offer’.  The offer will be valid until June 30th 2012, and they’re asking readers to keep it to 1 free license per organization.

We hope to work with David and his cohort at Red Gate to bring you a SQL Compare task plugin for Bamboo.  In the meantime, you can use a Command task to invoke sqlcompare.exe from your Bamboo plan.  

To keep learning about topics in database development and continuous integration, join the 10,000+ people who follow @redgate on Twitter.  

Many thanks to David for taking time to help us understand more about this compelling new trend in software development!


 Not using Bamboo? Learn about easy import from Jenkins

Database Continuous Integration 101