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.
Agile interaction between client and development team is always about a trust. It is easier to achieve with internal clients - they may have already established relationship with the team, or at least they can be influenced. Bringing aboard external client usually means that at some point Agile process should be transitioned to whatever client is used to. Some unlucky Product/Project manager has to act as a filter to accept Agile feed from the team and produce some kind of acceptable output for a client and vise versa. When looking for vendors, companies rarely have in mind something different from "fixed price for fixed features in fixed time". Software industry doesn't really have well established mechanisms for gathering and sharing success and failure statistics, so it is widely believed that such contracts minimize risks and uncertainty. It seems to provide common ground for bidding contest but in reality vendors are forced to compete in wishful thinking with their fingers crossed.
Blame is a crashing attacking blow. Be the first to point fingers. Don't hesitate even if the accused is another Office Kung Fu Master - he/she has to first repel your thrust and his/her responce can be easily depicted as a personal attack on you, the very person who cares about the company so much. The Leech Spree is an example of an indirect attack, which can be executed by a phrase like "Somebody changed this file". This will ignite an avalanche of blame. Be careful when blaming groups - the other Master may use the Summoning Block against you. 
Precede blaming attack by taking credit first, thus multiplying the destructive effect (The Tsunami Swoosh). On the contrary, you can emphasize your value to the organization by putting the blame first and taking credit immediately after (The Slapping Blossom). 
Posts
