Continuous Database Deployment with Red Gate – Step 1

2 Flares Twitter 0 Facebook 0 StumbleUpon 0 Google+ 2 LinkedIn 0 2 Flares ×

So I’ve been doing a lot of work lately with some of the tools from red-gate (http://www.red-gate.com/) – namely sql source control, sql schema compare and sql data compare. They really are a great set of tools, and I cant recommend them enough.

A lot of my recent work has been working with a continuous integration / deployment set-up, with support for continuous database deployment. I’ve done continuous database deployment before, and its never easy – in fact, its usually very complex. The complexity isn’t in the deployment of a database from scratch, but in the upgrade process. In steps the red-gate tools!

This set of articles is going to roughly outline some of the steps I’ve gone through in this process, and some of the kinks I’ve had to work out on the way. The base for this project is that I’m using TFS for source control, and have my databases checked in using SQL source control from red-gate. I’m using sql schema and data compare version 10.2 to push the changes around.

Step 1 – Get it working from the command line

SQL compare allows us to do a number of things, but what I’m interested in is deploying directly from our source controlled version of the database. For this I have 2 options:

  1. Direct from source control: for this we can point sql compare directly at source control itself
  2. Scripts Folder: here we can point sql compare at the database scripts folder (the one created by sql source control

My preference was to use a scripts folder – when I’m working with continuous deployments, I most often like to use a method that creates a deployment package that consists of all the parts of that specific build for the deployment. For the most part this can work with sql compare, HOWEVER – when deploying from a scripts folder migration scripts WILL NOT be picked up!

This was a deal breaker for me as the migration scripts feature is extremely powerful and is what enables the use of sql compare as an upgrade path. So the only choice was to go direct to source control.

My first step is always to get it working from the command line locally – its just common sense! Once you have it working locally the rest is pretty simple. When executing sql compare from the command line I much prefer to use an args file – the /argFile parameter allows you to move the command line arguments into a separate xml file – it is way more manageable! So my command would look fairly simple – something like this:

The complex bit is in the OrdersDatabaseArgsFile.xml:

To explain this a little:

  • Line 3 – this instructs sq compare to use source control as the comparison source
  • Line 4 – this specifies the specific version to sync to – in this case the keyword HEAD is the latest version. Here you could specify a specific changeset number (sql source control uses changeset numbers for versioning)
  • Line 5 – Is an xml file that points at the scripts folder location in source control – explained below
  • Line 6 – Is an xml file that points at the migration scripts folder in source control– explained below
  • Line 7 – Specifies the database server of the target database
  • Line 8 – Specifies the target database name
  • Lines 9 – 14 – Specify which database objects to include in the sync
  • Line 15 – This flag stops sql compare throwing an exception when both the source and target are identical
  • Line 16 – Instructs sql compare to perform an synchronize as well as the compare

Scripts and Migrations Folder Xml

One thing you’ll notice about the script above is the use of the scriptfolderxml and migrationfolderxml switches – I have to say here that I know these switches are marked as deprecated, but it was the only way I could get it to work! These switches point at xml files which tell sqlcompare how to locate the source control repository. They look something like this:

Scripts Folder XML

Migrations Folder XML

I worked out the format of these xml files by first of all creating the equivalent compare project in sql compare:

SqlCompareSetup

I then saved this file and opened it in my text editor:

SqlCompareContents

Notice in here that there are a elemtns corresponding to the ScriptFolderLocation and MigrationsFolderLocation – I simply copied the contents of these elements, decoded them at http://coderstoolbox.net/string/ and put them in  corresponding xml files. Running this should get sql compare deploying the latest head version with migration scripts, via the command line.

Please let me know if your need any further explanation, and I should follow this post with more details on how this was added to my TFS build.

Matt

Note – One thing I have noticed is that sql compare will only execute the migration scripts if it detects some conflict.

, , , ,

  • http://www.blogger.com/profile/01464518875535406193 Gary Pritchard

    Thanks for the clear explaination. I;d got about half way before I got stuck with the automated deployment from Source Control.

    One really handy resource which will help anybody else trying to set up a database CI environment is the free RedGate book for SQL Server team development which can be found here (http://www.red-gate.com/community/books/sql-server-team-based-development)

  • http://www.blogger.com/profile/13764961404030766662 Sammy Shields

    This must be added to the sql server support services for Red Gate service providers. It is similar to the Linux PostgreSQL using SPIRES.

  • http://daniellebruce.livejournal.com/ daniellebruce

    My colleague told me that those tools are great. I am interested to try this soon because I’m now convinced that it is really useful.

    -Danielle Bruce

  • http://www.blogger.com/profile/00510436120127859388 James

    Nice blog post- another way to get the XML is to look at the DB you’ve got linked to SQL Source Control. There’s some database level extended properties holding the XML settings you can simply copy and paste into your xml files.

    • http://www.blogger.com/profile/17691292108440292640 Matt Whetton

      Good point – I’ll look at updating the post with your suggestion!

  • Neil Cross

    Good post Matt, good work on wading through the SQL Compare marsh and getting this working. One small thing, your xml code samples are having their xml stripped, and just show as a list of strings. Would you mind fixing them so we know the required content?

    • http://www.codenutz.com/ Matthew Whetton

      Hey Neil,

      Yeah, getting all the sql compare stuff working can be a bit of a challenge!

      Apologies about the xml sections – some of this apparently got lost when I migrated my blog – I’ll dig out the original and update this article today.

    • http://www.codenutz.com/ Matthew Whetton

      I’ve just updated the xml snippets now.

      Good luck!

2 Flares Twitter 0 Facebook 0 StumbleUpon 0 Google+ 2 LinkedIn 0 2 Flares ×