Getting out of “a variable nightmare” – or how I learnt to let go and embrace conventions

I’ve recently been doing some work automating the release of our integration projects to our various clients, I was reviewing the work that had been done last week and noticed that there were a lot of variables being defined. If we continued on this course as we added new clients we would end up adding between 3-5 new variables, multiple that by say 100 new clients and soon we would have an incredible amount of variables to deal with.

In our project we use Octopus environments to represent our different clients; you can see from the image below that we were defining a lot of different variables for each of our clients.

As you can see above with only a handful of clients it’s already getting out of control – there has to be a better way!

Embrace convention, simplify your life

Consistency by convention

It struck me that we had inadvertently created a few conventions in the way we were defining different variables.

For example we had the following variables defined for each of our environments:

• Client Staging DB, which represents the name of a database used by an ETL process these all took the form Client_[client name]
• Client Staging User ID which took the form Staging_[client name].

These two variables (along with Client Staging Password) are used in the Client – National Conn String variable.

It dawned on me that with a very simple convention I could get rid of most of these repeated variables.

So instead of continuing on creating scoped variables for each of our clients (environments) I embraced the Octopus Environment Name variable. Now all of these existing variables that were scoped to each environment have been deleted and now there is a single variable shared by all environments.

Client – National Conn String has been replaced with

Data Source=xyz;User Id=Client_#{Octopus.Environment.Name};Password=#{Client Staging Password};Initial Catalog=Staging_#{Octopus.Environment.Name};Provider=SQLNCLI11.1;

This means longer having to define the Client Staging DB and Client Staging User ID variables.

There were a bunch of other variables that I went through and updated to use the same convention. After making this change I went from having 38 variables, down to 22.

We now have an opinionated deployment process, which comes with some some big advantages:

• The process is easier to reason about
• Everything is consistent
• Less setup work when on-boarding new clients

But it’s good to be flexible

There is still room for exceptions, take the Client – Local Conn String variable, I was able to standardize this for all of our clients except for the one where we run our database on their cluster. For them I have a variable scoped just to their environment.

Octopus Deploy makes it easy to be flexible with variable scoping.

Variable sets reduce noise

The other trick that I’ve used is putting groups of related variables into their own variable sets. They are great for sharing variables between projects, but I’ve used them to remove noise from the project variables listing.

No pain no gain

It’s worth mentioning there was a little bit of pain, not all of the environments were configured consistently, not all of the SQL users were named according to the convention. Which meant a bit of manual work fixing things up; but in the long run it’ll pay off.

Advertisement

Distributed multi-tenanted releases with Octopus Deploy

TL;DR; read the Octopus guidance, adopt the “environment per tenant” approach, try to make your process consistent across tenants and use scoped variables to make steps re-usable, keep complexity out.

In my last couple of posts I’ve written about the various aspects involved with automating the release of our client integration projects. I’ve shown how to get TeamCity to build Integration Services projects and then how to deploy them into SQL Server SSIS Catalog with Octopus Deploy.

In this post I’m going to explain how we setup Octopus Deploy for distributed multi-tenanted deployments.

What is a distributed multi-tenanted deployment?

A tenant in the context of DrDoctor it is a hospital. As mentioned in a previous post, when DrDoctor starts working with a new hospital we do some bespoke integration work to integrate their PAS system with our system. This ETL process is made up of a couple of staging databases and some SSIS packages which orchestrate the ETL process.

For each hospital that we work with, we are given a virtual machine within their network to which we deploy a database and a set of SSIS packages. We then have a schedule job that:

• Performs an ETL process on the client machine to transform the data from their PAS system into our schema
• Transfers the transformed data from the client site to our central database running in our data center.

So in our context we have a mixed deployment topology, some components go to a virtual machine running in a remote data center and some components go to our servers running in another data center.

Setting up Octopus Deploy

Octopus Deploy already has some very helpful guidance around different ways that it can be set up to support multi-tenanted deployments. There are broadly speaking two approaches: environment per tenant or project per tenant.

The process which I opted for was environment per tenant, in this approach there is a single project which contains all the different steps and variables, and multiple environments which represent the various tenants.

The reason I opted for environment per tenant is that our release process is the same for each of our clients, this keep the overhead of maintenance low and the process of setting up a new client easy.

setting up the environments

As per the Octopus guidance (linked to above) I created an environment for each of the hospitals DrDoctor currently works with. At this time they are all configured with an offline package drop deployment target while we work with the various IT departments to get their firewalls to allow the Octopus tentacles to communicate with our Octopus server.

I also added to each environment a listening tentacle deployment target for the tentacle inside our data center, this the same tentacle but with multiple environments applied to it.

I also created a new release life cycle for this project, which treats all the environments as equals.

The release process

This is the release process at time of writing.

Steps 2-4 and 6 I covered in my previous post about deploying SSIS projects with PowerShell and Octopus Deploy. All of these steps make use of environment specific variables so they will work uniformly across all of our clients.

The step that is interesting is actually step 5. As I noted above, we have to do bespoke integration work for each of our clients, this involves writing custom transforms (stored procedures) and having a different set of database tables to which we import data from and to.

In Visual Studio we have a SQL database project for each of our clients (Local.ClientX), with all the common objects in a referenced database project. The client specific database project contains the transform stored procedures, import tables and client specific data mappings.

Each of these database projects are built by TeamCity and turned into NuGet packages for Octopus Deploy.

In step 5 there is a step for each of our different clients, with each step being scoped to a specific environment. The actual work being done in each of the sub-steps are identical with the exception of the NuGet package which is being deployed.

Configuring step 5 was the only point where I felt that the functionality of Octopus Deploy was lacking elegance. It would be much nicer to have a single step which dynamically picked the correct NuGet package based on the environment variable.

That wasn’t possible as far as I could see, hence why step 5 has lots of duplication.

Roles

There are two roles:

1. client role – this is for all steps that should be run on the client integration machine
2. mayden role – this is for the steps that should be run on our central server

Using roles allow us to control where each of the steps are run.

Introducing Offline package drop

At time of writing we haven’t yet negotiated with the various IT departments to allow the tentacles to get through their firewalls. However, with the release of Octopus Deploy 3.x this is no longer a deal breaker thanks to a new feature called offline package drop.

This is one of a number of new deployment targets that have been added in version 3.x. When you deploy a release to an offline package drop the Octopus server will create a local directory for that release/environment and bundle up all the release artifacts (NuGet packages, PowerShell scripts etc) and create a handy script which can be run to deploy the release. This is the perfect feature where you can’t have either a listening or polling tentacle.

So the release process now becomes:

• Create a new release in Octopus
• Deploy the release to one or more environments
• Zip the deployment folder and copy to the client machine
• Execute the deployment script

Assuming everything worked the client is now on the latest release.

Multiple deployment targets

One of the great features about Octopus Deploy is how the environments can be configured. As I showed above, in our environments we two deployment targets each scoped to specific roles. This becomes really handy when trying to coordinate different steps across different machines.

In our scenario, the way it has been configured is to have all the client specific steps go to the offline package target, and the step for our central server will go to the listening tentacle and therefore be executed without any manual work by the person doing the deployment.

The other nice thing is that once our clients start opening their firewall we can swap out the offline package drop with a listening or polling tentacle and the release process will just work as is.

Thoughts and advice

One of the biggest problems I was trying to address with all this automation was fear or more specifically releasing is really hard…so I’ll avoid it as much as I can. Before we automated the process we would often over point user stories that involved making changes to our ETL process because releasing was so hard.

One of the consequences I believe that has come out of this is that we will as time goes in build confidence in releasing changes, and therefore more easily make changes. There are loads of things we would like to do to our ETL process to make the actually process more efficient, or refactor some of the data structures. My hope is that all those little things we’ll start doing because it’ll no longer be a big scary release.

The main advice I would give to someone looking to use Octopus Deploy for multi-tenanted releases is this: adopt the environment per tenant and only one project approach. This might mean a little bit of pain making everything uniform, but it will reduce your overhead and give you a much simpler process to reason about.

The more I use Octopus the more it shows itself to be extremely flexible and suited to many different scenarios, but at the same time it doesn’t have to be complicated, you could very easily have a simple project up and running in an afternoon.

Go forth and automate.

Deploying SSIS projects with Octopus Deploy

In my previous blog post I outlined how to configure TeamCity to build SQL Server Integration Services projects and then package the ispac file into a NuGet package.

In this post I’m going to show how I set up Octopus Deploy to deploy the ispac to SQL Server 2012 and configure SSIS DB projects and environments.

Deployment Overview

Here is an overview of the process as is currently defined in Octopus Deploy.

We are doing multi-tenanted releases for this project, which is why the Client DB (Step 4) is repeated multiple times. In a future post I’m going to cover off details about how we are doing multi-tenanted releases.

Setting up SSIS DB

The first step in the release process is to ensure that the SSIS DB is up to date for use with our SSIS projects, within this step we ensure that:

• A project folder exists within SSIS DB for our ispac to be deployed into
• An environment exists
• Environment variables are up to date

All of this is achieved with a little bit of PowerShell

$folderName = $OctopusParameters['SSISDB FolderName']
$environmentName = $OctopusParameters['SSISDB EnvironmentName']

$loadStatus = [Reflection.Assembly]::Load("Microsoft.SqlServer.Management.IntegrationServices, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91")

# Store the IntegrationServices Assembly namespace to avoid typing it every time            
$ISNamespace = "Microsoft.SqlServer.Management.IntegrationServices"            
            
Write-Host "Connecting to server ..."            
            
# Create a connection to the server            
$constr = "Data Source=localhost;Initial Catalog=master;Integrated Security=SSPI;"            

$con = New-Object System.Data.SqlClient.SqlConnection $constr

# Create the Integration Services object            
$ssis = New-Object $ISNamespace".IntegrationServices" $con

if ($ssis.Catalogs.Count -eq 0)
{
    Write-Error "SSISDB doesn't exist"
    throw "SSISDB doesn't exist"
}

$cat = $ssis.Catalogs["SSISDB"]

if ($cat.Folders[$folderName] -eq $null)
{
    Write-Host "Creating new folder" $folderName
    $newfolder = New-Object $ISNamespace".CatalogFolder" ($cat, $folderName, "Description")     
    $newfolder.Create()
}

$folder = $cat.Folders[$folderName]

if ($folder.Environments[$environmentName] -eq $null)
{
    Write-Host "Creating environment" $environmentName
    $newEnv = New-Object $ISNamespace".EnvironmentInfo" ($folder, $environmentName, "Descriptoin")
    $newEnv.Create()
}

$env = $folder.Environments[$environmentName]

Write-Host "creating variables"

if ($env.Variables["Local"] -eq $null)
{
    $env.Variables.Add("Local", [System.TypeCode]::String, $OctopusParameters['Client - Local Conn String'], $false, "Our local client db")
}
else
{
    $env.Variables["Local"].Value = $OctopusParameters['Client - Local Conn String']
}

# create other environment variables here...

#save changes to environment
$env.Alter()

(NB: This script and the subsequent one for deploying the ispac are based off this example by Matt Masson)

I’ve tried to write this script to be idempotent so that it can be run multiple times without causing any problems.

Here is what you will see in SQL Management Studio after this step has run:

Deploying SSIS Projects

The next step is to actually deploy the Integration Services project into the SSIS DB. In this step we use a PowerShell script to:

• Deploy the ispac into the project folder that was created in the previous step
• Ensure that the project has a reference to the environment that was created in the previous step

### Static Variables

$localToLocalETLFullPath = ".\DrDoctorClientETL.ispac"
$folderName = $OctopusParameters['SSISDB FolderName']
$environmentName = $OctopusParameters['SSISDB EnvironmentName']
#the projectName needs to match the output name that has been specified on the project, you can see this in the properties in SQL Server Data Tools
$projectName = "DrDoctor Client ETL"

###

$loadStatus = [Reflection.Assembly]::Load("Microsoft.SqlServer.Management.IntegrationServices, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91")

# Store the IntegrationServices Assembly namespace to avoid typing it every time            
$ISNamespace = "Microsoft.SqlServer.Management.IntegrationServices"            
            
Write-Host "Connecting to server ..."            
            
# Create a connection to the server            
$constr = "Data Source=localhost;Initial Catalog=master;Integrated Security=SSPI;"            
            
$con = New-Object System.Data.SqlClient.SqlConnection $constr      
# Create the Integration Services object            
$ssis = New-Object $ISNamespace".IntegrationServices" $con
$cat = $ssis.Catalogs["SSISDB"]
$folder = $cat.Folders[$folderName]

Write-Host "Deploying project ..."            
[byte[]] $projectFile = [System.IO.File]::ReadAllBytes($localToLocalETLFullPath)            
$folder.DeployProject($projectName, $projectFile)

#####

Write-Host "adding reference to environment"

$project = $folder.Projects[$projectName]
$env = $folder.Environments[$environmentName]

if ($project.References.Count -eq 0)
{
    $project.References.Add($env.Name, $folderName)
}
else
{
    Write-Host "assuming that the correct environment has been configured"
}

####

Write-Host "applying environment variables to project"

$targetVar = $env.Variables["Local"]
$sourceVar = $env.Variables["Source"]

Write-Host "Updating Extract package parameters"

$extractPackage = $project.Packages["Extract.dtsx"]

$extractPackage.Parameters["CM.Target.ConnectionString"].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced, $targetVar.Name)
$extractPackage.Parameters["CM.Source.ConnectionString"].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced, $sourceVar.Name)

$project.Alter()

After this step has run the Projects folder will contain the first of our SSIS projects:

Deploying a database project

In a previous blog post I showed how you can fiddle with the dtproj and add a reference to the Octopack NuGet package to have TeamCity turn your database project into a NuGet package for you.

However, since discovering how to use NuSpec files in TeamCity I’d now recommend against changing your dtproj file and instead moving to using a NuSpec to define the package.

Here is a sample NuSpec file that I started using this week

<?xml version="1.0" encoding="utf-8"?>
<package xmlns="http://schemas.microsoft.com/packaging/2010/07/nuspec.xsd">
  <metadata>
    <id>ClientDB</id>
    <version>1.0.0</version>
    <authors>DrDoctor</authors>
    <requireLicenseAcceptance>false</requireLicenseAcceptance>
    <description>Client database</description>
  </metadata>
  <files>
    <file src="bin\Release\ClientDB.dacpac" target="ClientDB.dacpac" /> 
    <file src="bin\Release\Reference.dacpac" target="Reference.dacpac" /> 
  </files> 
</package> 

(You can adapt this as you need for your requirements)

As shown in my previous post on building SSIS projects in TeamCity you will need to add this NuSpec file the list of packages to build within the NuGet Packaging step to have TeamCity build the NuGet package.

Once TeamCity is building the NuGet package, we can add a new Deploy NuGet Package step in our Octopus Deploy project, with the follow PowerShell script as a custom deployment script.

Here is the PowerShell script which will generate an upgrade script using SqlPackage.exe

$dbName = $OctopusParameters['Client Staging DB']
$server = $OctopusParameters['National DB Server']

$releaseNumber = $OctopusParameters['Octopus.Release.Number']

& 'C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\SqlPackage.exe' /Action:Script /OutputPath:Upgrade.$dbName.$releaseNumber.sql /SourceFile:DrDoctor_STG.dacpac /TargetServerName:$server /TargetDatabaseName:$dbName /p:IncludeCompositeObjects=true

New-OctopusArtifact -Path "Upgrade.$dbName.$releaseNumber.sql"

This will compare the dacpac to the database, if the database doesn’t exist then the upgrade script will create it, otherwise the upgrade script will contain a bunch of sql commands to upgrade the database to match the dacpac.

The New-Artifact command is a useful Octopus Deploy function that tells the tentecle to grab the file and make it part of the release, so it’s then available from within the Octopus Deploy web ui.

Alternatively, if you’re game you could configure SqlPackage to automatically upgrade the database by using Action:Publish instead.

In the next blog post I’ll be covering multi-tenanted releases with Octopus Deploy and using the Offline Package Drop feature which was new in 3.0.

Disclaimer: the PowerShell could definitely do with some tidying up, don’t hold that against me 🙂

Building and packaging SQL Server Integration Services projects in TeamCity

At DrDoctor we’ve been using Octopus Deploy for about a year and half now, and in that time we have developed a very robust way of releasing the core of our system. That is, two IIS websites and a bunch of windows services. Release automation has vastly increased the frequency at which we release software into production and in turn the speed at which the business can operate.

The missing part in all of this are the integration projects, when DrDoctor begins working with a new hospital we do a bunch of work integrating their PAS system into our system. This takes the form of a couple of staging databases and a couple of SQL Server Integration Services (SSIS) projects.

Whenever we are discussing changes that need to be made we always screw up our faces at the thought of doing the release. We also have no idea which clients have which version, or when we last did a release.

In this and a few subsequent posts I’m going to outline the various steps that I took to automate our deployments, so we can get on doing the interesting work and let the computers handle the boring deployment work.

Building SSIS Projects in TeamCity

The first step required is to have TeamCity build our Integration Services (SSIS) projects in TeamCity. In reality all it’s really doing is taking all the .dtsx packages that are part of the project and turning them into an ispac file.

The ispac is basically a zip file that contains all the packages and some other metadata which is used when deploying into SQL Server SSISDB.

There are two methods that come up from a Google search on getting TeamCity building SSIS projects, the first involves using a community MSBuild extension and a bunch of other bits, you can read more about it here or here, you can find the MSBuild extension on CodePlex. I tried this method, but didn’t have immediate success.

The second option is to use Visual Studio (SQL Server Data Tools) devenv.exe to build your project, this was already installed on our build server as it is required when building database projects.

To do this in the TeamCity project’s build configuration I added a new build step, which was a Command Line runner:

In the custom script I put in the full path to devenv.exe, our SSIS projects are built in SSDT BI 2012 (Visual Studio 2012 shell) so I’m using that version of devenv.exe on the build server.

Packaging SSIS Projects for deployment

Now that TeamCity is building the project it time to setup TeamCity to package the ispac file into a NuGet package. As mentioned in the intro, we use Octopus Deploy as our deployment system, it in turn uses the NuGet package format as the container for application files (more details).

Setting up TeamCity to automate the creation of NuGet packages turns out to be really easy.

Creating a NuSpec file

The first step is to create a NuSpec file, this is the manifest file that TeamCity will use when building the NuGet package (it tells TeamCity which files to include in the package).

To set this up I added a new file called Local_ETL.nuspec into my project folder with the following content

<?xml version="1.0" encoding="utf-8"?>
<package xmlns="http://schemas.microsoft.com/packaging/2010/07/nuspec.xsd">
  <metadata>
    <id>SSIS.Local_ETL</id>
    <version>1.0.0</version>
    <authors>DrDoctor</authors>
    <requireLicenseAcceptance>false</requireLicenseAcceptance>
    <description>Sync client to local</description>
  </metadata>
  <files>
    <file src="bin\Development\DrDoctor Client ETL.ispac" target="DrDoctorClientETL.ispac" />
  </files>
</package>

This as you can specifies a NuGet package ID, a version and a bunch of files that should be included.

Add build step in TeamCity

The next and final step is to configureTeamCity to create the NuGet package using the NuSpec file.

In TeamCity go into the Build Steps of your project, and click Add build step. From the runner type select NuGet Pack and configure the step with the following:

Make sure that you have ticked “Publish created packages to build artifacts”. This will ensure that the packages are available through the built-in NuGet repository.

In the next post I’ll show how I setup my deployment project in Octopus Deploy to deploy the SSIS ispac to SQL Server SSISDB.

Stay tuned.

Applying custom settings with Octopus Deploy

If you’re a .Net developer and not currently doing automated deployments then you really should be. If you’d like some help getting started send me a message in the comments and I’ll drop you an email.

The core of any deployment is the ability to easily manage and apply configuration values, across environments and across your different apps/web apps.

Octopus Deploy has first class support for managing variables, including setting different scopes or environments and steps in your deployment process. It also has first class support for substituting variables into your <appSettings> and <connectionStrings>. In this case, all you need to do is create a variable with the same name as the appSetting and Octopus Deploy will swap in this value during deployment. Easy.

So what’s the problem?

The trouble comes when you start using custom settings in your app.config and web.config files. For example, you might have a web.config file that looks like this:

<configuration>
  <configSections>
    <section name="SomeCrazySettings" type="SomeAssembly.SomeCrazySettings, SomeAssembly" />
  </configSections>
  <appSettings>
    <!-- omitted -->
  </appSettings>
  <SomeCrazySettings>
    <CustomThing key1="foo" key2="bar" />
  </SomeCrazySettings>
</configuration>

Octopus Deploy does have a built in way of dealing with this problem, but the downside is that you have to hard-code the values in the Octopus Deploy convention, e.g.

<SomeCrazySettings>
  <CustomThing key1="#{Crazy Key1}" key2="#{Crazy Key2}" /> 
</SomeCrazySettings>

The problem that I have with the above is when I want to hard-code the values for local development. One of the applications I work on has a config item called BaseUri and when I’m working locally I like to have it set to my local machine e.g. http://localhost:44301. So the syntax above doesn’t work unless I’m happy with constantly changing the values.

In this blog post I’m going to show how with a little bit of PowerShell we can update the custom settings shown above.

Deployment Script

PowerShell makes your deployments awesome, especially in the context of Octopus Deploy. Anything that isn’t baked in can easily be implement with a little bit of PowerShell.

$Path = $OctopusParameters["OctopusOriginalPackageDirectoryPath"]

$Config = Get-ChildItem "$Path\*" -Include *.exe.config | Select-Object -First 1
[xml]$ConfigXml = Get-Content $Config

$configNodes = $ConfigXml.SelectNodes("//SomeCrazySettings");

if ($configNodes.Count -eq 1)
{
    $ConfigXml.configuration.SomeCrazySettings.key1=$OctopusParameters['Key1']
    $ConfigXml.configuration.SomeCrazySettings.key2=$OctopusParameters['Key2']

    $ConfigXml.Save($Config)
}

This script does the following:

• Line 1-4: Finds the config file and then loads it as an XML object
• Line 6: Using XPath looks for the node with the custom configuration
• Line 8-14: If the node was found, updates the values of the custom keys, and then saves the file.

Go forth and automate.

Incremental Improvements – Quick Update on our Build Process

I’ve written previously about various aspects of the DrDoctor automated build process (you can read a few posts here, here and here). In the last week we’ve made a few more improvements that are worth a mention. The kudos for these improvements goes to my colleagues Steve (blog) and Jev.

Auto Tagging Production Releases

We have a branch that all of our releases into production go out from. We call it develop, but really we treat it more like master. Last week Steve setup TeamCity to automatically tag our git repository (hosted in BitBucket) after a successful build with the associated build number, but only on our develop (master) branch.

This is really useful, because up until that point we had no easy way of working out exactly which version of our code was running on our production box. We could work it out, but it involved clicking lots of links in Octopus Deploy, TeamCity and then git.

Now it’s the simple case of just looking at the name of the folder Octopus Deploy puts code into, as this will match the build number in TeamCity and therefore the tag in git.

Code deployed into folders with build number

Tags in out git repo (only for builds on our develop branch)

TeamCity build history

HipChat Notifications on Deployment

We use HipChat as a team to communicate throughout the day, we also have TeamCity integrated with it to tell us about successful/failed builds. Now, thanks to Jev we get a notification in HipChat when a deployment has been successful/failed into either our Testing or Production environment.

This is done as the final step in our Octopus Deploy Process using a Powershell script from the Octopus Deploy library which using HipChat API broadcasts into one of our chat rooms.

We have lots of ideas in the pipeline to continue making the build and deploy process even better. Stay tuned.

The Unexpected Consequences of Automating our Deployment

Six month ago when I joined DrDoctor we were doing manual deployments into production. It would take us somewhere between two and three days to prepare for, test and release into production. We had no Continuous Integration environment and nowhere to test or demo changes before releasing.

Today it is a very different picture. Each change that we push to our BitBucket repository is picked up by TeamCity, which then does a build and runs an extensive test suite. The build is then made available to Octopus Deploy which we use to deploy into a test environment that very closely mirrors our production server, we can then promote the release to Production.

Here are some of the unexpected consequences that have come about as a result:

1. The quality of the User Stories that our product owners produce has dramatically increased. Our stories are now (generally) focused on a vertical slice
2. When starting new areas of work we factor in the time required to set up the deployment steps necessary to release into production
3. We started off with a test server that kind of looked like our production server, we’ve invested a lot of time in making it mirror production as close as possible
4. We break things from time to time, but we’re okay with that – when we do break things we retrospect and figure out how to avoid it happening that way again
5. We try to be mindful of making architectural decisions that are going to fit with releasing often
6. We are actually able to pursue continual improvement – but after a while we became stuck as to what we needed to improve so we created a release diary which we fill in each time we deploy into production to identify pain points
7. Our deployment system (Octopus Deploy) has become living documentation about our production environment
8. At the beginning deploying database changes was too hard to automate, so we stuck with a manual process. Over time we’ve figured out how to automate right up unto the point of applying the changes
9. We had the mantra – deploy often – but then we found ourselves in the habit of bunching up lots of changes, which made it harder to deploy. To fix this we gave ourselves three days from the time a story had been accepted by the Product Owner (that means demoed in our test environment) to releasing it into Production – we don’t always hit it but we try our best
10. Releasing into production is no longer the job of the individual who had the most experience and knew all the quirks, rather it is something that everyone on the team can all do, with confidence

If you’re interested in how to get started with automating your deployments leave a comment, I would be very happy to help you along the way.

Deploying Database Projects with Octopus Deploy

Are you interested in automating your deployment process? Leave a comment and I would be more than happy to help you along the path, you won’t look back!

At DrDoctor we are currently under going a massive initiative to automate our deployment process. We have made excellent progress over the last couple of months,  and as it stands we can confidently and effortlessly release new versions of the various software components of the system (IIS websites, Windows services).

My colleague Steve has written on the stack which we are using to automate, at the core of it is TeamCity and Octopus Deploy.

From the beginning of this initiative there has always been an elephant in the room, a task that no one has wanted to tackle nor known how to. You can probably guess from the title of this post that I’m taking about database deployments.

At the moment the database upgrade part of our process looks like this:

1. Build the SQL Server Database Project in Visual Studio
2. Find the output directory of the SQL project
3. Connect to the remote server
4. Copy the DacPac file to the remote server
5. Look up syntax for SqlPackage.exe
6. Generate update script (using SqlPackage.exe)
7. Look over script in management studio
8. Run script

As you can see there are a lot of steps and one needs to remember various things, like the syntax for SqlPackage (I’ve just about memorised it now).

We are using Octopus Deploy as our deployment system, it works by copying NuGet packages to remote servers and then uses PowerShell scripts to install and configure. We also use TeamCity as our build server.

The guys at octopus deploy have a handy NuGet package which you install into the projects you want Octopus to deploy, and during the Build stage in TeamCity it will then turn these projects into NuGet packages, ready to be deployed.

Unfortunately there is no way to install NuGet packages into SQL Server Database Project types (not that I could find anyway), there are two ways of turning your database project into a NuGet package on TeamCity.

The first is to manually edit the project file adding the targets for OctoPack, the other method is to use a NuSpec file and the NuGet Package build step in TeamCity. The following snippet needs to be pasted at the very bottom of the .sqlproj file right before the </Project> tag.

<Import Project="..\..\..\packages\OctoPack.3.0.31\tools\OctoPack.targets" Condition="Exists('..\..\..\packages\OctoPack.3.0.31\tools\OctoPack.targets')" />
<Target Name="EnsureOctoPackImported" BeforeTargets="BeforeBuild" Condition="'$(OctoPackImported)' == ''">
  <Error Condition="!Exists('..\..\..\packages\OctoPack.3.0.31\tools\OctoPack.targets') And ('$(RunOctoPack)' != '' And $(RunOctoPack))" Text="You are trying to build with OctoPack, but the NuGet targets file that OctoPack depends on is not available on this computer. This is probably because the OctoPack package has not been committed to source control, or NuGet Package Restore is not enabled. Please enable NuGet Package Restore to download them. For more information, see http://go.microsoft.com/fwlink/?LinkID=317567." HelpKeyword="BCLBUILD2001" />
  <Error Condition="Exists('..\..\..\packages\OctoPack.3.0.31\tools\OctoPack.targets') And ('$(RunOctoPack)' != '' And $(RunOctoPack))" Text="OctoPack cannot be run because NuGet packages were restored prior to the build running, and the targets file was unavailable when the build started. Please build the project again to include these packages in the build. You may also need to make sure that your build server does not delete packages prior to each build. For more information, see http://go.microsoft.com/fwlink/?LinkID=317568." HelpKeyword="BCLBUILD2002" />
</Target>

NB: You will need to make sure that the relative paths to OctoPack is correct.

The downside to this obviously is that when a new version comes out I’ll have to manually update this path to point to the new version.

Now TeamCity will turn this into a NuGet package, at this point I can automate steps 1-4.

To automate steps 5 and 6 requires writing a PowerShell script which will call SqlPackage.exe. The following script will have the the required parameters to filled in during deployment by Octopus Deploy and use them to execute SqlPackage.exe:

$dbName = $OctopusParameters['DbName']
$user = $OctopusParameters['DbUser']
$pwd = $OctopusParameters['DbPassword']
$server = $OctopusParameters['DbServerName']

Write-Host DbName: $dbName
Write-Host User: $user
Write-Host Server: $server

& 'C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\SqlPackage.exe' /Action:Script /OutputPath:Upgrade.sql /SourceFile:National.dacpac /TargetServerName:$server /TargetDatabaseName:$dbName /p:IncludeCompositeObjects=true /TargetUser:$user /TargetPassword:$pwd

It used to take about 30 minutes on average to do all this and was a massive pain just to deploy a relatively minor change. Now it takes between 5 and 10 minutes, depending on the amount of changes being applied. We now have a very simple process to follow.

Octopus Deploy doing the deployment of the database project and generating the upgrade script

The upgrade.sql script generated by SqlPackage.exe

TeamCity – Branching Databases and Continuous Integration

This week there have been lots of build failures, thanks to our HipChat and TeamCity integration I’m notified constantly. As a “senior developer”, this matters to me. The problem comes down to multiple feature branches using a single CI database, which means that it isn’t always “up-to date”, hence the failing tests.

In this post I’m going to show you how I fixed this by setting up TeamCity to publish a database for each of our feature branches.

There are a two bits that need to be done to achieve this end goal:

1. TeamCity needs to publish the SQL Server Database Project into a separate database for each branch
2. All connection strings throughout the solution need to be updated to point to this new database

Publishing the SQL Database Project

To accomplish this we will be using the SqlPackage.exe command line utility (MSDN docs) which is shipped with SQL Server Data Tools (you will have probably already installed this on your build server to get it to build the sqlproj). This command line tool does the same thing as when you right click in Visual Studio on your project and select publish.

We can use SqlPackage.exe to publish a new database by using the following command:

sqlpackage.exe
/Action:Publish
/SourceFile:"c:\path\to\my.dacpac"
/TargetDatabaseName:targetdb
/TargetServerName:sqlserver1
/Profile:"mydatabase.ci.publish.xml"

The arguments are pretty self-explanatory, except for perhaps the last one. The profile as you will see in a minute allows you to define different parameters in an XML file which are then used during deployment.

Creating the Continuous Deployment Profile

For our CI environment deployments I always want the database to be dropped and recreated, this can be configured in the deployment profile.

In Visual Studio, right click on your database project and click Publish. This will bring up the Publish Database window.

Then click Advanced, this will bring up the Advanced Publish Settings. Tick “Always re-create database”. The options you can select are extensive and worth a look through.

Click OK to close this and go back to the Publish Settings window.

To create the Profile simply click the “Create Profile” button, this will add a new {projname}.publish.xml file to your project.

I’ve renamed mine to Database.CI.publish.xml

Setting up TeamCity

The next thing to do is to have TeamCity actually publish the database project. In TeamCity add a new Build step:

1. Runner type: Command Line
2. Step name: Publish database
3. Run: Executable with parameters
4. Command executable: C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\SqlPackage.exe
5. Command parameters:

/Action:Publish
/SourceFile:src/Database/bin/Output/MyDatabase.dacpac
/TargetDatabaseName:MyDatabase.%teamcity.build.branch%
/TargetServerName:[server]
/Profile:src/Database/Database.CI.publish.xml

Then click Save.

Updating Connection Strings

Once TeamCity has built the solution and published the latest version of the database I needed a way to update all the connection strings in the various app.config and web.config files.

To accomplish this I put together the following PowerShell script:

$connectionStringName = $args[0];
$newConnectionString = $args[1];

# get the directory of this script file
$currentDirectory = [IO.Path]::GetDirectoryName($MyInvocation.MyCommand.Path)

$fileList = gci $currentDirectory -Recurse -Filter *.config

foreach($file in $fileList)
{
    $appConfig = New-Object XML
    # load the config file as an xml object
    $appConfig.Load($file.FullName)

    foreach($connectionString in $appConfig.configuration.connectionStrings.add)
    {

        if ($connectionString.name -eq $connectionStringName)
        {
            'file: ' + $file.FullName
            # write the name to the console
            'name: ' + $connectionString.name
            # write the connection string to the console
            'connectionString: ' + $connectionString.connectionString
            # change the connection string
            $connectionString.connectionString = $newConnectionString
        }
    }
    # save the updated config file
    $appConfig.Save($file.FullName)
}

What this does is it will find all *.config files files recursively from the directory that the PowerShell script lives in, it will then load each config file as an XML file and find all the connection strings, any that have a name that matches the first argument will have the ConnectionString property updated to the value of the second argument.

To configure TeamCity add a new build step:

1. Runner type: Powershell
2. Step name: Update connection strings
3. Powershell run mode: Version 2.0; Bitness: x86
4. Script: File
5. Script file: ConnectionStringUpdate.ps1
6. Script execution mode: Execute .ps1 script with “-File” argument
7. Script arguments: “MyDatabaseConnString” “Datebase=MyDatabase.%teamcity.build.branch%;Server=…”

Back into the green

Now when code is pushed up to BitBucket TeamCity will run through the following steps:

1. Build the solution
2. Publish the database project(s)
3. Update connection strings
4. Run unit test / integration tests – currently one step

TeamCity is back to being happy

and more importantly, when the build completes HipChat notifies me that the build has been successful.

 

Automate your SalesForce SSIS Staging Packages with C#

This week I’ve been making some changes in one of my data warehouse projects, with the aim of making it a long lived and easy to maintain solution.

One of the big changes that I made was the way I was staging the data from my different sources, and this meant re-working a whole bunch of SSIS packages. Specifically there were a bunch that pull data from SalesForce which needed to be cleaned up.

So I got thinking, “all of these staging tables are loaded the same way, surely I should be able to automate this”.

In this post I’m going to show you a tool that I wrote in C# which connects to SalesForce, downloads metadata on all your objects, then generates staging tables and finally SSIS packages to load these tables.

The Manual Process

As I was re-working my packages, I was also recreating a lot of the staging tables. This was a very tedious process, as I had to go back and forth looking at the mapping of SalesForce types to their counterpart in SQL Server.

After creating the staging tables, the process of loading them from SalesForce is always the same:

1. Truncate staging table
2. Populate staging table with records from SalesForce (for a subset of column)

Down the line I have plans to extend this, so I’m only loading changed objects from SalesForce, which would result in the following process:

1. Truncate staging table
2. Lookup (from a meta data store) the last date loaded
3. Populate staging table with records from SalesForce with CreatedDate or LastModified after the last date loaded
4. Update the last data loaded value

The Solution

I designed a simple application in C#, this is what it looks like:

The example shown here is looking at the Opportunity object with a number of columns selected.

Clicking Generate Table DDL will pop up the following:

And clicking Yes will then create the object in your database.

Clicking Generate Package will create the SSIS package which follows the above design pattern:

(I’m using the SalesForce Source from PragmaticWorks)

When it comes to extending the design pattern to only load new and/or changed objects all I will need to do is update my package generator and re-create the packages. Saving me the hassle of going through each package and making the change.

Future Plans

The application needs a bit of a tidy up before I can make it available for download, it also uses the PragmaticWorks assemblies for SSIS so I need to find out if I can bundle them for download.

It was an interesting exercise building this application so I’m planning on writing a few posts on how to programmatically build SSIS packages with C#.