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)

  1. Introduction
  2. Project Management Tool
  3. SSDT (SQL Server Developer Tool)
  4. Team Explorer
  5. My Work
  6. Code Review
  7. Suspend and Resume
  8. Local Workspace
  9. Pending Change
  10. Diff and Merge tool
  11. Unit Testing Improvement
  12. Code Clones
  13. Build Improvement
  14. Storyboarding
  15. Microsoft Feedback Client
  16. Exploratory Testing Improvement
  17. IntelliTrace in Production
  18. Alerts
  19. Administration

——————————————————————————————————————————————-

SSDT (SQL Server Developer Tool)

In this post I will talk about the new tool for Database Development in Visual Studio 2012, which is used to know as Database Professional or Visual Studio Database Project, now we have a new tool, it’s very near to the old one but of course with a lot of enhancements and new features.

For more info about 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 cover the entire topic.

  1. SQL Express LocalDB

  2. SSDT (SQL Server Developer Tool)

  • SQL Express LocalDB

I will start by SQL Express LocalDB because it’s part of SSDT now, so it will make sense if we could get 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’s not a service anymore…….

LocalDB especially created for developers to provide the SQL Server Database Engine that 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

An Introducing about 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 run as a process with the same credential 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 run as a process with the same credential that open the Visual Studio.

  • SSDT (SQL Server Developer Tool)

As mention 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, 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 the Visual Studio that split 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 needed to maintain SQL Serve instance.

Examine the SQL Database Project after we execute some Stored Procedures by adding 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 enmeshments that really makes the life is 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 option click on Publish and examine how the database published to your database server.

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

After we 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 build the project.

“/t:Build /t:Publish /p:SQLPublishProfilePath=profilename.xml”

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

  • SQLCMD variables

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

I just add a variable x so I can give it value during the publish or during the build, but remember if the value will needed to be assign 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 request me to put a value for this variable (x).

Links:

Intro about SQL Server Development Tools
Microsoft SQL Server Data Tools: Database Development from Zero to Sixty in Build Event 2012
SQL Server Data Tools Team Blog
 SSDT vs. VS2010 Database Projects
Microsoft SQL Server Data Tools By MVP Isablle
A First Look at SQL Server Data Tools
Download Microsoft® SQL Server® 2012 Express
An Introducing about LocalDB, and improved SQL Express with helpful Q&A

One thought on “What’s New in TFS 2012?- SSDT (SQL Server Developer Tool)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s