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

jamiet — Sun, 27 Jan 2013 20:07:00 GMT

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:

Your new database will be viewable in the portal immediately:

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”:

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

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:

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:

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

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

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.

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…”

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):

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

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

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:

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…”

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

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

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:

Edit the Connection String to include your password:

NOW you can check in:

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:

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

(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:

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

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

@Jamiet

Search results matching tags 'TFS' and 'Continuous Integration'