THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Jamie Thomson

This is the blog of Jamie Thomson, a freelance data mangler in London

Continuous deployment of SSDT database projects to Windows Azure using Team Foundation Service

Continuous deployment is described by Wikipedia as:

Most CI systems allow the running of scripts after a build finishes. In most situations, it is possible to write a script to deploy the application to a live test server that everyone can look at. A further advance in this way of thinking is Continuous Deployment, which calls for the software to be deployed directly into production
http://en.wikipedia.org/wiki/Continuous_Integration

I think of Continuous Deployment as a natural extension of Continuous Integration where not only do we build the source code, we deploy it as well. As I continue to put together my SSDT Database Projects from the ground-up seminar it occurred to me that demonstrating Continuous Deployment for SSDT database projects would be very useful. It then occurred to me that the recently released Team Foundation Service (TFS) includes a facility to build source code so I wondered, could one perhaps use TFS to build an SSDT database project and deploy it to a Windows Azure SQL Database (aka SQL Azure)? It turns out that the answer is “yes” and this blog provides a step-by-step guide to doing just that.

Its worth noting that Team Foundation Service is free for up to five users. SSDT is also free. Windows Azure SQL Databases, however, are not free so you may have to pay a small amount to get one or alternatively adapt the steps herein to use Team Foundation Server to deploy to an on-premises SQL Server instance. Note that a MSDN subscription does include some small usage of Windows Azure SQL Database and that allowance will be more than enough to go through the steps herein.

We can break our process down into the following high-level steps:

  • Create a Windows Azure SQL Database
  • Sign up for Team Foundation Service (TFS)
  • Create an SSDT database project
  • Add the SSDT project to TFS Source Control
  • Setup your Publish Profile file
  • Create a Build Definition

Let’s dive into all of those steps.

Create a new Windows Azure SQL Database

Visit https://manage.windowsazure.com/ and sign up for a new Windows Azure SQL Database:

image

Your new database will be viewable in the portal immediately:

image

Windows Azure SQL Databases are, by default, closed to the outside world so you must edit the list of IP addresses that are allowed to access the server. Setting this up is outside the scope of this blog post however simply go to How to: Configure the Server-Level Firewall Settings (Windows Azure SQL Database) to learn how to do this (its very easy).

Sign up for Team Foundation Service

Visit https://tfs.visualstudio.com/ to sign up to use Team Foundation Service using your Microsoft Account (aka Windows Live ID). Once you have signed-up you will have a dedicated service URL (mine, for example,  is https://jamiet.visualstudio.com/). Visit that URL and click “New Team Project”:

image

Fill in the pertinent details and hit “Create Project”:

image

Creating the Team Project will take a minute or so but will be ready for you when you need it shortly.

Create an SSDT database project

For this you’re going to need SSDT for Visual Studio 2010 or Visual Studio 2012. They are both free and can be downloaded from here; I am using SSDT for Visual Studio 2012.

You will need to create a new SQL Server Database Project:

image

Once the project is created it will appear in Solution Explorer and be empty at this point. We are going to be deploying to Windows Azure SQL Database therefore we must tell the project that that will be the case. To do that right-click on the project and select Properties:

image

In Project Settings select Windows Azure SQL Database as the Target platform:

image

You’ll need to add a database object to your project otherwise this is all rather pointless. Given that this is simply for demo purposes we shall simply create a table called [Table1].

image

SNAGHTML4741cca

image

OK, we now have a Windows Azure SQL Database, a TFS service (is that Team Foundation Service service???) and a SSDT database project with a simple table in it. Let’s now hook them all together.

Add the SSDT project to TFS Source Control

Interaction with TFS from within Visual Studio is done using the Team Explorer pane.

image

From Team Explorer you will need to connect to your new TFS. Click on the address bar in Team Explorer and point to “Connect to Team Projects…”

image

Choose your project and click Connect. Your server is simply the name of the server that was setup earlier; remember that mine was jamiet.visualstudio.com (note that it uses an https connection, not http):

SNAGHTML48ae7f5

You should now be connected to your Team Foundation Service. Add your source code to TFS Source Control from inside Solution Explorer:

image

image

And check in your SSDT database project (or “commit” if you prefer that terminology):

image

image

