Início / dba / Perguntas / 331620
Accepted
variable
Asked: 2023-09-27 14:15:49 +0800 CST

Ao usar vários AGs básicos, é recomendado usar 1 ouvinte ou endpoint de espelhamento?

  • 772

Desejo configurar o HA DR para um servidor SQL de instância única adicionando uma nova instância em outra máquina e configurando vários AGs básicos ( com modo de failover manual ).

Quero que todos os aplicativos clientes possam usar a mesma cadeia de conexão para acessar o servidor. Para isso eu uso o listener (crio um listener para apenas 1 AG, ​​e uso isso em toda string de conexão) ou uso o endpoint de espelhamento de banco de dados?

sql-server

1 respostas

  1. Best Answer

    Para grupos de disponibilidade básicos, apenas uso um CNAME e um script PowerShell que fará failover de todos os AGs.

    por exemplo

    <#
    .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
}

    Nota: Alguns itens são codificados no script. Nome de domínio e CNAME

    • 1

relate perguntas