Blog - comments

Hi Olivier, That's a good question. I think the reason is that data_object_id cannot identify a segm...
@toms and others: -- create your output: iozone -Rab output.xls /u01-- scp off to desptop, or to loc...
Scott
Hi Frank,Very interesting post ! Congratulations for starting an Oracle 7 ; did you virtualize an HP...
Olivier Berthommé
Belle démo, je crois que je vais te la piquer ! Mais as tu essayé un DBCC CHECKCONSTRAINTS ou un DBC...
SQLpro

Thanks Franck , you are a savior! Great article indeed.

Prasad
Blog Stephane Haby SQL Server 2012: SQL Server Data Tools (SSDT) - Schema and Data Comparison

dbi services Blog

Welcome to the dbi services Blog! This IT blog focuses on database, middleware, and OS technologies such as Oracle, Microsoft SQL Server & SharePoint, EMC 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 our blog postings.

  • Home
    Home This is where you can find all the blog posts throughout the site.
  • Categories
    Categories Displays a list of categories from this blog.
  • Tags
    Tags Displays a list of tags that have been used in the blog.
  • Bloggers
    Bloggers Search for your favorite blogger from this site.
Posted by on in Technology Survey

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.
  • Support
    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...Wink

 

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…Innocent

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

 

The results?

Three types of actions appear:

  • Delete
  • Change
  • Add

Click on each item to expand it and see the actions for the destination.


You can see the script in the object Definitions Tab.

You can scroll the green bar to see the code, it's fun Cool.

To apply changes, we have 2 choices:

  • Applying updates directly



  • Or indirectly, with a magic button

Click on Excute.

And an error occurs....

But why???

This is easy, in options, the checkbox Block on possible data loss is selected.

 

Conclusion

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.

Rate this blog entry:
2

Stéphane Haby is Delivery Manager and Senior Consultant at dbi Services. He has more than ten years of experience in Microsoft solutions. He is specialized in SQL Server technologies such as installation, migration, best practices, and performance analysis etc. He is also an expert in Microsoft Business Intelligence solutions such as SharePoint, SQL Server and Office. Futhermore, he has many years of .NET development experience in the banking sector and other industries. In France, he was one of the first people to have worked with Microsoft Team System. He has written several technical articles on this subject. Stéphane Haby is Microsoft Certified Solutions Associate MCSA) for SQL Server 2012 as well as Microsoft Certified Technology Specialist (MCTS) and Microsoft Certified IT Professional (MCITP) for SQL Server 2008. He is also ITIL Foundation V3 certified. He holds a Engineer diploma in industrial computing and automation from France. His branch-related experience covers Chemicals & Pharmaceuticals, Banking / Financial Services, and many other industries.


MCSA  MCSE  mvp

Comments

  • Guest
    Ali Monday, 12 November 2012

    where is the data compare?

  • Guest
    STH Tuesday, 27 November 2012

    Hello,
    At this time, SSDT does not do data compares.
    BR,
    STH

Leave your comment

Guest Saturday, 01 November 2014
AddThis Social Bookmark Button
Deutsch (DE-CH-AT)   French (Fr)

Contact

Contact us now!

Send us your request!

Our workshops

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

Expert insight from insiders!

Fixed Price Services

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

A safe investment: our IT services at fixed prices!

Your flexible SLA

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

ISO 20000 certified & freely customizable!

dbi services Newsletter