SQL database clustered always on

Legacy Uncategorised
1

Has anyone successfully gotten deployments to work with sql clustered databases set to always on? If so can someone please shed some insight on how to do it? I couldn’t find much resources and I can’t imagine we’re the only ones interested in being able to do this.

Thanks in advance

2

Hi Danny,

First, I need to offer the caveat that my knowledge of SQL Server clustering is rather limited, so I apologize if any of the following information is inapplicable or incorrect :slight_smile:

I don’t believe you are the only ones interested in this.
For some light background reading into some of the issues around deploying to clustered resources, this thread is an interesting read.

Regarding SQL Server specifically, my understanding is there are two scenarios:

  • Any SQL you need to execute against the cluster, should be executed remotely (e.g. run from the Octopus Server or a single Tentacle) against the DNS name of the cluster.

  • For anything you need to execute on every node in the cluster, you will probably need to install a Tentacle agent on each node. These will then appear in Octopus as individual machines. You would then apply a common role to each of these machines (e.g. “SQL Server Cluster”), and execute your step against all machines with that role.

I hope that helps. I believe the reason we don’t provide documentation for this is we’re not sure there is a general solution.

Please feel welcome to ask any follow-up questions, or provide any feedback as to how we could better support your scenario.

Regards,
Michael

3

We had thought about heading towards the 2nd way but the issue is if we deployed to all the nodes since in a clustered database only 1 node is active that is able to be written to it will fail on all the other nodes showing the deployment as failed and we won’t know if the deployment was successful or not. What is this powershell script that you can tell which is the active node?

Are there worked examples on actually getting this to successfully working, even in a 'hack’ish way if there are no plans to support this? Most threads about this are unanswered from support even from the thread you pasted.

4

I may have a way to find the primary active node in a clustered instance by running this query:
SELECT SERVERPROPERTY(‘ComputerNamePhysicalNetBIOS’) as NodeName, Cast(‘Active’ as varchar(10)) as Active

Let’s say in my enviornment I have:
env1
env2 (Active)
env3

How do I setup the process to deploy my changes only to env2 with what comes back from a powershell script?

5

Maybe what I am looking for is just to use the target machine that’s coming from powershell pulled out of the query above and applying it to this step automatically that is probably how to get around the clustered always on deployments that people are asking for

6

Danny,

Perhaps I’m missing something, but it seems like you want to execute your SQL against the primary node.
Wouldn’t just using the DNS record for the cluster work? This should be handled transparently?

7

I figured this out finally. I don’t believe it’s transparent since the listener host is not a real server and you can’t run Redgate’s Release against a listener to deploy a package. You can only deploy to the Active node that the listener is pointed to since the other nodes will be read-only. The workaround is to just figure out which is the active node and point SQL Release to the active node by injecting this code before running SQL Release step in the Step Templates. Hope this helps someone.

$Connection = New-Object System.Data.SQLClient.SQLConnection
$Connection.ConnectionString = "server='$SQLReleaseDatabaseServer';database='$SQLReleaseDatabaseName';trusted_connection=true;"
$Connection.Open()
$Command = New-Object System.Data.SQLClient.SQLCommand
$Command.Connection = $Connection
$Command.CommandText = "Select ServerProperty('ComputerNamePhysicalNetBIOS') "
$Reader = $Command.ExecuteReader()
while ($Reader.Read()) {
     $SQLReleaseDatabaseServer = $Reader.GetValue($1)
}
$Connection.Close()

$databaseConnection = New-DatabaseConnection -ServerInstance $SQLReleaseDatabaseServer -Database $SQLReleaseDatabaseName -Username $SQLReleaseDatabaseUsername -Password $SQLReleaseDatabasePassword | Test-DatabaseConnection