主页 / dba / 问题 / 331620
Accepted
variable
Asked: 2023-09-27 14:15:49 +0800 CST

当使用多个基本AG时，是否建议使用1个监听器或镜像端点？

  • 772

我想通过在另一台计算机上添加新实例并配置多个基本 AG（使用手动故障转移模式）来为单实例 SQL Server 设置 HA DR 。

我希望所有客户端应用程序都能够使用相同的连接字符串来访问服务器。为此，我是否使用侦听器（仅为 1 AG 创建一个侦听器，并在所有连接字符串中使用它）还是使用数据库镜像端点？

sql-server

1 个回答

  1. Best Answer

    对于基本可用性组，我只需使用 CNAME 和 powershell 脚本即可对所有 AG 进行故障转移。

    例如

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

    注意：某些项目是硬编码在脚本中的。 域名和 CNAME

    • 1

相关问题