业务连续性和数据库恢复 - SQL ServerBusiness continuity and database recovery - SQL Server

适用于:Applies to: 是SQL Server 2016 (13.x)SQL Server 2016 (13.x)yesSQL Server 2016 (13.x)SQL Server 2016 (13.x) 及更高版本适用于:Applies to: 是SQL Server 2016 (13.x)SQL Server 2016 (13.x)yesSQL Server 2016 (13.x)SQL Server 2016 (13.x) and later

本文概述 SQL Server 中高可用性和灾难恢复的业务连续性解决方案。This article provides an overview of business continuity solutions for high availability and disaster recovery in SQL Server.

每个人在部署 SQL Server 时都需执行一项常见任务,即确保所有任务关键型 SQL Server 实例以及其中的数据库在业务和最终用户需要时(无论是朝九晚五还是全天候)可用。One common task everyone deploying SQL Server has to account for is making sure that all mission critical SQL Server instances and the databases within them are available when the business and end users need them, whether that is 9 to 5 or around the clock. 其目标是尽量减少或杜绝中断,保持业务正常运行。The goal is to keep the business up and running with minimal or no interruption. 此概念也称为业务连续性。This concept is also known as business continuity.

SQL Server 2017 在现有功能基础上引入了许多新功能或增强功能,其中部分功能用于提高可用性。SQL Server 2017 introduces many new features or enhancements to existing ones, some of which are for availability. SQL Server 2017 的最大变化在于在 Linux 发行版上增加了对 SQL Server 的支持。The biggest addition to SQL Server 2017 is the support for SQL Server on Linux distributions. 有关 SQL Server 2017 中新功能的完整列表,请参阅主题 SQL Server 的新增功能For a full list of the new features in SQL Server 2017, see the topic What's new in SQL Server.

本文重点介绍 SQL Server 2017 中的可用性方案以及 SQL Server 2017 中新增和增强的可用性功能。This article is focused on covering the availability scenarios in SQL Server 2017 as well as the new and enhanced availability features in SQL Server 2017. 这些方案包括能跨 Windows Server 和 Linux 上的 SQL Server 部署的混合方案,以及可增加数据库可读副本数量的方案。The scenarios include hybrid ones that will be able to span SQL Server deployments on both Windows Server and Linux, as well as ones that can increase the number of readable copies of a database. 虽然本文并未介绍 SQL Server 外的可用性选项(例如由虚拟化提供的可用性选项),但文中讨论的所有内容都适用于来宾虚拟机中的 SQL Server 安装,无论该虚拟机是位于公有云中还是由本地虚拟机监控程序服务器托管。While this article does not cover availability options external to SQL Server, such as those provided by virtualization, everything discussed here applies to SQL Server installations inside a guest virtual machine whether in the public cloud or hosted by an on premises hypervisor server.

使用可用性功能的 SQL Server 2017 方案SQL Server 2017 scenarios using the availability features

可用性组、FCI 和日志传送可通过多种方式使用,而非仅用于可用性。Availability groups, FCIs, and log shipping can be used in a variety of ways, and not necessarily just for availability purposes. 可用性功能的使用方式主要有以下四种:There are four main ways the availability features can be used:

  • 高可用性High availability
  • 灾难恢复Disaster recovery
  • 迁移和升级Migrations and upgrades
  • 扩大一个或多个数据库的可读副本Scaling out readable copies of one or more databases

每节内容都会介绍可用于该特定情况的相关功能。Each section will discuss the relevant features that can be used for that particular scenario. SQL Server 复制功能未涵盖在内。The one feature not covered is SQL Server replication. 虽然未正式指定为“AlwaysOn”下的可用性功能,但在某些情况下,它常用于使数据冗余。While not officially designated as an availability feature under the Always On umbrella, it is often used for making data redundant in certain scenarios. 在未来版本中,Linux 上的 SQL Server 将添加复制功能。Replication will be added to SQL Server on Linux in a future release.

重要

SQL Server 可用性功能不能替换对经过充分测试的可靠备份和还原策略的需求,后者是所有可用性解决方案最基本的构建基块。The SQL Server availability features do not replace the requirement to have a robust, well tested backup and restore strategy, the most fundamental building block of any availability solution.

高可用性High availability

请务必确保在数据中心或云区域中的单个区域存在局部问题的情况下,SQL Server 实例或数据库可用。Ensuring that SQL Server instances or database are available in the case of a problem that is local to a data center or single region in the cloud region is important. 本部分介绍 SQL Server 可用性功能如何帮助完成该任务。This section will cover how the SQL Server availability features can assist in that task. Windows Server 和 Linux 上都提供了此处描述的所有功能。All of the features described are available both on Windows Server as well as on Linux.

AlwaysOn 可用性组Always on availability groups

SQL Server 2012 中引入的 AlwaysOn 可用性组将数据库的每个事务发送到另一个实例,从而提供数据库级别的保护,该实例称为副本,其中包含处于特定状态的数据库副本。Introduced in SQL Server 2012, Always On Availability Groups (availability groups) provide database-level protection by sending each transaction of a database to another instance, known as a replica, that contains a copy of that database in a special state. 可用性组可部署在 Standard 版本或 Enterprise 版本上。An availability group can be deployed on Standard or Enterprise Editions. 参与可用性组的实例可以是独立实例,也可以是 AlwaysOn 故障转移群集实例(即下一节中介绍的 FCI)。The instances participating in an availability group can be either standalone or Always On Failover Cluster Instances (FCIs, described in the next section). 由于在事务发生时将它发送到副本,建议在需要较低恢复点目标和恢复时间目标的情况下使用可用性组。Since the transactions are sent to a replica as they happen, availability groups are recommended where there are requirements for lower recovery point and recovery time objectives. 副本之间的数据移动可以是同步的或异步的,Enterprise 版本允许同步多达三个副本(包括主要副本)。Data movement between replicas can be synchronous or asynchronous, with Enterprise Edition allowing up to three replicas (including the primary) as synchronous. 可用性组具有一个数据库的完全读/写副本且位于主要副本上,而所有次要副本都不能直接从最终用户或应用程序接收事务。An availability group has one fully read/write copy of the database which is on the primary replica, while all secondary replicas cannot receive transactions directly from end users or applications.

备注

AlwaysOn 是 SQL Server 中可用性功能的总称,涵盖可用性组和 FCI。Always On is an umbrella term for the availability features in SQL Server and covers both availability groups and FCIs. AlwaysOn 不是可用性组功能的名称。Always On is not the name of the availability group feature.

因为可用性组只提供数据库级保护,而非实例级保护,所以需要为每个次要副本手动同步事务日志中未捕获的或数据库中未配置的任何内容。Because availability groups only provide database-level, and not instance-level, protection, anything not captured in the transaction log or configured in the database will need to manually synchronized for each secondary replica. 必须手动同步的对象的一些示例为实例级登录、链接服务器和 SQL Server 代理作业。Some examples of objects that must be synchronized manually are logins at the instance level, linked servers, and SQL Server Agent jobs.

