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 –
* 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.