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.