One of the most exciting new features slated to be included with the release of SQL Server 2011, code named Denali, is a toolset Microsoft says will advance the art of database development. This new feature—tentatively named SQL Server Development Tools (SSDT) and code-named Juneau—is built within Microsoft Visual Studio 2010 and uses the Windows Presentation Foundation shell. As a result, SSDT supports a unified development environment that provides functionality for both data-tier and application-tier developers across SQL Server platforms, including support for Microsoft’s cloud-based database, SQL Azure.
If you’re familiar with SQL Server’s Business Intelligence Development Studio (BIDS), you’re already aware you can develop projects for SQL Server Integration Services, SQL Server Analysis Services, and SQL Server Reporting Services. With this framework in mind, think of SSDT as the next generation of BIDS, only the new toolset also supports database projects and incorporates all the functionality currently found in SQL Server Management Studio (SSMS). This means you can avoid the Alt + Tab scenarios that kept you leaping between BIDS and SSMS in previous versions of SQL Server.
In addition, SSDT lets you integrate your database projects with different types of Entity Framework projects, such as Web, application or class projects. The goal of SSDT is to ensure that the SQL Server experience is fully integrated with Microsoft Visual Studio 2010 so that a developer can create an entire application, including the database, from a single Visual Studio solution (a structure that lets you group multiple projects together).
One of SSDT's most notable advantages is that it lets you develop in and deploy to a variety of SQL Server platforms, including SQL Server 2005, 2008, 2008 R2 and 2011, as well as to SQL Azure. Part of the SSDT development effort is to make developing and deploying to SQL Server and SQL Azure exactly the same. You simply set the target platform, and SSDT validates your projects based on that target. You also have the option to output your project components directly to a Data-Tier Application Component Package (DACPAC).
Because SSDT is built on the Microsoft Visual Studio 2010 platform, you can use Team Foundation Server to manage your database project’s source control. In addition, you can create checkpoint databases that provide a snapshot of a database build at a particular point in time. You can then compare the schema of a checkpoint build with the schema of another checkpoint build, an offline sandbox database or an online database.
The T-SQL Editor lets you write and execute Transact-SQL code against both online and offline databases. The editor provides a rich editing and navigation environment that supports Microsoft’s autocompletion tool IntelliSense, debugging and declarative editing, and platform-specific validation. That means your code is validated based on the targeted version and edition of your database platform, whether you’re developing for SQL Azure or for one of the supported versions of SQL Server. Ultimately, SSDT plans to deliver a code editor that is as rich in features as what’s available in Visual Studio for C# and VB.NET.
One useful tool in SSDT is Table Designer, which provides a visual interface for creating and editing table objects in offline and online SQL Server databases. The tool displays the following panes to provide details about a table’s columns, dependencies and underlying code:
- Columns Grid pane: Detailed information in a grid format about each column in the table, including specifics about nullability, default values and data types.
- Context pane: A list of objects related to the table displayed in the Columns Grid pane. The list can include such objects as keys, constraints and triggers.
- Script pane: The T-SQL code behind the table displayed in the Columns Grid pane.
If you edit the table information in either the Columns Grid pane or the Script pane, the changes are reflected in both views. In addition, because SSDT is aware of object dependencies, it can generate a list of errors if you try to drop columns used by other objects.
The Server Explorer tool provides a view of SQL Server instances and their databases, similar to how you view databases and their objects in SSMS. Server Explorer also provides a view of the local sandbox development databases embedded in Visual Studio. If you’re already familiar with Visual Studio, Server Explorer should look familiar, except that the tool also provides a hierarchical view of database objects. In addition, you can use Server Explorer to create and edit database objects such as views, stored procedures, functions and triggers, just as you would in SSMS.
Developing and deploying to SQL Server
As mentioned above, SSDT lets you integrate application and database projects. For instance, you can generate an ADO.NET Entity Data Model based on a specified database, and you can then control how entities are mapped to the database objects. SSDT also lets you add SQL Common Language Runtime (SQL CLR) objects directly to a database project without needing to open the SQL CLR project. You can even create a new database development project based on a running database. In addition, regardless of whether you’re deploying to SQL Azure or to a supported version of SQL Server, SSDT resolves all object interdependencies and validates the project model. SSDT will analyze any schema changes between databases and generate a script that will update the target database. You can then view and edit the script before implementing the changes.
Clearly, SSDT takes database development to the next level. The toolset provides a single experience for both application and database developers, all within the familiar Microsoft Visual Studio environment. Keep in mind, however, that SSDT has yet to be included as part of a SQL Server 2011 CTP release. (Rumor has it that the toolset might be included in CTP 2.) Given the fluid nature of development and marketing strategies, the features described here could easily change. Even the name of the toolset is not yet certain. That said, you should now have a good overview of what’s coming your way. For many, incorporating database development in a Visual Studio environment has been long overdue, so SSDT should prove to be a welcome addition to Microsoft’s arsenal of development tools.
ABOUT THE AUTHOR
Robert Sheldon is a technical consultant and the author of numerous books, articles and training material related to Microsoft Windows, various relational database management systems, and business intelligence design and implementation. Find more information at http://rhsheldon.com.
This was first published in April 2011