A Programmer's Dream

Red Gate's SQL Compare

Posted by Stephen Wrighton on 25 Aug 2011

I have a new tool in my toolbox of things I get to play--while being paid to do so.  This one is the SQL Compare utility from Red Gate. 

This tool is designed to allow a user to compare--and update-- the schema's between two disparate databases.

Which all I can say in regards to this is a hearty YAY!

After all, consider the project upon which I'm currently working, which is our CloudBuster Web Framework system. This project is basically the love-child between a standard .NET CRM utility and SharePoint.   It's a massive undertaking in Visual Studio, with roughly 20 discrete projects hiding within it, all as optimized for genericacy and ease of updating and extending as I can make them.

Anyways, we have roughly 25 clients on this framework so far. Some using it as internal intranets (the Sharepoint market space) and others using it as a CRM tool (the DotNetNuke/Joomla market space).

And this is while I'm performing active maintenance and development making the system better and more robust, and have more functions and all that sort of good jazz.

But, the point here is that that's roughly 25 databases that need to be synchronized every time I make a change to my development database or add in a new functionality.  And while the code base works fine for either database, it's a matter of allowing new functionality, even as older code gets refactored into newer and better paradigms that drive this need to occasionally synchronize database schemas.  Previously, this involved scripting out all the changes I could find between my development database and one of the in-production ones, and then running that script against every database, praying that a) I caught all the changes and b) none of these missing changes were breaking.  This meant visually comparing every database structure in every client database.  A process that took roughly 6 hours when I had to perform it against 15 databases back in May.

Well, I got SQL Compare, and was able to run it against all 25 databases over the course of 2 hours.  Two hours which included time to run backups of every database, as well as checking each client's application both before and after the update.

Fun, fun fun!

Tweet me @kidananubix if you like this post.