Octopus Deployment to Multiple Client Databases on one SQL Server Instance

AntAnt Posts: 17 Bronze 1
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

Answers

  • AlexYatesAlexYates Cambridge, UK Posts: 216 Rose Gold 1
    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.
    Alex Yates
    DevOps Mentor and Coach

    Director of DLM Consultants
    Co-organisor for Data Relay
    Creator of Speaking Mentors
    Microsoft Data Platform MVP
    Friend of Redgate
    Twitter / LinkedIn
  • AntAnt Posts: 17 Bronze 1
    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
  • AlexYatesAlexYates Cambridge, UK Posts: 216 Rose Gold 1
    No probs. Good luck. Reach out if you want some help. :-)
    Alex Yates
    DevOps Mentor and Coach

    Director of DLM Consultants
    Co-organisor for Data Relay
    Creator of Speaking Mentors
    Microsoft Data Platform MVP
    Friend of Redgate
    Twitter / LinkedIn
  • AntAnt Posts: 17 Bronze 1
    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
  • AlexYatesAlexYates Cambridge, UK Posts: 216 Rose Gold 1
    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: 
    Technical lightning talk for engineers:
    Alex Yates
    DevOps Mentor and Coach

    Director of DLM Consultants
    Co-organisor for Data Relay
    Creator of Speaking Mentors
    Microsoft Data Platform MVP
    Friend of Redgate
    Twitter / LinkedIn
  • AntAnt Posts: 17 Bronze 1
    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

  • AlexYatesAlexYates Cambridge, UK Posts: 216 Rose Gold 1
    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!
    Alex Yates
    DevOps Mentor and Coach

    Director of DLM Consultants
    Co-organisor for Data Relay
    Creator of Speaking Mentors
    Microsoft Data Platform MVP
    Friend of Redgate
    Twitter / LinkedIn
  • AntAnt Posts: 17 Bronze 1
    We are going to automate the process wit hour provisioning app, so hopefully we will not need too much clicking...

    Thanks,
Sign In or Register to comment.