RSS

SSDT – Schema Comparison in SQL Server using SQL Server Data Tools

26 Apr

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:

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

About these ads
 

Tags: , , , , ,

2 responses to “SSDT – Schema Comparison in SQL Server using SQL Server Data Tools

  1. Bill Gibson, Microsoft, SQL Server Data Tools

    June 27, 2012 at 7:19 PM

    Hi Datta, Nice job!

    There’s a couple of minor things you may want to change or point out in your article. The product name is just SQL Server Data Tools – the ‘code name Juneau’ part was dropped last year when we formally announced the product.

    While I know you qualify the statement, be careful suggesting SSDT is a replacement for BIDS!

    To exclude an individual item from the update you just need to uncheck its action check box; Exclude All only applies to excluding the contents of a folder.

    And while I agree that checking the script is a good practice, the reason your change required the table to be rebuilt was you adding the SalariedFlag column in the middle of the table rather than at the end – it’s not something that Schema Compare does as a matter of course!

    Your readers might also want to check out the SSDT blog too – http://blogs.msdn.com/b/ssdt/

    Again, nice post!

     
  2. Dattatrey Sindol (Datta)

    June 27, 2012 at 11:07 PM

    Hi Bill,

    Thank you for your suggestions/corrections. I have updated the above article with your inputs.

    – Datta

     

What are your thoughts?

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s