SQL Schema Comparison with Visual Studio 2010

By: Arshad Ali -- 8/12/2010

Rating:<!-- Rating BEGIN -->

Win a Color Nook with SQL Server eBooks

Problem
Often we need to compare databases across two different environments (or even sometimes in the same environment) to identify the schema object differences, so that one database can be brought in sync with another one. For example, we normally do development on the development box; then during the build preparation we compare the development database with the QA/UAT/Production database to identify differences and generate schema objects for synchronization or incremental deployment scripts. How we can compare schema objects between databases and generate synchronization or incremental deployment scripts?

Solution
Although there are many tools available for schema comparison (some free and some you need to purchase) I am going to discuss the Microsoft Visual Studio Database edition for schema comparison. Microsoft Visual Studio Database edition offers several features for database development, for example you can create an offline representation of a database for database development and version control, Database Unit Testing, Code Analysis, Schema Comparison, Data Comparison etc.

In this demonstration I am going to show you how Schema Comparison works on Visual Studio 2010 Ultimate edition although you can do the same with Visual Studio 2005/2008 Database edition too.

Open the Microsoft Visual Studio IDE (Integrated Development Studio) and you will see a "Data" menu in the menu bar depending on the Microsoft Visual Studio edition you have installed (for more details click here). Select Schema Compare under Data menu and then New Schema Comparison as shown below.

You will see a dialog box like this, here you need to specify your source schema and target schema. The source or target could be either a database project (offline representation of your database) or the database itself (for which you need to set up a connection to your SQL Server instance) or a *.dbschema file. You can even save your selection as *.scmp file to use later on.

The moment you click on the OK button, it will start doing the comparison. You will notice a new tool bar, some of the options of this new tool bar are: you can filter out the comparison result as you can see in the image below, you can synchronize your target by writing updates to it (have a look on the note below before doing this), you can export your synchronization/incremental update script to a file or to the editor.

The schema comparison result screen will look like the image shown below. On the top pane there are four columns; Status tells the type of change of each object i.e.. if the object is New, Missing, Equal or has a different definition. The next column shows the name of the object at the source. Third column allows you to specify the kind of action which you want to take during synchronization for example if you want to Skip updating target, update target, drop from target if the object is missing in source, create on target if the object does not exist on the target. And finally the fourth column shows the name of the object at the target.

The next pane shows the object definition of the selected object. It marks the changes in different colors, the meaning of the color coding is available on the bottom of this pane as you can notice in the below screenshot.

The bottom pane shows the target schema update script depending on the selection of "Update Action" of each object. If you are not able to see it or want to refresh, click on "Refresh Update Script" icon on the toolbar.

The Schema Comparison tool allows you to specify the different schema comparison options as well as specify which types of objects are to be compared as shown; make note here by default the Schema Comparison tool ignores extended properties, you can change this default behavior as well in the comparison options:

Note: As long as your source has new objects there is nothing to worried about, but if you have renamed your objects or columns at the source you might incur data loss while updating the target. For example in the above schema comparison result image you can see the EmployeeID column has been renamed to BusinessEntityID, so while updating the target it will drop the EmployeeID column and add the BusinessEntityID column which will have no data even though the EmployeeID column has data in it. So it is recommended to review your deployment scripts and take a backup of your target database before updating the target database. You can change this default behavior of data loss in the Schema Compare Options, as discussed above, and check the "Block schema updates if data loss might occur" under Script generation options and then the generated script will look like this if it would cause any data loss.

/*
The column [HumanResources].[Employee].[ContactID] is being dropped, data loss could occur.
The column [HumanResources].[Employee].[EmployeeID] is being dropped, data loss could occur.
The column [HumanResources].[Employee].[ManagerID] is being dropped, data loss could occur.
The column [HumanResources].[Employee].[Title] is being dropped, data loss could occur.
The column [HumanResources].[Employee].[BusinessEntityID] on table [HumanResources].[Employee] must be added, but the column has no default value and does not allow NULL values. If the table contains data, the ALTER script will not work. To avoid this issue, you must add a default value to the column or mark it as allowing NULL values.
The column [HumanResources].[Employee].[JobTitle] on table [HumanResources].[Employee] must be added, but the column has no default value and does not allow NULL values. If the table contains data, the ALTER script will not work. To avoid this issue, you must add a default value to the column or mark it as allowing NULL values.
*/
IF EXISTS (select top 1 1 from [HumanResources].[Employee]) RAISERROR ('Rows were detected. The schema update is terminating because data loss might occur.', 16, 127) WITH NOWAIT
GO