Geeks With Blogs



Microsoft Community Contributor Award 2011

Jakob Ehn Visual Studio ALM MVP @Inmeta Crayon

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:

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.

Posted on Wednesday, April 25, 2012 9:29 PM TFS , Team Build , Visual Studio 2012 | Back to top

Comments on this post: Deploying SSDT Projects with TFS Build

# re: Deploying SSDT Projects with TFS Build
Requesting Gravatar...
Great article. Hard to find good coverage of SSDT and build strategies.
Left by Gary Stafford on Aug 14, 2012 1:59 AM

# re: Deploying SSDT Projects with TFS Build
Requesting Gravatar...
Great article. How would you suggest to maintain publish files when you have a number of similar environments, for instance 4 test rigs or a pre-prod environment similar to that of production?
Left by Hamid on Sep 19, 2012 10:00 AM

# re: Deploying SSDT Projects with TFS Build
Requesting Gravatar...
Kindly update this article for incremental updations to Test server, you mentioned it in text but i could not found any steps associated to it,
Left by HydTechie on May 21, 2013 8:57 AM

# re: Deploying SSDT Projects with TFS Build
Requesting Gravatar...
Hi, how do you handle the incremental updates of test database? i didnt find out.
Left by soso on Jul 02, 2013 9:03 PM

# re: Deploying SSDT Projects with TFS Build
Requesting Gravatar...
Hi, thanks a lot for this article.
Left by Balázs Máté on Oct 30, 2013 5:08 PM

# re: Deploying SSDT Projects with TFS Build
Requesting Gravatar...
I've been using publish profiles, and SQL publish profiles for a while. But recently have been struggling automating the DB publish. Of course, right clicking on my publish profiles works correctly, and runs as expected, but automating through TFS Build 2012 seems not to update anything, even when I match your parameters - any pointers?

I have a very simple CI system, which works flawlessly for our 20 or so web services. DB portion is troublesome...
Left by Jordan on Nov 26, 2013 5:49 PM

# re: Deploying SSDT Projects with TFS Build
Requesting Gravatar...
@Jordan: Hard to tell, are you sure that the SqlPublishProfilePath parameter matches an existing file?
Left by Jakob Ehn on Nov 26, 2013 10:02 PM

# re: Deploying SSDT Projects with TFS Build
Requesting Gravatar...
Is this possible when your build targets a solution with multiple projects? I would like to build my application as well as deploy my database changes
Left by Justin on Jan 17, 2014 10:17 PM

Your comment:
 (will show your gravatar)

Copyright © Jakob Ehn | Powered by: | Join free