Introduction to SQL Server Data Tools

By:   |   Comments (13)   |   Related: > Tools


Problem

I am starting to work with SQL Server 2012.  One of the first things I noticed after installing it is that Business Intelligence Development Studio (BIDS) is no longer included.  Instead there is something called SQL Server Data Tools (SSDT).  Can you provide some details on the capabilities of this new tool?  Check out this tip to learn more.

Solution

SQL Server Data Tools (SSDT) is indeed the replacement for Business Intelligence Development Studio (BIDS) which has been included in versions of SQL Server prior to SQL Server 2012. SSDT has all of the capabilities of BIDS along with some nice enhancements.  In this tip I will provide an introduction to SSDT and focus on the following areas:

  • Installation
  • Creating a SQL Server Database Project
  • Modify Database Schema
  • Schema Compare and Update
  • Snapshot Project
  • Publish

In this tip I am covering what is referred to as "Project-Oriented Offline Database Development" in the MSDN documentation.  Simply put you work with database objects and your changes are not automatically made in the target database.  SSDT allows you to update the target database a couple of different ways which I will cover.

SSDT also supports "Connected Database Development" where you are making changes immediately to a target database.  I will cover these capabilities in a future tip.

Installation of the SQL Server 2012 Data Tools

When you install SQL Server 2012, SQL Server Data Tools is included in the features available as shown below:

SQL Server 2012 Installation Features

Click the check box next to SQL Server Data Tools to install.  After installation you can launch SSDT from the SQL Server 2012 Program Group in the Start menu.  SSDT is contained in the Visual Studio 2010 Shell; this is a subset of the Visual Studio 2010 tool that developers use to build Windows, web, etc. applications.  When you create a new project you will see the following options:

Visual Studio Projects for SQL Server

The Business Intelligence (BI) templates are essentially the same as what you had in BIDS with the exception of the Analysis Services Tabular Project which is a new feature in SQL Server 2012.  I'm not covering any enhancements in the BI templates in this tip.

The SQL Server project is one that you will use to develop and maintain a SQL Server database.  When you select the SQL Server project template, you will see the following option:

Option in Visual Studio for the SQL Server Data Tools

The first time you create a SQL Server project, you will be prompted to download Microsoft SQL Server Data Tools as shown below:

Download the Microsoft SQL Server Data Tools

This is somewhat confusing; didn't we install SSDT with SQL Server 2012? We did, but this piece is the SQL Server project that runs in the Visual Studio 2012 Shell.  If you're still confused just take my word for it that you'll want this newer version of the SQL Server project. After clicking install your browser will navigate to http://msdn.microsoft.com/en-us/data/hh297027; click the link to download SSDT for Visual Studio 2012.  When the download completes click Run to install it.  Note that the installation will upgrade your version of the Microsoft .NET Framework to 4.5 if necessary (installing SQL Server 2012 requires version 4.0 of the .NET Framework). In my case I have no issue with this since I have a brand new virtual machine that I'm using.

After this SSDT install completes you can now launch Microsoft SQL Server Data Tools 2012 from the Start menu.  When you do so you will see the following when you create a new project:

Microsoft SQL Server Data Tools New Project Interface

In the sections that follow I will be using the AdventureWorks 2012 sample database. You can download the database here (click the AdventureWorks2012 Data File link).  After the download is completed, I moved the .mdf file to the default location for my SQL Server 2012 instance; e.g. C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA and attached the database.

Create a SQL Server Data Tools Project

The SQL Server project allows you to develop a SQL Server database by using an integrated environment that provides the familiar Visual Studio tools that developers are accustomed to like code navigation, intellisense, validation, debugging, declarative editing, etc.  Database objects are organized in a project and stored in individual source files just like the way we develop applications in languages such as VB.NET or C#. Even if you aren't a developer you should find that this approach works very well although it may take a little time to get acclimated.

To create a SQL Server project, select File, New, Project from the menu in SSDT and fill in the project name and location as shown below:

To create a SQL Server project, select File, New, Project from the menu in SSDT

You can import the schema from an existing database, a Microsoft SQL Server DAC Package File (.dacpac), or a T-SQL script by right clicking on the project in the Solution Explorer and selecting Import. For this example select Database and fill in the following dialog (click New Connection to point to your database):

Import the schema from an existing database, a Microsoft SQL Server DAC Package File (.dacpac), or a T-SQL script

Click Start and you will see a dialog similar to the following showing the actions performed:

Import database actions performed in SSDT

After completing the import, you can see the individual schema objects in the Solution Explorer as shown below:

SSDT Solution Explorer