You will now be able to browse to your TFS home page (mine is https://jamiet.visualstudio.com), click on your project and browse through your checked in code:

image

Setup your Publish Profile file

You need to tell SSDT where to deploy the project to, that information is stored in a Publish Profile file. In Solution Explorer right-click and select “Publish…”

image

Enter the details of the Windows Azure SQL Database that you prepared earlier:

image

Once the Publish Profile file has been saved it will be saved in your project:

image

At the time of writing Windows Azure SQL Database only supports SQL authentication so we have to edit the Publish Profile file to include the password for the account that will be used for deployment. Right-click on the Publish Profile file, select “Open with…” and from the resultant dialog select “XML (Text) Editor:

image

Edit the Connection String to include your password:

image

NOW you can check in:

image

image

Create a Build Definition

A Build Definition defines how and when the source code in the project should get built. Create a new Build Definition by selecting “Builds” in the Team Explorer address bar and choosing New Build Definition:

image

image

Follow the steps shown in the screenshots below to setup your Build Definition:

image

image

image

image

(Credit for informing me about the MSBuild arguments goes entirely to Jakob Ehn and his blog post Deploying SSDT Projects with TFS Build)

And you’re done!

If you have set everything up correctly then any future check in should trigger a build and thus a Publish of the SSDT project to your Windows Azure SQL Database. Here is my first build report:

image

and back in SSDT I can use SQL Server Object Explorer to browse my newly deployed table:

image

That’s Continuous Deployment for SSDT projects to Windows Azure SQL Database using Team Foundation Service. Awesome!

@Jamiet

Published Sunday, January 27, 2013 9:07 PM by jamiet

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Daniel said:

Great example!

It would be helpful to see similar examples for SSRS/SSIS/SSAS.

January 28, 2013 9:54 AM
 

jamiet said:

"It would be helpful to see similar examples for SSRS/SSIS/SSAS."

You bet it would. When you find someone willing to do that, let me know :)

The problem you have with SSIS/SSAS/SSRS is that they do not use msbuild for build/deployment. SSDT projects *do* use msbuild and hence why the above steps work.

January 28, 2013 10:02 AM
 

Kyle Hale said:

Hey Jamie,

Great tutorial, I learned a lot!

Can you make a note that when you're connecting to the TFS server that is an HTTPS connection, not HTTP? Took me a bit too long to figure out why it would never connect to the TFS service ...

January 28, 2013 9:08 PM
 

jamiet said:

Hi Kyle,

Good point. I have updated accordingly.

regards

JT

January 29, 2013 2:22 AM
 

Aaron Lowe said:

We actually created a custom build definition to include the IS/RS/AS projects. As stated there's nothing to actually "build" it is merely a deployment process.  However including that deployment allows to to do other tasks such as run IS jobs or Coded UI tests for RS. Hope to be blogging about it soon.

January 29, 2013 9:23 AM
 

Aaron Lowe said:

BTW (sorry I didn't mention it the first time).  Another excellent post Jamie!  

January 29, 2013 9:24 AM
 

piers7 said:

I've previously done the same for SSAS, SSIS and SSRS under Sql 2008 R2. Basically you have to shell out to devenv.com to get the projects built, then work out how to take the output artefacts and deploy them.

For SSAS you have to use the Microsoft.AnalysisServices.Deployment.exe to convert the .asdatabase file into a MDX script (see http://piers7.blogspot.com.au/2012/05/analysis-services-2008-r2-breaking.html)

For SSIS you can just collect the .dtsx's from the project output directory

For SSRS I wrote a script to walk the project file and locate the items to be deployed

I'm busy setting all this up on a SQL 2012 project right now, so I'll see what's changed. Little from an MSBuild perspective, sadly.

February 21, 2013 11:10 PM
 

jamiet said:

Hi Piers,

Yeah, nothing has changed from an msbuild perspective. However, a member of the SSIS team has produced an open source assembly that contains some msbuild tasks for building/deploying SSIS2012 projects - its called Microsoft.SqlSever.IntegrationServices.Build.dll.

The source is on Codeplex somewhere but I can't for the life of me find it. Get in touch with Matt Masson (https://twitter.com/mattmasson) for 'tis he that wrote it.

regards

Jamie

February 22, 2013 4:02 AM
 

Kirk Barnhart said:

Aaron Lowe.  Would you be willing to share your custom build definitions for IS/RS/AS projects

March 13, 2013 3:58 PM
 

Brian Reynolds said:

Jamie,

Great explanation. Would be helpful to talk about the database upgrade scenarios too.

Brian

April 4, 2013 2:52 PM
 

jamiet said:

Hi Brian,

In what context? The above should work for schema upgrades too - that's one of the benefits of the declarative development paradigm that SSDT extols.

regards

Jamie

April 9, 2013 6:36 AM
 

Dave Masters said:

Hi Jamie,

This post was really useful - particularly the editing of the publish file - I was getting a connection error on build and it was because the password was missing.

Anyway - do you have any advice on how to make this work for a multi-tenant situation - i.e. have the SSDT project publish to multiple instances of the same database?

I have achieved this with a local TFS server by adding a custom build task that called a console app I made that runs the script generated from a seed database deployment against all databases on the server (using the old VS2010 DB projects). However, I'm not sure I can do the same thing in the hosted build environment? I don't have access to the build server and therefore cannot add my custom .exe as a build task?

Any advice on the best way to achieve this appreciated.

Dave

August 30, 2013 10:13 AM
 

jamiet said:

Hi Dave,

In the example above I have used a publish profile file however you don't have to do that. sqlpackage.exe can accept command-line parameters instead of the information that is wrapped up in the publish profile file (the publish profile file is little more than a collection of propertyname-value pairs after all).

Look into the /TargetDatabaseName & /TargetServerName switches for sqlpackage.exe and change them accordingly for each tenant that you wish to deploy for.

regards

JT

August 30, 2013 12:23 PM
 

Dave Masters said:

Thanks for the quick reply!

I must admit I know nothing about sqlpackage.exe so I'll have to look into that.

I have previously successfully implemented this scenario using a local TFS server by using a custom build task (an .exe I made) that queried the db names and deployed a script generated from the old VS2010 database project which was pointing at a seed DB. But I've since upgraded to the new SSDT projects and moved to the hosted TFS service.

Couple more questions if I may:

1) Would using this mean I would be able to feed it a **dynamic** list of database names to deploy to? I don't know the databases I need to deploy to - I have to query this.

2) How can I use sqlpackage.exe on the hosted build environment? I don't have access to the build server, so I'm not sure how integrate it into the hosted build service.

