大网数据广告活动
数据库与应用安全

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

· 52CE 我爱测

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

适用对象:yesSQL ServeryesAzure SQL 数据库noAzure SQL 数据仓库no并行数据仓库

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

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

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

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

必备条件

创建可用性组前,需要:

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

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

Note: 以下命令利用 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;

SQL 登录身份验证

在次要副本可能未联接到 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 = '/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 的侦听终结点。 该脚本启动终结点,并将连接权限授予之前步骤中创建的服务帐户或 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 DATA_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'/var/opt/mssql/data/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 实例并查询数据库。 有关详细信息,请参阅可读次要副本。
  • 应用程序还可以使用只读路由,这需要一个侦听器。 有关详细信息,请参阅只读路由。

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

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

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

  • 强制手动故障转移(会丢失数据)
  • 手动故障转移(无数据丢失)

强制手动故障转移(会丢失数据)

当主要副本不可用且无法恢复时,请使用此方法。

若要强制执行会丢失数据的故障转移,请连接托管目标次要副本的 SQL Server 实例,然后运行以下命令:

ALTER AVAILABILITY GROUP [ag1] FORCE_FAILOVER_ALLOW_DATA_LOSS;

当以前的主要副本恢复时,它还将扮演主角色。 若要确保以前的主要副本转换为辅助角色,请在之前的主要副本上运行以下命令。

ALTER AVAILABILITY GROUP [ag1]  SET (ROLE = SECONDARY);

手动故障转移(无数据丢失)

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

手动故障转移(无数据丢失):

  1. 使目标次要副本 SYNCHRONOUS_COMMIT。

    ALTER AVAILABILITY GROUP [ag1] 
         MODIFY REPLICA ON N'<node2>' 
         WITH (AVAILABILITY_MODE = SYNCHRONOUS_COMMIT);
    
  2. 若要确定已将活动事务提交到主要副本和至少一个同步次要副本,请运行以下查询:

    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 时,会同步次要副本。

  3. 将 REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT 更新为 1。

    以下脚本在名为 ag1 的可用性组上将 REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT 设置为 1。 运行以下脚本前,将 ag1 替换为可用性组的名称:

    ALTER AVAILABILITY GROUP [ag1] 
         SET REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT = 1;
    

    此设置可确保将每个活动事务提交到主要副本和至少一个同步次要副本。

  4. 将主要副本降级为次要副本。 将主要副本降级后,该副本为只读。 若要将角色更新为 SECONDARY,在托管主要副本的 SQL Server 实例上运行以下命令:

    ALTER AVAILABILITY GROUP [ag1] 
         SET (ROLE = SECONDARY); 
    
  5. 将目标次要副本升级为主要副本。

    ALTER AVAILABILITY GROUP ag1 FORCE_FAILOVER_ALLOW_DATA_LOSS; 
    

    NOTE 若要删除可用性组,请使用删除可用性组。 对于使用群集类型为 NONE 或 EXTERNAL 创建的可用性组,请对可用性组的所有副本执行该命令。

后续步骤

历史资料。此文对应 2018-11-30 的中文文档快照,适用产品版本和命令行为以原文为准。

来源与署名:Microsoft 和文档贡献者(原始作者元数据 MashaMSFT)。固定版本原文。文档采用 CC BY 4.0,示例代码采用 MIT 授权。本站展开固定版本仓库内的引用内容,将 Markdown 转为 HTML,规范化链接,移除外部媒体、发布器占位标签和导航标记。

资料来源与适用范围

资料来源
Microsoft / SQL Server 中文文档历史快照
适用范围
历史中文快照 2018-11-30;详见原文产品版本;文档日期 05/24/2018
内容语言
中文;命令、路径和错误标识保留技术原文
本地收录日期
2026-11-16
授权说明
CC BY 4.0(文档)/ MIT(示例代码)

正文与所需资源由本站本地存储。查看来源及更新记录 ↗

请按适用版本和实际日志确认原因;本站没有对每一种系统或设备逐项执行文中的操作。