Using Octopus Deploy to Publish SSDT Projects Built with TeamCity

In another post I talked about how we were asked to move away from TFS to start using GitHub, TeamCity and Octopus Deploy.  In a further post I demonstrated a way of building and publishing an SSDT project under source control in a local git repository using TeamCity.  In this post I’m going to describe how to install Octopus Deploy and how to use it to publish an SSDT project built with TeamCity.

Overview

Before I start going into details of how to build and publish an SSDT project using TeamCity and Octopus Deploy I thought I’d run over how the end to end process will work.

  • An SSDT project exists in source control, for example GitHub or TFS
  • TeamCity is configured to listen for changes made to the source control repository and triggers a build of the SSDT project whenever a check in occurs
  • Once the build is complete a NuGet package is created that contains the DACPAC for the SSDT project and a Deploy.ps1 powershell file that actually does the deployment
  • The NuGet package gets fed up to Octopus Server which is configured to run the Deploy.ps1 file against the required tentacle

Installation

There are three parts to Octopus Deploy that we need to install to get it all working in the way we want and all of these are available from the Octopus Deploy download page. Firstly, there is Octopus Server. This is the bit that controls the deployment and should be installed somewhere appropriate, for example, on your build server or on it’s own box. Secondly, there is Octopus Tentacle. This needs to be installed on all the servers that you want to deploy to. So if your environment has three DBs servers, two apps servers and four web servers you’ll need to install this on every one. Octopus Server then connects to each of the Tentacles and deploys to them. One singe Octopus Server can control the deployment to all your environment so you can deploy to CI, staging and live all from one box. Finally, there is a TeamCity plugin for Octopus that can be deployed to control the publishing directly from TeamCity.  In this example I’m running everything on my local box which is running Windows 7.

Octopus Server

  • Firstly, download the latest version of the Octopus Server from the Octopus Deploy website.  In this example I’ve download the 64 bit version 2.4.5.46.
  • Run the msi and the installation wizard will start.  Accept the terms and conditions and choose and appropriate install path – I’m using D:\Octopus, then click Install.
  • Once the install has completed open the Octopus Manager (if it hasn’t opened already).
  • You should see a Welcome! screen so click on “Get started…” and make your way through to the Licence section
  • You can download a 45 day trial licence by clicking on the link and filling in the small form on the website.  You’ll then get a licence key emailed to you which is a piece of XML you can paste into the box in the Octopus Setup Wizard.
  • Choose a storage location – again I’m just using D:\Octopus.
  • Choose an HTTP listen port – I’m using 785
  • Set up the authentication – I’m just using a username and password stored in Octopus but if you’re using this for something other than a proof of concept you might want to use an active directory account.
  • Once the install has completed you should now have a windows service named “OctopusDeploy” installed and you can browse http://localhost:785.  You can configure the Octopus Server settings using the Octopus Manager.
Octopus Manager after successful installation

Octopus Manager after successful installation

Octopus Tentacle

  • Firstly, download the latest version of the Octopus Tentacle from the Octopus Deploy website.  In this example I’ve download the 64 bit version 2.4.5.46.
  • Run the msi and the installation wizard will start.  Accept the terms and conditions and choose and appropriate install path – I’m using D:\OctopusTentacle, then click Install.
  • Once the install has completed open the Tentacle Manager (if it hasn’t opened already).
  • You should see a Welcome! screen so click on “Get started…”
  • Choose appropriate storage paths – I’m using D:\OctopusTentacle
  • I used the default Listening Tentacle communication style
  • Once the install has completed you should now have a windows service named “OctopusDeploy” installed and you can browse http://localhost:785.  You can configure the Octopus Server settings using the Octopus Manager.
  • Browse to your Octopus server, e.g. localhost:785, and click on the Configuration tab.
  • Click on Certificates and copy the thumbprint value.  This needs to be added to every tentacle you set up.
  • Back in the Tentacle Setup Wizard, paste the Octopus thumbprint in and choose an appropriate listen port – I’m using the default 10933.
  • Once the install has completed you should now have a windows service named “OctopusDeploy Tentacle” installed. You can configure the Octopus Tentacle settings using the Tentacle Manager
Tentacle Manager after successful installation

Tentacle Manager after successful installation

Configure Environment

Now we’ve got an instance of Octopus Server and a Tentacle we can now configure our first environment

  • Browse to your Octopus server, e.g. http://localhost:785, and click on the Environments tab
  • Add a new environment and add a new machine
  • Enter the name of the machine you installed the Tentacle to and click discover.  If the server is able to connect to the tentacle you should see something like the following…

Octopus3

  • The value in the Thumbprint box will match the thumbprint shown in the Communications section on your Tentacle Manager
  • See the Octopus documentation for how to use the Environments and Roles values in the Deployment section – for this example I’m just going to give this tentacle a role of “Database”
  • Click on Save and you should now see your machine added.
  • If you have multiple environments with multiple machines you would add them all in the same way.

