This content is part of the Essential Guide: Exploring third-party SQL Server tools
Get started Bring yourself up to speed with our introductory content.

SQL Server Data Tools 2012 makes developing DB projects easier

SQL Server Data Tools 2012 can now do many of the tasks formerly reserved for SQL Server Management Studio. Find out more.

One of the new features in SQL Server 2012 that's been generating a lot of buzz since its release is SQL Server Data Tools 2012, a set of database and business intelligence development tools hosted in the Visual Studio 2010 integrated shell. Similar to Business Intelligence Development Studio (BIDS) in previous versions of SQL Server, SQL Server Data Tools (SSDT) 2012 provides the templates and components necessary to create Analysis Services, Reporting Services and Integration Services projects. But SSDT 2012 adds a couple other ingredients -- the ability to develop SQL Server database projects and to perform tasks traditionally found only in SQL Server Management Studio (SSMS), such as editing data, updating database objects, running queries and performing light-duty administration.

The confusing world of SQL Server Data Tools 2012

Despite being such a comprehensive set of tools, SQL Server Data Tools 2012 has gotten off to a bumpy start, mostly because what was meant by SSDT depended on whom you asked. Database developers who installed SSDT during the SQL Server 2012 setup process essentially saw a replacement for BIDS. Application developers, on the other hand, when they downloaded SSDT from the Web (which you can do for free), saw it as a Visual Studio add-on that replaced the database development tool Data Dude. The confusion resulted, in part, from the fact that when SSDT is first installed, it includes either the BI development tools or the database development tools, but never both.

Let's look at the SSDT that's part of a SQL Server installation. When you first open the application, you'll find an interface that looks similar to the one shown in Figure 1. If you've have already used SSDT to create development projects, they'll be listed in the Recent Projects section. You might also have opened one or more windows before, so those might be displayed as well. Even so, your interface should look fairly similar to what's shown in the figure. As you can see, everything else is just your standard "Getting Started" fare.

Initial interface for SSDT 2012
Click to enlarge. Figure 1. The Start Page opens when you first launch SQL Server Data Tools 2012.

It's when you create a new project that things get interesting. To do that, click the New Project link. This launches the New Project dialog box, shown in Figure 2. Notice in the Installed Templates pane, the first category listed is Business Intelligence. When this is selected, as it is in the figure, the central pane displays a list of the main templates available for BI development, such as Integration Services Project and Analysis Services Tabular Project.

SQL Server Data Tools 2012 new project dialog box
Figure 2. SQL Server Data Tools 2012 supports numerous templates for BI development.

If you were to expand the Business Intelligence node, you would find a list of BI project types: Analysis Services, Integrations Services and Reporting Services. For each type, you'll find a complete list of templates available to that type, as shown in Figure 3. For example, the Analysis Services type is selected in the figure, and, as to be expected, the center pane displays a list of templates specific to Analysis Services development.

SSDT 2012 templates
Figure 3. SSDT includes a variety of templates for SSAS development.

So far, so good. But notice there's another node listed in the Installed Templates pane -- SQL Server -- something you didn't see in BIDS. If you select that node, as shown in Figure 4, you'll find a single item in the center pane: Microsoft SQL Server Data Tools (Web Install). That's right:The database development component isn't included. Instead, you have to click the option, then follow the online prompts to install those tools.

SQL Server Data Tools 2012 download page
Figure 4. The SQL Server category contains a pointer for downloading the database tools.

Once you've installed the database tools, you must relaunch SSDT. When you do, you'll be prompted to choose your primary development environment, such as database development, BI development or one of the programming languages. However, once you've gotten through this, you're ready to develop a database project. As Figure 5 shows, the option available to the SQL Server node is updated after the tools have been installed, so there's no longer a reference to a Web installation. You should now have everything you need for both BI and database development in SSDT.

SSDT 2012 new project page after installation
Figure 5. Working with SQL Server Data Tools 2012 after the SQL Server Database Project template has been installed.

What you've seen here is, of course, a SQL Server-centric view of the world. Application developers will have a very different experience. They will, for the most part, go directly to the MSDN website to retrieve the database development tools. If they're already running Visual Studio, the tools are installed to the existing environment. If not, the Visual Studio shell and database tools are installed.

If you install SSDT in this way, then want to install the BI development tools, you must return to the SQL Server 2012 setup program and install the SSDT feature. The setup program is smart enough to detect that the SSDT database tools are already installed and will add only the BI tools.

SQL Server Data Tools 2012 BI tools

Business Intelligence development in SSDT is similar to what you're used to in BIDS. The differences you'll see reflect either new features added to the SQL Server 2012 BI stack, such as Analysis Services tabular projects, or updated interface components that have been tweaked to make development easier, such as the way Integration Services components are displayed when they're running. Figure 6 shows the control flow of a SQL Server Integration Services (SSIS) project. Notice that, as with BIDS, you select control flow components from the SSIS Toolbox window, work with those components on the design surface of the Control Flow tab, and view details about the solution and project in the Solution Explorer window.

