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:
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:
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:
The first time you create a SQL Server project, you will be prompted to download Microsoft SQL Server Data Tools as shown below:
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:
In the sections that follow I will be using the AdventureWorks 2012 sample database. You can download the database (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:
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):
Click Start and you will see a dialog similar to the following showing the actions performed:
After completing the import, you can see the individual schema objects in the Solution Explorer as shown below:
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:
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:
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:
Click the Compare button to view the schema comparison; e.g.:
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:
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).
