Red-Gate SQL Compare

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.

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