Microsoft – SQL Server – Sql Server Data Tools (SSDT) [v2012] – Comparing Database Schema

Introduction

Software Development cycles is a series of stages. One Develops, fine tune, deploy, debug, and repeat the process.

For Database applications, part of that cycle involves comparing schema and data between SQL Server Instances, as well.

Background

If one has not had to compare Schema in a while, then it is worthwhile to Google and see whether there are new tools since the last time.

In the next few sections, I will cover my experience with Microsoft SQL Server Data Tools.

Supported Operating Systems

Here are the list of Supported Operating Systems:

  • Windows 7 SP1 (x86 and x64)
  • Windows 8 (x86 and x64)
  • Windows Server 2008 R2 SP1 (x64)
  • Windows Server 2012 (x64)

Download

Download “SQL Server Data Tools” from “SQL Server Data Tools – October 2013 update (Visual Studio 2012)” [http://msdn.microsoft.com/en-us/jj650015].

Choose the ISO Image route and used SlySoft Virtual Drive (http://www.slysoft.com/en/virtual-clonedrive.html) to mount the image.

Install

Install the tool.

Usage

Start “Microsoft SQL Server 2012 / SQL Server Data Tools for Visual Studio 2012”

From you start menu, please start “Microsoft SQL Server 2012 / SQL Server Data Tools for Visual Studio 2012”.

Here is a copy of our start menu:

SQLServerDataTools in start-menu

 

 

Launch Schema Compare

If this is first time using the Application, prepare for a relative slow start….

Launch SQL Compare by using the menu – SQL / “Schema Compare”.

 

Hint

You want to pay attention to the Status bar at the bottom of the Screen; as it indicates the current stage of processing.

Select Target

In the screen below, we can see that the system is asking us to “Select source and target”.

SelectTarget

Select Target Schema

Upon us choosing the “Select Target” drop-down, the system displays the dialog shown below.

SelectTargetSchema

Select Target Schema – Connection Properties

For the target schema, please choose the Server name, authentication type (Windows or SQL Server Authentication), and Database.

SelectTargetSchema-ConnectionProperties

Select Target Schema – Connection Properties – After

By clicking the OK button on the previous screen, we are returned to the “Select Target Schema” screen.  And, our Target entries are accepted.

SelectTargetSchema-After

Select Source

Please click the “Select Source” drop-down to indicate the Source SQL Server data source.

SelectSource

Select Source Schema

Upon us choosing the “Select Target” drop-down, the system displays the dialog shown below.

SelectTargetSchema

Select Target Schema – Connection Properties

For the target schema, please choose the Server name, authentication type (Windows or SQL Server Authentication), and Database.

SelectTargetSchema-ConnectionProperties

 

Prepare To Compare

Once the Source and Target data sources have been selected, it is now time to click on the “Compare” button:

PrepareToCompare

Results of Comparison

Upon clicking the Compare button, the system reads the Source and Target schemas; and presents the differences:

Differences

Error Occurred – See Error List for Details

In cases where errors are encountered, you will see the list of errors detected.  The errors are listed in the “Error List” group box.

Error Occurred – View Error – View any definition permissions

In our case, the error we received was very helpful and explicit.   The specific error being “the login for the target does not have the View Any definition permission“:

Error-ViewError-ViewAnyDefinitionPermissions

Error Occurred – Management Studio – Script failed

In Management Studio, verified same – This property may not exist for this object, or may not be retrievable due to insufficient access rights.  The text is encrypted:

Error-Scriptfailed

Conclusion

Microsoft is really making a lot of engineering available for free.  And, it is very commendable.

References

References – Microsoft – SQL Server Data Tools

References – Comparison

 References – Permissions

4 thoughts on “Microsoft – SQL Server – Sql Server Data Tools (SSDT) [v2012] – Comparing Database Schema

  1. Good info, but one more piece desired. How do you clear “old” items in the list out? In my Database drop down, there is a list of databases that no longer exist, how do I purge those?

  2. In the Select Target Schema screen, there are 3 radio buttons Project, Database, Data-tier application. Under the Database list, how do I “clear” it?

Leave a Reply

Please log in using one of these methods to post your comment:

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