*** This post is now obsolete. You should read this post instead: https://workingwithdevs.com/tutorial-sql-release-octopus-deploy-databases/ ***

Red Gate already offers a tool for managing your deployments. It’s called Deployment Manager. However, last week one of our account reps asked me to prepare a demo for a customer who had already spent a lot of time building a deployment process around a different release management tool – they didn’t want to discard all that hard work.

I was tasked to put together a demo of a database continuous delivery solution using TFS for source control, TeamCity as a CI server and Octopus Deploy for release management. They wanted to use Red Gate technology to handle the database deployments throughout this pipeline.

I configured TFS according to these steps and I set up TeamCity in the way that I documented last month. However, I could not find any documentation for the PowerShell required to deploy the NuGet package that TeamCity generated for me using Octopus Deploy and the Red Gate SQL Automation Pack, so I wrote one myself.

The approach I have taken will simulate the ‘dynamic upgrade’ approach that we baked into Deployment Manager as standard. This PowerShell will enable people who use Octopus Deploy instead to access similar functionality.

How it all works (and what you need)

The Octopus deploy tentacle that you deploy to is going to make a call to a licenced instance of the sqlCI.exe command line tool which comes with the SQL Automation Pack.

You can put sqlCI.exe wherever you like as long as your Octopus tentacle can call to it with PowerShell and sqlCI.exe can reach the target database. Depending on your architecture a single instance of sqlCI.exe may be enough to deploy to all your targets or you might need multiple instances of sqlCI.exe in order to reach all your target databases or to deploy to multiple target databases in parrallel (rather than in sequence). If you need multiple instances of sqlCI.exe you will also need multiple SQL Automation Pack licences. Of course, you will also need a licence of Octopus Deploy.

To set up your sqlCI.exe instance simply download and install the SQL Automation Pack on the intended server, fire up ‘Intro to SQL Automation Pack’ from the start menu and click the ‘Open Folder’ button below ‘SQL CI’ as follows. This will reveal the path to your instance of sqlCI:

You need to configure a step in Octopus Deploy to deploy the Red Gate NuGet package created by your CI server. You then need to add the post deploy PowerShell script that I have provided below. You can do this by selecting ‘Configure features’ in the build step configuration page and selecting ‘Custom PowerShell scripts’.

The script requires the following variables to be set in Octopus Deploy:

• databaseName
• databaseServer
• databaseUsername
• databasePassword (Octopus allows you to mask sensitive information such as passwords in order to keep them safe.)
• sqlCiPath (This will be the path from your Octopus Tentacle to the instance of sqlCI.exe that you intend to use. You may want to scope this variable differently for different environments if you are using more  than one instance of sqlCI.exe.) 

Once these variables are set and you have configured the process step with the following Powershell as a post deploy script and you are away.

Enjoy! 🙂

# Substituting octopus configuration variables

$databaseName = '#{databaseName}' 
$databaseServer = '#{databaseServer}' 
$databaseUsername = '#{databaseUsername}' 
$databasePassword = '#{databasePassword}' 
$package = '#{Octopus.Tentacle.CurrentDeployment.PackageFilePath}' 
$sqlCiPath = '#{sqlCiPath}' 

 
# creating argument string from variables

$arguments = @(
'sync', 
"/databaseName:$($databaseName)", 
"/package:$($package)", 
"/databaseServer:$($databaseServer)", 
"/databaseUserName:$($databaseUsername)", 
"/databasePassword:$($databasePassword)", 
"/additionalCompareArgs=`"/options:IgnoreUsers,IgnoreCertificatesAndCryptoKeys,ForceColumnOrder,ThrowOnFileParseFailed`""
)

 
# Exposing variables in logs for troubleshooting

write-host $arguments


# Deploy NuGet package using sqlCI.exe

Write-Warning "Performing dynamic database upgrade."
& $sqlCiPath $arguments