Deploying SSDT Projects with TFS Build

As many of you probably have noticed by now, Visual Studio Database Projects are not supported in the next version of Visual Studio (currently named
Visual Studio 11 Beta). When you open a solution containing a VSDB project, VS11 wants to convert it to a SQL Server Developer Tools project instead.

This project type ships with SQL Server and has a feature set that covers most of the functionality of the VSDB project, plus some new features, such
a support for SQL 2012 and SQL Azure. A feature comparison list between the two project types can be found here:
http://blogs.msdn.com/b/ssdt/archive/2011/11/21/sql-server-data-tools-ctp4-vs-vs2010-database-projects.aspx

Once you have converted your project to a SSDT project, you will find that most of the functionality is very similar to VSDB, how you work with
schema objects, schema comparisons etc. Deploying a SSDT project is called Publish and is available in the Visual Studio context menu:

When you invoke the Publish command, Visual Studio will launch the Publish Profile dialog, where you can configure how and where you want to
deploy the database:

There are lots of options that you can configure, and these options are often different depending on the target environment. For example, locally you
typically want to recreate the database every time you deploy, but when deploying to a test server, you probably only want to update it incrementally
without removing any existing data. The settings that you enter can be stored in a separate profile file, which you will use when you are deploying the database.

So, create a publish profile for each environment that you want to deploy to. In the following example, I have one profile for deploying to my local
machine, and in addition publish profiles for the test and production environments:

(Note that you can right-click a publish profile and mark it as default. This is the profile that will be chosen when you select Publish in Visual Studio, so
in this case I would select Local.publish.xml)

The Publish command calls the Publish MSBuild target which will eventually call the SqlPublishTask MSBuild task which will do the work of deploying your
database. This means that the deployment of the database project is easy to integrate into TFS Build, since you can just specify that you want to invoke
the Publish target as part of your build:

Here, I have chosen to deploy the database using the Test profile, which would typically by a remote server used for testing of the build.

Using SQLCMD variables
Sometimes you need to use parameters in your scripts, e.g. values that you can pass in dynamically when the script is executed. These are called
SQLCMD variables, and you can define these on the properties page of the database project:

Here I have defined a variable called $(TargetServer), and given it a default value of localhost. Then I have references this variable inside a post
deployment script in side the project, like this:

EXEC master..xp_cmdshell 'bcp Daatabase.[dbo].[Table] in "TableContent.dat" -T -c -S$(TargetServer)'

This is a scenario we had at a client recently, where they used the BCP utility to bulk insert lots of data into a few tables as part of the deployment.

To be able to run BCP against different target servers (dev, test etc) in my build, I used the SQLCMD variable.

When you publish your database from Visual Studio, it will prompt you to give the variables a value. But when deploying from a build, the value need

to be set per configuration. This is done by opening the publish profile file for the target environment and store that value there:

Select “Save Profile As” and save it as your target publish profile. Since we are specifying our publish profile in our build definition, it will populate

the variables with the correct values.