When using multiple basic AGs is it recommended to use 1 listener or mirroring endpoint?

Question

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?

Answer 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