可用性组还有一个名为侦听器的组件,该组件允许应用程序和最终用户在不知道哪个 SQL Server 实例承载着主要副本的情况下进行连接。An availability group also has another component called the listener, which allows applications and end users to connect without needing to know which SQL Server instance is hosting the primary replica. 每个可用性组都有自己的侦听器。Each availability group would have its own listener. 虽然侦听器的实现在 Windows Server 与 Linux 上略有不同,但它提供的功能和使用方法是相同的。While the implementations of the listener are slightly different on Windows Server versus Linux, the functionality it provides and how it is used is the same. 下图显示了使用 Windows Server 故障转移群集 (WSFC) 的基于 Windows Server 的可用性组。The picture below shows a Windows Server-based availability group which is using a Windows Server Failover Cluster (WSFC). 无论是在 Linux 还是 Windows Server 上,实现可用性必须具备 OS 层的基础群集。An underlying cluster at the OS layer is required for availability whether it is on Linux or Windows Server. 该示例显示了以 WSFC 为基础群集的两个简单服务器、节点或配置。The example shows a simple two server, or node, configuration where a WSFC is the underlying cluster.

简单可用性组

就副本而言,Standard 版本和 Enterprise 版本具有不同的最大值。Standard and Enterprise Edition have different maximums when it comes to replicas. Standard 版本中的可用性组(称为 Basic 可用性组)支持两个副本(一个主要副本和一个次要副本),且可用性组中只有一个数据库。An availability group in Standard Edition, known as a Basic Availability Group, supports two replicas (one primary and one secondary) with only a single database in the availability group. Enterprise 版本不仅允许在一个可用性组中配置多个数据库,而且拥有的副本总数可多达 9 个(1 个主要副本,8 个次要副本)。Enterprise Edition not only allows multiple databases to be configured in a single availability group, but also can have up to nine total replicas (one primary, eight secondary). Enterprise 版本还提供了其他可选权益,如可读次要副本和备份次要副本的能力等。Enterprise edition also provides other optional benefits such as readable secondary replicas, the ability to make backups off of a secondary replica, and more.

备注

SQL Server 2012 中已弃用的数据库镜像在 Linux 版 SQL Server 上不可用,以后也不会添加该功能。Database mirroring, which was deprecated in SQL Server 2012, is not available on the Linux version of SQL Server nor will it be added. 仍在使用数据库镜像的客户应开始计划迁移到替代数据库镜像的可用性组。Customers still using database mirroring should start planning to migrate to availability groups, which is the replacement for database mirroring.

在可用性方面,可用性组可提供自动或手动故障转移。When it comes to availability, availability groups can provide either automatic or manual failover. 如果配置了同步数据移动,并且主要副本和次要副本上的数据库处于同步状态,则会发生自动故障转移。Automatic failover can occur if synchronous data movement is configured and the database on the primary and secondary replica are in a synchronized state. 只要使用侦听器,且应用程序使用较高版本的 .NET(3.5 更新版本,或 4.0 及更高版本),则应能利用侦听器,在尽量减小甚至不影响最终用户的情况下进行故障转移。As long as the listener is used and the application uses a later version of .NET (3.5 with an update, or 4.0 and above), the failover should be handled with minimal to no impact to end users if a listener is utilized. 可将使次要副本成为新的主要副本的故障转移配置为自动或手动,且测量的单位通常为秒。Failover to make a secondary replica the new primary replica can be configured to be automatic or manual, and generally is measured in seconds.

下面的列表突出显示了 Windows Server 与 Linux 上的可用性组之间存在的一些差异:The list below highlights some differences with availability groups on Windows Server versus Linux:

  • 由于基础群集在 Linux 和 Windows Server 上的工作方式不同,因此,在 Linux 上,可用性组的所有故障转移(手动或自动)都是通过群集完成的。Due to differences in the way the underlying cluster works on Linux and Windows Server, all failovers (manual or automatic) of availability groups are done via the cluster on Linux. 而在基于 Windows Server 的可用性组部署中,手动故障转移必须通过 SQL Server 完成。On Windows Server-based availability group deployments, manual failovers must be done via SQL Server. 自动故障转移则由 Windows Server 和 Linux 上的基础群集处理。Automatic failovers are handled by the underlying cluster on both Windows Server and Linux.
  • 在 SQL Server 2017 中,建议将 Linux 上的可用性组配置为至少三个副本。In SQL Server 2017, the recommended configuration for availability groups on Linux will be a minimum of three replicas. 这是因为基础群集的工作方式。This is due to the way that the underlying clustering works. 发布后,会提供两个副本配置的改进解决方案。An improved solution for a two replica configuration will come post-release.
  • 在 Linux 上,每个侦听器使用的公用名都在 DNS 中定义,而不是像 Windows Server 上那样在群集中定义。On Linux, the common name used by each listener is defined in DNS and not in the cluster like it is on Windows Server.

在 SQL Server 2017 中,可用性组有一些新功能和增强功能:In SQL Server 2017, there are some new features and enhancements to availability groups:

  • 群集类型Cluster types
  • REQUIRED_SECONDARIES_TO_COMMITREQUIRED_SECONDARIES_TO_COMMIT
  • 增强的 Microsoft 分布式事务处理协调器 (DTC) 支持基于 Windows Server 的配置Enhanced Microsoft Distributor Transaction Coordinator (DTC) support for Windows Server-based configurations
  • 只读数据库的其他横向扩展方案(本文后面进行了介绍)Additional scale out scenarios for read only databases (described later in this article)
AlwaysOn 可用性组群集类型Always on availability group cluster types

通过名为故障转移群集的功能启用 Windows Server 中群集的内置可用性形式。The built-in availability form of clustering in Windows Server is enabled via a feature named Failover Clustering. 通过它,用户可生成与可用性组或 FCI 一起使用的 WSFC。It allows you to build a WSFC to be used with an availability group or FCI. 由 SQL Server 提供的群集感知资源 DLL 进行可用性组和 FCI 的集成。Integration for availability groups and FCIs is provided by a cluster-aware resource DLLs shipped by SQL Server.

每个受支持的 Linux 分发都拥有自己的 Pacemaker 群集解决方案。Each supported Linux distribution ships its own version of the Pacemaker cluster solution. Linux 上的 SQL Server 2017 支持使用 Pacemaker。SQL Server 2017 on Linux supports the use of Pacemaker. Pacemaker 是一个开放堆栈解决方案,每个分发都可与其堆栈集成。Pacemaker is an open stack solution that each distribution can then integrate with their stack. 虽然分发提供 Pacemaker,但并不像 Windows Server 中的故障转移群集功能一样集成。While the distributions ship Pacemaker, it is not as integrated as the Failover Clustering feature in Windows Server.

