dbi services Blog
Welcome to the dbi services Blog! This blog focuses on IT infrastructure - featuring news, troubleshooting, and tips & tricks. It covers database, middleware, and OS technologies such as Oracle, Microsoft SQL Server & SharePoint, Documentum, MySQL, PostgreSQL, Sybase, Unix/Linux, etc. The dbi services blog represents the view of our consultants, not necessarily that of dbi services. Feel free to comment on the postings!
SQL Server 2012: SQL Server Data Tools (SSDT) - Schema and Data Comparison
In my life prior to SQL Server, I worked with Visual Studio Team System (VSTS). The "Schema and Data Comparison" feature of SQL Server 2012 Data Tools is a direct descendant of Visual Studio for Databases Professionnals (aka Data Dude) included in VSTS.
What is SQL Server Data Tools?
These new tools replace BIDS (Business Intelligence Development Studio). But they are more than just relational database development tools.
SQL Server Data Tools (SSDT), code-named Juneau, is based on the new Visual Studio 2010 and you can use it for all BI projects with SQL Server Integration Services (SSIS), SQL Server Analysis Services (SSAS) and SQL Server Reporting Services (SSRS).
What are the new features?
First, as I said, this tool replaces BIDS.
- Declarative Schema-Based Database Design
One of the main changes is the ability to perform a schema-based database design.
It can automatically generate the scripts to deploy new database versions.
You dont have to create a multitude of ALTER scripts ... very nice!
- T-SQL Editing and Debugging
You have the full T-SQL IntelliSense to run and debug your code like scripts, Stored Procedures and other database objects.
- Development of SQLCLR Projects
You can create SQLCLR objects without Visual Studio. You can build, run and debug SQLCLR objects.
It's supported for SQL Server 2005 and later.
You can develop SQL Azure database project.
- Schema and Data Comparison
You can use it to find the differences between 2 versions of the same database.
This just introduces my subject...
How to install SSDT?
SSDT can be installed either by the installation setup wizard or using the command line:
- Setup Wizard
In Feature Selection>Shared Features, check SQL Server Data Tools.
- Command Line
Setup.exe /q /ACTION=Install /FEATURES=BIDS /IACCEPTSQLSERVERLICENSETERMS
It's funny to see that the command line installation uses BIDS, the old application's name...
How to run it?
Go to Start Button and in the menu Microsoft SQL Server 2012, click on SQL Server Data Tools.
And… alas! No SQL menu…
Ok, it’s just a mistake and the menu comes when I create an SQL Server project…
Now, I'm suddenly forwarded to a web page to download SSDT!
I was surprised, because I have already installed it with the SQL Server setup…
In fact, SSDT in SQL Server Setup allows you to create SSIS, SSAS and SSRS Project. But in order to compare databases you must download SSDT.exe from Microsoft web site here.
How to compare 2 schemas?
Run the downloaded SSDT.exe and restart SQL Server Data Tools
Now, we have this SQL menu, click it and select Schema Compare > New Schema Comparison
A new project SqlShemaCompare1 is created and the first step is to select source and destination databases to be compared.
Don’t forget to click on the Test Connection button to ensure that your connection works.
Before you start a comparison, you can look and choose your options to customize the comparison
For further information about options, click here.
Then click Compare and wait.
In my demo, between AW2012_data1 and AW2012_data2, I just have changed the schema HumanRessources to HR as you can see in this script for AW2012_data2
Three types of actions appear:
Click on each item to expand it and see the actions for the destination.
You can scroll the green bar to see the code, it's fun .
To apply changes, we have 2 choices:
- Applying updates directly
Click on Excute.
This is easy, in options, the checkbox Block on possible data loss is selected.
I found that these tools are very easy and professional to use, with a lot of good options.
It can be useful for DBA or/and developers before upgrading a version of a software and its database.