在 Linux 上配置 SQL Server 可用性组用于读取缩放

项目
06/28/2024

适用于： SQL Server - Linux

本文介绍如何在不使用群集管理器的情况下在 Linux 上创建 SQL Server Always On 可用性组 (AG)。此体系结构仅提供读取缩放。它不提供高可用性。

存在两种类型的 AG 体系结构。高可用性体系结构利用群集管理器改善业务连续性。若要创建高可用性体系结构，请参阅配置 SQL Server Always On 可用性组以在 Linux 上实现高可用性。

设置有 CLUSTER_TYPE = NONE 的可用性组可包括不同操作系统平台上托管的副本。它无法支持高可用性。

先决条件

创建可用性组前，需要：

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

在 Linux 上，必须先创建可用性组，然后再将其添加为群集管理的群集资源。本文档举例说明了如何创建可用性组。

更新每个主机的计算机名。

每个 SQL Server 实例名称必须：
- 15 个字符或更少。
- 在网络中唯一。
若要设置计算机名，请编辑 /etc/hostname。以下脚本可使用 vi 编辑 /etc/hostname：
```
sudo vi /etc/hostname
```
配置主机文件。

注意

如果在 DNS 服务器中使用主机 IP 地址注册主机名，则无需执行以下步骤。验证要作为可用性组配置的一部分的所有节点是否可以互相通信。（对主机名的 ping 应使用相应的 IP 地址进行回复。）此外，请确保 /etc/hosts 文件不包含将节点主机名映射到 localhost IP 地址 127.0.0.1 的记录。

每个服务器上的主机文件包含将加入可用性组的所有服务器的 IP 地址和名称。

以下命令将返回当前服务器的 IP 地址：
```
sudo ip addr show
```
更新 /etc/hosts。以下脚本可使用 vi 编辑 /etc/hosts：
```
sudo vi /etc/hosts
```
下面的示例演示了 node1 上的 /etc/hosts，并补充了 node1、node2 和 node3。在此示例中，node1 指托管主要副本的服务器，node2 和 node3 指托管次要副本的服务器。
```
127.0.0.1    localhost localhost4 localhost4.localdomain4
::1          localhost localhost6 localhost6.localdomain6
10.128.18.12 node1
10.128.16.77 node2
10.128.15.33 node3
```

安装 SQL Server

安装 SQL Server。以下链接指向适用于各种分发的 SQL Server 安装说明：

启用 Always On 可用性组

在托管 SQL Server 实例的每个节点上启用 Always On 可用性组，然后启动 mssql-server。运行以下脚本：

sudo /opt/mssql/bin/mssql-conf set hadr.hadrenabled 1
sudo systemctl restart mssql-server

启用 AlwaysOn_health 事件会话

可选择性地启用扩展事件 (XE)，以便在对可用性组进行故障排除时帮助诊断根本原因。在每个 SQL Server 实例上运行以下命令：

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

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

创建证书

Linux 上的 SQL Server 服务使用证书验证镜像终结点之间的通信。

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

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<Master_Key_Password>';
CREATE CERTIFICATE dbm_certificate WITH SUBJECT = 'dbm';
BACKUP CERTIFICATE dbm_certificate
    TO FILE = '/var/opt/mssql/data/dbm_certificate.cer'
    WITH PRIVATE KEY (
        FILE = '/var/opt/mssql/data/dbm_certificate.pvk',
        ENCRYPTION BY PASSWORD = '<Private_Key_Password>'
    );

现在主 SQL Server 副本的证书位于 /var/opt/mssql/data/dbm_certificate.cer，私钥位于 var/opt/mssql/data/dbm_certificate.pvk。将这两个文件复制到所有要托管可用性副本的服务器上的同一位置。使用 mssql 用户或为 mssql 用户授予访问这些文件的权限。

例如，在源服务器上，以下命令可将文件复制到目标计算机。将 <node2> 值替换为要托管副本的 SQL Server 实例的名称。

cd /var/opt/mssql/data
scp dbm_certificate.* root@<node2>:/var/opt/mssql/data/

在每个目标服务器上，为 mssql 用户授予访问证书的权限。

cd /var/opt/mssql/data
chown mssql:mssql dbm_certificate.*

在辅助服务器上创建证书