相较于差异,WSFC 和 Pacemaker 存在更多的相似之处。A WSFC and Pacemaker are more similar than different. 两者都提供这样一种方式:使用多个单独的服务器,在配置中将它们合并,从而提供可用性;此外两者都具有资源、约束(尽管实施方式不同)、故障转移等概念。Both provide a way to take individual servers and combine them in a configuration to provide availability, and have concepts of things like resources, constraints (even if implemented differently), failover, and so on. 为支持 Pacemaker 的可用性组和 FCI 配置(包括自动故障转移等),Microsoft 为 Pacemaker 提供了 mssql-server-ha 包,它与 WSFC 中的资源 DLL 类似但不完全相同。To support Pacemaker for both availability group and FCI configurations including things like automatic failover, Microsoft provides the mssql-server-ha package, which is similar to, but not exactly the same as, the resource DLLs in a WSFC, for Pacemaker. WSFC 和 Pacemaker 之间的区别之一是 Pacemaker 中没有网络名称资源,该组件有助于提取 WSFC 上的侦听器名称(或 FCI 名称)。One of the differences between a WSFC and Pacemaker is that there is no network name resource in Pacemaker, which is the component that helps to abstract the name of the listener (or the name of the FCI) on a WSFC. DNS 在 Linux 上提供名称解析。DNS provides that name resolution on Linux.

由于群集堆栈有所不同,SQL Server 必须处理一些由 WSFC 本机处理的元数据,因此需要对可用性组进行一些更改。Because of the difference in the cluster stack, some changes needed to be made for availability groups because SQL Server has to handle some of the metadata that is natively handled by a WSFC. 最[!IMPORTANT]的更改是为可用性组引入了群集类型。The most [!IMPORTANT] change is the introduction of a cluster type for an availability group. 这存储在 cluster_type 和 cluster_type_desc 列的 sys.availability_groups 中。This is stored in sys.availability_groups in the cluster_type and cluster_type_desc columns. 有以下三种群集类型:There are three cluster types:

  • WSFCWSFC
  • 外部External
  • None

要求可用性的所有可用性组都必须使用基础群集,在 SQL Server 2017 中则为 WSFC 或 Pacemaker。All availability groups that require availability must use an underlying cluster, which in the case of SQL Server 2017 means a WSFC or Pacemaker. 对于使用基础 WSFC 的基于 Windows Server 的可用性组,默认群集类型为 WSFC 且无需设置。For Windows Server-based availability groups that use an underlying WSFC, the default cluster type is WSFC and does not need to be set. 对于基于 Linux 的可用性组,创建可用性组时,群集类型必须设置为“外部”。For Linux-based availability groups, when creating the availability group, the cluster type must be set to External. 在创建可用性组后配置与 Pacemaker 的集成,而在 WSFC 上,需在创建时进行集成。The integration with Pacemaker is configured after the availability group is created, whereas on a WSFC, it is done at creation time.

Windows Server 和 Linux 可用性组都可使用“无”群集类型。A cluster type of None can be used with both Windows Server and Linux availability groups. 将群集类型设置为“无”,表示可用性组不需要基础群集。Setting the cluster type to None means that the availability group does not require an underlying cluster. 这意味着 SQL Server 2017 是首个支持无群集可用性组的 SQL Server 版本,但其不利的一面是高可用性解决方案不支持此配置。This means SQL Server 2017 is the first version of SQL Server to support availability groups without a cluster, but the tradeoff is that this configuration is not supported as a high availability solution.

重要

SQL Server 2017 不允许在可用性组创建完成后,更改其群集类型。SQL Server 2017 does not allow the ability to change a cluster type for an availability group after it is created. 这意味着可用性组不能从“无”切换为“外部”或“WSFC”,反之亦然。This means that an availability group cannot be switched from None to External or WSFC, or vice versa.

有的用户想只添加额外的数据库只读副本,或者喜欢可用性组为迁移/升级提供的内容,却不希望基础群集甚至复制带来额外的复杂性,对于这样的用户,群集类型为“无”的可用性组是最佳解决方案。For those who are only looking to just add additional read only copies of a database, or like what an availability group provides for migration/upgrades but do not want to be tied to the additional complexity of an underlying cluster or even the replication, an availability group with a cluster type of None is a perfect solution. 有关详细信息,请参阅迁移和升级读取缩放部分。For more information, see the sections Migrations and Upgrades and read-scale.

下面的屏幕截图显示了对 SSMS 中各种群集类型的支持。The screenshot below shows the support for the different kinds of cluster types in SSMS. 必须运行 17.1 版或更高版本。You must be running version 17.1 or later. 下面的屏幕截图取自 17.2 版。The screenshot below is from version 17.2.

SSMS AG 选项

REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMITREQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT

SQL Server 2016 将 Enterprise 版本中支持的同步副本数从两个增加到了三个。SQL Server 2016 increased support for the number of synchronous replicas from two to three in Enterprise Edition. 然而,如果其中一个次要副本已同步,但另一个副本遇到问题,则无法控制告知主要副本等待运行异常的副本或允许它继续运行的行为。However, if one secondary replica was synchronized but the other was having a problem, there was no way to control the behavior to tell the primary to either wait for the misbehaving replica or to allow it to move on. 这表示即使次要副本未处于同步状态,在某种情况下主要副本仍会继续接收写入流量,这意味着次要副本上存在数据丢失。This means that the primary replica at some point would continue to receive write traffic even though the secondary replica would not be in a synchronized state, which means that there is data loss on the secondary replica. 现在,SQL Server 2017 中提供了一个选项,可控制在出现名为 REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT 的同步副本时执行何种操作的行为。In SQL Server 2017, there is now an option to be able to control the behavior of what happens when there are synchronous replicas named REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT. 该选项的工作原理如下所示:The option works as follows:

  • 有三个可能的值:0、1 和 2There are three possible values: 0, 1, and 2
  • 值是必须同步的次要副本数,它对数据丢失、可用性组可用性和故障转移都有影响The value is the number of secondary replicas that must be synchronized, which has implications for data loss, availability group availability, and failover
  • 对于 WSFC 和群集类型为“无”的情况,默认值为 0,可手动设置为 1 或 2For WSFCs and a cluster type of None, the default value is 0, and can be manually set to 1 or 2
  • 对于群集类型为“外部”的情况,该值默认由群集机制设置,并可手动重写。For a cluster type of External, by default, the cluster mechanism will set this and it can be overridden manually. 对于三个同步副本,默认值为 1。For three synchronous replicas, the default value will be 1. 在 Linux 上,REQUIRED SYNCHRONIZED SECONDARIES_TO_COMMIT 的值在群集中的可用性组资源上配置。On Linux, the value for REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT is configured on the availability group resource in the cluster. 在 Windows 上,则通过 Transact-SQL 设置。On Windows, it is set via Transact-SQL.

