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.

1-deployment-output

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

2-the-upgrade-file

The upgrade.sql script generated by SqlPackage.exe

Advertisements

15 thoughts on “Deploying Database Projects with Octopus Deploy

  1. Alex says:

    Thanks for sharing, setting this up now. As for manual update of octopack, if you have more than one project in your solution and add nuget package to non DB project, then your relative path would point to a latest package, updateable from another project (non-DB).

  2. clafollett says:

    Ken,

    Thanks for the post discussing your techniques to publish Dacpacs using Octopus. I’m also in need of a similar solution. One thing I’m curious about was if you created a .nuspec file for your DB Project. If so, do you mind providing some details on your file mappings?

    • KenR says:

      Hi, we use TeamCity to generate NuGet packages using the Octopus Deploy plugin. That takes care of everything so there is no need for a nuspec file, so long as your project includes the Octopack NuGet package. Hope that makes sense…?

      • clafollett says:

        Ok, hit another snag… what all did you have to install on the Build Server to get the project targets to map correctly. The “SQL Server Database Project” references this Import:

        Did you have to install VS and SQL Server on the build server to get this to work?

        Thanks again for your help.

      • clafollett says:

        Looks like word press ate the XML Import part of my post. Here is the Project attribute path:

        $(MSBuildExtensionsPath)\Microsoft\VisualStudio\v$(VisualStudioVersion)\SSDT\Microsoft.Data.Tools.Schema.SqlTasks.targets

      • KenR says:

        You will (unfortunately) have to install SQL Server Data Tools onto your build server, and then check that the paths all tie up.
        In TeamCity, I use the Visual Studio runner type to build my solution and in the config specify /p:VisualStudioVersion=11.0 so that the MSBuild paths tie up to the Microsoft.Data.Tools.Schema.SqlTasks.targets file. Hope that makes sense?!

      • clafollett says:

        That’s what we ended up doing was installing SSDT. I ended up manually altering the config to use 11.0 instead of the default 10.0. Working like a champ now. Thanks again for the pointers. Keep up the great work!

  3. mathis young says:

    HI, My name i Mathis and I working on deploying a database in a CI environment.

    The 1st issue is building my DB. I create a SQL project in VS 2015, build when I build unfortunately my project relies on other db references in the same db server.

    So I said no problem right. So I added a db reference , but then the new db reference need to reference the original db. Which I cannot reference back because I would get a circular error. I would first need some advise on how to fix this , so I did some research at the blog below http://blogs.solidq.com/en/sqlserver/ssdt-how-to-solve-the-circular-references-issue/… I not a db guru and not sure if this would fix my issue.

    After that my plan is to use Team city and octo deploy to drop newly added sql scripts to the QA server and have the TC trigger the automation on the product with the selenium agent. If you have a chance I would like to discuss certian scenarios of you are free to help . thanks

    Mathis

    Mathisyoung@yahoo.com

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s