Evaluate Weigh the pros and cons of technologies, products and projects you are considering.

Top five features in SQL Server Developer Tools

SQL Server Developer Tools, also known as Project Juneau, made its debut in this summer’s Denali preview. Read up on five SSDT features.

When Microsoft releases Denali -- code name for the next version of SQL Server -- the company also plans to release SQL Server Developer Tools (SSDT). Introduced as Project Juneau last November and made available in July as part of the second public Denali preview, SSDT provides an integrated environment to develop SQL Server databases and objects and deploy them to different SQL Server platforms. 

SSDT includes a set of features that facilitate and enhance these development efforts. Five of those features in particular go a long way to making the life of database developers easier, more efficient and a lot more fun.

Session-based updates
In SQL Server Developer Tools, you can connect to an instance of SQL Server and modify database objects just like in SQL Server Management Studio (SSMS). However, with SSDT, you connect to the SQL Server instance, make changes to the database object and verify the accuracy of those changes before committing them to the database. 

In the Transact-SQL Editor and Table Designer, SSDT automatically stores your edits in the current session rather than making the changes to the live database. If your changes generate any errors, those errors show up immediately in the Error List pane. You can then resolve the errors and commit the code changes to the database or generate an update script that you can run at a later time.

Offline development
Like SQL Server Business Intelligence Development Studio (BIDS), SSDT is integrated into the Visual Studio environment. And also like BIDS, SSDT includes project templates to support business intelligence development (SQL Server Integration Services, Reporting Services and Analysis Services). 

However, SSDT also includes a template for SQL Server database development. After you’ve set up your database project, you can copy a database schema from a SQL Server instance into the project environment. You can then modify the objects in the sandbox database, compare that schema with the schema of the original database and deploy those changes to the production environment. 

In addition, you can develop a database offline and then publish that database to a SQL Server instance. The SSDT database project also lets you deploy to a variety of SQL Server platforms, including SQL Server 2005, 2008, 2008 R2, Denali and SQL Azure, Microsoft’s cloud database service.

Table Designer
Table Designer provides a quick and easy way to create and modify tables and related objects. The design window includes an editable columns grid that lists the column names along with their data types, nullability and defaults, if any. 

You can also easily add primary keys, foreign keys, indexes and triggers. In addition, Table Designer includes a script pane that lets you edit the CREATE TABLE statement directly. Table Designer keeps the code in the script pane in sync with the columns grid. For instance, if you change a column’s nullability in the columns grid, that change is immediately reflected in the script pane and visa versa. The best part is, you can use Table Designer to modify database tables on SQL Server instances or tables within a database project.

Schema Compare
The Schema Compare tool lets you compare any two database schemas to determine how they differ. You can compare a database project to a database on an instance of SQL Server or a database project to a project snapshot, and you can compare databases in different instances of SQL Server. 

After the tool performs the comparison, you can sort the results by database object, schema or necessary action on the target database, such as creating or updating objects. The tool also displays the code from the compared objects so you can see specific differences. Once you’ve reviewed the differences, you can specify what objects to update in the target database. You can then perform those updates immediately or generate a script to update the target database later.

Project snapshots
A project snapshot provides a read-only checkpoint reflecting the database schema at the time the snapshot was created. If errors are introduced into a project after a snapshot is created, you can revert to the image by using Schema Compare to determine what has changed and what items should be rolled back. You can also import database objects from your snapshot into a project, and you can compare a snapshot to a database on a SQL Server instance to help troubleshoot problems that might have been introduced into your production environment.

SSDT’s arsenal of tools
Certainly, the features described here represent just some of the new features you’ll find in SQL Server Developer Tools, such as code snippets, source control integration and change tracking. But these five features go a long way in providing SQL Server developers with the tools they need to work in a fully integrated development environment, offering up an arsenal of tools that will change the way SQL Server databases are developed and deployed.

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.

Dig Deeper on SQL Server Database Modeling and Design