大于 0 的值可确保更高的数据保护程度,因为如果无法获得所需的次要副本数,那么在该问题解决之前,主要副本不可用。A value that is higher than 0 ensures higher data protection because if the required number of secondary replicas is not available, the primary will not be available until that is resolved. REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT 还影响故障转移行为,因为如果适当数量的次要副本未处于正确状态,则不会发生自动故障转移。REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT also affects failover behavior since automatic failover could not occur if the right number of secondary replicas were not in the proper state. 在 Linux 上,若该值为 0 则不允许自动故障转移,因此在 Linux 上结合使用同步与自动故障转移时,该值必须设置为大于 0 才能实现自动故障转移。On Linux, a value of 0 will not allow automatic failover, so on Linux, when using synchronous with automatic failover, the value must be set higher than 0 to achieve automatic failover. 在 Windows Server 上将该值设置为 0 是 SQL Server 2016 和更早版本的行为。0 on Windows Server is the SQL Server 2016 and earlier behavior.

增强的 Microsoft 分布式事务处理协调器支持Enhanced Microsoft distributed transaction coordinator support

在 SQL Server 2016 之前,对于需要分布式事务(在后台使用 DTC)的应用程序而言,在 SQL Server 中获取可用性的唯一方法是部署 FCI。Before SQL Server 2016, the only way to get availability in SQL Server for applications that require distributed transactions which use DTC underneath the covers was to deploy FCIs. 可通过以下两种方式之一执行分布式事务:A distributed transaction can be done in one of two ways:

  • 在同一 SQL Server 实例中跨越多个数据库的事务A transaction that spans more than one database in the same SQL Server instance
  • 跨越多个 SQL Server 实例或可能涉及非 SQL Server 数据源的事务A transaction that spans more than one SQL Server instance or possibly involves a non-SQL Server data source

SQL Server 2016 引入了部分 DTC 支持,适用于涵盖后一种情况的可用性组。SQL Server 2016 introduced partial support for DTC with availability groups that covered the latter scenario. SQL Server 2017 对此进行了完善,对以上两种情况都提供 DTC 支持。SQL Server 2017 completes the story by supporting both scenarios with DTC.

可用性组 DTC 支持的另一增强之处在于,在 SQL Server 2016 中,只有在创建可用性组时才能启用对可用性组的 DTC 支持,之后无法添加这种支持。Another enhancement to DTC support for availability groups is that in SQL Server 2016, enabling support for DTC to an availability group could only be done when the availability group was created, and could not be added later. 而在 SQL Server 2017 中,也可在可用性组创建后向它添加 DTC 支持。In SQL Server 2017, DTC support can also be added to an availability group after it is created.

备注

只能为基于 Windows Server 的 SQL Server 实例中的数据库配置 DTC 支持。DTC support can only be configured for databases in Windows Server-based SQL Server instances. 如果应用程序要求 DTC,则必须使用 Windows Server 作为 SQL Server 部署的 OS,不能使用 Linux。If DTC is an requirement for your application, you must use Windows Server as the OS for your SQL Server deployment, and cannot use Linux.

AlwaysOn 故障转移群集实例Always on failover cluster instances

自 6.5 版以来,群集安装一直是 SQL Server 的一项功能。Clustered installations have been a feature of SQL Server since version 6.5. FCI 称为实例,是一种行之有效的方法,可为整个 SQL Server 安装提供可用性。FCIs are a proven method of providing availability for the entire installation of SQL Server, known as an instance. 这意味着如果基础服务器遇到问题,则实例内的所有内容(包括数据库、SQL Server 代理作业、链接服务器等)都将移到另一台服务器。This means that everything inside the instance, including databases, SQL Server Agent jobs, linked servers, et al., will move to another server should the underlying server encounter a problem. 所有 FCI 都要求某种形式的共享存储,即使通过网络提供。All FCIs require some sort of shared storage, even if it is provided via networking. FCI 的资源只能在任何给定时间由一个节点运行和拥有。The FCI's resources can only be running and owned by one node at any given time. 下图中,由群集的第一个节点拥有 FCI,这也意味着它拥有与之相关联的共享存储资源,这些资源用与存储相连的实线表示。In the picture below, the first node of the cluster owns the FCI, which also means it owns the shared storage resources associated with it denoted by the solid line to the storage.

故障转移群集实例

故障转移后,所有权发生更改,如下图所示。After a failover, ownership changes as is seen in the picture below.

故障转移后

FCI 未出现数据丢失,但因为有一个数据副本,所以基础共享存储是单一故障点。There is zero data loss with an FCI, but the underlying shared storage is a single point of failure since there is one copy of the data. FCI 通常与其他可用性方法(如可用性组或日志传送)结合使用,具有数据库的冗余副本。FCIs are often combined with another availability method, such as an availability group or log shipping, to have redundant copies of databases. 部署的其他方法应使用与 FCI 物理上分离的存储。The additional method deployed should use physically separate storage from the FCI. FCI 故障转移到另一个节点时,它会在一个节点上停止,并在另一个节点上启动,这类似于关闭服务器然后再打开。When the FCI fails over to another node, it stops on one node and starts on another, not unlike powering a server off and turning it on. FCI 遍历常规恢复过程,这意味着将回滚需要前滚的任何事务以及任何不完整的事务。An FCI goes through the normal recovery process, meaning any transactions that need to be rolled forward will be, and any transactions that are incomplete will be rolled back. 所以,数据库在从数据点到故障或手动故障转移的时间这一期间始终如一,没有数据丢失。Therefore, the database is consistent from a data point to the time of the failure or manual failover, hence no data loss. 数据库仅在恢复完成后才可用,因此恢复时间取决于诸多因素,且通常比可用性组的故障转移时间更长。Databases are only available after recovery is complete, so recovery time will depend on many factors, and will generally be longer than failing over an availability group. 但不利的一面是,对可用性组进行故障转移时,可能需要执行额外的任务才能使数据库可用,例如启用 SQL Server 代理作业。The tradeoff is that when you fail over an availability group, there may be additional tasks required to make a database usable, such as enabling a SQL Server Agent jobs job.

如同可用性组一样,FCI 提取承载它的基础群集节点。Like an availability group, FCIs abstract which node of the underlying cluster is hosting it. FCI 始终保留相同的名称。An FCI always retains the same name. 应用程序和最终用户绝不会连接到节点;使用分配给 FCI 的唯一名称。Applications and end users never connect to the nodes; the unique name assigned to the FCI is used. FCI 可作为一个承载主要副本或次要副本的实例加入可用性组。An FCI can participate in an availability group as one of the instances hosing either a primary or secondary replica.

