New Features in SQL Server Data Tools

SQL Server Data Tools (SSDT) is one of the most important new development tools that Microsoft has added to SQL Server 2012. Formerly code-named Juneau, SSDT is based on the new Visual Studio 2010 shell and is intended to provide a unified development environment for SQL Server relational database and business intelligence (BI) projects. It essentially incorporates several different development capabilities that were previously found in other tools and combines them into a single IDE, eliminating the need for multiple development tools for the different parts of database projects. It’s important to note that SSDT doesn’t replace SQL Server Management Studio, which is still included in SQL Server 2012 and remains Microsoft’s primary only management console for SQL Server. Some of the most important new development features in SSDT follow.

Declarative Schema-Based Database Design

One of the main changes that SSDT brings is the ability to perform schema-based database design. With SSDT, the database schema is the ultimate definition of the database. The schema itself comes under version control, letting you more easily create and control multiple database versions. SSDT can automatically generate the scripts required to deploy new database versions, freeing you from needing to manually create a multitude of ALTER scripts to deploy each new version. In addition, SSDT works in both connected and disconnected modes.

Schema and Data Comparison

Two important features that are direct descendants of Visual Studio for Database Professionals (aka Data Dude) are SSDT’s schema and data comparison capabilities, which help you to find differences between two versions of the same database. They can also generate and execute the T-SQL scripts required to synchronize any differences that are found.

T-SQL Editing and Debugging

As you would expect from a SQL Server development tool, SSDT enables the development of T-SQL scripts, stored procedures, and other database objects. It provides full T-SQL IntelliSense as well as the ability to run and debug T-SQL code. It lets you debug stored procedures, set breakpoints, and single-step through the code.

Development of SQLCLR Projects

One of the new capabilities that SSDT brings is the ability to create SQLCLR objects without requiring Visual Studio. With SQL Server 2012, SSDT can be used to build, run, and debug SQLCLR objects. As you would expect, SSDT provides full support for C# and VB IntelliSense, as well as debugging and deployment of SQLCLR objects.

Replacement for Business Intelligence Development Studio

BIDS has been removed from SQL Server 2012, but SSDT replaces it. SSDT is more than just a relational database development tool; you can use it for all BI development projects, including Integration Services, Analysis Services, and Reporting Services.

Support for SQL Server 2005 and Later

SSDT wouldn’t be of much value if it connected only to the latest version of SQL Server. Fortunately, it can be used with SQL Server 2005 and later. It can also be used to develop SQL Azure database projects.