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)
{
try
{
if (!ResolveParameters(args)) return;
EvaluateVariables();
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);
WriteScriptToFile(script);
}
if (waitForInput)
{
Console.WriteLine("Press [Enter]");
Console.ReadLine();
}
}
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"/>
</exec>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.
Posts