下面的列表突出显示了 Windows Server 与 Linux 上的 FCI 之间存在的一些差异:The list below highlights some differences with FCIs on Windows Server versus Linux:

  • 在 Windows Server 上,FCI 属于安装过程。On Windows Server, an FCI is part of the installation process. 而 Linux 上的 FCI 则是在 SQL Server 安装完成后配置。An FCI on Linux is configured after installing SQL Server.
  • Linux 仅支持每个主机安装一个 SQL Server,因此所有 FCI 都是默认实例。Linux only supports a single installation of SQL Server per host, so all FCIs will be a default instance. Windows Server 支持每个 WSFC 具有最多 25 个 FCI。Windows Server supports up to 25 FCIs per WSFC.
  • Linux 中 FCI 使用的公用名在 DNS 中定义,并且名称应与为 FCI 创建的资源相同。The common name used by FCIs in Linux is defined in DNS, and should be the same as the resource created for the FCI.

日志传送Log shipping

如果恢复点和恢复时间目标更灵活,或者数据库中的任务并不是极为关键,则日志传送是 SQL Server 中另一个可靠的可用性功能。If recovery point and recovery time objectives are more flexible, or databases are not considered to be highly mission critical, log shipping is another proven availability feature in SQL Server. 基于 SQL Server 的本机备份,日志传送过程自动生成事务日志备份,并将其复制到一个或多个称为热备用状态的实例,然后自动将事务日志备份应用于该备用实例。Based on SQL Server's native backups, the process for log shipping automatically generates transaction log backups, copies them to one or more instances known as a warm standby, and automatically applies the transaction log backups to that standby. 日志传送使用 SQL Server 代理作业自动执行备份、复制和应用事务日志备份的过程。Log shipping uses SQL Server Agent jobs to automate the process of backing up, copying, and applying the transaction log backups.

日志传送

可以说,在某种程度上使用日志传送的最大优点在于它会考虑人为错误。Arguably the biggest advantage of using log shipping in some capacity is that it accounts for human error. 事务日志的应用可能会延迟。The application of transaction logs can be delayed. 因此,如果有人在没有 WHERE 子句的情况下发出类似 UPDATE 的内容,则备用可能尚未更改,如此便可在修复主系统时切换到该备用实例。Therefore, if someone issues something like an UPDATE without a WHERE clause, the standby may not have the change so you could switch to that while you repair the primary system. 虽然日志传送易于配置,但始终需要手动从主系统切换到热备用状态的实例(称为角色更改)。While log shipping is easy to configure, switching from the primary to a warm standby, known as a role change, is always manual. 角色更改通过 Transact-SQL 启动,并且像可用性组一样,必须手动同步事务日志中未捕获的所有对象。A role change is initiated via Transact-SQL, and like an availability group, all objects not captured in the transaction log must be manually synchronized. 还需配置每个数据库的日志传送,而单个可用性组可包含多个数据库。Log shipping also needs to be configured per database, whereas a single availability group can contain multiple databases. 与可用性组或 FCI 不同,日志传送不提取角色更改。Unlike an availability group or FCI, log shipping has no abstraction for a role change. 应用程序必须能够处理这种情况。Applications must be able to handle this. 可以使用 DNS 别名 (CNAME) 等技术,但存在利弊,例如切换后 DNS 刷新需要一定的时间。Techniques such as a DNS alias (CNAME) could be employed, but there are pros and cons, such as the time it takes for DNS to refresh after the switch.

灾难恢复Disaster recovery

主要可用性位置遭遇地震或洪水等灾难事件时,企业必须做好准备,在其他地方将系统联机。When your primary availability location experiences a catastrophic event like an earthquake or flood, the business must be prepared to have its systems come online elsewhere. 本部分介绍 SQL Server 可用性功能如何帮助实现业务连续性。This section will cover how the SQL Server availability features can assist with business continuity.

AlwaysOn 可用性组Always on availability groups

可用性组的优点之一是可使用单个功能配置高可用性和灾难恢复。One of the benefits of availability groups is that both high availability and disaster recovery can be configured using a single feature. 由于不需要确保共享存储也具有高可用性,可以更轻松地实现在一个数据中心内具有用于高可用性的本地副本,在其他数据中心内具有用于灾难恢复的远程备份,且每个备份都有单独的存储。Without the requirement for ensuring that shared storage is also highly available, it is much easier to have replicas that are local in one data center for high availability, and remote ones in other data centers for disaster recovery each with separate storage. 确保冗余的代价是具有额外的数据库副本。Having additional copies of the database is the tradeoff for ensuring redundancy. 下面的示例为跨越多个数据中心的可用性组。An example of an availability group that spans multiple data centers is shown below. 一个主要副本负责确保所有次要副本保持同步。One primary replica is responsible for keeping all secondary replicas synchronized.

可用性组

可用性组(群集类型为“无”的可用性组除外)要求所有副本都属于同一基础群集(无论是 WSFC 还是 Pacemaker)。Outside of an availability group with a cluster type of none, an availability group requires that all replicas are part of the same underlying cluster whether it is a WSFC or Pacemaker. 这意味着在上图中,WSFC 延伸到两个不同的数据中心,增加了复杂性。This means that in the picture above, the WSFC is stretched to work in two different data centers which adds complexity. 无论使用什么平台(Windows Server 或 Linux)。regardless of the platform (Windows Server or Linux). 跨距离延伸群集会增加复杂性。Stretching clusters across distance adds complexity. SQL Server 2016 中引入了分布式可用性组,它允许可用性组跨越在不同的群集上配置的可用性组。Introduced in SQL Server 2016, a distributed availability group allows an availability group to span availability groups configured on different clusters. 这降低了节点必须全部位于同一个群集中这一要求,使配置灾难恢复更加容易。This decouples the requirement to have the nodes all participate in the same cluster, which makes configuring disaster recovery much easier. 有关分布式可用性组的详细信息,请参阅分布式可用性组For more information on distributed availability groups, see Distributed availability groups.

分布式可用性组关系图。

AlwaysOn 故障转移群集实例Always on failover cluster instances

FCI 可用于灾难恢复。FCIs can be used for disaster recovery. 与一般可用性组一样,基础群集机制也必须扩展到所有位置,这会增加复杂性。As with a normal availability group, the underlying cluster mechanism must also be extended to all locations which adds complexity. FCI 还有一个注意事项:共享存储。There is an additional consideration for FCIs: the shared storage. 必须在主站点和辅助站点中使用相同的磁盘,因此需要借助外部方法,例如存储供应商在硬件层提供的功能或使用 Windows Server 中的存储副本,确保 FCI 使用的磁盘存在于其他地方。The same disks need to be available in the primary and secondary sites, so an external method such as functionality provided by the storage vendor at the hardware layer or using storage Replica in Windows Server, is required to ensure that the disks used by the FCI exist elsewhere.

AlwaysOn FCI

