This article is part of an Essential Guide, our editor-selected collection of our best articles, videos and other content on this topic. Explore more in this guide:
3. - Discover the capabilities of SQL Server native tools: Read more in this section
- Evaluating SQL Server auditing tools: Here's the lowdown
- Six absolutely essential SQL Server management tools
- Ease DB projects with SQL Server Data Tools 2012
Explore other sections in this guide:
- 1. - Enhance SQL Server management with essential tools
- 2. - Improve security, high availability, BI with third-party tools
- 4. - Quiz: Native and third-party SQL Server tools
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.
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.
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.
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.
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.
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.
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:
create database 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.
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 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.
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.
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.
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.
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.