SSDT (SQL Server Data Tool): Introduction

  • Article

This post will introduce what's new in the new tool for Database Development in Visual Studio, which is used to known 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

Video

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

[youtube=http://youtu.be/tNjp2hwuXkg]

  1. SQL Express LocalDB
  2. SSDT (SQL Server Developer Tool)

SQL Express LocalDB

We will start by** SQL Express LocalDB** because it's part of SSDT now, so it will make sense if we could 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'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.

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

http://mohamedradwan.files.wordpress.com/2012/08/connect-to-sql-express-localdb.png?w=300

Examine how SQL Express LocalDB run as a process with the same credential that opens the application (SQL Management Studio in our case).

http://mohamedradwan.files.wordpress.com/2012/08/sql-express-localdb-run-as-a-process.png?w=300

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

http://mohamedradwan.files.wordpress.com/2012/08/connect-to-sql-express-localdb-using-sql-server-object-explorer.png?w=300

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

http://mohamedradwan.files.wordpress.com/2012/08/sql-express-localdb-run-as-a-process-with-sql-server-explorer.png?w=300

SSDT (SQL Server Developer Tool)

As mention before, this is the new tool that replaced Visual Studio Database Project. Let's 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.

http://mohamedradwan.files.wordpress.com/2012/08/ssdt-designer-and-editor.png?w=300

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.

http://mohamedradwan.files.wordpress.com/2012/08/sql-database-project.png?w=300

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

http://mohamedradwan.files.wordpress.com/2012/08/ssdt-and-sql-server-project-debug.png?w=300

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.

http://mohamedradwan.files.wordpress.com/2012/08/sql-server-project-database-file.png?w=300

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

http://mohamedradwan.files.wordpress.com/2012/08/open-the-database-file-of-the-sql-server-database-project.png?w=300

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 enmeshment 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.

http://mohamedradwan.files.wordpress.com/2012/08/publish-sql-server-database-project.png?w=300

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

http://mohamedradwan.files.wordpress.com/2012/08/advanced-settings-in-the-publish-database.png?w=300

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

http://mohamedradwan.files.wordpress.com/2012/08/publish-database-and-exmain-that-its-exist.png?w=300

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

http://mohamedradwan.files.wordpress.com/2012/08/multi-publishing-profile1.png?w=300

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"

http://mohamedradwan.files.wordpress.com/2012/08/build-defination-to-use-publish-profile.png?w=300

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.

http://mohamedradwan.files.wordpress.com/2012/08/build-success-and-pubblish-the-database-to-the-sql-server.png?w=300

SQLCMD variables

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

Let's add a variable x so we 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.

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

http://mohamedradwan.files.wordpress.com/2012/08/sqlcmd-variables.png?w=300

When clicking publish on the SQL Database Project publish requests a value for this variable (x).

http://mohamedradwan.files.wordpress.com/2012/08/sqlcmd-variables-with-publish-window-of-the-sql-server-database-project.png?w=300

Back to Top