solving sql server problems for millions of dbas and developers since 2006
join MSSQLTips for free SQL Server tips














































Introduction to SQL Server Data Tools

MSSQLTips author Ray Barley By:   |   Read Comments (8)   |   Related Tips: More > 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).


Last Update: 11/6/2012


About the author
MSSQLTips author Ray Barley
Ray Barley is a Principal Architect at RDA Corporation and a MSSQLTips.com BI Expert.

View all my tips
Related Resources


print tip Print  
Become a paid author




Recommended For You








Comments and Feedback:
Tuesday, November 06, 2012 - 11:44:35 AM - Greg Robidoux Read The Tip

Hi Ray,

Congrats on your 100th tip.

Greg


Tuesday, November 06, 2012 - 7:22:13 PM - Jeremy Kadlec Read The Tip

+100...


Wednesday, May 01, 2013 - 7:51:26 AM - Suneetha N Read The Tip

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

 


Wednesday, May 01, 2013 - 10:51:03 AM - Raymond Barley Read The Tip

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, January 22, 2014 - 2:59:41 AM - munti Read The Tip

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, January 22, 2014 - 9:16:19 AM - Raymond Barley Read The Tip

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 - 12:00:34 PM - munti Read The Tip

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 - 3:30:41 PM - Raymond Barley Read The Tip

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.



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates

Signup for our newsletter


Comments
*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.