The Solution Explorer provides many capabilities such as:

  • Add object such as tables, views, etc. (right click, select add)
  • Edit an object (double click)
  • Compare the database schema (right click project, select Schema Compare) with a target database or Microsoft SQL Server DAC Package File (.dacpac)
  • Create a snapshot of the database schema (right click project, select Snapshot) in a Microsoft SQL Server DAC Package File (.dacpac); the snapshot can be used to create the database, import into a SQL Server project, perform a schema compare, etc.
  • Publish the database (right click project, select Publish) which essentially creates a new database using the objects in the project.

The above are just a few of the capabilities provided in the SQL Server project; right click on the project in the Solution Explorer to see the full list.

Modify Database Schema in the SQL Server Data Tools

In the previous section I created a SQL Server project and imported the schema from an existing database.  I now have a disconnected environment for changing and validating the database schema.  SSDT validates any schema changes I make against a local database that gets created automatically.  Let's add a column to an existing table.

Navigate to the Customer.sql file under the Sales, Tables node in the Solution Explorer; double click Customer.sql to show the designer:

Change the schema for the Customer table in SSDT

The table designer allows you to do the following:

  • Add new columns by filling in a row in the grid or editing the T-SQL
  • Change existing columns by using the grid or the T-SQL editor
  • Add keys, check constraints, indexes, foreign keys, or triggers (right click on object type then select add)
  • Edit keys, check constraints, indexes, foreign keys, or triggers (right click on object then select Switch to T-SQL Pane and make your changes)
  • Delete keys, check constraints, indexes, foreign keys, or triggers (right click on object type then select delete)

Let's just make a simple change; add a CustomerType column to the Customer table.  Fill in the grid as shown in the highlighted row:

Add a CustomerType column to the Customer table

Save the change by clicking the diskette icon on the toolbar or select File, Save Customer.sql from the menu.

Note that at this point we have not changed the table in the AdventureWorks2012 database; we have only changed the object in our database project.  In the next sections we're going to do a schema comparison of our project and the database, snapshot our updated schema, then update the database with the schema changes.

To add a database object right click on a node in the Solution Explorer and select the type of object to add; e.g. right click on Tables and select Add, Table; you will see the same designer as above.

Schema Compare and Update in SSDT

SSDT provides a schema comparison tool allowing you to compare two schemas, update the target schema to match the source schema, and / or generate a script to perform the update.  The output from schema compare shows the schema differences in a side-by-side display.  The options for schema targets are the database project, a particular database or a Microsoft SQL Server DAC Package File (.dacpac).  To begin a schema compare, right click on the project in the Solution Explorer and select Schema Compare.  By default the schema compare dialog is displayed with the project selected as one schema and a dropdown allowing you to specify the target schema to compare.  Click the Select Target dropdown and fill in the Select Target Schema with a database as shown below:

Schema Compare and Update

Click the Compare button to view the schema comparison; e.g.:

Click the Compare button to view the schema comparison in SSDT

In the previous section I added the CustomerType column to the Customer table; you can see above how the schema compare has identified the one and only change.

Note the Update button in the above screen shot and the icons to the right of it.  If you click Update the target schema will be updated to match the schema per the database project.  You can click the next icon to the right and generate a T-SQL script that you can run at your leisure.

Snapshot Project in the SQL Server Data Tools

The Snapshot Project function allows you to generate a Microsoft SQL Server DAC Package File (.dacpac) from the project.  Per the MSDN library a DAC is "an entity that contains all of the database and instance objects used by an application".  Think of it as a file that can be used to deploy your database as you've designed it in the database project.  To create a snapshot, right click on the project in the Solution Explorer and select Snapshot Project.  You will see the snapshot in the Snapshots folder in the Solution Explorer.  As mentioned previously you can use the snapshot as a target when you do a schema compare.  You can snapshot your project at specific milestones to keep a history of the evolution of the database project.

Publish you project with the SQL Server Data Tools

Publish allows you to deploy your database project to a SQL Server 2005, 2008, 2008 R2, 2012, or SQL Azure instance.  The database project is "published" to the target you specify and validations are performed to make sure the database code is valid for the target.  To publish right click the project in the Solution Explorer and select Publish; fill in the dialog as shown below:

Deploy your database poject to a SQL Server 2005, 2008, 2008 R2, 2012, or SQL Azure instance

You can publish and / or generate a script.  Click the Edit button to specify the target database.

Next Steps
  • I think SQL Server Data Tools is an extremely useful tool that you can use for database development.  You can take advantage of any source code control provider that Visual Studio supports to tightly control the versioning of your database development.
  • Take a look at the Microsoft SQL Server Data Tools page in the MSDN library.  You can download the tool, watch videos, read the documentation, etc.  You can install the tool without installing SQL Server 2012 as I did in this tip.
  • Download the sample database project here.  This will only work with SQL Server 2012 and SSDT 2012 with the September update (see the Installation section above for the details).


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Ray Barley Ray Barley is a Principal Architect at IT Resource Partners and a MSSQLTips.com BI Expert.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Monday, April 11, 2016 - 8:34:22 AM - Ray Barley Back To Top (41188)