日志传送Log shipping

日志传送是为 SQL Server 数据库提供灾难恢复最古老的方法之一。Log shipping is one of the oldest methods of providing disaster recovery for SQL Server databases. 日志传送通常与可用性组和 FCI 结合使用,在其他选项可能由于环境、管理技能或预算而可能不太适用的情况下,提供经济高效且更简单的灾难恢复。Log shipping is often used in conjunction with availability groups and FCIs to provide cost-effective and simpler disaster recovery where other options may be challenging due to environment, administrative skills, or budget. 与日志传送的高可用性情况类似,许多环境会延迟加载事务日志,以便解决人为错误。Similar to the high availability story for log shipping, many environments will delay the loading of a transaction log to account for human error.

迁移和升级Migrations and upgrades

部署新实例或升级旧实例时,业务不容许出现长时间的中断。When deploying new instances or upgrading old ones, a business cannot tolerate long outage. 本部分介绍如何使用 SQL Server 的可用性功能,最大限度地减少执行计划内体系结构更改、服务器交换、平台更改(例如 Windows Server 和 Linux 之间)时的停机时间或修补过程中的停机时间。This section will discuss how the availability features of SQL Server can be used to minimize the downtime in a planned architecture change, server switch, platform change (such as Windows Server to Linux or vice versa), or during patching.

备注

其他方法(例如使用备份和在别处还原)也可用于迁移和升级。Other methods, such as using backups and restoring them elsewhere, can also be used for migrations and upgrades. 本文中不作介绍。They are not discussed in this paper.

AlwaysOn 可用性组Always on availability groups

包含一个或多个可用性组的现有实例可就地升级至 SQL Server 2017。An existing instance containing one or more availability groups can be upgraded in place to SQL Server 2017. 虽然这要求一定的停机时间,但可通过适量计划,将此时间减至最少。While this will require some amount of downtime, with the right amount of planning, it can be minimized.

如果目标是迁移到新服务器,而不更改配置(包括操作系统或 SQL Server 版本),那么可将这些服务器作为节点添加到现有基础群集,并添加到可用性组。If the goal is to migrate to new servers and not change the configuration (including the operating system or SQL Server version), those servers could be added as nodes to the existing underlying cluster and added to the availability group. 副本处于正确状态后,新服务器可能会发生手动故障转移,之后可将旧服务器从可用性组中删除并最终停止使用。Once the replica or replicas are in the right state, a manual failover could occur to a new server, and then the old ones could be removed from the availability group, and ultimately, decommissioned.

分布式 AG 也是另一种迁移到新配置或升级 SQL Server 的方法。Distributed AGs are also another method to migrate to a new configuration or upgrade SQL Server. 因为分布式 AG 在不同体系结构上支持不同的基础 AG,例如,可以从在 Windows Server 2012 R2 上运行的 SQL Server 2016 更改为在 Windows Sever 2016 上运行的 SQL Server 2017。Because a distributed AG supports different underlying AGs on different architectures, for example, you could change from SQL Server 2016 running on Windows Server 2012 R2 to SQL Server 2017 running on Windows Server 2016.

分布式 AG

最后,群集类型为“无”的可用性组也可用于迁移或升级。Finally, availability groups with a cluster type of None can also be used for migration or upgrading. 在典型的可用性组配置中,不能混合搭配群集类型,因此所有副本都需是“无”类型。You cannot mix and match cluster types in a typical availability group configuration, so all replicas would need to be a type of None. 分布式可用性组可用于跨越配置了不同群集类型的可用性组。A distributed availability group can be used to span availability groups configured with different cluster types. 不同的 OS 平台上也支持这种方法。This method is also supported across the different OS platforms.

用于迁移和升级的可用性组的所有变体都允许超时完成工作中最耗时的部分 - 数据同步。All variants of availability groups for migrations and upgrades allow the most time consuming portion of the work to be done over time - data synchronization. 开始切换到新配置时,相对于长时间的停机,直接转换是短暂的中断,在此期间需完成包括数据同步在内的所有工作。When it comes time to initiate the switch to the new configuration, the cutover will be a brief outage versus one long period of downtime where all the work, including data synchronization, would need to be completed.

可用性组在正在完成修补时手动将主要副本故障转移到次要副本,从而最大限度地缩短基础 OS 修补期间的停机时间。Availability groups can provide minimal downtime during patching of the underlying OS by manually failing over the primary to a secondary replica while the patching is being completed. 从操作系统的角度来看,在 Windows Server 上执行此操作更为常见,因为该基础 OS 的维护可能经常(但非始终)需要重启。From an operating system perspective, doing this would be more common on Windows Server since often, but not always, servicing the underlying OS may require a reboot. 修补 Linux 有时也需要重启,但并不频繁。Patching Linux sometimes needs a reboot, but it can be infrequent.

修补参与可用性组的 SQL Server 实例也可以尽量减少停机时间,具体取决于可用性组体系结构的复杂程度。Patching SQL Server instances participating in an availability group can also minimize downtime depending on how complex the availability group architecture is. 若要修补参与可用性组的服务器,需先修补次要副本。To patch servers participating in an availability group, a secondary replica is patched first. 正确数量的副本修补完成后,将主要副本手动故障转移到另一个节点,进行升级。Once the right number of replicas are patched, the primary replica is manually failed over to another node to do the upgrade. 此时还可升级任何剩余的次要副本。Any remaining secondary replicas at that point can be upgraded, too.

AlwaysOn 故障转移群集实例Always on failover cluster instances

FCI 自身无法为传统的迁移或升级提供帮助;必须为 FCI 中的数据库以及参与其中的所有其他对象配置可用性组或日志传送。FCIs on their own cannot assist with a traditional migration or upgrade; an availability group or log shipping would have to be configured for the databases in the FCI and all other objects accounted for. 然而,需要修补基础 Windows Server 时,Windows Server 中的 FCI 仍然是常用选项。However, FCIs under Windows Server are still a popular option for when the underlying Windows Servers need to be patched. 可启动手动故障转移,这意味着会出现短暂的中断,而不是使实例在整个 Windows Server 修补期间完全不可用。A manual failover can be initiated, which means a brief outage instead of having the instance completely unavailable for the entire time Windows Server is being patched. FCI 可就地升级到 SQL Server 2017。An FCI can be upgraded in place to SQL Server 2017. 有关详细信息,请参阅升级 SQL Server 故障转移群集实例For information, see Upgrade a SQL Server Failover Cluster Instance.

日志传送Log shipping

