Doing multi-server development with MSSQL? You need Red Gate tools.
About a year ago, I came across the Red-Gate suite of products for MSSQL. Between then and now, I’ve used SQL Compare and Data Compare about once a week, sometimes more often. These tools are one of those things that, once you have them, you ask yourself, “How did I survive without it?”
For example, let’s say you’re working on a database locally. You add a new table to your database, hooking up a couple foreign key constraints, and in the meantime, you remove an obsolete table.
But unbeknownst to you, your coworker (or Steve in my case), has added a different table to his copy of the same database. Oh and he added a few lookup records to a static table.
How would you synchronize these databases only using tools shipping with MSSQL?
Everyone would do it differently, but I’d start by reversing time and clicking the “Save changes to script” button on the new table designer in Enterprise Manager. Then I’d email that .sql file to Steve which would add it to his database. Oh and those new records he added? He’d set up a DTS to me or dump the data to a text file or script and send it to me to run from Query Analyzer. Or maybe he’d just synch his schema to mine and then restore a backup I took of my database. But then I’d have to zip up the bak file, email it to him… ugh.
These are all very do-able processes. In fact, I’ve spent a LOT of time managing this type of process. But it’s a little kludgy, and far worse, it requires foresight, follow-through, manual merging and multiple steps.
Foresight to make scripted changes to the database and write down what changes are made.
Follow-through to actually push those changes to other servers.
Manual merging by the DBA of that server. (In the case of developer workstations, that means us.)
Multiple steps of scripting the change, sending it, getting him to update, and maybe back again. More steps means more chance for failure, more time spent.
The Red Gate tools eliminate all those headaches. Using SQL Compare, you just point your database to another database (LAN or Internet) and in a few short seconds, it identifies all differences. You check some boxes to declare which changes you want made, hit next-next-next, and it’s all done. Then you can reverse the process to update in reverse.
Data Compare works similarly (although more slowly) to compare data in columns in tables. You can tell it only to compare specific tables (to avoid those ten-thousand-row-monsters) and you can save any comparison project to rapidly execute it in the future.
Best couple hundred bucks you’ll ever spend if you work with MSSQL a lot.
Oh and by the way, there is a free SQL tool called SQL Prompt which will make your time spent in Query Analyzer downright *pleasurable*. It adds table and column insight for the database just like Homesite/Dreamweaver/Eclipse do for ColdFusion tags:
http://www.red-gate.com/products/SQL_Prompt/
Comment by Nat Papovich — November 21, 2006 @ 12:00 am
We use AdeptSQL (www.adeptSQL.com). It’s much nicer (integrated schema and data diff), IMO, and faster and cheaper! The version including the data diff tools is only $320 – a full $170 cheaper than the red gate bundle.
The developer support is also top-notch. We’ve never had an issue turnaround that took more than 24 hours.
Comment by Roland Collins — November 21, 2006 @ 12:00 am
Now that I discovered there is a setting to sync just a couple of specified tables instead of the entire db, i have fallen in love with it again. This is a truly remarkable tool.
Every developer should get a copy of this tool even if you work by yourself and need to update your own servers.
Comment by Steve Nelson — November 21, 2006 @ 12:00 am
Hey Dave. Not sure if you’ve heard of SQLYog yet, but if not, then you should definitely look into it:
http://www.webyog.com/en/
We’ve used that for MySQL Administration for years now, and it’s a really well-built tool. And while I’m not sure exactly how its synch/diff features compare to Redgate’s, it does in fact have these features built-in. We’ve always found it to be a super fast, exceedingly non-buggy piece of software.
Comment by Patrick — November 22, 2006 @ 12:00 am
We’ve been using Red-Gate products for a couple of years and couldn’t live without it. All our developers sync to a beta server, after QA we sync the beta server with the production box and all is right with the world. We also use Code Co-op (http://www.relisoft.com/) for CVS. The two products give us a highly reliable 1-2 punch for keeping everybody on the same page.
Comment by Dave Kopecek — November 22, 2006 @ 12:00 am
Does anybody know of a Red-Gate SQLcompare style product that works with MySQL ?
Comment by Dave Kopecek — November 22, 2006 @ 12:00 am
If you like those tools you should try their SQL Refactoring. I used it once aand it spread like a fire through our place. Format heaven.
BTW, Sync Database for MySQL, it does it pretty well. It’s made by Spectral something.
Comment by Paul Pareti — February 21, 2007 @ 12:00 am
There is also Quest software that do very good software although they are very expensive. I like particularly Performance Analysis and Litespeed.
During compression testing, Quest was ahead of red gate but I believe Red gate might be cheaper licence wise.
Comment by Clement Huge — June 28, 2010 @ 2:15 pm