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:
- Direct from source control: for this we can point sql compare directly at source control itself
- 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:
I then saved this file and opened it in my text editor:
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.
Note – One thing I have noticed is that sql compare will only execute the migration scripts if it detects some conflict.