SSDT 2012 and SSIS
Figure 6. The BI tools in SQL Server Data Tools 2012 let you develop an SSIS solution.

If you're already familiar with BIDS, you should have little problem adapting to BI development in SSDT. Conceptually, little has changed. If you're not already familiar with BIDS, your learning curve in SSDT will be about the same as it would have been with BIDS -- in which case you'll want to start digging through SQL Server Books Online and other resources for details on how to get started.

SSDT database tools

For the SQL Server developer who has relied primary on SSMS for database development, SSDT offers a very different view of that world. SSDT supports project-oriented offline development, which means you can create, delete and update database objects without touching the production database until you're ready. Although you can still make changes directly to that database, offline project-based development provides an efficient and safe mechanism for database development. And you can develop projects for all editions of SQL Server from 2005 on, including Windows Azure SQL Database.

To better understand the power available in the SSDT tools, let's walk through a couple of the SSDT database development features. But first, to demonstrate these features, we need to create a test database and table on an available SQL Server instance. For this story, the examples are based on the following database and table definitions:

use master;

create database DbTest;

use DbTest;

create table TestTable
ColA int primary key,  
ColB varchar(60) not null,  
ColC datetime not null default getdate()

When you create a SQL Server project, SSDT displays a window similar to the one shown in Figure 7. In this case, both the SQL Server Object Explorer and Solution Explorer windows are open, each displaying the new project (in this case, DbTest2). The SQL Server Object Explorer window is similar to the Object Explorer window in SSMS, in terms of displaying and providing access to databases and their objects in hierarchical order (as well as some of the server objects). However, the window also displays your database project and the objects associated with that project.

SQL Server Data Tools 2012 database projects
Figure 7. Database projects are listed in Solution Explorer and SQL Server Object Explorer.

The Solution Explorer window in SSDT is similar to the Solution Explorer windows in BIDS and the BI tools component of SSDT. It provides a hierarchical view of a solution, its projects and the projects' files, each of which is an SQL script file. And because the SQL Server Object Explorer and Solution Explorer windows display some of the same information, you can perform several operations from either window, such as accessing the table designer tool.

Suppose that we now want to modify the DbTest schema, but don't want to touch the live database. We can instead import the schema into our project. To do so, right-click the DbTest2 node in Solution Explorer, point to Import, click Database, then follow the prompts for importing the database. Once you've imported the schema, SQL Server Object Explorer and Solution Explorer display TestTable in the list of tables, as shown in Figure 8.

SSDT 2012 schema import

Figure 8. You can import an existing schema into a database project in SQL Server Data Tools 2012.

SSDT also includes a table designer to make updating schema elements easier. Right-click the table in SQL Server Object Explorer or Solution Explorer, then click View Designer. This opens a tab in SSDT that lets you easily modify columns, keys, constraints and triggers, as shown in Figure 8. On this tab, you can make changes within the grid, the Properties window or the T-SQL pane at the bottom, and those changes are automatically reflected in the other sections.

SSDT 2012 database updates

Figure 9, caption: SQL Server Data Tools 2012 provides a design view for updating database tables.

For example, let's add a column named ColD to the grid, assign the bit data type to the column and permit null values. As you can see in Figure 10, when you make these changes, they're automatically reflected in the T-SQL code in the bottom pane.

SSDT 2012 updated T-SQL code

Figure 10. Table updates appear immediately in the T-SQL code.

You then can compare the database schema in your project to the schema of the target database. In SQL Server Object Explorer or Solution Explorer, right-click DbTest2, click Schema Compare, then select the target database (in this case, DbTest). When you click the Compare button, SSDT compares the two schemas and displays the results, as shown in Figure 11. Notice that the column we added to TestTable in DbTest2 is highlighted in yellow, and the column is shown as missing in DbTest.

SSDT 2012 schema comparison view

Figure 11. SQL Server Data Tools 2012 lets you compare database schemas.

Based on this comparison, you then can update the target database (DbTest) immediately or generate a script to run the update later. Another option is to create DbTest2 on your target SQL Server instance. To do so, right-click DbTest2 in Solution Explorer, click Publish, then follow the prompts. You'll have the chance to publish the new database immediately or to generate a script that you can run later. Once you've published your new database, it's reflected in SQL Server Object Explorer, as shown in Figure 12.

SSDT 2012 new database publish

Figure 12. You can publish your new database to a SQL Server instance.

These examples, of course, represent only a small portion of the database development capabilities in SQL Server Data Tools 2012. The tools also support IntelliSense, code debugging, search , and a number of other components for developing and maintaining a database. Add these to the BI development capabilities and you have a set of tools to meet the needs of just about all your SQL Server database development efforts. Indeed, SQL Server database and BI development have never been easier than they are with SSDT. You have little to lose and much to gain by giving the tools a try, especially if you're ready to incorporate new approaches to your database development.

Connect with on Facebook and Twitter. Robert Sheldon may be reached at his website.

Next Steps

Four must-have SQL Server tools

Dig Deeper on Microsoft SQL Server Tools and Utilities

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.