Using the Visual Studio 2010 Database Comparer Tools

I wanted to share one of the tools Visual Studio offers for database development.

When building an application, we often run into schema differences between two databases — say, development versus production, or, in my case, development split across teams in two cities. Keeping the schemas of two databases in sync, when both keep evolving to each developer's needs, can be hard work.

We used to handle this by manually noting every change, with a very high risk of human error — so our applications often broke on deploy because a schema update had been forgotten in the production database. Realizing this, we turned to the Database Comparer from SQL Effect Clarity (community edition) — and, importantly, it was free. :)

It helped a little, though in my view it had plenty of shortcomings and wasn't very user-friendly, probably because the free edition has limited features.

Eventually our company moved to Visual Studio 2010, and we explored its built-in Database Schema & Data Comparer. These tools are very user-friendly and highly accurate, which makes it much easier to sync databases between development and production, or between development databases split across two locations.

You select the two databases to compare, and Visual Studio shows the differing items; you then choose which to sync, and it generates the T-SQL for the update. The Data Comparer works similarly, generating T-SQL for the differing rows across tables, which you can run in SQL Server Management Studio while preserving the relationships in the schema you're updating.

We highly recommend the Visual Studio 2010 tools for syncing two databases.

* We use Visual Studio 2010 Ultimate Edition; other VS 2010 editions would need further testing.
** The database used is SQL Server 2008 R2. We haven't explored others such as Oracle, DB2, or MySQL.

— Rizal Ricky Rumanda