您现在访问的是微软AZURE全球版技术文档网站,若需要访问由世纪互联运营的MICROSOFT AZURE中国区技术文档网站,请访问 https://docs.azure.cn.

为 SQL Server 设置灾难恢复Set up disaster recovery for SQL Server

本文介绍如何结合使用 SQL Server 业务连续性和灾难恢复 (BCDR) 技术与 Azure Site Recovery 来保护应用程序的 SQL Server 后端。This article describes how to protect the SQL Server back end of an application using a combination of SQL Server business continuity and disaster recovery (BCDR) technologies, and Azure Site Recovery.

在开始之前,请确保了解 SQL Server 灾难恢复功能,包括故障转移群集、Always On 可用性组、数据库镜像和日志传送。Before you start, make sure you understand SQL Server disaster recovery capabilities, including failover clustering, Always On availability groups, database mirroring, and log shipping.

SQL Server 部署SQL Server deployments

许多工作负载使用 SQL Server 作为基础。可将 SQL Server 与 SharePoint、Dynamics 和 SAP 等应用集成来实现数据服务。Many workloads use SQL Server as a foundation, and it can be integrated with apps such as SharePoint, Dynamics, and SAP, to implement data services. 可通过多种方式部署 SQL Server:SQL Server can be deployed in a number of ways:

  • 独立 SQL Server:SQL Server 和所有数据库都托管在一台计算机(物理或虚拟)上。Standalone SQL Server: SQL Server and all databases are hosted on a single machine (physical or a virtual). 当虚拟化时,主机群集用于本地高可用性。When virtualized, host clustering is used for local high availability. 不会实现来宾级别的高可用性。Guest-level high availability isn't implemented.

  • SQL Server 故障转移群集实例 (AlwaysOn FCI) :在一个 Windows 故障转移群集中配置两个或更多个运行带共享磁盘的 SQL Server 实例的节点。SQL Server Failover Clustering Instances (Always On FCI): Two or more nodes running SQL Server instanced with shared disks are configured in a Windows Failover cluster. 如果某个节点关闭,群集可将 SQL Server 故障转移到其他实例。If a node is down, the cluster can fail SQL Server over to another instance. 此设置通常用于在主站点上实现高可用性。This setup is typically used to implement high availability at a primary site. 此部署不能防止共享存储层中出现故障或中断。This deployment doesn't protect against failure or outage in the shared storage layer. 共享磁盘可以使用 iSCSI、光纤通道或共享 vhdx 来实现。A shared disk can be implemented using iSCSI, fiber channel or shared vhdx.

  • SQL AlwaysOn 可用性组:使用同步复制与自动故障转移在可用性组中配置 SQL Server 数据库时,在不共享任何内容的群集中设置两个或更多个节点。SQL Always On Availability Groups: Two or more nodes are set up in a shared nothing cluster, with SQL Server databases configured in an availability group, with synchronous replication and automatic failover.

    本文利用以下本机 SQL 灾难恢复技术将数据库恢复到远程站点:This article leverages the following native SQL disaster recovery technologies for recovering databases to a remote site:

  • SQL Always On 可用性组,针对 SQL Server 2012 或 2014 Enterprise Edition 提供灾难恢复。SQL Always On Availability Groups, to provide for disaster recovery for SQL Server 2012 or 2014 Enterprise editions.

  • SQL Server Standard Edition(任何版本)或 SQL Server 2008 R2 高安全性模式下的 SQL 数据库镜像。SQL database mirroring in high safety mode, for SQL Server Standard edition (any version), or for SQL Server 2008 R2.

Site Recovery 支持Site Recovery support

支持的方案Supported scenarios

Site Recovery 可以保护下表中汇总的 SQL Server。Site Recovery can protect SQL Server as summarized in the table.

方案Scenario 到辅助站点To a secondary site 到 AzureTo Azure
Hyper-VHyper-V Yes Yes
VMwareVMware Yes Yes
物理服务器Physical server Yes Yes
AzureAzure NANA Yes

支持的 SQL Server 版本Supported SQL Server versions

