Monday, December 3, 2007

Red-Gate SQL Compare

SQL Compare Pro Every now and then I come across a program that becomes so ingrained in my daily work that I hardly know how I'd get by without it.  I'll probably break down a couple over the next few days, but for database work, I have never found anything as good as Red Gate's SQL Compare and SQL Data Compare

Essentially these tools let you compare two SQL Server databases (all objects, users, permissions, functions, diagrams, anything) and update changes to whichever database you want.  This is amazingly useful for deploying database changes to a test or production environment (do it to production with ridiculous care, even though it will generate a SQL Script for you and run all updates in one transaction), and making sure everything is synchronized.

SQL Compare Window

SQL Data Compare Window For releases we can just generate the compare script, confirm that the changes match the updates we want to go out, and store it all in one place with the release details.  This is true for both the structure and the data, to get those lookups matching.  This has also been useful when pulling down production data to our test servers to debug problems specific users are having, and test solutions without ever editing the production data itself.

After a lot of tests with different tools, SQL Compare and SQL Data Compare are so simple and safe that we finally dropped the $600 for the SQL Compare Bundle.  The Pro versions look interesting, but for what we use, the comparison is all that's needed.

The interface and set-up is incredibly easy.  In the few years we've been using it, technical support has been great, though all I've needed support with is in getting my activations correct.

Once you choose what you want to synchronize, you go through some simple steps confirming what will be updated and how the update will occur.

All that greatness being said, there are probably three complaints I'd have about the program.

1) Walking through steps to generate the change script (or update the database) makes things easy, but I swear that with every new release they seem to add another step.  It gets to be annoying at times.

2) I think it's a bit expensive.  Not overly so at $600 per developer license, but given that it does just one or two things (Even though it does them exceedingly well) makes it hard to get a company to pay for it, for each developer.  That being said, if you have the personal cash, it's worth buying even if the job won't cover it.  Also, that $600 is for a year of updates, for future versions you have to essentially pay again (which is why we still use version 5 while 6 is now available).

3) The biggest failing is that it only compares SQL Server.  We've had times where we need to compare Oracle databases, and none of the tools available come close to the ease, speed or correctness or SQL Compare.  But we can't use SQL Compare.  So, going back to the cost question, it's hard to explain $600 per developer if you work on SQL Server and other database platforms.

All that being said, Red-Gate has continued to make the comparison and update process faster, incredibly simple to use, and I know it will make the correct updates to databases (many other tools messed up conversions or some other area . . . often a problem of tools which use ODBC to connect).  Their tag line is really pretty true, their stuff is "ingeniously simple to use".

Peace,
+Tom

6 comments:

Missa said...

??

Andras B said...

Hi Tom,
Being responsible for a large part of this compare tool I am always glad to read reviews like yours.
Concerning some of the complaints, please let us know via our forums what extra steps you find redundant, or how you think it can be improved. Our usability people are always open for suggestions. Version 6 has not added any extra steps :), indeed, one goal was to reduce the number of clicks for many common tasks, and make navigating even faster via filtering, searches, etc.
I will certainly save your request for comparing against other database management systems; however, it is a trade off between supporting comparison and synchronization at a very fine granularity and comparison across different systems.
Concerning the price, it is worth contacting our sales team. There are plenty of price breaks for upgrades and for multi user licences. Just as an example, a ten user license will give about a 50% discount. For five and ten user licenses these reductions are available on the website on the “add items to shopping cart” page, and our sales will be more than happy to help to find the best deal.
Kind regards,
Andras
Andras Belokosztolszki, PhD
Red Gate Software Ltd

Gerhard Pretorius said...

Hi Tom,

justa note: SQL Compare does NOT compare or syncronize Diagrams...

from the Manual:
http://help.red-gate.com/help/SQLCompare6/2/en/SQLCompare.htm

Tom said...

That's a good point. I hadn't realized it didn't do that comparison since I've always used it to sync databases to test or production, in which case we don't use the diagramming, but it would be useful since we don't use another ER tool.

Thanks!

David Atkinson said...

Hi Tom,
You mentioned the need to contact customer support to resolve your activation issues. We've tried to improve this by providing de-activation functionality. You can read about it by selecting the Deactivating Your Products from this page: http://www.red-gate.com/supportcenter/GeneralList.aspx?t=Licensing_Activating

Kind regards,

David Atkinson
Red Gate Software

David Atkinson said...

Not sure if you're aware, but Red Gate released Schema Compare for Oracle last year. We'd love to get your feedback!