日志传送仍然是迁移和升级数据库的常用选项。Log shipping is still a popular option to both migrate and upgrade databases. 与可用性组相似,但在这种情况下使用事务日志作为同步方法,可在服务器切换之前启动数据传播。Similar to availability groups, but this time using the transaction log as the synchronization method, the data propagation can be started well in advance of the server switch. 切换时,一旦所有流量在来源处停止,则记录最终事务日志,并将其复制、应用到新配置。At the time of the switch, once all traffic is stopped at the source, a final transaction log would need to be taken, copied, and applied to the new configuration. 此时,数据库即可联机工作。At that point, the database can be brought online. 通常,日志传送对速度较慢的网络的包容性更强,虽然相较于使用可用性组或分布式可用性组执行的切换而言,这种切换可能耗时稍长,但通常也以分钟为单位,而不是以小时、天或周为单位。Log shipping is often more tolerant of slower networks, and while the switch may be slightly longer than one done using an availability group or a distributed availability group, it is usually measured in minutes - not hours, days, or weeks.

与可用性组相似,日志传送可提供一种在修补时切换到其他服务器的方法。Similar to availability groups, log shipping can provide a way to switch to another server in the event of patching.

其他 SQL Server 部署方法和可用性Other SQL Server deployment methods and availability

还可通过另外两种方法部署 Linux 上的 SQL Server:容器和使用 Azure(或其他公有云提供程序)。There are two other deployment methods for SQL Server on Linux: containers and using Azure (or another public cloud provider). 无论如何部署 SQL Server,一般都需要本文中介绍的可用性。The general need for availability as presented throughout this paper exists regardless of how SQL Server is deployed. 若要使 SQL Server 具有高可用性,使用这两种方法时需留意一些特别的注意事项。These two methods have some special considerations when it comes to making SQL Server highly available.

使用 Docker 的容器是部署 SQL Server 的新方法,适用于 Windows Server 或 Linux。Containers using Docker are a new way of deploying SQL Server, either for Windows Server or Linux. 容器是可供运行的 SQL Server 的完整映像。A container is a complete image of SQL Server that is ready to run. 但是,目前没有对群集的本机支持,因此也没有对高可用性或灾难恢复的本机支持。However, there is currently no native support for clustering, and thus, direct high availability or disaster recovery. 目前,借助容器使 SQL Server 数据库可用的可选方法是日志传送以及备份和还原。Currently, the options to make SQL Server databases available using containers would be log shipping and backup and restore. 虽然可以配置群集类型为“无”的可用性组,但如前所述,并不将此视为真正的可用性配置。While an availability group with a cluster type of None can be configured, as noted earlier, it is not considered a true availability configuration. Microsoft 正在寻找使用容器启用可用性组或 FCI 的方法。Microsoft is looking at ways to enable availability groups or FCIs using containers.

如果现在使用容器,若容器丢失,可再次部署并附加到之前所用的共享存储(具体取决于容器平台)。If you are using containers today, if the container is lost, depending on the container platform, it can be deployed again and attached to the shared storage that was used. 这种机制中的某些内容由容器业务流程协调程序提供。Some of this mechanism is provided by the container orchestrator. 虽然这确实提供了一些复原能力,但是会出现与数据库恢复相关的停机时间,而且并不像使用可用性组或 FCI 那样真正的高度可用。While this does provide some resiliency, there will be some downtime associated with database recovery and is not truly highly available as it would be if using an availability group or FCI.

可通过使用 Azure 安装的 SQL Server 部署 Linux IaaS 虚拟机。Linux IaaS virtual machines can be deployed with SQL Server installed using Azure. 与基于本地的安装一样,支持的安装需要使用 Pacemaker 本身以外的 STONITH(俗称将其它节点“爆头”)。As with on premises-based installations, a supported installation requires the use of STONITH (Shoot the Other Node in the Head) which is external to Pacemaker itself. 通过隔离可用性代理提供 STONITH。STONITH is provided via fencing availability agents. 一些分发将其作为平台的一部分提供,其他则依赖于外部硬件和软件供应商。Some distributions ship them as part of the platform, others rely on external hardware and software vendors. 查看你的首选 Linux 分发,了解其提供的是何种形式的 STONITH,以便能在公有云中部署支持的解决方案。Check with your preferred Linux distribution to see what forms of STONITH are provided so that a supported solution can be deployed in the public cloud.

跨平台和 Linux 分发互操作性Cross-platform and Linux distribution interoperability

现在,Windows Server 和 Linux 都支持 SQL Server,本部分介绍它们如何协同工作提供可用性和其他用途的相关方案,以及包含多个 Linux 分发的解决方案。With SQL Server now supported on both Windows Server and Linux, this section covers the scenarios of how they can work together for availability in addition to other purposes, as well as the story for solutions that will incorporate more than one Linux distribution.

在介绍跨平台和互操作性方案之前,需要说明以下两个事实:Before covering the cross-platform and interoperability scenarios, two facts need to be stated:

  • 不存在基于 WSFC 的 FCI 或可用性组直接与基于 Linux 的 FCI 或可用性组一起使用的情况。There are no scenarios where a WSFC-based FCI or availability group will work with a Linux-based FCI or availability group directly. Pacemaker 节点不能扩展 WSFC,反之亦然。A WSFC cannot be extended by a Pacemaker node and vice versa.
  • FCI 或群集类型为“外部”的可用性组不支持混合 Linux 分发。Mixing Linux distributions is not supported with FCIs or an availability group that has a cluster type of External. 在这种情况下,所有可用性组副本必须配置相同的 Linux 分发,以及相同的版本。All availability group replicas in that scenario must be configured not only the same Linux distribution, but also the same version. 支持 SQL Server 在两个平台或 Linux 的多个分发中运行的两种方式分别是可用性组和日志传送。The two supported ways that SQL Server can operate across the two platforms or multiple distributions of Linux are availability groups and log shipping.

分布式可用性组Distributed availability groups

分布式可用性组旨在跨可用性组配置,无论可用性组下的两个基础群集是两个不同的 WSFC、Linux 分发,还是一个在 WSFC 上,另一个在 Linux 上。Distributed availability groups are designed to span availability group configurations, whether those two underlying clusters underneath the availability groups are two different WSFCs, Linux distributions, or one on a WSFC and the other on Linux. 它会成为具有跨平台解决方案的主要方法。A distributed availability group will be the primary method of having a cross platform solution. 也是迁移的主要解决方案,例如,公司需要从基于 Windows Server 的 SQL Server 基础结构迁移到基于 Linux 的基础结构。A distributed availability group is also the primary solution for migrations such as converting from a Windows Server-based SQL Server infrastructure to a Linux-based one if that is what your company wants to do. 如上所述,可用性组,尤其是分布式可用性组,可尽量减少应用程序无法使用的时间。As noted above, availability groups, and especially distributed availability groups, would minimize the time that an application would be unavailable for use. 下面显示了跨 WSFC 和 Pacemaker 的分布式可用性组示例。An example of a distributed availability group that spans a WSFC and Pacemaker is shown below.

显示跨 WSFC 和 Pacemaker 的分布式可用性组的关系图。

