Microsoft Visual Studio SQL Data Tools provides functionality that allows you to compare the structure of the database tables, stored procedures, views, and other database objects. There are many occasions where you may need to compare the schema between two databases. For example, comparing Development and Production databases against each other, or comparing a database against a SQL Server Database project.
Let’s walk through a scenario in which we use Schema Comparison. I have two databases – say Database1 and Database2 – that each have a Customer table with the same fields:
First, open up Visual Studio and create a blank project. In the ‘Tools’ menu, select ‘SQL Server’ > ‘New Schema Comparison’.
A new window with two drop down menus – “Select Source” and “Select Target” – opens up. Here we can connect to our databases.
Specify the connection properties of the source (Database1) to connect to it.
Follow the same steps to connect the target (Database2). Now that we have both the source and the target databases set up, we can do an initial comparison by clicking the ‘Compare’ button. At the moment, the schemas of two databases are identical, as shown below:
Now, to demonstrate the true handiness of Schema Comparison, let’s make some changes in Database1 by adding an additional column ‘Email’ to the Customer table:
As we do one more Schema comparison, the new schema differences are detected and highlighted in the object definitions window. Imagine how incredibly useful this feature is with larger database schemas!
Currently we have two options to synchronize these schemas: we can either generate a script to update the target database or let Visual Studio do it all behind the scenes by clicking the “Update” button.
For now let’s generate the script:
Once the script is generated, it’s always a good practice to go over the generated statements in the script as a sanity check before executing.
Finally click “Update”. This will update the target (Database2).
Schema Compare is a very handy tool. It’s most useful in situations where you want to compare multiple databases in a complex application development environment, e.g. distributed servers with several databases that need to be synchronized at different releases.
Vasundhara Chimmad
Posted by Brian Pohl
