为 AlwaysOn 可用性组配置读取缩放

项目
04/26/2023

适用于： SQL Server

可在 Windows 上配置 SQL Server AlwaysOn 可用性组用于读取缩放工作负载。可用性组有两类体系结构：

使用群集管理器提供改善的业务连续性并且可包含可读次要副本的高可用性体系结构。若要创建此高可用性体系结构，请参阅在 Windows 上创建和配置可用性组。
只支持读取缩放工作负载的体系机构。

本文介绍如何在不使用群集管理器的情况下创建可用性组，用于读取缩放工作负载。此体系结构仅提供读取缩放。它不提供高可用性。

注意

带 CLUSTER_TYPE = NONE 的可用性组可包括各种操作系统平台上托管的副本。它无法支持高可用性。对于 Linux 操作系统，请参阅在 Linux 上配置 SQL Server 可用性组用于读取缩放。

先决条件

创建可用性组前，需要：

设置环境，以便所有托管可用性副本的服务器能够通信。
安装 SQL Server。有关详细信息，请参阅安装 SQL Server。

启用 AlwaysOn 可用性组，并重启 mssql-server

注意

以下命令利用 PowerShell 库中发布的 sqlserver 模块中的 cmdlet。可使用 Install-Module 命令安装此模块。

在托管 SQL Server 实例的每个副本上启用 AlwaysOn 可用性组。然后重启 SQL Server 服务。运行以下命令，启用并重启 SQL Server 服务：

Enable-SqlAlwaysOn -ServerInstance <server\instance> -Force

启用 AlwaysOn_health 事件会话

若要在对可用性组进行故障排除时帮助诊断根本原因，可选择性地启用 AlwaysOn 可用性组扩展事件 (XEvents) 会话。为此，请在 SQL Server 的每个实例上运行以下命令：

ALTER EVENT SESSION  AlwaysOn_health ON SERVER WITH (STARTUP_STATE=ON);
GO

有关此 XEvents 会话的详细信息，请参阅 AlwaysOn 可用性组扩展事件。

数据库镜像终结点身份验证

要让同步正常运行，读取缩放可用性组中涉及的副本需要通过终结点进行身份验证。接下来的部分中涵盖了可用于此类身份验证的两种主要方案。

服务帐户

在所有次要副本加入同一个域的 Active Directory 环境中，SQL Server 可使用该服务帐户进行身份验证。必须在每个 SQL Server 实例上显式创建服务帐户的登录名：

CREATE LOGIN [<domain>\service account] FROM WINDOWS;

在次要副本可能未联接到 Active Directory 域的环境中，必须使用 SQL 身份验证。以下 Transact-SQL 脚本创建名为 dbm_login 的登录名和名为 dbm_user 的用户。使用强密码更新脚本。若要创建数据库镜像终结点用户，请在所有 SQL Server 实例上运行以下命令：

CREATE LOGIN dbm_login WITH PASSWORD = '**<1Sample_Strong_Password!@#>**';
CREATE USER dbm_user FOR LOGIN dbm_login;

证书身份验证

如果利用要求使用 SQL 身份验证进行身份验证的次要副本，请使用证书在镜像终结点之间进行身份验证。

以下 Transact-SQL 脚本创建主密钥和证书。然后备份证书，并使用私钥保护文件。使用强密码更新脚本。在主 SQL Server 实例上运行脚本以创建证书：

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '**<Master_Key_Password>**';
CREATE CERTIFICATE dbm_certificate WITH SUBJECT = 'dbm';
BACKUP CERTIFICATE dbm_certificate
   TO FILE = 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\dbm_certificate.cer'
   WITH PRIVATE KEY (
       FILE = 'c:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\dbm_certificate.pvk',
       ENCRYPTION BY PASSWORD = '**<Private_Key_Password>**'
   );

现在主 SQL Server 副本的证书位于 c:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\dbm_certificate.cer，私钥位于 c:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\dbm_certificate.pvk。将这两个文件复制到所有要托管可用性副本的服务器上的同一位置。

在每个次要副本上，请确保 SQL Server 的服务帐户有权访问该证书。

在辅助服务器上创建证书