Configure Communication between TeamCity and Octopus Deploy

I believe there are at least a couple of ways to do this, with one of them being using one of the build runner types added as part of the TeamCity Octopus plugin but for this example I’m going to set up a NuGet feed.  If you’ve not already set up TeamCity please see my previous post. For the remaining steps I’m assuming you’ve got TeamCity set up and connected to some source control.

  • Browse to you TeamCity instance and click on Administration in the top right corner
  • Click on the NuGet Settings under Integrations and enable to the NuGet Server.  You will then be given a Authenticated Feed URL.  Copy this URL.

Octopus4

  • Next click on the NuGet.exe tab, then “Fetch NuGet”.  Choose the version NuGet you want to install and click Add

Octopus5

  • Browse to your Octopus Server site and click on the Library tab.
  • Under the “External feeds” tab click “Add feed”.
  • Give it an appropriate name and paste in the NuGet Server URL from TeamCity and enter the username and password for you TeamCity account.

Octopus6

  • Click on “Save and test”.  Ignore the test for the moment, we first need to create a NuGet package containing our DACPAC and then we can test it.

Octopack – tricking SSDT into using it

If you’ve not already got an SSDT project that you want to publish then create a test one now.  I’ve just created a very simple one named “SimonOctopusPlay” with a single table named “SimonTest” with a single column named “Id”. In order for Octopus to deploy our DACPAC it needs to be passed to it in the form of an artefact.  In this case the artefact is a NuGet package containing the DACPAC and a powershell to do the deployment. Octopus provides a NuGet package named Octopack that can be added to certain project types and a TeamCity plugin that handles the creation of the artefact for you.

Octopus8

However, Octopack does not support SSDT projects

Octopus9

The reason is doesn’t support SSDT projects is because what we’re trying to put in the artefact is not what Octopus is expecting.  Octopus wants to be passed the actual change scripts rather than the full model inside the DACPAC.  For this reason Octopus only supports a change-script-driven deployment approach rather than a model-driven approach which is what SSDT is.  Octopus supports ReadyRoll and DbUp as shown in their documentation.  However, change-script-driven development seems absolutely awful.  I’ve only had a brief play with ReadyRoll but in my opinion it’s nowhere near as good a tool as SSDT.  If you’re a developer who does front end, application and database development and you have a pretty simple data model then change-script-driven development may be better for you, but if you are a database specialist I would recommend SSDT.  However, at the end of day these are all tools that you can get working so it’s down to what ones you prefer. Even though Octopack doesn’t support SSDT we can trick it quite simply.

  • There may be another way to get Octopack but one way is to add a console application to your solution and add the Octopack package to it.
  • Now within your solution folder you’ll have a “packages” folder that contains the Octopack package.
  • You can now delete the console app and open the SSDT project file in a text editer
  • Manually add an import to the bottom of the project file as follows…
<Import Project="..\packages\OctoPack.3.0.31\tools\OctoPack.targets" />

I’m not sure why it has to go at the bottom but if not then the TeamCity plugin doesn’t seem to pick it up.

If you’ve not already done so we need to include the solution folder into source control for TeamCity to pick up.  I briefly mentioned how to do this in my previous post. Now we need to install the TeamCity plugin for Octopus.

Installation – TeamCity plugin

Now we’ve tricked our SSDT project into using Octopack we need to install the TeamCity plugin to allow the build to use it.

  • Firstly, download the latest version of the TeamCity plugin from the Octopus Deploy website.
  • Copy the downloaded zip file to C:\ProgramData\JetBrains\TeamCity\plugins
  • As soon as you drop it in here it will automatically be unpacked into the .unpacked subdirectory

Octopus11

  • Now restart the TeamCity Server windows service.

Octopack – create NuGet package

Now we can set up our build definition in TeamCity as per my previous post but only build the solution rather than publishing it.

As we’ve installed the TeamCity plugin you will now see an additional couple of settings under “Octopus Packaging”.

Octopus12

Now is a good time to ensure the build step in TeamCity runs okay.  If it does we can then tell it to use Octopack to create a NuGet package containing the DACPAC. To do this we need to create two files within all the SSDT projects we want to publish. Firstly create a .nuspec file with the same name as the SSDT project, for example SimonOctopusPlay.nuspec.  This .nuspec file is the thing that controls what files need to be included in the Octopus artefact and should look something like this…

<?xml version="1.0"?>
<package xmlns="http://schemas.microsoft.com/packaging/2010/07/nuspec.xsd">
 <metadata>
 <id>SimonOctopusPlay</id>
 <version>1.0.0.1</version>
 <authors>Simon Peacock</authors>
 <description>Package for SimonOctopusPlay</description>
 </metadata>
 <files>
 <file src="\bin\Release\SimonOctopusPlay.dacpac" target="Content" />
 <file src="\bin\Release\Deploy.ps1" />
 </files>
