SSDT – Schema Comparison in SQL Server using SQL Server Data Tools
Microsoft has recently released a tool/plug-in for Visual Studio called SQL Server Data Tools (SSDT) code-named Juneau. It can be considered as a combination of various client tools used to access SQL Server and to do development. At a high level it includes the following:
Business Intelligence Development Studio (BIDS) Templates like SSIS, SSAS, and SSRS Project Templates etc.
Visual Studio Database Project Template (with quite a few enhancements).
Some functionality of SQL Server Management Studio or SSMS (Mostly development related). This can be accessed/viewed in a dedicated window called SQL Server Object Explorer.
And few other additional features.
This tool can be considered as a replacement for BIDS and SSMS when it comes to SQL Server BI Development. Following are some of the links related to SSDT:
Hardware/Software Requirements: http://msdn.microsoft.com/en-us/data/hh297027
Download Details: http://msdn.microsoft.com/en-us/data/hh297027
One of the features offered by SSDT is the Schema Comparison. At a high level, the Schema Comparison feature offers the following functionality:
Comparison of Schema between Source and Target Objects. These objects include Tables (Columns, Data Types, and Null Option etc.), Views, Functions, Stored Procedures, Indexes and a lot more. It’s a really long list of objects that can be compared including objects like Permissions (Users/Roles) etc.
Identify the schema differences and update the Target Database from within SSDT at the click of a mouse.
Generate a Script to update the Target Database with all the differences identified as part of the comparison.
In this article, I will walk you through an example of Schema Comparison for Tables between Source and Target Databases (TestDB).
To work through this example, create two databases (one on each server or two different databases with different names on the same server) with the same name TestDB. Run the TestDBSourceSchema.sql and TestDBTargetSchema.sql scripts in the Source and Target Databases respectively.
Once the Source and Target databases are setup and required tables are created in each of them, follow the below listed steps to do a quick schema comparison.
Step 1: Launch SQL Server Data Tools (SSDT)
Step 2: Launch New Schema Comparison Window
Step 3: Select the Source and Target Database Connections if they already exist. Else create a new connection for Source and Target Databases.
Step 4: Set the Connection Information for Source and Target Connections (if you are creating them newly)
Step 5: Click on Options button and select the General Options for Schema Comparison before starting the Schema Comparison Operation
Step 6: Select the Type of Objects to be included in the Schema Comparison. For this demo, we will only select Tables as shown below.
Step 7: Start the Schema Comparison. Ensure that the Source and Target is selected and click on Compare button to start the Schema Comparison.
Step 8: Verify the Schema Differences Generated by the Tool as shown in the below screenshot.
At this stage you can either generate a T-SQL script to update the Target Database or directly update the Target Database using the Update button as shown in the above screenshot.
Step 9: Schema Differences can also be viewed in the Object Definition window at the bottom as shown in the below screenshot.
Step 10: Exclude the objects which you do not want in the Target Database Modification script by right clicking on the appropriate item (or parent item/folder as appropriate) in the comparison results window and selecting “Exclude All” option as shown in the below screenshot. Use Exclude All option to exclude all the contents of a folder and to exclude an individual item, uncheck the corresponding Action check box (Updated on 2012-06-27. Refer to comment from Bill Gibson for more information).
Step 11: Generate the Modification Script for the Target Database by clicking on the Generate Script button.
TestDB_Update1.publish.sql script shows the Modification Script generated by the tool. Carefully examine the script and ensure that you understand it correctly before actually executing it on the Target Database.
A few of points to note here:
Avoid updating the Target Database directly from within the Tool. Instead, generate a Modification Script and then execute the script manually.
Examine the Modification Script generated by the tool and understand it to make sure you know what changes are going to be made on the Target Database.
In the script generated in this exercise, you can see that for the tables where columns are modified, the script actually creates another table (say xyz), loads the data into table, drops the original table and then renames this new table (xyz) to original table name. This approach can be really expensive if the table in question is huge say contains data in GBs.
Scripts referenced in this article can be downloaded from my SkyDrive.
Until we meet next time, happy learning
[Updated : 2012-06-27]: The code name Juneau was dropped when the product was officially announced last year – Refer to the comment below from Bill Gibson
Posted on April 26, 2012, in SQL Server, SQL Server 2012, SSDT, T-SQL and tagged Schema Comparison, SQL Server, SQL Server 2012, SQL Server Data Tools, SSDT, T-SQL. Bookmark the permalink. 2 Comments.