以下 Transact-SQL 脚本根据在主 SQL Server 副本上创建的备份创建主密钥和证书。使用强密码更新脚本。解密密码与在此前的步骤中创建 .pvk 文件使用的密码相同。若要创建证书，请在所有辅助服务器上运行以下脚本：

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<Master_Key_Password>';
CREATE CERTIFICATE dbm_certificate
    FROM FILE = '/var/opt/mssql/data/dbm_certificate.cer'
    WITH PRIVATE KEY (
        FILE = '/var/opt/mssql/data/dbm_certificate.pvk',
        DECRYPTION BY PASSWORD = '<Private_Key_Password>'
    );

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

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

以下 Transact-SQL 脚本为可用性组创建名为 Hadr_endpoint 的侦听终结点。它启动终结点，并向创建的证书授予连接权限。在运行该脚本之前，替换 < ... > 之内的值。（可选）可以包含 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;

注意

如果在一个节点上使用 SQL Server Express Edition 托管仅限配置的副本，则 ROLE 的唯一有效值为 WITNESS。在 SQL Server Express Edition 上运行以下脚本：

CREATE ENDPOINT [Hadr_endpoint]
    AS TCP (LISTENER_PORT = 5022)
    FOR DATABASE_MIRRORING (
        ROLE = WITNESS,
        AUTHENTICATION = CERTIFICATE dbm_certificate,
        ENCRYPTION = REQUIRED ALGORITHM AES
    );

ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED;

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

重要

对于 SQL Server 2017 (14.x)，数据库镜像终结点支持的唯一身份验证方法是 CERTIFICATE。 WINDOWS 选项不可用。

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

创建可用性组

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

以下 Transact-SQL 脚本创建名为 ag1 的 AG。脚本使用 SEEDING_MODE = AUTOMATIC 配置 AG 副本。此设置会导致 SQL Server 在数据库添加到 AG 后自动在每个辅助服务器上创建数据库。为环境更新以下脚本。将 <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 实例加入 AG

以下 Transact-SQL 脚本将服务器加入名为 ag1 的 AG。为环境更新脚本。在每个辅助 SQL Server 副本上运行以下 Transact-SQL 脚本，从而加入 AG：

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

ALTER AVAILABILITY GROUP [ag1] GRANT CREATE ANY DATABASE;

将数据库添加到可用性组

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

CREATE DATABASE [db1];
GO

ALTER DATABASE [db1]
SET RECOVERY FULL;
GO

BACKUP DATABASE [db1]
TO DISK = N'/var/opt/mssql/data/db1.bak';

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

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

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

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

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;
GO

此 AG 不是高可用性配置。如果需要高可用性，请遵循配置 SQL Server Always On 可用性组以在 Linux 上实现高可用性中的说明。具体而言，用 CLUSTER_TYPE=WSFC（在 Windows 中）或 CLUSTER_TYPE=EXTERNAL（在 Linux 中）创建 AG。然后通过在 Windows 上使用 Windows Server 故障转移群集或在 Linux 上使用 Pacemaker 来集成一个群集管理器。

连接到只读次要副本

连接只读次要副本有两种方法。应用程序可直接连接到托管次要副本的 SQL Server 实例并查询数据库。它们还可以使用只读路由，这需要一个侦听器。

故障转移读取缩放 AG 上的主要副本

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

通过

在 Linux 上配置 SQL Server 可用性组用于读取缩放

先决条件

安装 SQL Server

启用 Always On 可用性组

启用 AlwaysOn_health 事件会话

创建证书

在辅助服务器上创建证书

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

创建可用性组

将辅助 SQL Server 实例加入 AG

将数据库添加到可用性组

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

连接到只读次要副本

故障转移读取缩放 AG 上的主要副本

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

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

反馈

其他资源

通过

在 Linux 上配置 SQL Server 可用性组用于读取缩放

先决条件

安装 SQL Server

启用 Always On 可用性组

启用 AlwaysOn_health 事件会话

创建证书

在辅助服务器上创建证书

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

创建可用性组

将辅助 SQL Server 实例加入 AG

将数据库添加到可用性组

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

连接到只读次要副本

故障转移读取缩放 AG 上的主要副本

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

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

相关内容

反馈

其他资源