I have been using Red Gate's SQL Compare since it was in beta, circa 1999 and I last wrote a product review back in April of 2004 when version 3.0 was released. I recently had occasion to download and use version 5 of the product (when I started doing more with SQL Server 2005), and I feel compelled to update my product review as well. The price is still right, starting at $295 for a single license. I continue to assert that it is one of the tools that no SQL Server developer should be without.
The basic function of this tool is, as the name implies, to compare two SQL Server databases. I won't bore the reader with step-by-step descriptions of what the wizard does, but suffice it to say that the user interface is so intuitive, you can be running a compare within minutes of installation and viewing the results, in some cases, within seconds ... it's that fast. The image below shows the screen that greets you when you first launch the product. Simply point it at two databases and click Compare Now. The Options tab allows you to fine-tune the compare parameters to avoid getting false positives and to filter differences you may not be concerned with, like developer comments in stored procs. For my sample run, the databases with 118 objects required less than two minutes to compare. The results screen is shown below.
Working The Results
The new interface is easy to read and convenient to use. The image above shows the Filter Objects panel visible, where you can refine your search for the objects whose changes you wish to script. Once you have made your selection of objects, the Synchronization Wizard walks you through the process of migrating changes to or from either database. In my first attempt, I could not get the synchronization to work. Another developer had made drastic changes to the tables, adding primary-foreign key constraints and additional data that was absent in my copy of the application. So, I guess there are limitations, but they are the same limitations I would have faced trying to script such changes from Query Analyzer. At least SQL Compare found all the differences and generated the change scripts, saving me hours of work. The time saved in this one project justified the purchase price of the product.
In addition to synchronization, you may export the compare results in any of a number of formats, as shown in the image below. The results generated by the Interactive HTML option are incredible, but alas, too difficult to display here. While I am not at liberty to display the results of the HPOD database used in the above compare example, I did generate a Simple HTML report for one of the stock databases that come with SQL Server when Reporting Services is installed. Give it a look and you will agree that version 5 of Red Gate's SQL Compare has really become a mature product: Simple SQL Compare Report
As I finish this review, I realize that, other than describing every mouse click required to generate a compare, there isn't much to tell. SQL Compare is a great product that does one thing, and does it well. There are a few more options that may be set and as in previous versions, you can create snapshots of your database and save the comparisons over time in a sort of pseudo-source-safe fashion, but what you see above is what this product is all about.
I have reviewed a lot of products over the years and while I can usually report that they perform as advertised, I cannot always say they provide genuine value. Red Gate SQL Compare is one of those rare products that have the potential to 'save the day' and make you look like a hero. SQL Compare is an indispensable part of my developer's toolbox.