Deploy Database Project using Octopus

Octopus is a deployment tool that use the Nuget packaging mechanism to pack your application and deploy it over multiple environments.

Unfortunately it does not have a native support (yet) for Visual Studio Database project, so I had to provide a sort of workaround in my project structure to allow Octopus to deploy also a “Database Project Nuget package”.

Visual Studio .dacpac

Visual Studio Database project is capable to generate diff scripts, a full schema deployment script and also a post deployment script (in case you need to populate the database with some demo data, for example). When you compile a Database project this is the outcome:

image

As you can see we have two different .dacpac files. One for the master Database and one for my own database. A dacpac file is what is called “Data Tier Application” and it’s used within SQL Server to deploy a database schema.

Another interesting thing is the schema structure, in every database project you will have also a second output folder with the following structure:

image

And in the obj folder we have an additional output:

image

which contains a Model.xml file. This file can be used to integrate entity framework with our database schema. The postdeploy.sql is a custom script that we generate and execute after the database deployment.

Package everything with Nuget and OctoPack

So, what do we need in order to have a proper Nuget package of our database schema? Well, first of all let’s see what we should carry on in our package. Usually I create a package with the following structure:

image

The steps to obtain this structure are the following:

1 – Modify the database project to run OctoPack

  <Import 
        Project="$(SolutionDir)\.nuget\NuGet.targets" 
        Condition="Exists('$(SolutionDir)\.nuget\NuGet.targets')" />
  <Import 
        Project="$(SolutionDir)\.octopack\OctoPack.targets" />
</Project>

2 – Provide a .nuspec file with the following structure:

<?xml version="1.0"?>
<package xmlns="http://schemas.microsoft.com/packaging/2010/07/nuspec.xsd">
  <metadata>
    <!-- Your file specifications -->
  </metadata>
  <files>
    <!-- The Database Schema -->
    <file src="\dbo\**\*.sql" 
            target="Content\Schema"/>
    <!-- The deployment script -->
    <file src="\obj\**\*.sql" 
            target="Content\Deploy" />
    <file src="\obj\**\*.xml" 
            target="Content\Deploy" />
    <!-- Your .dacpac location -->
    <file src="..\..\..\..\..\..\bin\**\*.dacpac" 
            target="Content\Deploy" />
  </files>
</package>

And of course have your Build Server the RunOctoPack variable enabled.

Install the package using Powershell

The final step to make the package “digestable” by Octopus using PowerShell. In our specific case we need a power shell script that can execute the .dacpac package and the post deployment script. That’s quite easy.

In order to install a .dacpac with power shell we can use this command:

# load Dac Pac
add-type -path "C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\Microsoft.SqlServer.Dac.dll"

# make DacServices object, needs a connection string 
$d = new-object Microsoft.SqlServer.Dac.DacServices "server=(local)"

# Load dacpac from file & deploy to database named pubsnew 
$dp = [Microsoft.SqlServer.Dac.DacPackage]::Load($DacPacFile) 
$d.Deploy($dp, $DatabaseName, $true)

In my case I set some variables in Octopus in order to be able to dynamically create the database and locate the .dacpac file.

image

The final result is available through Octopus deployment console, cause I always set my PShell commands using | Write-Host at the end:

image

Final note: remember that the only way to stop a deployment step in Octopus using Power Shell is to return –1. In my case I wrap the code in a Try/Catch and return –1 if you want to stop the deployment but you can find a better explanation here.

4 thoughts on “Deploy Database Project using Octopus

  1. Hi Giulio, I use already IISExpress command line but I wasn’t aware of that option of MsBuild. I’ll have a look, thanks.

  2. Interesting approach. I have yet to investigate octopus, holding for v2, but we are using database projects.

    I’m curious if you use this only to create empty databases on a dev/test/staging server, or actually use this to update a database in production (seems risky to me)? Can’t tell by your code if you drop and recreate the database and what you do with incremental deployments of dacpac that would result in data loss (ie drop column).

    I see in the screenshot that you have more variables in octopus than you are showing in the powershell script. For sake of simplicity in this post?

    1. Hi, thanks for your comment.
      Yes I do that using the following workflow:
      DEV->STAGING->PRODUCTION

      Visual Studio Database project handles very well in-place upgrade, versioning and custom data.
      I use .dacpac for everything related to the database schema and data, and then the power of PowerShell to run the .dacpac remotely.
      You should not wait for v2 cause v1 is already quite reliable and stable

Comments are closed.