Continuously deploying your database with Release Management – Part 2

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.

image

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

image

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.

image

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.

image

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.

image 

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.

image

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.

image

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.

image

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.

image

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.

image

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.

image

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.

image

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

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.

image

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.

image

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.

image

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.

image

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

Wouter de Kort works as a lead architect and consultant. He helps organizations stay on the cutting edge of software development. Wouter focuses on DevOps. He loves solving complex problems and helping other developers to grow. Wouter authored the book DevOps on the Microsoft stack and a couple of other books. Wouter is a Microsoft MVP and an ALM Ranger. You can find him on Twitter (@wouterdekort), on his blog at wouterdekort.com and at the various conferences where Wouter speaks.

Share

Leave a Reply

Your email address will not be published. Required fields are marked *

Post comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.