SQL Server Data Tools in Visual Studio 2013

Visual Studio 2013 has Sql Server Data Tools built in. (Pro, Premium and Ultimate) We use it to compare and sync databases.

The first time I started the data compare it did not work. When comparing the data between databases no tables could be selected.
data.comparison.missing.tables
The MSDN article about the SQL Server Data Compare explains why:

Tables and views must meet two criteria to appear in the listing. First, the schemas of the objects must match between the source and target databases. Second, only tables and views that have a primary key, a unique key, a unique index, or a unique constraint appear in the list. If no tables or views meet both criteria, the list will be empty.

Since my database was created using the Sql Server Export Data feature no indexes or keys were created. Makes sense as it only copies the data (hence the name)

Schema compare

To correct missing keys and indexes or other object differences, do a schema compare. After the compare, click Update Target to create the missing indexes and keys.
schema.comparison.missing.indexes
To save the changes for deployment use Generate Script.

Data compare

Data compare will now show data differences for the tables. There are none, because no changes were made after the Sql Server Export Data had run.
data.comparison.identical.records
When there are differences: as with schema compare you can Update Target for immediate sync or use Generate Script to save the changes for deployment.

Final thoughts

The data tools are a good option when you can connect to both environments with Visual Studio. You can create scripts for the changes and use that for your deployments.
For the initial setup with base data Microsoft has the bacpac files. You can create them with the commandline tool sqlpackage.exe. (download)

About erictummers

Working in a DevOps team is the best thing that happened to me. I like challenges and sharing the solutions with others. On my blog I’ll mostly post about my work, but expect an occasional home project, productivity tip and tooling review.
This entry was posted in Tooling and tagged , , , , , . Bookmark the permalink.

4 Responses to SQL Server Data Tools in Visual Studio 2013

  1. Alberto Rechy says:

    Hello, where do I find this features? I mean in the menu… thanks

  2. I don’t have SQL Server in my tools menu. How can I install the SSDT in VS2013?

    • erictummers says:

      Since SQL Server tooling is included in VS, the updates will be pushed through VS Update and users will be prompted when VS is open. If you’d like to check for updates manually, open Visual Studio 2013 and choose the Tools > Extensions and Updates menu. SQL Server tooling updates will appear in the Updates list.
      msdn article

Leave a Reply

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 )

Facebook photo

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

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.