</package>

Octopus13

Now we need to create the Deploy.ps1 file.  This is a powershell script that Octopus will automatically run if exists in each deployment.  This is the thing that does the actual deployment and we just use the Microsoft.SqlServer.Dac.dll methods to do this. For full details on what you can do with this see MSDN. The DacDeployOptions matches the options you can configure when publishing in Visual Studio.

Octopus14

Here is an example of a Deploy.ps1 script

# Set params
$databaseName = "SimonOctopusPlay"

# Add the DLL
# For 32-bit machines
#Add-Type -path "C:\Program Files\Microsoft SQL Server\110\DAC\bin\Microsoft.SqlServer.Dac.dll"
# For 64-bit machines
Add-Type -path "C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\Microsoft.SqlServer.Dac.dll"

# Create the connection strnig
$d = New-Object Microsoft.SqlServer.Dac.DacServices "server=(local)"

$dacpac = (Get-Location).Path + "\Content\" + $databaseName + ".dacpac"

Write-Host $dacpac

# Load dacpac from file & deploy to database
$dp = [Microsoft.SqlServer.Dac.DacPackage]::Load($dacpac)

# Set the DacDeployOptions
$options = New-Object Microsoft.SqlServer.Dac.DacDeployOptions -Property @{
 'BlockOnPossibleDataLoss' = $true;
 'DropObjectsNotInSource' = $false;
 'ScriptDatabaseOptions' = $true;
 'IgnorePermissions' = $true;
 'IgnoreRoleMembership' = $true
}

# Generate the deplopyment script
$deployScriptName = $databaseName + ".sql"
$deployScript = $d.GenerateDeployScript($dp, $databaseName, $options)

# Return the script to the log
Write-Host $deployScript

# Write the script out to a file
$deployScript | Out-File $deployScriptName

# Deploy the dacpac
$d.Deploy($dp, $databaseName, $true, $options)

The Deploy.ps1 file must get included in the output of the build so to do this make sure the “Copy to Output Directory” for the file is set to “Copy Always” or “Copy if newer”.

Octopus17

Now we can check in our nuspec and ps1 files and update TeamCity to create the nuspec package that will be used as the Octopus Deploy artefact.

TeamCity – running the build

We can now check the “Run OctoPack” in our build step and try the build again.

Octopus18

Please note: if you are using a git repository for your source control then you will probably see the following error when you run the build.

error MSB4062: The “OctoPack.Tasks.GetAssemblyVersionInfo” task could not be loaded from the assembly C:\TeamCity\buildAgent\work\245900cf36cf882e\packages\OctoPack.3.0.31\tools\OctoPack.Tasks.dll.

The DLL in question is part of the OctoPack pacakge but your global gitignore file is probably set to ignore all DLL files so this is not actually checked into your repository.  Simply update you global gitignore to not ignore this particular DLL and you should now be able to check this into your git repo.  You’ll need to do the same for the NuGet.exe file in the same folder.

Octopus19

Now you should hopefully have a successful build and you should see that an artefact has been created for the database project and it contains the Deploy.ps1 file and the compiled DACPAC.

Octopus21

Octopus Deploy

Now that we have our artefact this should be available to Octopus for deployment.  If we go to our Octopus Server web page and click on the Projects tab we can create a new deployment project.  Then under the Process tab we can add a deployment step to “Deploy a NuGet package”.  Choose the NuGet feed that we set up earlier and enter the name of the NuGet package as the NuGet package ID

Octopus22

Now we can click the “Create Release” button in the top right corner and Save the release. Then we can deploy this release to our environment.  If it is successful the database and table should have been created.

The Octopus TeamCity plugin also gives you three additional build runners: OctopusDeploy : Create release, OctopusDeploy: Deploy release and OctopusDeploy: Promote release.  This can be used to automate the release and deployment thr0ugh TeamCity rather than doing it manually through Octopus.

Advanced Settings

If you have a database project that has a database reference to another project or DACPAC then you need to make sure that you add all the DACPACs for the referenced projects into the NuGet package so they are available for when the Deploy.ps1 file is run.

If you use any SQLCMD variables in you database projects you will need to handle these in your powershell script within the DacDeployOptions.  This can be hard coded into the Deploy.ps1 script or you can set up variables in TeamCity for this.

Hopefully if you followed through these steps you should have something that works.  If not let me know and I’ll help where I can.

Advertisements

2 thoughts on “Using Octopus Deploy to Publish SSDT Projects Built with TeamCity

  1. Pingback: Source control, build and deploy of SQL Server databases using GitHub, TeamCity and Octopus Deploy | Simon Learning SQL Server
  2. Pingback: Connect VSTS Package Management to Octopus – Phoebix

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