支持的方案支持以下 SQL Server 版本:These SQL Server versions are supported, for the supported scenarios:

  • SQL Server 2016 Enterprise 和 StandardSQL Server 2016 Enterprise and Standard
  • SQL Server 2014 Enterprise 和 StandardSQL Server 2014 Enterprise and Standard
  • SQL Server 2012 Enterprise 和 StandardSQL Server 2012 Enterprise and Standard
  • SQL Server 2008 R2 Enterprise 和 StandardSQL Server 2008 R2 Enterprise and Standard

支持的 SQL Server 集成Supported SQL Server integration

Site Recovery 可与表中汇总的本机 SQL Server BCDR 技术集成,以提供灾难恢复解决方案。Site Recovery can be integrated with native SQL Server BCDR technologies summarized in the table, to provide a disaster recovery solution.

功能Feature 详细信息Details SQL ServerSQL Server
Always On 可用性组Always On availability group SQL Server 的多个独立实例,每个实例在包含多个节点的故障转移群集中运行。Multiple standalone instances of SQL Server each run in a failover cluster that has multiple nodes.

数据库可以分组到可在 SQL Server 实例上复制(镜像)的故障转移组,因此不需要任何共享存储。Databases can be grouped into failover groups that can be copied (mirrored) on SQL Server instances so that no shared storage is needed.

在主站点与一个或多个辅助站点之间提供灾难恢复。Provides disaster recovery between a primary site and one or more secondary sites. 使用同步复制与自动故障转移在可用性组中配置 SQL Server 数据库时,可以在不共享任何内容的群集中设置两个节点。Two nodes can be set up in a shared nothing cluster with SQL Server databases configured in an availability group with synchronous replication and automatic failover.
SQL Server 2016、SQL Server 2014 和 SQL Server 2012 Enterprise EditionSQL Server 2016, SQL Server 2014 & SQL Server 2012 Enterprise edition
故障转移群集 (Always On FCI)Failover clustering (Always On FCI) SQL Server 利用 Windows 故障转移群集实现本地 SQL Server 工作负载的高可用性。SQL Server leverages Windows failover clustering for high availability of on-premises SQL Server workloads.

使用共享磁盘运行 SQL Server 实例的节点是在故障转移群集中配置的。Nodes running instances of SQL Server with shared disks are configured in a failover cluster. 如果实例关闭,群集将故障转移到另一个节点。If an instance is down the cluster fails over to different one.

群集无法防止共享存储的故障或中断。The cluster doesn't protect against failure or outages in shared storage. 共享磁盘可以使用 iSCSI、光纤通道或共享 VHDX 来实现。The shared disk can be implemented with iSCSI, fiber channel, or shared VHDXs.
SQL Server Enterprise 版本SQL Server Enterprise editions

SQL Server Standard 版本(仅限两个节点)SQL Server Standard edition (limited to two nodes only)
数据库镜像(高安全性模式)Database mirroring (high safety mode) 在单个辅助副本中保护单个数据库。Protects a single database to a single secondary copy. 提供高安全性(同步)和高性能(异步)复制模式。Available in both high safety (synchronous) and high performance (asynchronous) replication modes. 不需要故障转移群集。Doesn’t require a failover cluster. SQL Server 2008 R2SQL Server 2008 R2

SQL Server Enterprise 的所有版本SQL Server Enterprise all editions
独立 SQL ServerStandalone SQL Server SQL Server 和数据库托管在单个服务器(物理或虚拟)上。The SQL Server and database are hosted on a single server (physical or virtual). 如果是虚拟服务器,则主机群集用于高可用性。Host clustering is used for high availability if the server is virtual. 没有来宾级别的高可用性。No guest-level high availability. Enterprise 或 Standard 版本Enterprise or Standard edition

部署建议Deployment recommendations

下表汇总了有关将 SQL Server BCDR 技术与 Site Recovery 集成的建议。This table summarizes our recommendations for integrating SQL Server BCDR technologies with Site Recovery.