以下 Transact-SQL 脚本根据在主 SQL Server 副本上创建的备份创建主密钥和证书。该命令还会授权用户访问证书。使用强密码更新脚本。解密密码与此前步骤中用于创建 .pvk 文件的密码相同。要创建证书，在所有次要副本上运行以下脚本：

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '**<Master_Key_Password>**';
CREATE CERTIFICATE dbm_certificate
    AUTHORIZATION dbm_user
    FROM FILE = 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\dbm_certificate.cer'
    WITH PRIVATE KEY (
        FILE = 'c:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\dbm_certificate.pvk',
        DECRYPTION BY PASSWORD = '**<Private_Key_Password>**'
    );

在所有副本上创建数据库镜像终结点

数据库镜像终结点使用传输控制协议 (TCP) 在参与数据库镜像会话或承载可用性副本的服务器实例之间发送和接收消息。数据库镜像终结点在唯一的 TCP 端口号上进行侦听。

以下 Transact-SQL 脚本为可用性组创建名为 Hadr_endpoint 的侦听终结点。该脚本启动终结点，并将连接权限授予之前步骤中创建的服务帐户或 SQL 登录名。在运行该脚本之前，替换 **< ... >** 之内的值。（可选）可包含 IP 地址 LISTENER_IP = (0.0.0.0)。侦听器 IP 地址必须是 IPv4 地址。还可以使用 0.0.0.0。

为所有 SQL Server 实例上的环境更新以下 Transact-SQL 脚本：

CREATE ENDPOINT [Hadr_endpoint]
    AS TCP (LISTENER_PORT = **<5022>**)
    FOR DATABASE_MIRRORING (
        ROLE = ALL,
        AUTHENTICATION = CERTIFICATE dbm_certificate,
        ENCRYPTION = REQUIRED ALGORITHM AES
    );
ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED;
GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [<service account or user>];

防火墙上的 TCP 端口必须对侦听器端口打开。

有关详细信息，请参阅数据库镜像终结点 (SQL Server)。

创建可用性组

创建可用性组。设置 CLUSTER_TYPE = NONE。此外，使用 FAILOVER_MODE = NONE 设置每个副本。运行分析或报告工作负荷的客户端应用程序可直接连接到辅助数据库。还可以创建一个只读路由列表。与主要副本的连接将读取连接请求循环转发到路由列表中的每个次要副本。

以下 Transact-SQL 脚本创建名为 ag1 的可用性组。该脚本使用 SEEDING_MODE = AUTOMATIC 配置可用性组副本。此设置会导致 SQL Server 在数据库添加到可用性组后自动在每个辅助服务器上创建数据库。

为环境更新以下脚本。将 <node1> 和 <node2> 值替换为托管副本的 SQL Server 实例的名称。使用为终结点设置的端口替换 <5022> 值。在主 SQL Server 副本上运行以下 Transact-SQL 脚本：

CREATE AVAILABILITY GROUP [ag1]
    WITH (CLUSTER_TYPE = NONE)
    FOR REPLICA ON
        N'<node1>' WITH (
            ENDPOINT_URL = N'tcp://<node1>:<5022>',
		    AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
		    FAILOVER_MODE = MANUAL,
		    SEEDING_MODE = AUTOMATIC,
                    SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)
		    ),
        N'<node2>' WITH (
		    ENDPOINT_URL = N'tcp://<node2>:<5022>',
		    AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
		    FAILOVER_MODE = MANUAL,
		    SEEDING_MODE = AUTOMATIC,
		    SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)
		    );

ALTER AVAILABILITY GROUP [ag1] GRANT CREATE ANY DATABASE;

将辅助 SQL Server 实例加入可用性组

以下 Transact-SQL 脚本将服务器加入名为 ag1 的可用性组。为环境更新脚本。若要加入可用性组，请在每个辅助 SQL Server 副本上运行以下 Transact-SQL 脚本：

ALTER AVAILABILITY GROUP [ag1] JOIN WITH (CLUSTER_TYPE = NONE);

ALTER AVAILABILITY GROUP [ag1] GRANT CREATE ANY DATABASE;

将数据库添加到可用性组

