OctopusDeploy - SQL Always-On Deployment

Question

I've got an OctopusDeploy process for deploying to a database server. The steps include copying db backups from a location to the local server, restoring them into SQL Server, then running a dacpac against them to upgrade them to a specific version.

This all works fine, but we've now added a new environment and I can't work out how to configure the deployment process for it.

Initially, the server was to be a windows clustered environment, with the tentacle running as a clustered service (which meant a single deployment target).

However, the company setting up our servers couldn't get clustering to work for whatever reason, and have now given us something in between:
We have two servers, each with the tentacle installed, configured and running on it.
Each tentacle has a unique thumbprint, and are always running and accessible.
Upon the windows servers, SQL Server has been installed and configured as "always on", with one server being the primary and the other being the secondary.

The idea being that if the primary dies, the secondary picks up the pieces and runs fine.

Conceptually, this works for us, as we have a "clustered" ip for the SQL server connection and our web app won't notice the difference.

(It's important to note, I CANNOT change this setup - it's a case of work with what we're given....)

Now, in Octopus, I need to ONLY deploy to one of the servers in this environment, as if I were to deploy to both, I'd either be duplicating the task (if run as a rolling deployment), or worse, have conflicting deployments (if run asynchronous).

I initially tried added a secondary role to each server ("PrimaryNode", "SecondaryNode"), but I then discovered Octopus treats roles as an "or" rather than an "and", so this wouldn't work for us out of the box

I then looked at writing powershell scripts that checked if the machine that had the roles "dbserver" AND "primarynode" had a status of "Online" and a health of "healthy", then set an output variable based on the status:

##CONFIG##
$APIKey = "API-OBSCURED"
$MainRole = "DBServer" 
$SecondaryRole = "PrimaryNode"

$roles = $OctopusParameters['Octopus.Machine.Roles'] -split ","

$enableFailoverDeployment = $false

foreach($role in $roles)
{
    if ($role -eq "FailoverNode")
    {
        #This is the failovernode - check if the primary node is up and running
        Write-Host "This is the failover database node. Checking if primary node is available before attempting deployment."

        $OctopusURL = "https://myOctourl"  ##$OctopusParameters['Octopus.Web.BaseUrl']
        $EnvironmentID = $OctopusParameters['Octopus.Environment.Id']
        $header = @{ "X-Octopus-ApiKey" = $APIKey }
        $environment = (Invoke-WebRequest -UseBasicParsing "$OctopusURL/api/environments/$EnvironmentID" -Headers $header).content | ConvertFrom-Json
        $machines = ((Invoke-WebRequest -UseBasicParsing ($OctopusURL + $environment.Links.Machines) -Headers $header).content | ConvertFrom-Json).items
        $MachinesInRole = $machines | ?{$MainRole -in $_.Roles}
        $MachinesInRole = $MachinesInRole | ?{$SecondaryRole -in $_.Roles}
        $measure = $MachinesInRole | measure
        $total = $measure.Count

        if ($total -gt 0)
        {
            $currentMachine = $MachinesInRole[0]
            $machineUri = $currentMachine.URI

            if ($currentMachine.Status -eq "Online")
            {
                if ($currentMachine.HealthStatus -eq "Healthy")
                {
                    Write-Host "Primary node is online and healthy."
                    Write-Host "Setting flag to disable failover deployment."
                    $enableFailoverDeployment = $false
                }
                else
                {
                    Write-Host "Primary node has a health status of $($currentMachine.HealthStatus)."
                    Write-Host "Setting flag to enable failover deployment."
                    $enableFailoverDeployment = $true
                }
            }
            else
            {
                Write-Host "Primary node has a status of $($currentMachine.Status)."
                Write-Host "Setting flag to enable failover deployment."
                $enableFailoverDeployment = $true
            }
        }
        break;
    }
}

Set-OctopusVariable -name "EnableFailoverDeployment" -value $enableFailoverDeployment

This seemingly works - I can tell if I should deploy to the primary OR the secondary.

However, I'm now stuck at how I get the deployment process to use this.
Obviously, if the primary node is offline, then the deployment won't happen on it anyway. Yet, if BOTH tentacles are online and healthy, then octopus will just attempt to deploy to them.

The deployment process contains about 12 unique steps, and is successfully used in several other environments (all single-server configurations), but as mentioned, now needs to ALSO deploy to a weird active/warm environment.

Any ideas how I might achieve this? (If only you could specify "AND" in roles..)

UPDATE 1 I've now found that you can update specific machines "IsDisabed" via the web api, so I added code to the end of the above to enable/disable the secondary node depending on the outcome instead of setting an output variable.

Whilst this does indeed update the machine's status, it doesn't actually effect the ongoing deployment process. If I stop and restart the whole process, the machine is correctly picked up as enabled/disabled accordingly, but again, if it's status changes DURING the deployment, Octopus doesn't appear to be "smart" enough to recognise this, ruling this option out. (I did try adding a healthcheck step before and after this script to see if that made a difference, but whilst the healthcheck realised the machine was disabled, it still made no difference to the rest of the steps)

Update 2 I've now also found the "ExcludedMachineIds" property of the "Deployment" in the API, but I get a 405 (not allowed) error when trying to update it once a deployment is in process.
gah.. why can't this be easy?

Answer 1

ok - so the route we took with this was to have a script run against the clustered Always-On SQL instance, which identified the primary and secondary nodes, as follows:

SELECT TOP 1 hags.primary_replica 
FROM  sys.dm_hadr_availability_group_states hags 
INNER JOIN sys.availability_groups ag 
   ON ag.group_id = hags.group_id 
WHERE ag.name = '$alwaysOnClusterInstance';

This allowed me to get the hostname of the primary server. I then took the decision to include the hostname in the actual display name of the machine within OctopusDeploy.

I then do a simple "like" comparison with Powershell between the result from the above SQL and the current machine display name ($OctopusParameters['Octopus.Machine.Name'])

If there's a match, then I set an output variable from this step equal to the internal ID of the OctopusDeploy machine ($OctopusParameters['Octopus.Machine.Id'])

Finally, at the start of each step, I simply compare the current machine id against the above mentioned output variable to determine if I am on the primary node or a secondary node, and act accordingly (usually by exiting the step immediately if it's a secondary node)

The last thing to note is that every single step where I care what machine the step is being run on, has to be run as a "Rolling step", with a windows size of 1.

Luckily, as we are just usually exiting if we're not on the primary node, this doesn't add any real time to our deployment process.