版本Version 版本Edition 部署Deployment 本地到本地On-prem to on premises 本地到 AzureOn-prem to Azure
SQL Server 2016、2014 或 2012SQL Server 2016, 2014 or 2012 EnterpriseEnterprise 故障转移群集实例Failover cluster instance Always On 可用性组Always On availability groups Always On 可用性组Always On availability groups
EnterpriseEnterprise 用于实现高可用性的 Always On 可用性组Always On availability groups for high availability Always On 可用性组Always On availability groups Always On 可用性组Always On availability groups
标准Standard 故障转移群集实例 (FCI)Failover cluster instance (FCI) 使用本地镜像进行 Site Recovery 复制Site Recovery replication with local mirror 使用本地镜像进行 Site Recovery 复制Site Recovery replication with local mirror
Enterprise 或 StandardEnterprise or Standard 独立Standalone 站点恢复复制Site Recovery replication 站点恢复复制Site Recovery replication
SQL Server 2008 R2 或 2008SQL Server 2008 R2 or 2008 Enterprise 或 StandardEnterprise or Standard 故障转移群集实例 (FCI)Failover cluster instance (FCI) 使用本地镜像进行 Site Recovery 复制Site Recovery replication with local mirror 使用本地镜像进行 Site Recovery 复制Site Recovery replication with local mirror
Enterprise 或 StandardEnterprise or Standard 独立Standalone 站点恢复复制Site Recovery replication 站点恢复复制Site Recovery replication
SQL Server(任何版本)SQL Server (Any version) Enterprise 或 StandardEnterprise or Standard 故障转移群集实例 - DTC 应用程序Failover cluster instance - DTC application 站点恢复复制Site Recovery replication 不支持Not Supported

部署先决条件Deployment prerequisites

  • 运行受支持 SQL Server 版本的本地 SQL Server 部署。An on-premises SQL Server deployment, running a supported SQL Server version. 通常还需要为 SQL Server 安装 Active Directory。Typically, you also need Active Directory for your SQL server.
  • 要部署的方案所要满足的要求。The requirements for the scenario you want to deploy. 详细了解有关复制到 Azure本地的支持要求以及部署先决条件Learn more about support requirements for replication to Azure and on-premises, and deployment prerequisites.

设置 Active DirectorySet up Active Directory

在辅助恢复站点上安装 Active Directory,使 SQL Server 能够正常运行。Set up Active Directory, in the secondary recovery site, for SQL Server to run properly.

  • 小型企业 - 如果使用少量的应用程序和适用于本地站点的单个域控制器,并且想要故障转移整个站点,我们建议使用 Site Recovery 复制将域控制器复制到辅助数据中心或 Azure。Small enterprise—With a small number of applications, and single domain controller for the on-premises site, if you want to fail over the entire site, we recommend you use Site Recovery replication to replicate the domain controller to the secondary datacenter, or to Azure.
  • 中大型企业 - 如果使用大量的应用程序和 Active Directory 林,并且想要按应用程序或工作负荷进行故障转移,我们建议在辅助数据中心或 Azure 中设置附加的域控制器。Medium to large enterprise—If you have a large number of applications, an Active Directory forest, and you want to fail over by application or workload, we recommend you set up an additional domain controller in the secondary datacenter, or in Azure. 如果使用 Always On 可用性组恢复到远程站点,我们建议在辅助站点或 Azure 上配置另一个域控制器,供已恢复 SQL Server 实例使用。If you're using Always On availability groups to recover to a remote site, we recommend you set up another additional domain controller on the secondary site or in Azure, to use for the recovered SQL Server instance.

本文中的说明假设辅助位置提供了域控制器。The instructions in this article presume that a domain controller is available in the secondary location. 详细了解如何使用 Site Recovery 保护 Active Directory。Read more about protecting Active Directory with Site Recovery.

与 SQL Server Always On 集成以便复制到 AzureIntegrate with SQL Server Always On for replication to Azure

下面是需要执行的操作:Here's what you need to do:

  1. 将脚本导入到 Azure 自动化帐户中。Import scripts into your Azure Automation account. 这包括用于在 Resource Manager 虚拟机经典虚拟机中对 SQL 可用性组进行故障转移的脚本。This contains the scripts to failover SQL Availability Group in a Resource Manager virtual machine and a Classic virtual machine.

    部署到 AzureDeploy to Azure

  2. 将 ASR-SQL-FailoverAG 添加为恢复计划的第一个组的准备操作。Add ASR-SQL-FailoverAG as a pre action of the first group of the recovery plan.

  3. 按照脚本中提供的说明创建一个自动化变量来提供可用性组的名称。Follow the instructions available in the script to create an automation variable to provide the name of the availability groups.