确保添加到可用性组的数据库处于完整恢复模式，并具有有效的日志备份。如果数据库是测试数据库或新建的数据库，请执行数据库备份。若要创建和备份名为 db1 的数据库，请在主 SQL Server 实例上运行以下 Transact-SQL 脚本：

CREATE DATABASE [db1];
ALTER DATABASE [db1] SET RECOVERY FULL;
BACKUP DATABASE [db1]
   TO DISK = N'c:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\db1.bak';

若要将名为 db1 的数据库添加到名为 ag1 的可用性组中，请在主 SQL Server 副本上运行以下 Transact-SQL 脚本：

ALTER AVAILABILITY GROUP [ag1] ADD DATABASE [db1];

验证是否已在辅助服务器上创建了数据库

若要查看是否创建并同步了 db1 数据库，请在每个次要 SQL Server 副本上运行以下查询：

SELECT * FROM sys.databases WHERE name = 'db1';
GO
SELECT DB_NAME(database_id) AS 'database', synchronization_state_desc FROM sys.dm_hadr_database_replica_states;

此可用性组不是高可用性配置。如果需要高可用性，请遵循为 Linux 上的 SQL Server 配置 AlwaysOn 可用性组或在 Windows 上创建和配置可用性组中的说明。

连接到只读次要副本

可根据以下两种方法之一连接到只读次要副本：

应用程序可直接连接到托管次要副本的 SQL Server 实例并查询数据库。有关详细信息，请参阅可读次要副本。
应用程序还可以使用只读路由，这需要一个侦听器。如果在不使用群集管理器的情况下部署读取缩放方案，你仍然可以创建一个侦听器，该侦听器指向当前主要副本的 IP 地址和与 SQL Server 所侦听端口相同的端口。故障转移后，需要重新创建指向新的主 IP 地址的侦听器。有关详细信息，请参阅只读路由。

故障转移读取缩放可用性组上的主要副本

每个可用性组仅有一个主要副本。主要副本允许读取和写入操作。若要更改哪个副本为主要副本，可进行故障转移。在典型的可用性组中，群集管理器自动执行故障转移过程。在群集类型为 NONE 的可用性组中，需手动执行故障转移过程。

在群集类型为 NONE 的可用性组中，有两种对主要副本进行故障转移的方法：

手动故障转移（无数据丢失）
强制手动故障转移（会丢失数据）

手动故障转移（无数据丢失）

主要副本可用时使用此方法，但你需要暂时或永久更改托管主要副本的实例。若要避免潜在的数据丢失，发出手动故障转移前，确保目标次要副本为最新版本。

手动故障转移（无数据丢失）：

将当前的主要副本和目标次要副本设置为 SYNCHRONOUS_COMMIT。

ALTER AVAILABILITY GROUP [AGRScale] 
     MODIFY REPLICA ON N'<node2>' 
     WITH (AVAILABILITY_MODE = SYNCHRONOUS_COMMIT);

若要确定已将活动事务提交到主要副本和至少一个同步次要副本，请运行以下查询：

SELECT ag.name, 
   drs.database_id, 
   drs.group_id, 
   drs.replica_id, 
   drs.synchronization_state_desc, 
   ag.sequence_number
FROM sys.dm_hadr_database_replica_states drs, sys.availability_groups ag
WHERE drs.group_id = ag.group_id;

当 synchronization_state_desc 为 SYNCHRONIZED 时，会同步次要副本。

将 REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT 更新为 1。

以下脚本在名为 ag1 的可用性组上将 REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT 设置为 1。运行以下脚本前，将 ag1 替换为可用性组的名称：
```
ALTER AVAILABILITY GROUP [AGRScale] 
     SET (REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT = 1);
```
此设置可确保将每个活动事务提交到主要副本和至少一个同步次要副本。

注意

此设置并非特定于故障转移，应根据环境要求进行设置。
将主要副本和不参与故障转移的次要副本设置为脱机，以便为角色更改做好准备：
```
ALTER AVAILABILITY GROUP [AGRScale] OFFLINE
```

将目标次要副本升级为主要副本。