Thanks Dave

August 31, 2013 4:50 AM
 

jamiet said:

Dave,

"1) Would using this mean I would be able to feed it a **dynamic** list of database names to deploy to? I don't know the databases I need to deploy to - I have to query this."

I'm sure there are many ways to skin that particular cat. Have you considererd Powershell? Using that you could issue your query, loop over the results and for each one call out to sqlpackage.exe.

2) How can I use sqlpackage.exe on the hosted build environment? I don't have access to the build server, so I'm not sure how integrate it into the hosted build service.

sqlpackage.exe is distributed in the DAC Framework (http://technet.microsoft.com/en-us/library/ee210549.aspx) This can be installed, for free, wherever you choose.

regards

Jamie

September 2, 2013 3:11 AM
 

Justin said:

In your build definition, you say that I should choose the SQL project to target.  My application has more projects to build than just the sql project.  Is there a way to do this when targeting the solution?

January 17, 2014 4:25 PM
 

jamiet said:

Hi Justin,

good question, i don't know the answer but i would doubt that's possible.

all is not lost however, simply call the command for each db. You can (i think) enter multiple commands. I'm not a TFS expert but there'll be a way to do it, I'm certain.

regards

JT

January 17, 2014 4:37 PM
 

Drew said:

Thanks for this. Very helpful. I have the same situation as Justin. The SSDT project was one in a solution of multiple projects. I setup my mvc app to use the (localdb)\projects instance for my SSDT project, and I was able to get the Azure Cloud Service to automatically pick up the MVC app easily by simply linking the two (which created a continuous deployment build), but I was struggling with deploying the database from (localdb)\projects to SQL Azure in a CD fashion until I found this.

The only gotchya with the above approach is that when you specify the target as a command line argument for msbuild, it will try to Build and Publish every project in the sln file. The simplest way to work around this was to just setup another build, with a CI trigger, but which only targets the DB project. You can change the items the template will use to build by pointing it to the entire sln file, or individual proj files. So I just have 2 CI/CD builds targeting the same solution, but different projects.

February 17, 2014 9:05 AM
 

Drew said:

Rereading Justin's comment, it sounds like he specifically wants to target the sln file? if you insist, then the only way that I found to have 1 build that targets the entire solution is to edit the sqlproj file directly (in a text editor), change the DefaultTargets attribute to "Build;Publish", and remove the /t:Build /t:Publish msbuild command line parameters. Now the build will simply use the "DefaultTargets" property for all projects in the solution.

The unfortunate side effect of that is that it also tries to do it locally when you build in VS, and it of course fails because you haven't supplied the SqlPublishProfilePath parameter. So you need to find a way around this (e.g. remove from local build configs, or supply a profile which deploys to...) you decide. I didn't investigate further because the above comment is a much simpler and less fragile solution.

February 17, 2014 9:13 AM
 

Tomas said:

Thanks for showing the edit profile trick to enter password there. I had some connection errors troubling me and there it was.

Just wanted to inform you that you might want to hide your full sql server name that's showing in one of your pictures above. You've hidden it in all but one.

Thanks again.

April 3, 2014 4:45 AM
 

Integration Services (SSIS) for the DBA ??? David Peter Hansen said:

November 5, 2014 12:42 PM
 

JaY said:

Hi Jamie,

wonderful article !! Is there any way we can automate the deployment process , I mean scheduling it like a job ??

Waiting for your response

Jay

November 14, 2014 4:41 PM
 

John said:

Summary

Other Errors and Warnings

1 error(s), 1 warning(s)

This build has been stopped automatically by the server due to loss of connectivity to the build machine. All requests included in the build have been placed back into the queue and will be retried when connectivity is restored.

Build stopped by MitBuild Build User

I am getting this error when I tried to Automate build can anyone please help me with this ??

November 17, 2014 1:39 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement