David Duffett

Software Architect with a particular focus on user experience, .NET and e-commerce.
Posts I Like

This is part 2 of a series of posts on implementing continuous integration and automated deployment of your database.  See part 1 for an introduction.

Your first step in introducing continuous integration of your database should be to set up an environment where each developer is working on their own dedicated, local, database.  Think of this as much the same as your own local DVCS repository.  It allows the developer to do what they like to their own database (later I’ll show you how they can actually roll back database changes they have made if needed!).

1. Install Red Gate SQL Source Control

You can download SQL Source Control here.  It’s very easy to install.  Once it is installed you will see in your SQL Management Studio a new icon in your Object Explorer, and a SQL Source Control tab:

2. Create your version control repository

SQL Source Control supports Subversion, Git, Mercurial and many more version control systems.  I won’t go into the details of setting up a repository, but you will need to set one up in order to version control your database (duh).

3. Link your database to source control

In SQL Management Studio, click on your database, and the Setup tab of SQL Source Control.  You will see that it gives you an option to Link database to source control…

Click that and you will be presented with options to link your database to version control.  I’m going to link it to a Mercurial repository on my hard drive.

Make sure you choose the Dedicated database Development Model, and click Link.

You’ll now see that the Setup tab of SQL Source Control states that it is linked to your chosen repository. In that repository, you will see that it has created a folder structure for all the different database objects, however this folder structure is empty.  This is because you haven’t actually committed any objects to it yet.

4. Choose static data tables to version control

Static data that you might want to version control include reference records for enums (such as “status” records), or maybe reports or email templates that you update in each release.  To include these in version control, right-click on your database, choose Other SQL Source Control Tasks, and Link/Unlink Static Data.

This will show you a list of all your tables.  Simply tick the ones that you want to version control.

Note:  SQL Source Control can only version control the entire table, including all columns and rows.  So do ensure that your static data tables do not include timestamp columns, or references to other tables that could be different between your development, test and production environments.

5. Commit Changes

Now you are ready to commit your changes to version control.  Click on the Commit Changes tab, and click Commit!  Your database is now version controlled.

You can add a comment to your commit, and in a future post I’ll show you how your can link these comments to cases in your bug tracking software, such as FogBugz.

6. Roll out to the team!

Rolling this out to your team is as easy as them following the same steps above, except after they have linked their database to version control, they should go to the Get Latest tab in SQL Source Control to get all of the database objects you committed to version control.

Everyone should now be able to make local changes, commit when they are ready, and get everyone else’s latest changes from version control.

Still to come in this series: continuous integration, automated database deployment to your test and production environments, linking your database changes to FogBugz, and more.

  1. davidduffett posted this