What's New in TFS 2012?- SSDT (SQL Server Developer Tool)

In this series, I will start introducing what’s new in Visual Studio 11 and Team Foundation Server 11 (TFS 11) or as we expect to be Visual Studio 2012 and Team Foundation Server 2012 (TFS 2012).

Introduction
Project Management Tool
SSDT (SQL Server Developer Tool)
Team Explorer
My Work
Code Review
Suspend and Resume
Local Workspace
Pending Change
Diff and Merge tool
Unit Testing Improvement
Code Clones
Build Improvement
Storyboarding
Microsoft Feedback Client
Exploratory Testing Improvement
IntelliTrace in Production
Alerts
Administration

SSDT (SQL Server Developer Tool)

In this post, I will talk about the new tool for Database Development in Visual Studio 2012, which was previously known as Database Professional or Visual Studio Database Project. Now we have a new tool that is very similar to the old one but with a lot of enhancements and new features. For more info about the comparison between SQL Server Developer Tool and Visual Studio 2010 Database Projects, see the following link: SSDT vs. VS2010 Database Projects. For more info about SQL Server Developer Tool, see the following link: SQL Server Data Tools Team Blog.

The following is a step-by-step video that covers the entire topic.

SQL Express LocalDB

I will start by SQL Express LocalDB because it is part of SSDT now. It will make sense if we get a clear explanation about that point before we talk about SSDT.

There is a new version of SQL Express called SQL Express LocalDB. Microsoft SQL Server 2012 Express LocalDB is an execution mode of SQL Server Express. It is not a service anymore…

LocalDB is specially created for developers to provide the SQL Server Database Engine needed to develop, debug, and run database development, so the Developer Tools provide us as developers a way to write and test T-SQL code without having to manage a full server instance of SQL Server as before.

Download Microsoft SQL Server 2012 Express

Introducing LocalDB, and improved SQL Express with helpful Q&A

Connect to SQL Express LocalDB using SQL Server Management Studio 2008 R2.

Examine how SQL Express LocalDB runs as a process with the same credentials that open the application (SQL Management Studio in our case).

Connect to SQL Express LocalDB using Visual Studio SQL Server Object Explorer.

Examine how SQL Express LocalDB runs as a process with the same credentials that open Visual Studio.

SSDT (SQL Server Developer Tool)

As mentioned before, this is the new tool that replaced Visual Studio Database Project. I will describe the following capabilities:

Table and Stored Procedure Designer
Debugging SQL Project using SQL Express LocalDB
Publishing Database locally and on Build Server
SQLCMD variables

For more info, see the SQL Server Data Tools Team Blog.

Table and Stored Procedure Designer

Now there is a very powerful designer for Tables and Stored procedures. It looks similar to the HTML designer in Visual Studio that splits the page into two sections, one for the designer and the second for the code, and they are synchronized with each other.

Debugging SQL Project using SQL Express LocalDB

We can run any Stored procedure without connecting to a real Database. This is using the Debug Configuration of SQL Server Database Project and the SQL Express LocalDB feature.

First, we will create a new SQL Server Database Project.

Configure the debug option to use SQL Express LocalDB (configured by default).

Examine the database files that will be attached to the process of SQL Express LocalDB so we can execute our T-SQL commands without needing to maintain a SQL Server instance.

Examine the SQL Database Project after we execute some Stored Procedures by adding a database connection.

Publishing Database locally and on Build Server

The Publish feature is the same as the Deploy feature in the Visual Studio Database Project but with a lot of enhancements that really make life very easy.

Right-click on Database 1 > Publish > Edit, this will enable you to enter the Target Connection String.

You may click Advanced to configure the advanced options of the deployment.

After we set our connection string and advanced options, click on Publish and examine how the database is published to your database server.

We can have multiple publishing profiles so we can publish our Database Project differently on different machines.

After creating the build definition, we will put the needed publish profile in the MS Build Argument. This is the profile that will be used while the build machine builds the project.

"/t:Build /t:Publish /p:SQLPublishProfilePath=profilename.xml"

After we queue a build using our build definition that specifies the needed publish profile, the database will be published using the specified publish profile.

SQLCMD variables

This feature enables us to use any variable during our build or publish and gives the needed value at the appropriate time.

I just add a variable (x) so I can give it a value during the publish or during the build, but remember if the value will need to be assigned during the build on the build server, the value must be saved inside the publish profile.

I can also give the variable (x) a default value.

When I click publish on the SQL Database Project, the publish requests me to provide a value for this variable (x).

Links: