在不同的 Azure 区域中配置 SQL Server Always On 可用性组Configure a SQL Server Always On availability group across different Azure regions

适用于:是Azure VM 上的 SQL ServerAPPLIES TO: yesSQL Server on Azure VM

本文介绍了如何在位于远程 Azure 位置的 Azure 虚拟机上配置 SQL Server Always On 可用性组副本。This article explains how to configure a SQL Server Always On availability group replica on Azure virtual machines in a remote Azure location. 使用此配置可为灾难恢复提供支持。Use this configuration to support disaster recovery.

本文适用于处于 Resource Manager 模式的 Azure 虚拟机。This article applies to Azure Virtual Machines in Resource Manager mode.

下图显示了 Azure 虚拟机上可用性组的常见部署:The following image shows a common deployment of an availability group on Azure virtual machines:

可用性组

在此部署中,所有虚拟机位于一个 Azure 区域。In this deployment, all virtual machines are in one Azure region. 在 SQL-1 和 SQL-2 上,可以使用自动故障转移为可用性组副本配置同步提交。The availability group replicas can have synchronous commit with automatic failover on SQL-1 and SQL-2. 若要构建此体系结构,请参阅可用性组模板或教程To build this architecture, see Availability Group template or tutorial.

如果无法访问 Azure 区域,此体系结构很容易停机。This architecture is vulnerable to downtime if the Azure region becomes inaccessible. 为了解决此漏洞,可在不同的 Azure 区域添加一个副本。To overcome this vulnerability, add a replica in a different Azure region. 下图显示了新体系结构的大致形式:The following diagram shows how the new architecture would look:

可用性组 DR

上图显示了名为 SQL-3 的新虚拟机。The preceding diagram shows a new virtual machine called SQL-3. SQL-3 位于不同的 Azure 区域中。SQL-3 is in a different Azure region. SQL-3 已添加到 Windows Server 故障转移群集。SQL-3 is added to the Windows Server Failover Cluster. SQL-3 可以托管可用性组副本。SQL-3 can host an availability group replica. 最后,请注意,SQL-3 所在的 Azure 区域具有一个新的 Azure 负载均衡器。Finally, notice that the Azure region for SQL-3 has a new Azure load balancer.

备注

如果同一区域中有多个虚拟机,则需要创建 Azure 可用性集。An Azure availability set is required when more than one virtual machine is in the same region. 如果区域中只有一个虚拟机,则不需要可用性集。If only one virtual machine is in the region, then the availability set is not required. 只能在创建虚拟机时将虚拟机放入可用性集。You can only place a virtual machine in an availability set at creation time. 如果虚拟机已在可用性集中,以后可为附加的副本添加虚拟机。If the virtual machine is already in an availability set, you can add a virtual machine for an additional replica later.

在此体系结构中,远程区域中的副本通常配置了异步提交可用性模式和手动故障转移模式。In this architecture, the replica in the remote region is normally configured with asynchronous commit availability mode and manual failover mode.

如果可用性组副本位于不同 Azure 区域中的 Azure 虚拟机上,则每个区域需要:When availability group replicas are on Azure virtual machines in different Azure regions, each region requires:

  • 一个虚拟网络网关A virtual network gateway
  • 一个虚拟网络网关连接A virtual network gateway connection

下图显示了数据中心之间的网络通信方式。The following diagram shows how the networks communicate between data centers.

可用性组

重要

在 Azure 区域之间复制数据时,使用此体系结构需支付出站数据费用。This architecture incurs outbound data charges for data replicated between Azure regions. 请参阅带宽定价See Bandwidth Pricing.

创建远程副本Create remote replica

若要在远程数据中心创建副本,请执行以下步骤:To create a replica in a remote data center, do the following steps:

  1. 在新区域中创建虚拟网络Create a virtual network in the new region.

  2. 使用 Azure 门户配置 VNet 到 VNet 连接Configure a VNet-to-VNet connection using the Azure portal.

    备注

    在某些情况下,可能需要使用 PowerShell 创建 VNet 到 VNet 连接。In some cases, you may have to use PowerShell to create the VNet-to-VNet connection. 例如,如果使用不同的 Azure 帐户,则无法在门户中配置该连接。For example, if you use different Azure accounts you cannot configure the connection in the portal. 这种情况请参阅使用 Azure 门户配置 VNet 到 VNet 连接In this case see, Configure a VNet-to-VNet connection using the Azure portal.

  3. 在新区域中创建域控制器Create a domain controller in the new region.

    如果主站点中的域控制器不可用,此域控制器可提供身份验证。This domain controller provides authentication if the domain controller in the primary site is not available.

  4. 在新区域中创建 SQL Server 虚拟机Create a SQL Server virtual machine in the new region.

  5. 在新区域的网络中创建 Azure 负载均衡器Create an Azure load balancer in the network on the new region.

    此负载均衡器必须:This load balancer must:

    • 与新虚拟机位于同一网络和子网中。Be in the same network and subnet as the new virtual machine.
    • 可用性组侦听器具有静态 IP 地址。Have a static IP address for the availability group listener.
    • 包括后端池,该池只由负载均衡器所在区域中的虚拟机构成。Include a backend pool consisting of only the virtual machines in the same region as the load balancer.
    • 使用特定于 IP 地址的 TCP 端口探测。Use a TCP port probe specific to the IP address.
    • 具有特定于同一区域中 SQL Server 的负载均衡规则。Have a load balancing rule specific to the SQL Server in the same region.
    • 如果后端池中的虚拟机不是单个可用性集或虚拟机规模集的一部分,则为标准负载均衡器。Be a Standard Load Balancer if the virtual machines in the backend pool are not part of either a single availability set or virtual machine scale set. 有关其他信息,请查看 Azure 负载均衡器标准概述For additional information review Azure Load Balancer Standard overview.
  6. 向新的 SQL Server 添加故障转移群集功能Add Failover Clustering feature to the new SQL Server.

  7. 将新的 SQL Server 添加到域Join the new SQL Server to the domain.

  8. 将新的 SQL Server 服务帐户设置为使用域帐户Set the new SQL Server service account to use a domain account.

  9. 将新的 SQL Server 添加到 Windows Server 故障转移群集Add the new SQL Server to the Windows Server Failover Cluster.

  10. 将 IP 地址资源添加到群集。Add an IP address resource to the cluster.

    可在故障转移群集管理器中创建 IP 地址资源。You can create the IP address resource in Failover Cluster Manager. 选择群集的名称,然后在“群集核心资源”下右键单击群集名称,并选择“属性”:Select the name of the cluster, then right-click the cluster name under Cluster Core Resources and select Properties:

    群集属性

    在“属性”对话框的“IP 地址”下选择“添加”,然后从远程网络区域添加群集名称的 IP 地址。 On the Properties dialog box, select Add under IP Address, and then add the IP address of the cluster name from the remote network region. 选择“IP 地址”对话框上的“确定”,然后在“群集属性”对话框中再次选择“确定”,以保存新的 IP 地址 。Select OK on the IP Address dialog box, and then select OK again on the Cluster Properties dialog box to save the new IP address.

    添加群集 IP

  11. 为核心群集名称添加 IP 地址作为依赖项。Add the IP address as a dependency for the core cluster name.

    再次打开群集属性,选择“依赖关系”选项卡。为两个 IP 地址配置 OR 依赖关系:Open the cluster properties once more and select the Dependencies tab. Configure an OR dependency for the two IP addresses:

    群集属性

  12. 将 IP 地址资源添加到群集中的可用性组角色。Add an IP address resource to the availability group role in the cluster.

    在故障转移群集管理器中右键单击可用性组角色,选择“添加资源”,“更多资源”,然后选择“IP 地址” 。Right-click the availability group role in Failover Cluster Manager, choose Add Resource, More Resources, and select IP Address.

    创建 IP 地址

    按如下所示配置此 IP 地址:Configure this IP address as follows:

    • 使用远程数据中心内的网络。Use the network from the remote data center.
    • 从新的 Azure 负载均衡器分配 IP 地址。Assign the IP address from the new Azure load balancer.
  13. 将 IP 地址资源设成侦听器客户端访问点(网络名称)群集的依赖项。Add the IP address resource as a dependency for the listener client access point (network name) cluster.

    以下屏幕截图显示了正确配置的 IP 地址群集资源:The following screenshot shows a properly configured IP address cluster resource:

    可用性组

    重要

    该群集资源组包含这两个 IP 地址。The cluster resource group includes both IP addresses. 这两个 IP 地址是侦听器客户端接入点的依赖项。Both IP addresses are dependencies for the listener client access point. 在群集依赖项配置中使用 OR 运算符。Use the OR operator in the cluster dependency configuration.

  14. 在 PowerShell 中设置群集参数Set the cluster parameters in PowerShell.

    使用在新区域中的负载均衡器上配置的群集网络名称、IP 地址和探测端口运行 PowerShell 脚本。Run the PowerShell script with the cluster network name, IP address, and probe port that you configured on the load balancer in the new region.

    $ClusterNetworkName = "<MyClusterNetworkName>" # The cluster name for the network in the new region (Use Get-ClusterNetwork on Windows Server 2012 of higher to find the name).
    $IPResourceName = "<IPResourceName>" # The cluster name for the new IP Address resource.
    $ILBIP = "<n.n.n.n>" # The IP Address of the Internal Load Balancer (ILB) in the new region. This is the static IP address for the load balancer you configured in the Azure portal.
    [int]$ProbePort = <nnnnn> # The probe port you set on the ILB.
    
    Import-Module FailoverClusters
    
    Get-ClusterResource $IPResourceName | Set-ClusterParameter -Multiple @{"Address"="$ILBIP";"ProbePort"=$ProbePort;"SubnetMask"="255.255.255.255";"Network"="$ClusterNetworkName";"EnableDhcp"=0}
    
  15. 在 SQL Server 配置管理器中的新 SQL Server 上,启用 Always On 可用性组On the new SQL Server in SQL Server Configuration Manager, enable Always On Availability Groups.

  16. 在新的 SQL Server 上打开防火墙端口Open firewall ports on the new SQL Server.

    需要打开的端口号取决于环境。The port numbers you need to open depend on your environment. 打开镜像终结点和 Azure 负载均衡器运行状况探测的端口。Open ports for the mirroring endpoint and Azure load balancer health probe.

  17. 将副本添加到新 SQL Server 上的可用性组Add a replica to the availability group on the new SQL Server.

    对于远程 Azure 区域中的副本,请将它设置为使用手动故障转移进行异步复制。For a replica in a remote Azure region, set it for asynchronous replication with manual failover.

设置多个子网的连接Set connection for multiple subnets

远程数据中心内的副本是可用性组的一部分,但位于不同的子网。The replica in the remote data center is part of the availability group but it is in a different subnet. 如果此副本成为主副本,可能会发生应用程序连接超时。If this replica becomes the primary replica, application connection time-outs may occur. 多子网部署中的本地可用性组也存在相同的行为。This behavior is the same as an on-premises availability group in a multi-subnet deployment. 若要允许从客户端应用程序建立连接,请更新客户端连接,或者在群集网络名称资源上配置名称解析缓存。To allow connections from client applications, either update the client connection or configure name resolution caching on the cluster network name resource.

最好是将客户端连接字符串更新为设置 MultiSubnetFailover=YesPreferably, update the client connection strings to set MultiSubnetFailover=Yes. 请参阅使用 MultiSubnetFailover 进行连接See Connecting With MultiSubnetFailover.

如果无法修改连接字符串,可以配置名称解析缓存。If you cannot modify the connection strings, you can configure name resolution caching. 请参阅出现超时错误并且在多子网环境中无法连接到 SQL Server 2012 AlwaysOn 可用性组侦听程序See Time-out error and you cannot connect to a SQL Server 2012 AlwaysOn availability group listener in a multi-subnet environment.

故障转移到远程区域Fail over to remote region

要测试侦听器与远程区域之间的连接,可将副本故障转移到远程区域。To test listener connectivity to the remote region, you can fail over the replica to the remote region. 尽管副本是异步的,但故障转移存在丢失数据的可能性。While the replica is asynchronous, failover is vulnerable to potential data loss. 要故障转移并防止丢失数据,请将可用性模式更改为同步,将故障转移模式设置为自动。To fail over without data loss, change the availability mode to synchronous and set the failover mode to automatic. 使用以下步骤:Use the following steps:

  1. 在“对象资源管理器”中连接到承载主副本的 SQL Server 实例。In Object Explorer, connect to the instance of SQL Server that hosts the primary replica.
  2. 在“AlwaysOn 可用性组”的“可用性组”下,右键单击可用性组,然后选择“属性” 。Under AlwaysOn Availability Groups, Availability Groups, right-click your availability group and select Properties.
  3. 在“常规”页上的“可用性副本”下,将灾难恢复站点中的辅助副本设置为使用“同步提交”可用性模式和“自动”故障转移模式。On the General page, under Availability Replicas, set the secondary replica in the DR site to use Synchronous Commit availability mode and Automatic failover mode.
  4. 如果辅助副本和主副本位于同一站点,且辅助副本具有高可用性,则将辅助副本设置为“异步提交”和“手动”。If you have a secondary replica in same site as your primary replica for high availability, set this replica to Asynchronous Commit and Manual.
  5. 选择“确定”。Select OK.
  6. 在“对象资源管理器”中,右键单击可用性组中,然后选择“显示仪表板” 。In Object Explorer, right-click the availability group, and select Show Dashboard.
  7. 在仪表板中,验证 DR 站点中的副本是否已同步。On the dashboard, verify that the replica on the DR site is synchronized.
  8. 在“对象资源管理器”中,右键单击可用性组中,然后选择“故障转移...” 。SQL Server Management Studio 将打开向导对 SQL Server 进行故障转移。In Object Explorer, right-click the availability group, and select Failover.... SQL Server Management Studios opens a wizard to fail over SQL Server.
  9. 选择“下一步”,然后选择灾难恢复站点中的 SQL Server 实例。Select Next, and select the SQL Server instance in the DR site. 再次选择“下一步”。Select Next again.
  10. 连接到灾难恢复站点中的 SQL Server 实例,然后选择“下一步”。Connect to the SQL Server instance in the DR site and select Next.
  11. 在“摘要”页上查看设置,然后选择“完成” 。On the Summary page, verify the settings and select Finish.

测试连接后,将主副本移回主数据中心,将可用性模式设置回其正常运行的设置。After testing connectivity, move the primary replica back to your primary data center and set the availability mode back to their normal operating settings. 下表显示了本文档所述体系结构的正常运行的设置:The following table shows the normal operational settings for the architecture described in this document:

位置Location 服务器实例Server Instance 角色Role 可用性模式Availability Mode 故障转移模式Failover Mode
主数据中心Primary data center SQL-1SQL-1 主要Primary 同步Synchronous 自动Automatic
主数据中心Primary data center SQL-2SQL-2 次要Secondary 同步Synchronous 自动Automatic
辅助或远程数据中心Secondary or remote data center SQL-3SQL-3 辅助Secondary 异步Asynchronous 手动Manual

有关计划内和强制手动故障转移的详细信息More information about planned and forced manual failover

有关详细信息,请参阅以下主题:For more information, see the following topics:

后续步骤Next steps