SQL Schema Comparision in Visual Studio 2010

We all know there are tools available for comparing database schema, and much of them are advanced to compare at the binary level. As we are aware, these tools are never available free and though they supply with free-to-try concept but with restricted functionality.

So, what a tool should possess in order to satisfy the below common requirements –
* Availability
* Unrestricted version
* Basic functionality such as report the differences and generate script out of it
* Filter different set of objects within RDBMS
* Write updates on the target database
* Can work with different RDBMS

Visual Studio 2010, as we all aware, this IDE is evolved a lot since its inception. With the recent advancement, VS team has come up with the most of the in-built options to make developer’s life easier – viz., Intellisense, Advance Debugging, IntelliTrace, Architecture and UML Designing, to name a few. I am sure, as developers, we do not use each feature very rigorously in our day-to-day development. If developer is involved in traditional development cycle – like writing code and storing data in SQL Server database, than there are chances where developer has to switch between the 2 different IDEs’ – Visual Studio 2010 for coding and development, whereas SQL Server Management Studio (SSMS) for database operations. Luckily, they both belong to MS platform!

Common database operations –
* Creating & Altering Tables
* Writing Stored Procedures, Views
* Creating UDF
* Writing Packages and Scripts
* Generating (change) scripts
* Backup and Restore

In VS 2010, as a developer, you can do most of the database tasks without switching to SSMS. Like creating and altering objects (tables, views, stored procedure), complex Select statements, etc. and I am sure, this is something most of the developer are aware of and if they are working in controlled environments, there are chances these tools are being used very extensively. There are times, during development, developer will change staging database and want to replicate it on production during roll-out.

But, when comes to replicating database changes, how many of us write database changes in notepad (Yes! I still write) to generate change script, and at times, when working in a team, these changes are written on team’s whiteboard for a successful roll-out. That’s when database comparison tool rescue developers. However, on cost effective projects, management usually avoid to buy such tools, and who knows how effective these tools would be and how much time has to be spent in learning those tools.

Schema Compare in VS2010 can rescue you in such hard times, not fully, however to some extent. It fulfills above basic requirements, except support of different RDBMS. It only supports SQL Server.

Below is feature usage and you can quickly learn and make use of it in your hard times.

Goto Data menu >> Schema Compare >> New Schema Comparision

You will be prompted with a dialog box ready to accept the SQL Server connection of your source (let’s assume Staging) and target (let’s assume Production) database.

Once configured, you can set the comparison options by using Options and SQL CMD variables.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

Blog at WordPress.com.

Up ↑

%d bloggers like this: