OPTEN, das einzige Umbraco-zertifizierte Unternehmen der Schweiz

Our Octopus Odyssey: Part 4. Database

This blog is part 4 of a series

Part 1. The Basics
Part 2. Build Script
Part 3. Variables and Powershell
Part 5. web.config
Part 6. NuGet Repository
Part 7. less & CSS

The process

When deploying bigger changes we often have database scripts that we need to run as part of the deploy. The way we handled this was to log onto the database server via remote desktop, then we would make up a backup of the current database in case something went wrong. Then we would run the change script. Octopus made it easy to automate this process.


The team at Octopus Deploy have thought about the needs of users who want to update their Sql Server database at the same time as deploying and have created a simple little tool called DbUp http://dbup.github.io/. It is really simple and easy to use and there is good documentation on the github site.

DbScripts Project

The way we integrated DbUp into our solution was to create a new project which I called DbScripts. Then I installed the DbUp and Octopack nuget packages for this project. Once installed I followed the conventions described in the DbUp documentation creating a Program.cs file with the following code:

class Program
    static int Main(string[] args)
        string connectionString = System.Configuration.ConfigurationManager.


        var upgrader =

        var result = upgrader.PerformUpgrade();

        if (!result.Successful)
            Console.ForegroundColor = ConsoleColor.Red;
            return -1;

        Console.ForegroundColor = ConsoleColor.Green;
        return 0;


Then I added the connection string to the database in the App.config file. There does not need to be a value for the connection string, so long as the name is also the name of a variable on the Octopus Server, the connection string can be set with the appropriate variable value when deploying:

<?xml version="1.0" encoding="utf-8" ?>
        <add name="SHOPTEN_DB1"
            providerName="System.Data.SqlClient" />

        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />

Update scripts

Then I added a folder to the new project named Scripts. This is where all the update scripts go. I decided to name our scripts with the date and time first, followed by a short description with the following format: Script{date}_{time} – {description}.sql This way the scripts will be organised chronologically, DbUp will always run them in the correct order. It is also important that for each script the Build Action is set to EmbeddedResource. This means that the scripts will be compiled into the nuget package. Do do this select the script, then open the properties window. From there it is easy to set the Build Action using the dropdown menu.


The final things that I added to the DbScripts project were two powershell files. One named Deploy.ps1.  This is a very simple script, which just runs the console program to run the scripts on the database:

Write-Host "running deploy scripts"

& .\DbScripts.exe

The second Powershell file is named PreDeploy.ps1. This is run before the database scripts and backs up the database. This script takes the connection string and name of the database from the Octopus variables, then it constructs the sql to backup the database, then executes this sql against the new connection. Here is the code with comments to explain each part:

# Logging to output
Write-Host "backing up db"

# get current date and time
$date = Get-Date -format "yyyyMMdd_HHmmss"
Write-Host "date: " + $date

# get Octopus variables
$connectionString = $OctopusParameters["SHOPTEN_DB1"]
$backupDbName = $OctopusParameters["databaseBackup"]

# create connection to database
$cn = New-Object System.Data.SQLClient.SQLConnection
$cn.ConnectionString = $connectionString

# create sql to backup database
$sql = "BACKUP DATABASE "+$backupDbName+"_DB1 TO DISK = 'E:\PathToDbBackups \"
$sql = $sql+$backupDbName+"_DB1_backup_" +$date+".bak'"

# run sql on database
$cmd = new-object System.Data.SqlClient.SqlCommand
$cmd.Connection = $cn
$cmd.CommandText = $sql
$cmd.CommandTimeout = 600

Finished project

That is all that is needed in the Visual Studio solution, here is a screenshot of the project in the solution explorer:


I created an External tool to package and publish this project in the same way as I did the web applications in my Solution (see part 2).

Octopus Deploy step

To deploy this new database project I loaded Octopus Deploy and added a new step to my deploy process named Update Database and made sure to add the 'Configuration variables' feature. This means that if the name of the connection string in the app.config matches the name of a variable then the connection string will be replaced by that variable. 


I made a copy of the connection string variable scoped to each role and environment, which means that the correct connection string will always be used. The live database will not be updated when deploying to staging etc.


DbUp creates a table in each database where it is run named dbo.SchemaVersions. In this table it keeps a record of all scripts which have been run against this database. This means that the same script will not be run twice.



I really like DbUp, it is simple to use, there are not too many complicated features, it does one thing, running update scripts upon deploy, and it does that one thing well. It means that we can add the DbScripts project into our solution and commit all the update scripts to our Git repository so they are all in one place and versioned along with the code. Also the database update nuget packages are versioned inside the Octopus Server so there is a good record of what changed in the database and when. It also means that it is easy to run update scripts against the staging database first and test whether they work before promoting the same script to the live database. The one thing which DbUp cannot do is roll back a script when reverting to an earlier release using Octopus Deploy, this is why I added the preDeploy step of backing up the database, so the earlier database can be restored manually in the case that something goes very wrong.

Next: web.config

With the database project it was very easy to replace the connection string in my app.config when deploying the project. The same thing can be done to replace a connection string in the web.config file, and any appsettings in the web.config. Unfortunately it cannot automatically replace other values in the web.config which is something we needed to do with our project. In the next blog I will explain how we solved this problem.


0 Kommentar(e):