Octopus Deployment to Multiple Client Databases on one SQL Server Instance

Ant

Hi,

We currently have the need to deploy the same database on the same SQL Instance to a number (100+) customers and we want to automate the process.

We currently use TeamCity and DLM Automation to create NuGet packages for the repeatable database and pass these to the Octopus Package Library for Redgate Deploy From Package. 

Is there anyway to automate this process so that we can call the deploy for each of our customer databases (DB_CustGUID), basically duplicating the deploy with a different target database name, without having to manually create the deploy step for each?

Thanks in advance for any help.

regards,
Anthony

AlexYates

How's your PowerShell?

The step templates don't allow for it but the raw PowerShell does.

https://documentation.red-gate.com/dlma2/cmdlet-reference/use-dlmdatabaserelease

You'll want to take an input variable containing a list of target DB names and then you'll want to create aalist of  DlmDatabaseConnection objects for each target DB. Then you'll use Use-DlmDatabaseRelease to deploy your release to each DlmDatabaseConnection object in the list.

Let me know if you have any Qs or want some help.

Ant

Hi Alex,

Powershell, is not something I've played with much, but we do have guys who are very adept. 
I'm sure we'll have more questions, when we look in to this further. I hoped it wasn't the case, but expected that it would rely on PS scripting.

Thanks for the link and quick response,

Anthony

AlexYates

No probs. Good luck. Reach out if you want some help. :-)

Ant

Hi Alex,

I don't suppose you have an example of multi database deployment I could have a look at and how it would be triggered from Octopus.

cheers,

Anthony

AlexYates

Sorry for the delay. I've been very busy and on vacation. Catching up on emails etc now.

Firstly, following the release of SQL Change Automation in the last few weeks, you should now use this link instead:
https://documentation.red-gate.com/sca3/reference/powershell-cmdlets

Open disclosure: I've not tested this, but I expect something like the following may work. With 100+ database you probably want to use a list and a for each loop to save all your target databases but I'll let you google the syntax for that:

#Defining variables
$nuget = C:\path\to\your.nupkg
$target1 = New-DatabaseConnection -ServerInstance SERVER01 -Database yourdb  | test-DatabaseConnection # This uses WinAuth
$target2 = New-DatabaseConnection -ServerInstance SERVER02 -Database yourotherdb -Username user -password P4ssword1!! | test-DatabaseConnection # This uses SQL Auth
$releasePath = C:\some\file\share\$OctopusParameters["Octopus.Environment.Name"]\$OctopusParameters["Octopus.Project.Id"]\$OctopusParameters["Octopus.Release.Number"]

#Creating release artifacts
$release = New-ReleaseArtifact -Source $nuget -Target $target1
# Alternatively, try this to validate that all targets are in same start state. It will take more time to run but alerts you for drift earlier
# $release = New-ReleaseArtifact -Source $nuget -Target @($target1, $target2)
Export-DatabaseReleaseArtifact $release -path $releasePath

#You might like to split this script into two at this point, allowing you to insert an Octopus manual intervention step in between to review the release artifacts.
#You might also like to try a dry run deploy to one environment before rolling out to the rest.

#Deploying the release
Use-DatabaseReleaseArtifact $release -DeployTo $target1
Use-DatabaseReleaseArtifact $release -DeployTo $target2

For reference, several years ago I worked wth the folks at Skyscanner and they employed a strategy very similar to this one.

Marketing video for non-techy managers: https://www.youtube.com/watch?v=sNsPnCv7hHo
Technical lightning talk for engineers: https://www.youtube.com/watch?v=91n1mkyrSp8

Ant

Hi ALex,

Thanks for this, it may be useful in the future, but we've gotten around the issue by letting Octopus do all of the deployments and we are controlling that with multiple tenants, created by our web based provisioning service. Time will tell whether this will scale!

Cheers,
Anthony

AlexYates

Ah yes,

That's also a good solution - and aligns better with Octopus paradigms. It just means you have a lot more clicking to do in the Octo config - but your way will get you more of the Octopus good-ness so its probably the more natural solution.

Good luck!

Ant

We are going to automate the process wit hour provisioning app, so hopefully we will not need too much clicking...

Thanks,