Continuously deploying your database with Release Management - Part 2

2014-08-12    

In Continuously deploying your database with Release Management – Part 1, we looked at how to use the Sql Server Data Tools to bring your database under source control. By using the Publish wizard you can easily deploy your changes to a target database.

But this process was still manual. In this blog post, you’ll use Release Management to deploy both your database and web application to a target server.

Configuring your target environment

When using Release Management you need a couple of components:

  • Team Foundation Server with Release Management installed and configured
  • Build server
  • Deployment Agent
  • Release Management Client

In my scenario, I have TFS running on a virtual machine in Azure (a great way to setup a demo environment!). I’ve installed both the Deployment Agent and the Client at my local development pc (if you have problems connecting your client and agent to the server see Connecting release management clients and deployment agents across domains). I’ve configured Release Management to link to my TFS instance.

Configuring your release path

After you have installed the deployment agent and client on your development machine, you can start configuring your release path.

First you need to add a new server that points to your local PC. By going to Configure Paths –> Servers –> Scan for New you can select your local PC and add it as a new server.

Scan for new

Make sure that you configure the Drop Location Access correctly for your server. If your Agent is part of the Team Foundation Server domain and can access the Build drop location of your build server by UNC Path, you can select the left option. If you’re Agent is outside of the Build domain, you can select the HTTP option. This means that all sources and tools will be copied to your target machine over HTTP(S).

Drop Location Access

In Release Management, you link your servers together in an Environment. Maybe your application uses both a database and a web server and you want to link those two servers together. In this sample we use only one server that will host both the database and the website.

By going to Configure Paths –> Environments you can create a new Environment. By choosing Link Existing you can pick the server you just added to your environment.

Create Environment

Now that you have a server that’s part of an environment it’s time to create your Release Path. A Release Path is the workflow that your application will go through. For example, maybe you are running a DTAP (Development, Test, Acceptance, Production). Your Release Path will determine the order in which you want to deploy to those environments, who’s responsible for which environment and what steps are manual (like verifying a deployment) and which are automated (like running tests).

If you don’t have any Stage types defined, first go to Administration –> Manage Pick Lists and Add a new Stage Type.

Add New Stage Type

You can now create a new Release Path by going to Configure Paths –> Release Paths and choosing New. In this case, your Release Path only has one Stage since we’re using only one environment. If you would have multiple environments you can add multiple stages.

Create Release Path

Configuring your Build

Before getting into creating your actual release template, you need to make sure that there is something to deploy. You do so by creating a build that runs on your TFS Build Server and creates the required packages for deploying your application.

To connect your build to Release Management you need a special Build Template. You can download the correct version here. You need to copy this file to the BuildProcessTemplates folder in your source control (create the folder if it doesn’t exist).

After checking in the template file, you can now create a build that uses this template. You create a new template by navigation to your Team Explorer –> Builds and selecting New Build Definition.

You can configure a name for your template and some trigger settings but that’s not really important for now.

What is important is the tab Build Defaults. Here you should select the option Copy build output to the following drop folder. This drop folder should be accessible by your Build Server and the Release Management Server.

Set Build Output Folder

On your Process tab, you need to select the ReleaseTfvcTemplate.12.xaml file that you just added to source control. By selecting the New button you can browse source control and find your file in the BuildProcessTemplates folder.

Set BuildProcessTemplate

In the bottom part of the process tab you need to configure your build process. You should specify the solution you want to build and add the build and release configurations.

Configure Build Definition

Now you can save your Build Definition and fire off a new build! This build will fail because it can’t release your application yet. After your build has failed you can navigate to the drop folder that you configured on your Build Defaults tab. Here you will see a new folder showing up that contains a bunch of files and folders.

One of those files should be your MyDatabase.dacpac file. Your website will be stored in the folder \PublishedWebsitesGettingStartedWithSSDT.

Both the location of your dacpac and website are required for configuring your release template.

Configuring your Release Template

Now you can switch back to the Release Management client and take the final steps in setting up Release Management.

At the moment you have the following:

  • A Visual Studio Solution with a website and database project
  • A Build Definition based on Release Management
  • A Release Path with one environment that consists of one server

The final step is creating your Release Template. Your application has two parts: the website and your database. For those parts, you need to create a Component in Release Management.

If you go to Configure Apps –> Components –> New you can create a new Component. To create a component for your website you need to first specify the location in your Build Drop Location.

Crreate Component

And on the Deployment tab you should configure how your website needs to be deployed. One of the easiest options in Release Management is to choose XCopy. This will just copy your website to a specified location (probably an IIS folder) on your target server.

XCopy Deployer

To create a component for your database specify as the Build Drop Location to signal that your dacpac is located at the root of your build drop.

dacpac location

And for your Deployment Tool you can select the DACPAC Database Deployer. In my case, I’ve used a custom component called DACPAC Database Deployer v12 to deploy to SQL Server 2014. You can find instructions on how to add this component yourself here: Release Management for Visual Studio 2013 and SQL Server 2014 Database Deployments.

Dacpac database deployer

Creating your Release Template

Now that you have your components, you can use them in your actual Release Template. You start by going to Configure Apps –> Release Templates –> New.

Create Release Template

Here you map your Release Template to the Release Path you created and to the Build Definition on your TFS server. As you can see, everything is starting to come together.

In your Toolbox you will see a category called Components. By right clicking here and selecting Add you can link your database and website component to this template.

You can now drag your target server and the two components you have created to your workflow.

Create Workflow

This template will deploy the dacpac to your localdb instance and copy the website to a local folder on your pc. Of course this template is not production ready yet! You should add backup and rollback steps to make sure that your server stays in a good state when a deployment fails.

But for this demo, you have setup continuous deployment of your database and website!

Running a Release

If you now switch back to Visual Studio and queue a new build you will trigger the release template you just created.

Your build will now succeed and you will have a new database MyReleasedDatabase on your localdb instance. If you check your c:temp folder you should also find your published website there.

Configuring your connection string

One thing you haven’t taken care of is making sure that the connection string of your website points to your newly configured database.

Release Management supports a process called tokenizing where you define values in your code that you want to be changed during deployment.

You start by making a copy of your web.config file and naming it web.config.token. Make sure that your file is copied to your output directory.

Always copy web.config

In the token file you will see that your connection string is:

<add name="MyModelContainer" connectionString="metadata=res://\*/MyModel.csdl|res://\*/MyModel.ssdl|res://\*/MyModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=(localdb)v11.0;initial catalog=SSDTSample;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />

To create  a token you change your connection string to:

<add name="MyModelContainer" connectionString="metadata=res://\*/MyModel.csdl|res://\*/MyModel.ssdl|res://\*/MyModel.msl;provider=System.Data.SqlClient; provider connection string=&quot;\_\_ConnectionString\_\_&quot;" providerName="System.Data.EntityClient" />

By replacing the provider connection string with __ConnectionString__ you’ve introduced a token called ConnectionString. After adding this file to source control you can configure your token further in your Release Management Client.

By going to your Website component you can add your tokens on the Configuration Variables tab.

Create token

Now when you navigate to your Release Template you see that your Website component has an extra configuration variable named ConnectionString. Here you can specify the value you want to use for the ConnectionString at deployment.

Set Connection String

And that’s it. Queue a new Build and watch how your web.config now has the correct connection string.

You now have automatic deployment of your website and database with Release Management!

Questions? Feedback? Please leave a comment