用于执行测试故障转移的步骤Steps to do a test failover

SQL Always On 无法原生支持测试性故障转移。SQL Always On doesn’t natively support test failover. 因此,我们建议:Therefore, we recommend the following:

  1. 在虚拟机上设置 Azure 备份,该虚拟机在 Azure 中托管可用性组副本。Set up Azure Backup on the virtual machine that hosts the availability group replica in Azure.

  2. 触发对恢复计划进行测试性故障转移之前,请从上一步骤中进行的备份恢复虚拟机。Before triggering test failover of the recovery plan, recover the virtual machine from the backup taken in the previous step.

    从 Azure 备份进行还原

  3. 在从备份还原的虚拟机中强制实施仲裁Force a quorum in the virtual machine restored from backup.

  4. 将侦听程序的 IP 更新为测试故障转移网络中可用的某个 IP。Update IP of the listener to an IP available in the test failover network.

    更新侦听器 IP

  5. 使侦听器联机。Bring listener online.

    使侦听器联机

  6. 使用在前端 IP 池下创建的与每个可用性组侦听器对应的一个 IP 与在后端池中添加的 SQL 虚拟机创建一个负载均衡器。Create a load balancer with one IP created under frontend IP pool corresponding to each availability group listener and with the SQL virtual machine added in the backend pool.

    创建负载均衡器 - 前端 IP 池

    创建负载均衡器 - 后端池

  7. 对恢复计划进行测试性故障转移。Do a test failover of the recovery plan.

用于执行故障转移的步骤Steps to do a failover

在恢复计划中添加脚本并通过执行测试故障转移对恢复计划进行验证后,可以执行恢复计划的故障转移。Once you have added the script in the recovery plan and validated the recovery plan by doing a test failover, you can do failover of the recovery plan.

与 SQL Server Always On 集成以便复制到辅助本地站点Integrate with SQL Server Always On for replication to a secondary on-premises site

如果 SQL Server 使用可用性组(或 FCI)实现高可用性,我们建议也在恢复站点上使用可用性组。If the SQL Server is using availability groups for high availability (or an FCI), we recommend using availability groups on the recovery site as well. 请注意,这适用于不使用分布式事务的应用。Note that this applies to apps that don't use distributed transactions.

  1. 配置数据库Configure databases into availability groups.
  2. 在辅助站点上创建虚拟网络。Create a virtual network on the secondary site.
  3. 在该虚拟网络与主站点之间配置站点到站点 VPN 连接。Set up a site-to-site VPN connection between the virtual network, and the primary site.
  4. 在恢复站点上创建虚拟机,并在其上安装 SQL Server。Create a virtual machine on the recovery site, and install SQL Server on it.
  5. 将现有的 Always On 可用性组扩展到新的 SQL Server VM。Extend the existing Always On availability groups to the new SQL Server VM. 将此 SQL Server 实例配置为异步副本。Configure this SQL Server instance as an asynchronous replica copy.
  6. 创建可用性组侦听器,或更新现有的侦听器,以包含异步副本虚拟机。Create an availability group listener, or update the existing listener to include the asynchronous replica virtual machine.
  7. 确保应用程序场是使用侦听器设置的。Make sure that the application farm is set up using the listener. 如果它是使用数据库服务器名称设置的,请将其更新为使用侦听器,以便不需要在故障转移后重新配置该场。If it's setup up using the database server name, update it to use the listener, so you don't need to reconfigure it after the failover.

对于使用分布式事务的应用程序,我们建议使用 VMware/物理服务器站点到站点复制部署 Site Recovery。For applications that use distributed transactions, we recommend you deploy Site Recovery with VMware/physical server site-to-site replication.