如果可用性组配置了“无”群集类型,则可跨越 Windows Server 和 Linux 以及多个 Linux 分发。If an availability group is configured with a cluster type of None, it can span Windows Server and Linux as well as multiple Linux distributions. 这不是真正的高可用性配置,所以不应用于任务关键型部署,但可用于读取缩放或迁移/升级方案。Since this is not a true high availability configuration, it should not be used for mission critical deployments, but for read-scale or migration/upgrade scenarios.

日志传送Log shipping

由于日志传送只基于备份和还原,所以 Windows Server 上的 SQL Server 与 Linux 上的 SQL Server 在数据库、文件结构等方面没有任何差异。Since log shipping is just based on backup and restore, and there are no differences in the databases, file structures, etc., for SQL Server on Windows Server versus SQL Server on Linux. 这意味着可在基于 Windows Server 的 SQL Server 安装和基于 Linux 的安装之间以及在 Linux 分发之间配置日志传送。This means that log shipping can be configured between a Windows Server-based SQL Server installation and a Linux one as well as between distributions of Linux. 其他所有内容保持不变。Everything else remains the same. 唯一需要注意的是,就像可用性组一样,若源的 SQL Server 主版本高于目标的 SQL Server 版本,则无法使用日志传送。The only caveat is that log shipping, just like an availability group, cannot work when the source is at a higher SQL Server major version against a target that is at a lower version of SQL Server.

读取缩放read-scale

自在 SQL Server 2012 中引入次要副本后,它们已用于只读查询。Since their introduction in SQL Server 2012, secondary replicas have had the ability to be used for read-only queries. 可用性组可实现两种方式:允许直接访问次要副本以及配置只读路由,后者需要使用侦听器。There are two ways that can be achieved with an availability group: by allowing direct access to the secondary as well as configuring read only routing which requires the use of the listener. SQL Server 2016 引入了通过使用轮循机制算法的侦听器负载均衡只读连接的功能,允许只读请求分布在所有可读副本中。SQL Server 2016 introduced the ability to load balance read-only connections via the listener using a round robin algorithm, allowing read-only requests to be spread across all readable replicas.

备注

可读次要副本仅是 Enterprise 版本中的功能,承载可读副本的每个实例都需要 SQL Server 许可证。Readable secondary replicas is a feature only in Enterprise Edition, and each instance hosting a readable replica would need a SQL Server license.

通过可用性组缩放数据库的可读副本这一功能最先是与分布式可用性组一起引入 SQL Server 2016 的。Scaling readable copies of a database via availability groups was first introduced with distributed availability groups in SQL Server 2016. 这使公司能够通过少量配置,不仅在本地,并且可以在区域范围和全局范围内拥有数据库的只读副本,并通过在本地执行查询来减少网络流量和延迟。This would allow companies to have read-only copies of the database not only locally, but regionally and globally with a minimal amount of configuration and reduce network traffic and latency by having queries executed locally. 可用性组的每个主要副本即使不是完全读/写副本,也可播种另外两个可用性组,因此每个分布式可用性组最多可支持 27 个可读取数据副本。Each primary replica of an availability group can seed two other availability groups even if it is not the fully read/write copy, so each distributed availability group can support up to 27 copies of the data that are readable.

显示与读取扩展相关的分布式可用性组的关系图。

从 SQL Server 2017 开始,可以创建准实时只读解决方案,其中可用性组的群集类型配置为“无”。Starting with SQL Server 2017, It is possible to create a near-real time, read-only solution with availability groups configured with a cluster type of None. 如果目标是将可用性组用于可读次要副本而不是可用性,那么此操作可去除使用 WSFC 或 Pacemaker 的复杂性,并以更简单的部署方法提供可用性组的可读优势。If the goal is to use availability groups for readable secondary replicas and not availability, doing this removes the complexity of using a WSFC or Pacemaker, and gives the readable benefits of an availability group in a simpler deployment method.

唯一需要特别注意的是,由于没有群集类型为“无”的基础群集,配置只读路由稍有不同。The only major caveat is that due to no underlying cluster with a cluster type of None, configuring read only routing is a little different. 从 SQL Server 的角度来看,即使没有群集,仍然需要侦听器来路由请求。From a SQL Server perspective, a listener is still required to route the requests even though there is no cluster. 使用主要副本的 IP 地址或名称,而不是配置传统的侦听器。Instead of configuring a traditional listener, the IP address or name of the primary replica is used. 然后,使用主要副本路由只读请求。The primary replica is then used to route the read only requests.

在技术上,可通过还原数据库 WITH STANDBY 为可读使用配置日志传送热备用状态。A log shipping warm standby can technically be configured for readable usage by restoring the database WITH STANDBY. 但是,由于事务日志需要独占使用数据库进行恢复,这意味着用户不能在这种时候访问数据库。However, because the transaction logs require exclusive use of the database for restoration, it means that users cannot be accessing the database while that happens. 因此,日志传送并非理想解决方案,特别是在需要准实时数据的情况下。This makes log shipping a less than ideal solution - especially if near real-time data is required.

对于具有可用性组的所有读取缩放方案,应注意的一点是,与使用所有数据都是实时数据的事务复制不同,每个次要副本都不处于可应用唯一索引的状态,该副本是对主要副本的精确复制。One thing that should be noted for all read-scale scenarios with availability groups is that unlike using transactional replication where all of the data is live, each secondary replica is not in a state where unique indexes can be applied, the replica is an exact copy of the primary. 这意味着如果需要任何索引进行报告或需要处理数据,则必须在主要副本的数据库上执行。This means that if any indexes are required for reporting or data needs to be manipulated, it must be done on the database(s) on the primary replica. 如果需要灵活性,复制是对可读数据而言较佳的解决方案。If you need that flexibility, replication is a better solution for readable data.

总结Summary

在 Windows Server 和 Linux 上使用相同的功能,可实现 SQL Server 2017 实例和数据库的高可用性。Instances and databases of SQL Server 2017 can be made highly available using the same features on both Windows Server and Linux. 除本地高可用性和灾难恢复的标准可用性方案之外,还可使用 SQL Server 中的可用性功能尽量减少与升级和迁移相关的停机时间。Besides standard availability scenarios of local high availability and disaster recovery, downtime associated with upgrades and migrations can be minimized with the availability features in SQL Server. 可用性组还可提供数据库的其他副本,用作同一体系结构的一部分,扩大可读副本。Availability groups can also provide additional copies of a database as part of the same architecture to scale out readable copies. 无论是使用 SQL Server 2017 部署新的解决方案还是考虑升级,SQL Server 2017 均可提供所需的可用性和可靠性。Whether you are deploying a new solution using SQL Server 2017 or considering an upgrade, SQL Server 2017 has the availability and reliability you require.

后续步骤Next steps

可用性组Availability groups

故障转移群集Failover clusters