Thursday, August 21, 2008

Managing Continuous Database Integration with Red Gate. Part II. "The Achievement"

When you done with bitching the rest is easy. It is confirmed that you need nothing but SQL Comparison SDK (currently in version 7). Help projects are pretty good and offer valuable help. You will be using SQL Compare API to generate the structure update script and SQL Data Compare - for data upgrade script (i.e. for a lookup tables).

Another option is to purchase the whole SQL Toolbelt. Version 7.0 includes SQL Compare and SQL Data Compare Professional, which is necessary to run these tools from a command line. This approach will cost you dare $1600 and your options are pretty much limited to a batch scripting so I decided to stick with the API.

You have to download SQL Toolbelt and install SQL Compare, SQL Data Compare and SQL Comparison SDK. You have to pay for the SDK license only (I guess this means that you won't be able to use Toolbelt UI).

The project has to refer the RedGate.Shared.SQL, RedGate.Shared.Utils and RedGate.SQLCompare.Engine libraries, while RedGate.Licensing.Client, RedGate.SQLCompare.ASTParser and RedGate.SQLCompare.Rewriter DLLs should reside side-by-side with listed above in order the project to compile. My test solution uses only SQL Compare so I guess there will be couple more required for the SQL Data Compare.

At some point I've created a license.licx filie, which contains one line

RedGate.SQLCompare.Engine.Database, RedGate.SQLCompare.Engine
but I migrated solution from version 6.0 to 7.0 since then an I am not sure if it still required.

This is the core code:
private static string folder; //folder
private static string server1; //DEV Server 
private static string server2; //BAT server
private static string db1; //DEV database
private static string db2; //BAT database
private static string file; //script file name
private static bool waitForInput=true;
private static bool verbose=true;

static void Main(string[] args)
if (!ResolveParameters(args)) return;

using (Database widgetDEV = new Database(), widgetBAT = new Database())
var options = Options.Default;
widgetDEV.Register(new ConnectionProperties(server1, db1), options);
widgetBAT.Register(new ConnectionProperties(server2, db2), options);

var dev_bat = GetDifferences(widgetDEV, widgetBAT);

if (verbose)
Write(null, dev_bat,
item =>
string.Format("{0} {1} {2}", ((Difference) item).Type,
((Difference) item).DatabaseObjectType, ((Difference) item).Name));

var script = GenerateScript(dev_bat);
if (verbose) Write("Script length:", script);
if (waitForInput)
Console.WriteLine("Press [Enter]");
catch (ApplicationException ex)
Console.WriteLine("ERROR OCCURED: " + ex.Message);

static string GenerateScript(Differences dev_bat)
// Calculate the work to do using sensible default options
var work = new Work();
work.BuildFromDifferences(dev_bat, Options.Default, true);
if (verbose) Write("Messages:", work.Messages, item => ((Message) item).Text);
if (work.Warnings.Count>0 && verbose)
Write("Warnings:", work.Warnings, item => ((Message) item).Text);
var script = (work.ExecutionBlock!=null)?work.ExecutionBlock.GetString():null;
return script;

private static Differences GetDifferences(Database widgetDEV, Database widgetBAT)
var dev_bat = widgetDEV.CompareWith(widgetBAT, Options.Default);
foreach (Difference difference in dev_bat) difference.Selected = true;
return dev_bat;
The omitted methods are simple but tedious argument extractors, file writers and loggers.The application compares a development database DEV (which had some changes) with testing/production database BAT and generates one-way upgrade script for the latter. At this point I didn't find out yet how to include objects, which were removed from the DEV database, and if this feature even required. This kind of functionality can present some potential danger.

The NAnt script calls the provided EXE with the following task:

<exec basedir="${CCNetWorkingDirectory}/Binaries/UpgradeGenerator"
program="UpgradeGenerator.exe" timeout="10000">
<arg line="s1=dbserverDEV s2=dvserverBAT
db1=testdb_dev db2=testdb_bat f=update-${CCNetLabel}.sql"/>
The rest is obvious. Once again - the sample projects provide great insight for the functionality.

There are few things to consider, though.

My original intent was to keep the solution as a source code and check it out and compile during the build. This would require you to install the SQL Comparison SDL license on the server, otherwise it will be trying to fire popup and effectively kill the build. So I ended up placing a compiled executable in the Subversion, in Binaries folder, which effectively made it a part of any tracked project.

Another mystery, which still remains unresolved, is an unexpected behavior of the application which remains in the memory after execution. As a result the CC.NET worker thread hangs indefinitely and your build will be showing "Building" until you manually kill the generator process. As a workaround I limited a timeout for the <exec> task (refer the code above) and call this task with failonerror="false" attribute. This is a dirty trick, but it does its job on this stage.

As a conclusion I note that while more sophisticated approaches available, including your own NAnt task, even this simple approach works just fine.


Chris Barrow said...

Very, very nice. This completes the entire CI package doesn't it? Too bad the work to get there is incredibly painful.

Michael Goldobin said...

... and it's not free. Too bad that Reflector is to follow.

Zarathustra said...


Thanks for your enthusiasm about the SDK! Red Gate is working on making the API usage a bit easier and cheaper. You may want to have a look at as I have build a (very minimal) installer with (almost) everything you'll need to start coding with the APIs.

Part of this installer is similar to what you're doing -- trying to deploy/upgrade the sample databases. The deployment uses the SDK, and it dynamically compensates to user changes in the databases if they already exist. For instance, it will not drop objects that do not exist in the reference, but do exist on the 'live' database. This is done by deselecting 'missing' objects:

if (!quiet) Console.WriteLine("Settings: protect existing objects");
foreach (Difference d in diffs)
if (d.ObjectIn1 == null)
d.Selected = false;
if (!quiet) Console.WriteLine("Preserving " + d.Name);

You could use the same logic to preserve objects that you have dropped in DEV, otherwise they will also be dropped in PROD.

Hope this helps!


Michael Goldobin said...

Thanks Brian! I definitely don't want to see automatic drops in the target database - it will make more harm than good.

Michael Goldobin said...

Now if somebody could explain me what I did wrong to let the executable remain in the memory and freeze the build. Should I close the Database objects some specific way?

Lovemore said...

I can't reproduce the problem you are having with the hang. There is one object which you haven't disposed of, which is the ExecutionBlock, but this doesn't seem to make it hang.

Have you tried pausing execution and seeing what threads are running, or using a memory profiler?

© 2008-2013 Michael Goldobin. All rights reserved