0

I want to setup HA DR for a single instance SQL server by adding a new instance on another machine and configuring multiple basic AGs (with manual failover mode).

I want all client apps to be able to use the same connection string to reach the server. For this do I use the listener (create a listener for only 1 AG, and use this in all connection string) or do I use the database mirroring endpoint?

1 Answer 1

1

For basic availability groups I just go with a CNAME and a powershell script that will failover all the AGs.

e.g.

<#
    .SYNOPSIS
        Perform availability group failover

    .DESCRIPTION
        Perfom the following actions to failover availability groups:
            * Switch to synchronous commit
            * Failover
            * Update CNAME

    .PARAMETER FailoverFrom
        The name of the server we are failing over from

    .PARAMETER FailoverTo
        The name of the server we are failing over to

    .EXAMPLE
        ./FailoverScript -FailoverFrom "SERVER1" -FailoverTo "SERVER2"
#>
param(
    [Parameter(Mandatory=$true)]
    [string]$FailoverFrom,
    [Parameter(Mandatory=$true)]
    [string]$FailoverTo
)
$cname = "YOUR_ALIAS"
$newAlias = $FailoverTo + ".yourdomain.local."

# Check we are running as administrator (required for service start/stop)
$currentPrincipal = New-Object Security.Principal.WindowsPrincipal([Security.Principal.WindowsIdentity]::GetCurrent())
if (!$currentPrincipal.IsInRole([Security.Principal.WindowsBuiltInRole]::Administrator))
{
    throw "Please run this script as administrator"
}

# Check we have primary replicas where we are failing over from
if ( @(Get-DbaAgDatabase $FailoverFrom | Where-Object {$_.LocalReplicaRole -eq "Primary"}).Count -eq 0){
    throw "No primary replicas to failover"
}
# Check we have specified a different instance to failover to
if ($FailoverTo -eq $FailoverFrom){
    throw "Can't failover to same instance"
}
 
# Set synchronous commit
Get-DbaAgReplica -SqlInstance $FailoverFrom | Where-Object {$_.AvailabilityMode -ne "SynchronousCommit"} | Set-DbaAgReplica -AvailabilityMode SynchronousCommit

# Give time to synchronize
Start-Sleep -s 20

# Check that DBs are in a synchronized state ready to failover
if (@(Get-DbaAgDatabase $FailoverTo | Where-Object {$_.SynchronizationState -ne "Synchronized"}).Count -gt 0){
    throw "Databases are not synchronized.  Wait for databases to synchronize and run the script again to initiate failover."
}
 
# Failover
Get-DbaAvailabilityGroup -SqlInstance $FailoverTo | Invoke-DBaAgFailover -Confirm:$false

# Set secondary to be async commit
Get-DbaAgReplica -SqlInstance $FailoverTo | Where-Object {$_.Role -eq "Secondary" -and $_.AvailabilityMode -ne "AsynchronousCommit"} | Set-DbaAgReplica -AvailabilityMode AsynchronousCommit

# Switch dns CNAME over to new primary
$cnameRecord = (Get-DnsServerResourceRecord -ComputerName "yourdomain.local" -Name $cname -ZoneName "yourdomain.local")
if ($cnameRecord.RecordData.HostNameAlias -eq $newAlias){
    "CNAME is up to date"
}
else{
    "Updating CNAME"
    Add-DnsServerResourceRecordCName -ComputerName "yourdomain.local" -Name $cname -HostNameAlias $newAlias -ZoneName "yourdomain.local" -TimeToLive 60
}

Note: Some items are hard coded in the script. Domain name, & CNAME

4
  • The CNAME is instead of using a listener. It's a DNS alias that you create (outside of SQL in DNS). Each computer in your AD will have a host A DNS record. The CNAME can point to a Host A of your primary instance and it can be swapped to another instance in the case of failover. Your applications point to the CNAME. en.wikipedia.org/wiki/CNAME_record Commented Sep 27, 2023 at 9:03
  • 2
    CNAMEs will work without issue, however they will disable some functionality on higher editions of SQL Server, for example read only routing. Commented Sep 27, 2023 at 10:10
  • @SeanGallardy - Yeah, with Enterprise edition I would opt to use the Listener. 👍 For basic AGs if the databases are supposed to failover as a unit rather than independently then you don't want to be creating a listener per DB. I guess you could create a listener for just one of the DBs and use that - but my preference is just a CNAME at this point. The other issue is getting the DBs to failover as a single unit. You don't want to be using the GUI for this - a powershell script like the one I provided will help. Whatever process you have, test it. Commented Sep 27, 2023 at 10:37
  • 1
    The DNS record gets cached so it can take some time for clients to pick up the change automatically. You can adjust the TTL on the DNS record as required so it's more responsive. Definitely worth testing the process so you know what to expect when failing over. Commented Sep 27, 2023 at 13:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.