Re: Export Schema Compare

 

I don't see a way to export what you see on the screen when you do a schema compare.  While you are viewing the schema compare you can generate a script that will show you the changes that would be needed to synchronize the target to the source.  

The SQLPackage.exe utility which has a DeployReport option that creates an XML report of the changes that would be made by a publish action; see https://msdn.microsoft.com/en-us/library/hh550080(v=vs.103).aspx.  I don't recall where SQLPackage.exe is or if you need to download with a feature pack.  

Finally you can run the schema compare from MSBUILD.  I think you have the option of a text report or an html report.  See https://blogs.msdn.microsoft.com/ssdt/2014/07/15/msbuild-support-for-schema-compare-is-available/

 

 


Monday, April 11, 2016 - 6:48:15 AM - Bhavya Back To Top (41185)

 HI,

How can i export the Report of Schema Compare?

 

 

 


Friday, March 18, 2016 - 11:30:33 AM - Ray Barley Back To Top (40991)

Responding to the question below - how can I stop the users to go to SSMS and change something in there directly without going through the Source Control? - the best way is if you can not give them the permission to do so but that may not be possible.

I think the most practical way of dealing with this is to create a Snapshot (i.e. a .DACPAC) every time you publish changes to the database.  Then you can do the schema compare between the database and snapshot and see what has changed.  Ideally you should see that nothing has changed but I know it does take time to get people to stop making changes in SSMS instead of using the database project and source control. 

Every time you find a change that wasn't done in the database project and published you can bring it to the attention of the offender and they will eventually get tired of you badgering them and they will do it right.  However in my experience this has sometimes taken a long time.

 


Thursday, March 17, 2016 - 10:16:20 AM - Amy Back To Top (40973)

 Thank you very much for your post, it's really helpful. I do have a question though - how can I stop the users to go to SSMS and change something in there directly without going through the Source Control?

 

Thank you

 

Sincerelly,

 

Amy

 


Thursday, August 14, 2014 - 2:28:57 PM - Dery Gate Back To Top (34142)

Thanks Ray for this great tip. I just started learning about database projects but I didn't know where to start. I found your article to be a great guidance in the right direction. It is concise and straightforward.


Wednesday, January 22, 2014 - 3:30:41 PM - Raymond Barley Back To Top (28181)

I think the easiest way is to use SQL Server Management Studio.  Right click on the database where you want to import the data then select Tasks then Import Data.  This will launch the SQL Server Import and Export Wizard.  Choose Flat File Source as the Data Source.


Wednesday, January 22, 2014 - 12:00:34 PM - munti Back To Top (28177)

thanks Mr. Raymond Barley

can you help me about how to import the text file containing data into sql server? or what is the way can i do that?


Wednesday, January 22, 2014 - 9:16:19 AM - Raymond Barley Back To Top (28173)

If you have a snapshot (also known as a data tier application or dacpac file) you can import that into a SQL Server Data Tools SQL Server project.  You right click on the project in the solution explorer and select Import, Data Tier Application.

You can't import text files containing data into a SQL Server database or a SQL Server project.   For that you may be able to use the import data feature in SQL Server Management Studio.


Wednesday, January 22, 2014 - 2:59:41 AM - munti Back To Top (28169)

how i can import a snapshot file or file contains my database (as text files) to the sql server? i wanna use this file for making  some operation test in my server in(VM Ware) before preforming it to the real server.please help me.


Wednesday, May 1, 2013 - 10:51:03 AM - Raymond Barley Back To Top (23660)

Not sure - do you have data in the table?  I work in disconnected mode and use the Publish feature to recreate my database whenever I'm ready to test whatever changes I've made.  When you publish you can click the Advanced button and specify that you want to always re-create  the database.  I use the post-deployment script to insert reference data like lookups.


Wednesday, May 1, 2013 - 7:51:26 AM - Suneetha N Back To Top (23651)

When a column is added or removed from Table defintion, SSDT project is generating table re-creation script. Is there a way to resolve this issue.

Is it because of ANSI_NULLS ON ? Please help us in resolving this issue

 


Tuesday, November 6, 2012 - 7:22:13 PM - Jeremy Kadlec Back To Top (20235)

+100...


Tuesday, November 6, 2012 - 11:44:35 AM - Greg Robidoux Back To Top (20230)

Hi Ray,

Congrats on your 100th tip.

Greg















get free sql tips
agree to terms