ALTER AVAILABILITY GROUP AGRScale FORCE_FAILOVER_ALLOW_DATA_LOSS;

将旧的主要和其他次要副本的角色更新为 SECONDARY，在托管旧的主要副本的 SQL Server 实例上运行以下命令：
```
ALTER AVAILABILITY GROUP [AGRScale] 
     SET (ROLE = SECONDARY); 
```
注意

若要删除可用性组，请使用删除可用性组。对于使用群集类型为 NONE 或 EXTERNAL 创建的可用性组，请对可用性组的所有副本执行该命令。
恢复数据移动，为托管主要副本的 SQL Server 实例上的可用性组中的每个数据库运行以下命令：
```
ALTER DATABASE [db1]
     SET HADR RESUME
```
重新创建出于读取缩放目的创建且不受群集管理器管理的所有侦听器。如果原始侦听器指向旧的主要副本，请将其删除，然后将其重新创建为指向新的主要副本。

强制手动故障转移（会丢失数据）

如果主要副本不可用且无法立即恢复，则需要强制执行向次要副本的故障转移（存在数据丢失）。但是，如果原始主要副本在故障转移后恢复，它将承担主要角色。若要避免每个副本处于不同的状态，在存在数据丢失的情况下进行强制故障转移后，从可用性组中删除原始主要副本。原始主要副本重新联机后，从该副本完全删除该可用性组。

若要强制执行从主要副本 N1 到次要副本 N2 的手动故障转移（存在数据丢失），请执行以下步骤：

在次要副本 (N2) 上，启动强制故障转移：

ALTER AVAILABILITY GROUP [AGRScale] FORCE_FAILOVER_ALLOW_DATA_LOSS;

在新的主要副本 (N2) 上，删除原始主要副本 (N1)：
```
ALTER AVAILABILITY GROUP [AGRScale]
REMOVE REPLICA ON N'N1';
```
验证所有的应用程序流量均指向侦听器和/或新的主要副本。
如果原始主要副本 (N1) 进入联机状态，则立即在原始主要副本 (N1) 上使可用性组 AGRScale 脱机：
```
ALTER AVAILABILITY GROUP [AGRScale] OFFLINE
```
如果存在数据或未同步的更改，则通过备份或其他可满足业务需求的数据复制选项来保存这些数据。
接下来，从原始主要副本 (N1) 中删除可用性组：
```
DROP AVAILABILITY GROUP [AGRScale];
```
删除原始主要副本 (N1) 上的可用性组数据库：
```
USE [master]
GO
DROP DATABASE [AGDBRScale]
GO
```
（可选）如果需要，现可将 N1 作为新的次要副本添加回可用性组 AGRScale 中。

请注意，如果使用某个侦听器进行连接，则在执行故障转移后将需要重新创建该侦听器。

通过

为 AlwaysOn 可用性组配置读取缩放

先决条件

启用 AlwaysOn 可用性组，并重启 mssql-server

启用 AlwaysOn_health 事件会话

数据库镜像终结点身份验证

服务帐户

证书身份验证

在辅助服务器上创建证书

在所有副本上创建数据库镜像终结点

创建可用性组

将辅助 SQL Server 实例加入可用性组

将数据库添加到可用性组

验证是否已在辅助服务器上创建了数据库

连接到只读次要副本

故障转移读取缩放可用性组上的主要副本

手动故障转移（无数据丢失）

强制手动故障转移（会丢失数据）

后续步骤

反馈

其他资源

通过

为 AlwaysOn 可用性组配置读取缩放

先决条件

启用 AlwaysOn 可用性组，并重启 mssql-server

启用 AlwaysOn_health 事件会话

数据库镜像终结点身份验证

服务帐户

SQL 登录身份验证

证书身份验证

在辅助服务器上创建证书

在所有副本上创建数据库镜像终结点

创建可用性组

将辅助 SQL Server 实例加入可用性组

将数据库添加到可用性组

验证是否已在辅助服务器上创建了数据库

连接到只读次要副本

故障转移读取缩放可用性组上的主要副本

手动故障转移（无数据丢失）

强制手动故障转移（会丢失数据）

后续步骤

反馈

其他资源