恢复计划注意事项Recovery plan considerations

  1. 将此示例脚本添加到主站点和辅助站点上的 VMM 库。Add this sample script to the VMM library, on the primary and secondary sites.

     Param(
     [string]$SQLAvailabilityGroupPath
     )
     import-module sqlps
     Switch-SqlAvailabilityGroup -Path $SQLAvailabilityGroupPath -AllowDataLoss -force
    
  2. 为应用程序创建恢复计划时,请向 Group-1 脚本化步骤中添加一个准备操作,用以调用脚本来对可用性组进行故障转移。When you create a recovery plan for the application, add a pre action to Group-1 scripted step, that invokes the script to fail over availability groups.

保护独立 SQL ServerProtect a standalone SQL Server

在此方案中,建议使用 Site Recovery 复制保护 SQL Server 计算机。In this scenario, we recommend that you use Site Recovery replication to protect the SQL Server machine. 确切步骤取决于 SQL Server 是 VM 还是物理服务器,以及是要复制到 Azure 还是辅助本地站点。The exact steps will depend whether SQL Server is a VM or a physical server, and whether you want to replicate to Azure or a secondary on-premises site. 了解 Site Recovery 方案Learn about Site Recovery scenarios.

保护 SQL Server 群集(标准版/SQL Server 2008 R2)Protect a SQL Server cluster (standard edition/SQL Server 2008 R2)

对于运行 SQL Server Standard 版本或 SQL Server 2008 R2 的群集,建议使用 Site Recovery 复制来保护 SQL Server。For a cluster running SQL Server Standard edition, or SQL Server 2008 R2, we recommend you use Site Recovery replication to protect SQL Server.

本地到本地On-premises to on-premises

  • 如果应用使用分布式事务,我们建议针对 Hyper-V 环境使用 SAN 复制,或者针对 VMware 环境使用 VMware/物理服务器到 VMware 的复制,来部署 Site Recovery。If the app uses distributed transactions we recommend you deploy Site Recovery with SAN replication for a Hyper-V environment, or VMware/physical server to VMware for a VMware environment.
  • 对于非 DTC 应用程序,可以使用上述方法通过利用本地高安全性数据库镜像将群集恢复为独立服务器。For non-DTC applications, use the above approach to recover the cluster as a standalone server, by leveraging a local high safety DB mirror.

本地到 AzureOn-premises to Azure

复制到 Azure 时,Site Recovery 未提供来宾群集。Site Recovery doesn't provide guest cluster support when replicating to Azure. SQL Server 也不会为 Standard 版本提供低成本灾难恢复解决方案。SQL Server also doesn't provide a low-cost disaster recovery solution for Standard edition. 在此方案中,建议在独立 SQL Server 中保护本地 SQL Server,并在 Azure 中恢复它。In this scenario, we recommend you protect the on-premises SQL Server cluster to a standalone SQL Server, and recover it in Azure.

  1. 在本地站点中配置其他独立 SQL Server 实例。Configure an additional standalone SQL Server instance on the on-premises site.
  2. 将此实例配置为想要保护的数据库的镜像。Configure the instance to serve as a mirror for the databases you want to protect. 在高安全模式下配置镜像。Configure mirroring in high safety mode.
  3. 在本地站点上为 Hyper-VVMware VM/物理服务器配置 Site Recovery。Configure Site Recovery on the on-premises site, for (Hyper-V or VMware VMs/physical servers).
  4. 使用 Site Recovery 复制将新的 SQL Server 实例复制到 Azure。Use Site Recovery replication to replicate the new SQL Server instance to Azure. 由于该实例是高安全性镜像副本,因此会将它与主群集同步,但会使用 Site Recovery 复制将它复制到 Azure。Since it's a high safety mirror copy, it will be synchronized with the primary cluster, but it will be replicated to Azure using Site Recovery replication.

标准群集

故障回复注意事项Failback considerations

对于 SQL Server Standard 群集,计划外故障转移后的故障回复需要从镜像实例 SQL Server 备份并还原到原始群集,并重新建立镜像。For SQL Server Standard clusters, failback after an unplanned failover requires a SQL server backup and restore, from the mirror instance to the original cluster, with reestablishment of the mirror.

后续步骤Next steps

详细了解 Site Recovery 体系结构。Learn more about Site Recovery architecture.