526互联

如何在 SQL Server 2016 中为可用性组配置只读路由

发布时间 2023-07-21 23:12:18作者: 雪竹子

SQL Server Always On 可用性组概念在 SQL Server 2012 中首次引入，作为企业级高可用性和灾难恢复解决方案，将取代数据库镜像功能。Always On Availability Group 在组级别提供高可用性解决方案，其中每个组可以包含任意数量的数据库，这些数据库可以复制到多个辅助服务器（称为副本）。

SQL Server 2016 最多支持八个副本。默认情况下，辅助副本不允许任何工作负载，这与 SQL Server 镜像站点的辅助方相同，其中副本仅用于灾难恢复情况下的故障转移目的。辅助副本还可以配置为主动可读辅助副本，以允许对所有辅助数据库进行只读访问，因为辅助数据库中的数据接近实时数据。仅当应用程序的连接字符串包含 Application Intent=Readonly 参数时，将可读辅助服务器设置为 Read-Intent Only 将允许辅助服务器提供只读工作负载。可以使用名为“Always On Availability Group”的新功能将应用程序意图值等于 ReadOnly 的连接强制执行到辅助副本只读路由。这样，将执行定向到 Always On 可用性组侦听器的只读进程的查询将被重定向到辅助副本而不是主副本。

在 SQL Server 2012 和 2014 版本中，只读工作负载重定向过程将专门将查询重定向到路由列表中定义的第一个辅助副本（除非它不可访问），然后将连接定向到路由列表中的下一个副本。其他辅助副本将不会参与提供只读工作负载，从而将负载平衡机制限制为仅一个副本。作为解决方法，可以定期更新只读路由列表，以便您确保所有副本都将用于服务只读工作负载。SQL Server 2016 通过引入本机负载平衡机制消除了这一限制，我们稍后会看到。

假设我们配置了包含三个副本的可用性组 AG40VS：DB41VS、DB42VS 和 DB43VS。DB41VS SQL Server 配置为主副本，DB42 和 DB43 SQL Server 配置为可读辅助副本。DB41VS 和 DB42VS 均配置为自动故障转移。

为了创建只读路由列表，我们应该首先检查是否配置了可用性组侦听器，因为只读客户端会将连接请求定向到可用性组侦听器。我们可以通过查询 sys.availability_group_listeners DMV 并将其与 sys.availability_groups DMV 连接来获取可用性组名称来实现这一点，如下所示：

我们案例中的结果将显示可用性组侦听器已配置如下：

创建只读路由列表的第二个先决条件是至少应将一个辅助副本配置为只读访问，可以将其设置为允许所有连接进行只读访问或仅允许读取意图连接。从 SQL Server Management Studio 展开 AlwaysOn 高可用性节点，右键单击可用性组，然后选择属性。在“可用性组属性”窗口中，将每个辅助副本的“可读辅助属性”更改为“是”值以允许所有连接进行只读访问，或更改为“只读”以仅允许读取意图连接。在此演示中，我们将仅允许读取意图连接，如下所示：

现在我们准备配置只读路由。只读路由允许 SQL Server 路由传入的读意图连接，这些连接连接到由可用的可读辅助副本提供服务的可用性组侦听器。为了支持这一点，可读辅助副本应该有一个只读路由 URL，该 URL 在该副本作为辅助副本工作时起作用。只读路由 URL 由定义可读辅助副本的系统地址或端口号组成，类似于配置 SQL Server 镜像时使用的终结点 URL。可以为每个可读辅助副本分配一个只读路由 URL，该 URL 将用于将读取意图连接请求路由到特定的可读辅助副本。这样，只读路由 URL 是在逐个副本的基础上定义的。

不幸的是，在 SSMS 17.4 之前，无法通过 GUI 使用 SQL Server Management Studio 定义只读路由。它只能通过 T-SQL 或 PowerShell 命令定义。下面的 T-SQL 脚本修改 Always On 可用性组中的每个副本，以在充当辅助副本时允许 Read_Only 工作负载，并为每个副本定义只读路由 URL：

sys.availability_replicas DMV 可用于查看每个副本配置的只读路由 URL，如下所示：

在我们的例子中，结果将是这样的：

对于每个主副本，您应该至少定义一个辅助副本，该副本将用作只读工作负载将重定向到的路由目标。这些将为这些重定向的只读请求提供服务的辅助副本可以在只读路由列表中定义，仅当副本在主要角色下运行时才会考虑该副本。下面的 T-SQL 脚本用于定义每个副本充当主副本时的只读路由列表。例如，如果 DB41VS SQL Server 是主副本，则只读工作负载将被重定向到可读辅助副本；DB42VS 和 DB43VS 因此：

可以使用 sys.availability_read_only_routing_lists DMV 检查 AlwaysOn 可用性组只读路由列表，该 DMV 返回 AlwaysOn 可用性组中每个可用性组副本的只读路由列表，并与 sys.availability_replicas 和 sys.availability_groups DMV 连接，如下所示：

在我们的例子中，带有路由 URL 和路由优先级的只读路由列表如下：

开始测试之前要检查的最后一点是只读工作负载定向到的副本是否处于已同步或正在同步状态。可以从 SQL Server Management Studio 的数据库节点检查同步状态，如下所示：

