简介针对SQL Server 2016 Always On可用性组运维场景该docx操作文档面向数据库管理员与运维工程师解决已有集群需新增数据库节点时的实操落地问题。文档从环境角色表切入明确主副本、辅助副本及新增副本的IP、主机名与读写策略再逐步演示新节点SQL Server安装、故障转移群集功能启用、hosts文件同步修改、加入Windows群集、开启Always On、最后向可用性组添加副本等关键步骤每一步都有详细界面截图且步骤经过真实环境验证能有效避开群集配置中常见的遗漏点。资源压缩包为1.92MB共1个docx文件图文编排紧凑、操作顺序清晰既适合DBA按文档逐项执行也可作为同类高可用组扩展任务的参考模板。已有101人学习下载适合正在扩展Always On集群或维护高可用组的DBA阅读。1. 在 SQL Server 2016 Always On 集群里新增数据库节点先分清“加库”和“加副本”“在 sql server 2016 always on集群里新增一个数据库节点”这句话我在生产环境被问过不下十次。每次都要先反问一句你是要把一个新数据库纳入可用性组保护还是要新加一台装 SQL Server 2016 的服务器进集群当辅助副本这两个操作都叫“新增节点”前置条件和踩坑点完全不同。把新业务库纳入 AG 是日常运维一条 ALTER 语句加一次备份还原就能收工而新加一台副本服务器要动 Windows 故障转移集群WSFC、AG 端点、同步模式任何一环漏了都会让集群在故障转移时翻车。本文按“加库”和“加副本”两条路线拆开讲命令都可复现坑位也都标清楚。2. 动手前的检查清单数据库状态、恢复模式与集群侧先决条件新增数据库进 Always On 之前别急着敲 ALTER 语句。SQL Server 2016 的可用性组对数据库有硬性门槛集群侧也有必须先确认的状态。我见过太多人在数据库还是 SIMPLE 恢复模式下直接执行 ADD DATABASE报错之后一脸懵。这里的检查项不多但每一项都直接决定后续同步能不能跑起来。2.1 数据库进 AG 的三个硬门槛恢复模式、备份、日志链先说门槛这是新手第一处翻车的地方恢复模式必须是 FULL。Always On 同步本质是靠日志重放SIMPLE 模式下日志会被 checkpoint 截断辅助副本根本跟不上。切换语句USE [master]; GO ALTER DATABASE [YourDB] SET RECOVERY FULL; GO这一句执行后会产生一个日志链断点别紧接着就做 ADD DATABASE。先做一次完整备份把日志链的起点建立起来。注意RECOVERY FULL 切换后如果此前从未做过完整备份辅助副本初始化仍会失败。Always On 要求数据库至少存在一个完整备份它是同步初始化的基线。至少存在一份完整备份。辅助副本要么靠“备份还原”初始化要么靠 SQL Server 2016 新增的自动种子化automatic seeding把数据和日志推过去两种方式都以主副本完整备份的 LSN 为起点。没有完整备份日志链就是断的辅助副本不知道从哪个点开始追。数据库必须在线且没有被其他 AG 占用。一个数据库同一时间只能属于一个可用性组。如果目标库已经在另一个 AG 里要先移除才能加入新组。还有一种常见误用辅助副本上存在同名库且状态是 ONLINE 或 RESTORING会在 JOIN 时报冲突。2.2 集群与副本侧检查WSFC 节点状态、角色与端点Always On 构建在 Windows Server Failover Clustering 之上SQL Server 2016 的 AG 依赖 WSFC 提供故障检测和仲裁。加库之前先确认三件事所有 WSFC 节点在线仲裁配置正常。用 PowerShell 或故障转移集群管理器查看Get-ClusterNode | Select-Object Name, State, NodeWeightState 必须全部为 UpNodeWeight 不能被清零成 0否则节点失去仲裁投票权集群可能在网络抖动时整体挂掉。仲裁出问题的典型症状是 AG 显示 RESOLVING所有库一起不健康。确认当前实例是主副本。ADD DATABASE、ADD REPLICA 这类变更语句都只能发在主副本上在辅助副本上执行会直接报“当前副本不是主副本”。AG 端点存在且已启动。SQL Server 2016 的 AG 端点默认监听 TCP 5022用于副本间的 HADR 会话。端点异常时数据库加进去之后同步状态会卡在 INITIALIZING 或 SYNCHRONIZING 不动。这在从旧版本升级上来的环境里尤其常见端点可能启用了老旧的加密算法或过期证书。2.3 用一条 T-SQL 把现有 AG 拓扑和同步基线摸清楚动手前花一分钟跑下面这条查询把 AG 名称、副本清单、每个数据库的同步状态全拉出来作为操作基线SELECT ag.name AS ag_name, ar.replica_server_name, CASE WHEN ars.is_primary 1 THEN PRIMARY ELSE SECONDARY END AS role_desc, ISNULL(DB_NAME(dbrs.database_id), NO DB) AS db_name, dbrs.synchronization_state_desc, dbrs.synchronization_health_desc, dbrs.last_hardened_lsn, dbrs.last_commit_time FROM sys.availability_groups ag JOIN sys.availability_replicas ar ON ag.group_id ar.group_id LEFT JOIN sys.dm_hadr_availability_replica_states ars ON ar.replica_id ars.replica_id LEFT JOIN sys.dm_hadr_database_replica_states dbrs ON ar.replica_id dbrs.replica_id ORDER BY ag.name, ar.replica_server_name, DB_NAME(dbrs.database_id); GO这段查询的核心是 sys.dm_hadr_database_replica_states它是排查 Always On 同步问题最常用的 DMV。last_hardened_lsn 表示已经持久化到本地副本的日志 LSN辅助副本追上主副本时这个值和主库一致synchronization_state_desc 通常表现为 NOT SYNCHRONIZING、SYNCHRONIZING、SYNCHRONIZED 三态。执行后先确认当前实例角色是 PRIMARY 再往下走。还要留意有没有 synchronization_health_desc 为 FAILED 的库如果有说明日志流已经在中断边缘先把老问题清掉再添加新库否则新库会被一起拖下水。3. 往现有可用性组里新增数据库T-SQL 两条路与向导操作库和集群侧检查都通过后开始正式操作。加数据库的方式按数据量大小有两种主流选择自动种子化和备份还原。SQL Server 2016 引入了自动种子化但很多从 2012/2014 迁移上来的团队还是沿用老一套备份还原两种方式各有适用场景。3.1 直接 ADD DATABASE走自动种子化的最小命令如果 AG 里所有副本都启用了自动种子化SEEDING_MODE AUTOMATIC加库只需在主副本上执行一行USE [master]; GO ALTER AVAILABILITY GROUP [YourAG] ADD DATABASE [YourDB]; GO执行后SQL Server 2016 会自动在主副本上发起一次完整备份通过 AG 端点把备份流推送到各辅助副本在每个辅助副本上完成还原和 JOIN全程不需要人工介入。这是 SQL Server 2016 里“新增数据库节点”最省事的一条路。参数说明ADD DATABASE 后面的库名必须是主副本上存在的数据库如果某个辅助副本上碰巧已有同名库自动种子化不会覆盖它而是直接报错。此时需要先到对应辅助副本上把同名库删掉或改名再重新触发种子化。自动种子化适合几十 GB 以内的中小型库对几百 GB 甚至上 TB 的库它的超时机制和日志增长速度会让你头疼建议直接看 3.2 的备份还原路线。3.2 先备份再还原大数据库加进 AG 的稳妥做法大库进 AG生产环境的主流做法是“手动备份 - 还原 - JOIN”。这招在 2016 上依然适用而且比自动种子化更可控。整个过程分三段。第一段在主副本上做完整备份BACKUP DATABASE [YourDB] TO DISK ND:\Backup\YourDB_Full.bak WITH INIT, COMPRESSION, CHECKSUM; GOCHECKSUM 强烈建议加备份损坏在还原或同步阶段才暴露是最糟糕的场景COMPRESSION 能省磁盘和网络传输时间代价是备份阶段多耗一些 CPU。第二段在每一个辅助副本上还原必须加 NORECOVERYRESTORE DATABASE [YourDB] FROM DISK ND:\Backup\YourDB_Full.bak WITH MOVE YourDB TO ND:\Data\YourDB.mdf, MOVE YourDB_log TO ND:\Log\YourDB_log.ldf, REPLACE, NORECOVERY; GOMOVE 子句把数据文件和日志文件放到辅助副本实例期望的路径上路径不匹配会还原失败这是最常见的低级错误。REPLACE 用于覆盖辅助副本上可能残留的同名库。NORECOVERY 是重点它让数据库停在 RESTORING 状态这是后续 JOIN 到 AG 的前提。注意如果辅助副本上有多个库要加入同一个 AG备份还原可以并行做。但 JOIN 动作要在所有副本上按顺序完成且主副本要先完成 ADD DATABASE。第三段分别执行 ADD 和 JOIN。先在主副本上执行ALTER AVAILABILITY GROUP [YourAG] ADD DATABASE [YourDB]; GO然后在每个辅助副本实例上执行USE [master]; GO ALTER DATABASE [YourDB] SET HADR AVAILABILITY GROUP [YourAG]; GOALTER DATABASE ... SET HADR AVAILABILITY GROUP 是把处于 RESTORING 状态的数据库“挂”到可用性组上的核心语句。执行成功后数据库会自动从 RESTORING 变为 ONLINE开始追赶主副本的日志。如果漏了这步主副本上数据库虽然加入了 AG辅助副本上没有对应成员AG 会报告“数据库在部分副本上缺失”。3.3 用 SSMS 向导添加数据库与它的隐藏坑不带命令行习惯的团队习惯用 SSMS 的 Always On 高可用向导。右键 AG 名称 → 添加数据库 → 勾选目标库 → 下一步。向导会自动检查恢复模式和备份条件然后走备份还原流程把库带进 AG。但我几乎不用向导做这件事原因是它把太多细节藏进了黑匣子。向导默认会创建数据库镜像端点连接、自动做备份还原失败时给到操作者的错误信息往往很含蓄排查路径反而更长。而且向导对已有 AG 不支持“仅自动种子化”这种精细控制总会走备份还原流程。真正适合用向导的场景是AG 刚搭建、副本数很少、库也都是几十 GB 以内想快速验证流程。生产上我一般还是用 T-SQL 脚本因为每条语句的进度、报错和 LSN 位置都清清楚楚出问题方便定位到具体环节。4. 新增一个副本节点进集群从 Windows 故障转移集群到可用性组副本如果标题里的“数据库节点”指的是新加一台服务器作为 AG 的辅助副本操作链路会长不少。最常见的误解是以为装好 SQL Server 就能 JOIN 进 AG。实际上新服务器必须先成为 WSFC 的成员节点才能谈 AG 副本。4.1 新节点加入 WSFC 与 SQL Server 2016 的 Always On 开关新服务器加入已有集群用故障转移集群管理器或 PowerShell 都行Add-ClusterNode -Cluster SQL-AG-CLUSTER -Name SQLNode03执行成功后到故障转移集群管理器里确认 SQLNode03 显示为 Up仲裁配置仍是原有模式。如果这台服务器此前加入过别的集群Add-ClusterNode 会报错需要先用 Remove-ClusterNode 清理旧集群元数据。拿退役副本机器做新节点时经常撞上这个残留问题。然后安装 SQL Server 2016。安装类型不需要选“故障转移集群实例”AG 不是 FCI它只是 WSFC 之上的一层可用性组。装完之后打开 SQL Server 配置管理器进入 SQL Server 服务的属性页切到“AlwaysOn 高可用性”标签勾选“启用 AlwaysOn 可用性组”重启 SQL Server 服务。这个开关经常被漏掉。服务没启用 AlwaysOn后面 ADD REPLICA 时会报“实例未启用 AlwaysOn”之类错误。运气更差的情况是命令能执行但副本角色起不来排查半天发现开关没开。另外新节点上的 AG 端点也要提前建好否则数据流起不来端点创建脚本见 5.4。4.2 在可用性组里 ADD REPLICA参数怎么定新节点准备好后在主副本上添加副本。SQL Server 2016 的完整语法如下ALTER AVAILABILITY GROUP [YourAG] ADD REPLICA ON NSQLNode03 WITH ( ENDPOINT_URL NTCP://SQLNode03:5022, AVAILABILITY_MODE SYNCHRONOUS_COMMIT, FAILOVER_MODE AUTOMATIC, SEEDING_MODE AUTOMATIC, BACKUP_PRIORITY 50, SECONDARY_ROLE(ALLOW_CONNECTIONS NO) ); GO参数逐个说。ENDPOINT_URL 指向新节点上 AG 端点的地址和端口5022 是默认端口如果新实例端点用了别的端口这里要跟着改AVAILABILITY_MODE 决定同步模式SYNCHRONOUS_COMMIT 表示事务要在主副本和该副本都提交才算成功数据零丢失但会增加主库提交延迟ASYNCHRONOUS_COMMIT 适合跨机房容灾允许数据延迟FAILOVER_MODE 配 AUTOMATIC 的前提是该副本是同步提交模式配合 WSFC 仲裁才能实现自动故障转移SEEDING_MODE 设 AUTOMATIC 表示新副本上的数据库可以自动种子化BACKUP_PRIORITY 决定该副本参与备份作业的优先程度数字越大越优先。注意故障转移模式是逐副本设置的。如果 AG 原本的主副本是 MANUAL把新副本设成 AUTOMATIC 不会改变整体策略要确认所有需要自动故障转移的同步副本都配成 AUTOMATIC。ADD REPLICA 只是把副本元数据写进 AG。如果这个 AG 配置了可用性组监听器新副本加进来通常不用改监听器WSFC 会自动把新节点纳入可用性组资源组。但如果你配置了只读路由要记得为新副本指定只读路由 URL否则客户端按只读副本连接时找不到它ALTER AVAILABILITY GROUP [YourAG] MODIFY REPLICA ON NSQLNode03 WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL NTCP://SQLNode03:1433)); GO4.3 新副本上的数据库 JOIN 与同步状态确认新副本加进来后AG 会为它准备现有库。如果副本配了自动种子化不需要手动还原只需在新副本上执行 JOINALTER DATABASE [YourDB] SET HADR AVAILABILITY GROUP [YourAG]; GO这条语句配合自动种子化SQL Server 2016 会通过 AG 端点把主副本的备份流推送到新副本自动完成还原并把库带进 ONLINE。如果没开自动种子化就按 3.2 的备份还原流程在新副本上做一遍再执行同样的 SET HADR JOIN。还原时路径必须与该副本实例的数据目录匹配MOVE 子句不可省略。全部完成后跑一条针对新副本的同步检查SELECT DB_NAME(database_id) AS db_name, synchronization_state_desc, synchronization_health_desc, last_hardened_lsn, DATEDIFF(second, last_commit_time, SYSUTCDATETIME()) AS commit_lag_sec FROM sys.dm_hadr_database_replica_states WHERE replica_id (SELECT replica_id FROM sys.availability_replicas WHERE replica_server_name NSQLNode03); GOcommit_lag_sec 接近 0说明新副本已经追上主副本。对同步提交模式确认数据一致后再考虑把 FAILOVER_MODE 从 MANUAL 改成 AUTOMATIC。这个顺序不能反否则等于把故障转移的安全垫提前抽掉。5. 新增库/节点后翻车现场排查四个高频问题与解决顺序加库和加副本的坑我按出现频率排了个序。每一条都按“现象 → 原因 → 解决”写后面对着症状查就行。以下是我在多个生产集群里攒下的血泪经验。5.1 同步状态卡在 INITIALIZING或反复 SYNCHRONIZING 又倒回现象执行完 ADD DATABASE 后查 sys.dm_hadr_database_replica_states新库在辅助副本上一直 INITIALIZING几个小时不动或者进度走到 80% 突然掉回 20%反复重来。原因自动种子化场景下最常见的是网络带宽瓶颈和 AG 端点连接不稳定备份还原场景下多半是还原完成后没有及时 JOIN日志在辅助副本上累积导致追赶变慢。还有一种玄学来源是安全软件扫描数据目录导致 IO 卡顿。解决先看 SQL Server 错误日志里有没有 AG 端点连接超时TCP 5022的报错有则查防火墙、端口占用和网络抖动。网络没问题再看主副本上备份进程是否被其他作业抢占。对大库超时可以直接调大EXEC sp_configure show advanced options, 1; RECONFIGURE; EXEC sp_configure hadr auto seed timeout, 60; RECONFIGURE;hadr auto seed timeout 默认 15 分钟大库种子化很容易超时。调大后重新执行一次 ADD DATABASE 即可。5.2 辅助副本上库处于 RESTORING 状态JOIN 报错现象备份还原流程走完执行 ALTER DATABASE [YourDB] SET HADR AVAILABILITY GROUP [YourAG]报错信息里出现“数据库正在还原无法启用可用性组”之类的提示。原因还原时没有带 NORECOVERY或者还原后又误执行了 RECOVERY 把数据库带到了 ONLINE。JOIN 动作要求数据库必须停在 RESTORING 状态处于 ONLINE 的普通数据库无法直接挂上 AG。解决这不是数据损坏重新覆盖还原一次即可RESTORE DATABASE [YourDB] FROM DISK ND:\Backup\YourDB_Full.bak WITH REPLACE, NORECOVERY; GO还原后确认状态SELECT name, state_desc FROM sys.databases WHERE name YourDB; GO输出必须是 RESTORING然后再执行 SET HADR。别担心覆盖还原会丢数据备份来自主副本辅助副本本来就是要被覆盖的。5.3 大库自动种子化超时失败日志文件在辅助副本上疯涨现象几百 GB 的库用 SEEDING_MODE AUTOMATIC 加进 AG种子化反复失败辅助副本的日志文件在几分钟内涨了几十 GB。原因自动种子化走的是日志流推送种子化期间主副本上有大量业务写入时会拖慢进度失败重试还会让辅助副本日志持续累积。这是 2016 自动种子化对超大库不友好的老毛病。解决大库不要走自动种子化切回备份还原手动流程。如果已经走了先移除这个库成员ALTER AVAILABILITY GROUP [YourAG] REMOVE DATABASE [YourDB]; GO然后在辅助副本上把处于 RESTORING 的库删掉释放被占用的日志空间再走 3.2 的备份还原流程。删库前确认该库在 AG 里至少还有一个健康副本否则等于让 AG 失去一个有数据的节点。5.4 新副本端点连不上副本一直 NOT SYNCHRONIZING现象ADD REPLICA 执行成功但新副本一直 NOT SYNCHRONIZING错误日志里出现“数据库镜像端点无法连接到 SQLNode03:5022”或证书验证失败。原因三个常见来源防火墙没放行 5022 端口新节点上 AG 端点没创建或状态是 STOPPED节点间系统时钟偏差大、证书不一致导致握手失败。解决先测端口通不通telnet SQLNode03 5022连不上就查防火墙和端点状态SELECT type_desc, port, state_desc FROM sys.tcp_endpoints WHERE type 2; GOstate_desc 必须是 STARTED。端点不存在时创建端口要和 ADD REPLICA 的 ENDPOINT_URL 一致CREATE ENDPOINT Hadr_endpoint STATE STARTED AS TCP (LISTENER_PORT 5022) FOR DATABASE_MIRRORING (ROLE ALL, ENCRYPTION REQUIRED ALGORITHM AES); GO证书问题常见于新旧节点安装时间差大、时钟漂移的环境。检查节点间时间同步状态并确保所有副本端点加密算法一致。跨域节点或工作组节点最容易踩这个坑我强烈建议新节点进集群前先统一校对时钟。6. 新增节点后的日常维护一张监控视图盯住同步状态新增库或副本后的第二天才是真正考验的开始。我的习惯是给 AG 建一张常驻监控视图随时能看到每个库在每个副本上的同步情况IF OBJECT_ID(Ndbo.v_AGHealth, NV) IS NOT NULL DROP VIEW dbo.v_AGHealth; GO CREATE VIEW dbo.v_AGHealth AS SELECT ag.name AS ag_name, ar.replica_server_name, DB_NAME(drs.database_id) AS db_name, drs.synchronization_state_desc, drs.synchronization_health_desc, drs.redo_queue_size AS kbytes_pending_redo, drs.log_send_queue_size AS kbytes_pending_log, DATEDIFF(second, drs.last_commit_time, SYSUTCDATETIME()) AS commit_lag_sec FROM sys.availability_groups ag JOIN sys.availability_replicas ar ON ag.group_id ar.group_id JOIN sys.dm_hadr_database_replica_states drs ON ar.replica_id drs.replica_id; GOredo_queue_size 和 log_send_queue_size 的单位是 KB是判断同步压力的第一手指标。这两个值持续大于 0 说明辅助副本在拼命追但追不上写入速度commit_lag_sec 大于 5 秒就要关注同步提交模式下它直接反映主库的提交响应时间。我给自己定的规矩每周一早上看一遍这张视图重点盯有没有 NOT SYNCHRONIZING 的成员副本节点打过系统补丁重启后第一件事就是确认 AG 角色恢复且没有 FAILED 状态。故障转移演练每季度做一次手动把主副本切到每个辅助副本上轮流跑一遍这样才不会出现“真故障时才发现某个副本角色起不来”的场面。还有个容易被忽视的细节新增节点后把备份作业的优先级和备份偏好一起 review。AG 的备份策略会自动挑可用的副本执行备份如果新副本 BACKUP_PRIORITY 配高了备份会转移到新节点上跑而新节点的磁盘容量未必按这个预期规划过。我吃过这个亏加完副本第二天备份任务全堆在新节点上磁盘差点写满。Always On 集群本质是一台会自己选举主副本的机器新增节点只是给机器加了块冗余。真正让它稳定的是每天盯同步状态、定期做切换演练、保持端点证书和时钟一致。希望这些经验帮到你也祝你的 AG 集群每次故障转移都像演练一样干净利落。本文还有配套的精品资源点击获取
简介针对SQL Server 2016 Always On可用性组运维场景该docx操作文档面向数据库管理员与运维工程师解决已有集群需新增数据库节点时的实操落地问题。文档从环境角色表切入明确主副本、辅助副本及新增副本的IP、主机名与读写策略再逐步演示新节点SQL Server安装、故障转移群集功能启用、hosts文件同步修改、加入Windows群集、开启Always On、最后向可用性组添加副本等关键步骤每一步都有详细界面截图且步骤经过真实环境验证能有效避开群集配置中常见的遗漏点。资源压缩包为1.92MB共1个docx文件图文编排紧凑、操作顺序清晰既适合DBA按文档逐项执行也可作为同类高可用组扩展任务的参考模板。已有101人学习下载适合正在扩展Always On集群或维护高可用组的DBA阅读。1. 在 SQL Server 2016 Always On 集群里新增数据库节点先分清“加库”和“加副本”“在 sql server 2016 always on集群里新增一个数据库节点”这句话我在生产环境被问过不下十次。每次都要先反问一句你是要把一个新数据库纳入可用性组保护还是要新加一台装 SQL Server 2016 的服务器进集群当辅助副本这两个操作都叫“新增节点”前置条件和踩坑点完全不同。把新业务库纳入 AG 是日常运维一条 ALTER 语句加一次备份还原就能收工而新加一台副本服务器要动 Windows 故障转移集群WSFC、AG 端点、同步模式任何一环漏了都会让集群在故障转移时翻车。本文按“加库”和“加副本”两条路线拆开讲命令都可复现坑位也都标清楚。2. 动手前的检查清单数据库状态、恢复模式与集群侧先决条件新增数据库进 Always On 之前别急着敲 ALTER 语句。SQL Server 2016 的可用性组对数据库有硬性门槛集群侧也有必须先确认的状态。我见过太多人在数据库还是 SIMPLE 恢复模式下直接执行 ADD DATABASE报错之后一脸懵。这里的检查项不多但每一项都直接决定后续同步能不能跑起来。2.1 数据库进 AG 的三个硬门槛恢复模式、备份、日志链先说门槛这是新手第一处翻车的地方恢复模式必须是 FULL。Always On 同步本质是靠日志重放SIMPLE 模式下日志会被 checkpoint 截断辅助副本根本跟不上。切换语句USE [master]; GO ALTER DATABASE [YourDB] SET RECOVERY FULL; GO这一句执行后会产生一个日志链断点别紧接着就做 ADD DATABASE。先做一次完整备份把日志链的起点建立起来。注意RECOVERY FULL 切换后如果此前从未做过完整备份辅助副本初始化仍会失败。Always On 要求数据库至少存在一个完整备份它是同步初始化的基线。至少存在一份完整备份。辅助副本要么靠“备份还原”初始化要么靠 SQL Server 2016 新增的自动种子化automatic seeding把数据和日志推过去两种方式都以主副本完整备份的 LSN 为起点。没有完整备份日志链就是断的辅助副本不知道从哪个点开始追。数据库必须在线且没有被其他 AG 占用。一个数据库同一时间只能属于一个可用性组。如果目标库已经在另一个 AG 里要先移除才能加入新组。还有一种常见误用辅助副本上存在同名库且状态是 ONLINE 或 RESTORING会在 JOIN 时报冲突。2.2 集群与副本侧检查WSFC 节点状态、角色与端点Always On 构建在 Windows Server Failover Clustering 之上SQL Server 2016 的 AG 依赖 WSFC 提供故障检测和仲裁。加库之前先确认三件事所有 WSFC 节点在线仲裁配置正常。用 PowerShell 或故障转移集群管理器查看Get-ClusterNode | Select-Object Name, State, NodeWeightState 必须全部为 UpNodeWeight 不能被清零成 0否则节点失去仲裁投票权集群可能在网络抖动时整体挂掉。仲裁出问题的典型症状是 AG 显示 RESOLVING所有库一起不健康。确认当前实例是主副本。ADD DATABASE、ADD REPLICA 这类变更语句都只能发在主副本上在辅助副本上执行会直接报“当前副本不是主副本”。AG 端点存在且已启动。SQL Server 2016 的 AG 端点默认监听 TCP 5022用于副本间的 HADR 会话。端点异常时数据库加进去之后同步状态会卡在 INITIALIZING 或 SYNCHRONIZING 不动。这在从旧版本升级上来的环境里尤其常见端点可能启用了老旧的加密算法或过期证书。2.3 用一条 T-SQL 把现有 AG 拓扑和同步基线摸清楚动手前花一分钟跑下面这条查询把 AG 名称、副本清单、每个数据库的同步状态全拉出来作为操作基线SELECT ag.name AS ag_name, ar.replica_server_name, CASE WHEN ars.is_primary 1 THEN PRIMARY ELSE SECONDARY END AS role_desc, ISNULL(DB_NAME(dbrs.database_id), NO DB) AS db_name, dbrs.synchronization_state_desc, dbrs.synchronization_health_desc, dbrs.last_hardened_lsn, dbrs.last_commit_time FROM sys.availability_groups ag JOIN sys.availability_replicas ar ON ag.group_id ar.group_id LEFT JOIN sys.dm_hadr_availability_replica_states ars ON ar.replica_id ars.replica_id LEFT JOIN sys.dm_hadr_database_replica_states dbrs ON ar.replica_id dbrs.replica_id ORDER BY ag.name, ar.replica_server_name, DB_NAME(dbrs.database_id); GO这段查询的核心是 sys.dm_hadr_database_replica_states它是排查 Always On 同步问题最常用的 DMV。last_hardened_lsn 表示已经持久化到本地副本的日志 LSN辅助副本追上主副本时这个值和主库一致synchronization_state_desc 通常表现为 NOT SYNCHRONIZING、SYNCHRONIZING、SYNCHRONIZED 三态。执行后先确认当前实例角色是 PRIMARY 再往下走。还要留意有没有 synchronization_health_desc 为 FAILED 的库如果有说明日志流已经在中断边缘先把老问题清掉再添加新库否则新库会被一起拖下水。3. 往现有可用性组里新增数据库T-SQL 两条路与向导操作库和集群侧检查都通过后开始正式操作。加数据库的方式按数据量大小有两种主流选择自动种子化和备份还原。SQL Server 2016 引入了自动种子化但很多从 2012/2014 迁移上来的团队还是沿用老一套备份还原两种方式各有适用场景。3.1 直接 ADD DATABASE走自动种子化的最小命令如果 AG 里所有副本都启用了自动种子化SEEDING_MODE AUTOMATIC加库只需在主副本上执行一行USE [master]; GO ALTER AVAILABILITY GROUP [YourAG] ADD DATABASE [YourDB]; GO执行后SQL Server 2016 会自动在主副本上发起一次完整备份通过 AG 端点把备份流推送到各辅助副本在每个辅助副本上完成还原和 JOIN全程不需要人工介入。这是 SQL Server 2016 里“新增数据库节点”最省事的一条路。参数说明ADD DATABASE 后面的库名必须是主副本上存在的数据库如果某个辅助副本上碰巧已有同名库自动种子化不会覆盖它而是直接报错。此时需要先到对应辅助副本上把同名库删掉或改名再重新触发种子化。自动种子化适合几十 GB 以内的中小型库对几百 GB 甚至上 TB 的库它的超时机制和日志增长速度会让你头疼建议直接看 3.2 的备份还原路线。3.2 先备份再还原大数据库加进 AG 的稳妥做法大库进 AG生产环境的主流做法是“手动备份 - 还原 - JOIN”。这招在 2016 上依然适用而且比自动种子化更可控。整个过程分三段。第一段在主副本上做完整备份BACKUP DATABASE [YourDB] TO DISK ND:\Backup\YourDB_Full.bak WITH INIT, COMPRESSION, CHECKSUM; GOCHECKSUM 强烈建议加备份损坏在还原或同步阶段才暴露是最糟糕的场景COMPRESSION 能省磁盘和网络传输时间代价是备份阶段多耗一些 CPU。第二段在每一个辅助副本上还原必须加 NORECOVERYRESTORE DATABASE [YourDB] FROM DISK ND:\Backup\YourDB_Full.bak WITH MOVE YourDB TO ND:\Data\YourDB.mdf, MOVE YourDB_log TO ND:\Log\YourDB_log.ldf, REPLACE, NORECOVERY; GOMOVE 子句把数据文件和日志文件放到辅助副本实例期望的路径上路径不匹配会还原失败这是最常见的低级错误。REPLACE 用于覆盖辅助副本上可能残留的同名库。NORECOVERY 是重点它让数据库停在 RESTORING 状态这是后续 JOIN 到 AG 的前提。注意如果辅助副本上有多个库要加入同一个 AG备份还原可以并行做。但 JOIN 动作要在所有副本上按顺序完成且主副本要先完成 ADD DATABASE。第三段分别执行 ADD 和 JOIN。先在主副本上执行ALTER AVAILABILITY GROUP [YourAG] ADD DATABASE [YourDB]; GO然后在每个辅助副本实例上执行USE [master]; GO ALTER DATABASE [YourDB] SET HADR AVAILABILITY GROUP [YourAG]; GOALTER DATABASE ... SET HADR AVAILABILITY GROUP 是把处于 RESTORING 状态的数据库“挂”到可用性组上的核心语句。执行成功后数据库会自动从 RESTORING 变为 ONLINE开始追赶主副本的日志。如果漏了这步主副本上数据库虽然加入了 AG辅助副本上没有对应成员AG 会报告“数据库在部分副本上缺失”。3.3 用 SSMS 向导添加数据库与它的隐藏坑不带命令行习惯的团队习惯用 SSMS 的 Always On 高可用向导。右键 AG 名称 → 添加数据库 → 勾选目标库 → 下一步。向导会自动检查恢复模式和备份条件然后走备份还原流程把库带进 AG。但我几乎不用向导做这件事原因是它把太多细节藏进了黑匣子。向导默认会创建数据库镜像端点连接、自动做备份还原失败时给到操作者的错误信息往往很含蓄排查路径反而更长。而且向导对已有 AG 不支持“仅自动种子化”这种精细控制总会走备份还原流程。真正适合用向导的场景是AG 刚搭建、副本数很少、库也都是几十 GB 以内想快速验证流程。生产上我一般还是用 T-SQL 脚本因为每条语句的进度、报错和 LSN 位置都清清楚楚出问题方便定位到具体环节。4. 新增一个副本节点进集群从 Windows 故障转移集群到可用性组副本如果标题里的“数据库节点”指的是新加一台服务器作为 AG 的辅助副本操作链路会长不少。最常见的误解是以为装好 SQL Server 就能 JOIN 进 AG。实际上新服务器必须先成为 WSFC 的成员节点才能谈 AG 副本。4.1 新节点加入 WSFC 与 SQL Server 2016 的 Always On 开关新服务器加入已有集群用故障转移集群管理器或 PowerShell 都行Add-ClusterNode -Cluster SQL-AG-CLUSTER -Name SQLNode03执行成功后到故障转移集群管理器里确认 SQLNode03 显示为 Up仲裁配置仍是原有模式。如果这台服务器此前加入过别的集群Add-ClusterNode 会报错需要先用 Remove-ClusterNode 清理旧集群元数据。拿退役副本机器做新节点时经常撞上这个残留问题。然后安装 SQL Server 2016。安装类型不需要选“故障转移集群实例”AG 不是 FCI它只是 WSFC 之上的一层可用性组。装完之后打开 SQL Server 配置管理器进入 SQL Server 服务的属性页切到“AlwaysOn 高可用性”标签勾选“启用 AlwaysOn 可用性组”重启 SQL Server 服务。这个开关经常被漏掉。服务没启用 AlwaysOn后面 ADD REPLICA 时会报“实例未启用 AlwaysOn”之类错误。运气更差的情况是命令能执行但副本角色起不来排查半天发现开关没开。另外新节点上的 AG 端点也要提前建好否则数据流起不来端点创建脚本见 5.4。4.2 在可用性组里 ADD REPLICA参数怎么定新节点准备好后在主副本上添加副本。SQL Server 2016 的完整语法如下ALTER AVAILABILITY GROUP [YourAG] ADD REPLICA ON NSQLNode03 WITH ( ENDPOINT_URL NTCP://SQLNode03:5022, AVAILABILITY_MODE SYNCHRONOUS_COMMIT, FAILOVER_MODE AUTOMATIC, SEEDING_MODE AUTOMATIC, BACKUP_PRIORITY 50, SECONDARY_ROLE(ALLOW_CONNECTIONS NO) ); GO参数逐个说。ENDPOINT_URL 指向新节点上 AG 端点的地址和端口5022 是默认端口如果新实例端点用了别的端口这里要跟着改AVAILABILITY_MODE 决定同步模式SYNCHRONOUS_COMMIT 表示事务要在主副本和该副本都提交才算成功数据零丢失但会增加主库提交延迟ASYNCHRONOUS_COMMIT 适合跨机房容灾允许数据延迟FAILOVER_MODE 配 AUTOMATIC 的前提是该副本是同步提交模式配合 WSFC 仲裁才能实现自动故障转移SEEDING_MODE 设 AUTOMATIC 表示新副本上的数据库可以自动种子化BACKUP_PRIORITY 决定该副本参与备份作业的优先程度数字越大越优先。注意故障转移模式是逐副本设置的。如果 AG 原本的主副本是 MANUAL把新副本设成 AUTOMATIC 不会改变整体策略要确认所有需要自动故障转移的同步副本都配成 AUTOMATIC。ADD REPLICA 只是把副本元数据写进 AG。如果这个 AG 配置了可用性组监听器新副本加进来通常不用改监听器WSFC 会自动把新节点纳入可用性组资源组。但如果你配置了只读路由要记得为新副本指定只读路由 URL否则客户端按只读副本连接时找不到它ALTER AVAILABILITY GROUP [YourAG] MODIFY REPLICA ON NSQLNode03 WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL NTCP://SQLNode03:1433)); GO4.3 新副本上的数据库 JOIN 与同步状态确认新副本加进来后AG 会为它准备现有库。如果副本配了自动种子化不需要手动还原只需在新副本上执行 JOINALTER DATABASE [YourDB] SET HADR AVAILABILITY GROUP [YourAG]; GO这条语句配合自动种子化SQL Server 2016 会通过 AG 端点把主副本的备份流推送到新副本自动完成还原并把库带进 ONLINE。如果没开自动种子化就按 3.2 的备份还原流程在新副本上做一遍再执行同样的 SET HADR JOIN。还原时路径必须与该副本实例的数据目录匹配MOVE 子句不可省略。全部完成后跑一条针对新副本的同步检查SELECT DB_NAME(database_id) AS db_name, synchronization_state_desc, synchronization_health_desc, last_hardened_lsn, DATEDIFF(second, last_commit_time, SYSUTCDATETIME()) AS commit_lag_sec FROM sys.dm_hadr_database_replica_states WHERE replica_id (SELECT replica_id FROM sys.availability_replicas WHERE replica_server_name NSQLNode03); GOcommit_lag_sec 接近 0说明新副本已经追上主副本。对同步提交模式确认数据一致后再考虑把 FAILOVER_MODE 从 MANUAL 改成 AUTOMATIC。这个顺序不能反否则等于把故障转移的安全垫提前抽掉。5. 新增库/节点后翻车现场排查四个高频问题与解决顺序加库和加副本的坑我按出现频率排了个序。每一条都按“现象 → 原因 → 解决”写后面对着症状查就行。以下是我在多个生产集群里攒下的血泪经验。5.1 同步状态卡在 INITIALIZING或反复 SYNCHRONIZING 又倒回现象执行完 ADD DATABASE 后查 sys.dm_hadr_database_replica_states新库在辅助副本上一直 INITIALIZING几个小时不动或者进度走到 80% 突然掉回 20%反复重来。原因自动种子化场景下最常见的是网络带宽瓶颈和 AG 端点连接不稳定备份还原场景下多半是还原完成后没有及时 JOIN日志在辅助副本上累积导致追赶变慢。还有一种玄学来源是安全软件扫描数据目录导致 IO 卡顿。解决先看 SQL Server 错误日志里有没有 AG 端点连接超时TCP 5022的报错有则查防火墙、端口占用和网络抖动。网络没问题再看主副本上备份进程是否被其他作业抢占。对大库超时可以直接调大EXEC sp_configure show advanced options, 1; RECONFIGURE; EXEC sp_configure hadr auto seed timeout, 60; RECONFIGURE;hadr auto seed timeout 默认 15 分钟大库种子化很容易超时。调大后重新执行一次 ADD DATABASE 即可。5.2 辅助副本上库处于 RESTORING 状态JOIN 报错现象备份还原流程走完执行 ALTER DATABASE [YourDB] SET HADR AVAILABILITY GROUP [YourAG]报错信息里出现“数据库正在还原无法启用可用性组”之类的提示。原因还原时没有带 NORECOVERY或者还原后又误执行了 RECOVERY 把数据库带到了 ONLINE。JOIN 动作要求数据库必须停在 RESTORING 状态处于 ONLINE 的普通数据库无法直接挂上 AG。解决这不是数据损坏重新覆盖还原一次即可RESTORE DATABASE [YourDB] FROM DISK ND:\Backup\YourDB_Full.bak WITH REPLACE, NORECOVERY; GO还原后确认状态SELECT name, state_desc FROM sys.databases WHERE name YourDB; GO输出必须是 RESTORING然后再执行 SET HADR。别担心覆盖还原会丢数据备份来自主副本辅助副本本来就是要被覆盖的。5.3 大库自动种子化超时失败日志文件在辅助副本上疯涨现象几百 GB 的库用 SEEDING_MODE AUTOMATIC 加进 AG种子化反复失败辅助副本的日志文件在几分钟内涨了几十 GB。原因自动种子化走的是日志流推送种子化期间主副本上有大量业务写入时会拖慢进度失败重试还会让辅助副本日志持续累积。这是 2016 自动种子化对超大库不友好的老毛病。解决大库不要走自动种子化切回备份还原手动流程。如果已经走了先移除这个库成员ALTER AVAILABILITY GROUP [YourAG] REMOVE DATABASE [YourDB]; GO然后在辅助副本上把处于 RESTORING 的库删掉释放被占用的日志空间再走 3.2 的备份还原流程。删库前确认该库在 AG 里至少还有一个健康副本否则等于让 AG 失去一个有数据的节点。5.4 新副本端点连不上副本一直 NOT SYNCHRONIZING现象ADD REPLICA 执行成功但新副本一直 NOT SYNCHRONIZING错误日志里出现“数据库镜像端点无法连接到 SQLNode03:5022”或证书验证失败。原因三个常见来源防火墙没放行 5022 端口新节点上 AG 端点没创建或状态是 STOPPED节点间系统时钟偏差大、证书不一致导致握手失败。解决先测端口通不通telnet SQLNode03 5022连不上就查防火墙和端点状态SELECT type_desc, port, state_desc FROM sys.tcp_endpoints WHERE type 2; GOstate_desc 必须是 STARTED。端点不存在时创建端口要和 ADD REPLICA 的 ENDPOINT_URL 一致CREATE ENDPOINT Hadr_endpoint STATE STARTED AS TCP (LISTENER_PORT 5022) FOR DATABASE_MIRRORING (ROLE ALL, ENCRYPTION REQUIRED ALGORITHM AES); GO证书问题常见于新旧节点安装时间差大、时钟漂移的环境。检查节点间时间同步状态并确保所有副本端点加密算法一致。跨域节点或工作组节点最容易踩这个坑我强烈建议新节点进集群前先统一校对时钟。6. 新增节点后的日常维护一张监控视图盯住同步状态新增库或副本后的第二天才是真正考验的开始。我的习惯是给 AG 建一张常驻监控视图随时能看到每个库在每个副本上的同步情况IF OBJECT_ID(Ndbo.v_AGHealth, NV) IS NOT NULL DROP VIEW dbo.v_AGHealth; GO CREATE VIEW dbo.v_AGHealth AS SELECT ag.name AS ag_name, ar.replica_server_name, DB_NAME(drs.database_id) AS db_name, drs.synchronization_state_desc, drs.synchronization_health_desc, drs.redo_queue_size AS kbytes_pending_redo, drs.log_send_queue_size AS kbytes_pending_log, DATEDIFF(second, drs.last_commit_time, SYSUTCDATETIME()) AS commit_lag_sec FROM sys.availability_groups ag JOIN sys.availability_replicas ar ON ag.group_id ar.group_id JOIN sys.dm_hadr_database_replica_states drs ON ar.replica_id drs.replica_id; GOredo_queue_size 和 log_send_queue_size 的单位是 KB是判断同步压力的第一手指标。这两个值持续大于 0 说明辅助副本在拼命追但追不上写入速度commit_lag_sec 大于 5 秒就要关注同步提交模式下它直接反映主库的提交响应时间。我给自己定的规矩每周一早上看一遍这张视图重点盯有没有 NOT SYNCHRONIZING 的成员副本节点打过系统补丁重启后第一件事就是确认 AG 角色恢复且没有 FAILED 状态。故障转移演练每季度做一次手动把主副本切到每个辅助副本上轮流跑一遍这样才不会出现“真故障时才发现某个副本角色起不来”的场面。还有个容易被忽视的细节新增节点后把备份作业的优先级和备份偏好一起 review。AG 的备份策略会自动挑可用的副本执行备份如果新副本 BACKUP_PRIORITY 配高了备份会转移到新节点上跑而新节点的磁盘容量未必按这个预期规划过。我吃过这个亏加完副本第二天备份任务全堆在新节点上磁盘差点写满。Always On 集群本质是一台会自己选举主副本的机器新增节点只是给机器加了块冗余。真正让它稳定的是每天盯同步状态、定期做切换演练、保持端点证书和时钟一致。希望这些经验帮到你也祝你的 AG 集群每次故障转移都像演练一样干净利落。本文还有配套的精品资源点击获取