现在，只读路由已完全配置，辅助副本已准备好接收只读工作负载。从应用程序端，应修改应用程序连接字符串以将应用程序意图属性值设置为“ReadOnly”，以便来自该应用程序的连接请求将被分类为读取意图请求。将应用程序意图类型指定为 ReadOnly 的连接字符串示例如下所示：

服务器= tcp:AG40VS,50000; 数据库=测试；集成安全=SSPI；多子网故障转移=真；应用程序意图=只读；

从 SQL Server 2012 开始，添加了ApplicationIntent ODBC 连接字符串关键字以支持 SQL Server Native Client 中的 Always On 可用性组。如果您的应用程序使用 ODBC 连接字符串连接到 SQL Server，您将能够从 ODBC 数据源配置将应用程序意图值指定为 ReadOnly，其工作方式与将“ApplicationIntent=ReadOnly”值添加到连接字符串相同。这样，只读工作负载将被定向到可读的辅助副本，如下所示：

假设您设法使用辅助副本来运行只读查询，以免影响主副本的性能。如果您尝试连接到 DB42VS 辅助副本并从 TEST 数据库运行简单的 SELECT 语句，将生成一条错误，显示该数据库仅可用于 ReadOnly 应用程序意向连接，如下所示：

为了解决这个问题，我们需要将 ApplicationIntent=ReadOnly参数传递到 SSMS 中的“附加连接参数”屏幕，当您单击“连接到服务器”对话框中的“选项”按钮时，将显示该屏幕，如下所示：

添加此连接参数指定来自 SSMS 的连接请求将被分类为读意图请求。如果您尝试运行相同的先前 SELECT 语句，查询将成功执行，从辅助副本中检索请求的数据，而不影响主副本性能，如下所示：

正如之前在只读路由列表中配置的那样，当 DB41VS 是主副本时，读意向工作负载将重定向到 DB42VS 辅助副本，而当 DB42VS 是主副本时，读意图工作负载将重定向到 DB43VS。

让我们使用sqlcmd工具 实际测试工作负载重定向。为此，我们应该为 –K 应用程序意图参数指定 ReadOnly 值。我们还应该在 –S 参数中提供可用性组侦听器名称，并在 –d 参数中提供可用性组数据库名称。

在我们当前的可用性组设置中，DB41VS SQL Server 是主副本。运行以下 SQL CMD 命令：

sqlcmd -S AG40VS,50000 -E -d 测试 -K 只读

结果将向我们显示，当前接收我的 ReadOnly 应用程序意向工作负载的服务器是只读路由列表中的第一个辅助副本，即 DB42VS SQL Server，如下所示：

让我们将可用性组故障转移到 DB42VS 副本：

现在，DB42VS 是主要副本。如果我们运行与之前相同的 SQL CMD 命令：

sqlcmd -S AG40VS,50000 -E -d 测试 -K 只读

结果将向我们表明，为我的 ReadOnly 应用程序意图请求提供服务的服务器是只读路由列表中的第一个辅助副本，即 DB43VS SQL Server，如下所示：

我们之前提到过，只有只读路由列表中的第一个辅助副本才会接收只读应用程序意向连接，其他辅助副本将不会参与为该只读连接提供服务。为了解决该问题，SQL Server 2016 在只读路由列表中引入了负载平衡列表。

让我们修改充当主副本的 DB41VS SQL Server 的只读路由列表以使用下面的负载平衡列表：

这个新的只读路由列表有两个路由列表：第一个列表包含 DB42VS 和 DB43VS 辅助副本，第二个列表仅包含 DB41VS 副本。第一个收到的只读连接将由 DB42VS 副本提供服务，第二个只读连接将路由到 DB43VS 副本，并在这两个副本之间循环分配只读连接。如果第一只读路由列表中的辅助副本之一不可用，则只读连接将仅由第一只读路由列表中的可用副本接收。当第一只读路由列表中的所有副本变得不可用时，第二只读路由列表将开始接收只读连接。

将 DB41VS SQL Server 作为主副本。如果我们再次运行之前的 SQL CMD 命令两次：

sqlcmd -S AG40VS,50000 -E -d 测试 -K 只读

结果将向我们表明，第一个只读请求将由 DB42VS 辅助副本提供服务，第二个只读请求将由 DB43VS 辅助副本提供服务，如下所示：

结论

SQL Server Always On 可用性组是一项企业级高可用性和灾难恢复功能，可在数据库组级别提供高可用性解决方案。默认情况下，辅助副本拒绝任何只读工作负载，除非您将其配置为接收所有只读访问连接或仅读取意图连接。默认情况下未配置的只读路由允许定向到 Always On 可用性组侦听器的只读请求由辅助副本而不是主副本提供服务。这可以通过为每个副本配置只读路由 URL 并为这些副本定义只读路由列表来执行。SQL Server 2016 通过定义新的负载平衡列表解决了负载平衡问题，其中只读路由列表中的所有辅助副本都可以处理只读工作负载，而不是在之前的 SQL Server 版本中将工作负载引导到只读路由列表中的第一个辅助副本。本文中的示例向我们展示了所有这些实际上是如何工作的。