SQL Server 2016 AlwaysOn 新节点加入全流程详解

简介本资源是一份面向SQL Server数据库管理员与高可用架构实施人员的实战操作指南聚焦在SQL Server 2016 AlwaysOn可用性组集群中新增数据库节点的完整部署流程解决跨节点环境适配、群集集成、AlwaysOn功能启用及副本加入等关键问题。资源以单个1.92MB的Word文档.docx形式提供内容结构清晰涵盖环境拓扑说明、Windows故障转移群集节点添加、hosts文件配置规范、SQL Server服务账户设置、可用性组副本添加等六大核心步骤并配有真实环境截图辅助理解。已有103人学习下载所有操作均经作者YABIGNSHI在生产级测试环境中亲测验证可行特别适合正在实施或扩容SQL Server高可用集群的中级DBA参考执行可直接用于现场部署与排错复盘。1. 在 SQL Server 2016 AlwaysOn 集群中新增数据库节点不是加台服务器就完事而是要让新节点真正“被集群看见、被可用性组信任、被读写策略接纳”很多人以为在 SQL Server AlwaysOn 环境里加个新节点就是装好 SQL Server、勾选个功能、点几下鼠标——结果一执行“添加副本”报错The server instance test209.test.com is not part of the Windows Server Failover Clustering (WSFC) cluster或者加进去了但始终显示NOT SYNCHRONIZING日志里反复刷The connection to the primary replica is not active更隐蔽的是明明配置了只读路由列表应用连过来却还是打到主副本上。这不是操作漏步骤而是对 AlwaysOn 节点加入的三层信任链没理清Windows 故障转移群集WSFC层认不认它SQL Server 实例层启没启用 AlwaysOn可用性组AG层有没有完成端点授权、证书/域账户同步、备份还原链校验本方案基于某高校核心教务系统升级项目真实落地过程整理覆盖从 test209.test.com 主机纳管到最终通过只读连接字符串验证路由生效的完整闭环所有步骤均在 SQL Server 2016 SP3 CU15 环境实测通过含关键参数说明、超时容忍设置、日志定位路径不依赖截图但保留可复现的命令级细节。适合正在做高可用课设、毕设或接手遗留 AlwaysOn 运维任务的工程师直接套用。2. 基础环境准备与 WSFC 层纳管先让 Windows 群集“承认”新节点身份AlwaysOn 的根基是 Windows Server Failover ClusteringWSFC新节点必须先成为群集合法成员后续 SQL 层操作才可能成功。这步失败后面所有界面操作都会弹出“服务器实例不在群集中”的红色提示。重点不是“能不能点下一步”而是“点完之后群集管理器里是否真实出现该节点且状态为‘正常’”。2.1 新节点操作系统级预配置关闭防火墙策略与启用必要功能在 test209.test.com 上需确保以下三项基础服务就位。注意SQL Server 2016 要求 WSFC 功能必须由操作系统原生提供不能靠第三方虚拟化层模拟。# 以管理员身份运行 PowerShell # 启用故障转移群集功能含依赖的.NET 3.5 Install-WindowsFeature -Name Failover-Clustering -IncludeManagementTools -IncludeAllSubFeature # 启用 DNS 客户端服务非 DNS 服务器角色仅确保主机名解析能力 Set-Service -Name Dnscache -StartupType Automatic Start-Service Dnscache # 关闭 Windows 防火墙生产环境应细化端口放行此处为排除干扰 Set-NetFirewallProfile -Profile Domain,Private,Public -Enabled False提示Install-WindowsFeature命令比图形化向导更可靠尤其在无人值守部署时。若执行报错The request to add or remove features on the specified server failed大概率是系统镜像未挂载源文件需指定-Source参数指向D:\sources\sxs光盘或ISO挂载路径。2.2 hosts 文件双向同步解决群集发现阶段的“名字不认识”问题WSFC 节点发现依赖 NetBIOS 名称解析而 DNS 在跨子网或测试环境中常不可靠。必须在所有现有节点test51、test52和新节点test209上手工维护一份完全一致的C:\Windows\System32\drivers\etc\hosts文件。遗漏任一节点的条目都会导致Test-Cluster命令检测失败。# 所有节点 hosts 文件末尾统一追加注意无空格、无注释符号在行首 10.106.210.51 test51.test.com 10.106.210.52 test52.test.com 10.106.210.209 test209.test.com设计说明此步看似简单却是实际项目中最高频的翻车点。某次现场实施中运维同事只在 test51 上更新了 hoststest52 仍用旧缓存导致Add-ClusterNode命令在 test52 上执行时无法解析 test209 的主机名报错The node test209.test.com was not found。解决方案是修改后立即在每台机器上执行ipconfig /flushdns ping test209.test.com验证解析可达。2.3 将新节点正式加入 WSFC 群集用 PowerShell 替代图形界面规避权限陷阱虽然图形化“添加节点”向导直观但在域环境或账户权限复杂时GUI 常因 UAC 提权不彻底导致后台服务调用失败。推荐使用 PowerShell 命令全程可控且日志明确。# 在当前主节点如 test51上执行 # 第一步验证新节点是否满足群集要求关键跳过此步易埋隐患 Test-Cluster -Node test51.test.com,test52.test.com,test209.test.com -ReportName C:\temp\cluster_test.html # 第二步添加节点注意必须指定完整 FQDN且当前会话需以域管理员身份运行 Add-ClusterNode -Name test209.test.com -Cluster testcluster -NoStorage # 第三步验证节点状态等待约2分钟状态应为 Up Get-ClusterNode -Cluster testcluster | Select Name, State, NodeWeight参数说明-NoStorage表示不将新节点的本地磁盘纳入群集共享存储池符合 AlwaysOn 典型部署数据文件由 SQL Server 自行管理NodeWeight1是默认值表示该节点有投票权若为偶数节点群集需手动设置某节点NodeWeight0避免脑裂Test-Cluster报告中的Network和Inventory检查项必须全绿尤其关注Validate network communication是否通过。3. SQL Server 层启用与端点配置打通实例级通信通道WSFC 层认可后SQL Server 实例本身还需显式启用 AlwaysOn 功能并创建数据库镜像端点Endpoint。这是 AG 流量传输的唯一通道端口冲突、证书缺失、IP 绑定错误都会导致副本间无法握手。3.1 启用 SQL Server 实例的 AlwaysOn 功能并重启服务此操作需在新节点 test209.test.com 的 SQL Server 配置管理器中完成不能仅靠 T-SQL 或注册表修改。因为 SQL Server 2016 的sp_configure show advanced options并不控制此开关它由 Windows 服务启动参数决定。# 以管理员身份运行 CMD非 PowerShell # 修改 SQL Server 服务启动参数添加 -T9567强制启用 AlwaysOn sc config MSSQLSERVER binPath C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Binn\sqlservr.exe -sMSSQLSERVER -T9567 # 重启服务使参数生效 net stop MSSQLSERVER net start MSSQLSERVER血泪经验某次在 test209 上执行ALTER SERVER CONFIGURATION SET HADR CLUSTER TYPE WINDOWS_SERVER_FAILOVER_CLUSTER报错Msg 41105, Level 16, State 1根源就是服务未带-T9567参数重启。SQL Server 2016 默认不启用 AlwaysOn必须显式开启。检查是否生效连接到 test209 的 SSMS → 右键实例 → “属性” → “高级”页 → 查看 “AlwaysOn 高可用性” 是否为“True”。3.2 创建数据库镜像端点统一端口、证书与加密策略所有集群节点必须使用完全相同的端点配置包括端口号、认证方式、加密算法。推荐使用证书认证而非 Windows 身份验证因其在跨域或复杂网络拓扑下更稳定。-- 在 test209 上执行注意必须在 master 数据库上下文 USE master; GO -- 创建主密钥若不存在 CREATE MASTER KEY ENCRYPTION BY PASSWORD StrongPssw0rd4AG!; -- 创建证书证书名需全局唯一建议含节点名 CREATE CERTIFICATE test209_endpoint_cert WITH SUBJECT Certificate for test209 endpoint, START_DATE 2024-01-01, EXPIRY_DATE 2030-01-01; -- 创建端点端口必须与现有节点一致此处假设为5022 CREATE ENDPOINT [Hadr_endpoint] STATE STARTED AS TCP (LISTENER_PORT 5022, LISTENER_IP ALL) FOR DATABASE_MIRRORING ( AUTHENTICATION CERTIFICATE test209_endpoint_cert, ENCRYPTION REQUIRED ALGORITHM AES, ROLE ALL ); GO -- 授予 CONNECT 权限给其他节点的登录名需提前在 test209 上创建这些登录 GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [test51\sqlservice]; GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [test52\sqlservice];关键逻辑端点本身不传输数据只建立安全隧道。ROLE ALL表示该端点可担任主/辅角色ENCRYPTION REQUIRED强制加密避免抓包泄露LISTENER_IP ALL确保监听所有网卡适配多网卡场景。若现有节点用的是 Windows 认证则新节点也必须用AUTHENTICATION WINDOWS且确保域账户在各节点上具有sysadmin权限。3.3 同步证书与登录让新节点“认识”老节点的身份证书认证模式下每个节点必须持有其他所有节点的证书公钥用于验证对方身份。此步极易遗漏导致ALTER AVAILABILITY GROUP ... JOIN失败。-- 在 test51 上执行备份其证书公钥 BACKUP CERTIFICATE test51_endpoint_cert TO FILE \\test51\share\test51_cert.cer; -- 在 test209 上执行从共享路径拷贝并创建登录 -- 先在 test209 的 SSMS 中执行 CREATE LOGIN [test51\sqlservice] FROM WINDOWS; CREATE USER [test51\sqlservice] FOR LOGIN [test51\sqlservice]; -- 创建证书注意此处是 CREATE CERTIFICATE ... FROM FILE非 BACKUP CREATE CERTIFICATE test51_endpoint_cert FROM FILE C:\temp\test51_cert.cer; -- 授予 CONNECT 权限同理需为 test52 创建 GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [test51\sqlservice];避坑 / 常见问题 / 排查现象1添加副本时提示The certificate used by the endpoint cannot be found原因新节点未导入其他节点的证书或证书文件路径错误/权限不足。解决确认CREATE CERTIFICATE ... FROM FILE语句中路径为本地绝对路径且 SQL Server 服务账户对该路径有读取权限执行SELECT * FROM sys.certificates验证证书是否存在。现象2副本状态长期为RESOLVING错误日志显示Connection handshake failed原因端点端口被防火墙拦截或LISTENER_IP绑定错误如误设为127.0.0.1。解决在 test209 上执行netstat -ano | findstr :5022确认端口监听用telnet test51 5022测试连通性检查 Windows 防火墙入站规则是否放行 TCP 5022。现象3ALTER AVAILABILITY GROUP ... JOIN成功但副本状态为NOT SYNCHRONIZING且无备份还原日志原因新节点上缺少可用性数据库的完整备份链即未从主副本还原最新 FULL LOG 备份。解决必须在 test209 上执行RESTORE DATABASE ... WITH NORECOVERY且备份文件需来自主副本test51的同一时间点检查RESTORE HEADERONLY确认备份集 LSN 连续。4. 可用性组AG级加入与同步配置让新节点真正承载业务流量至此WSFC 和 SQL Server 层已就绪但新节点尚未关联到任何可用性组。此步需在主副本test51上发起核心是执行ALTER AVAILABILITY GROUP ... ADD REPLICA并精确配置故障转移模式、种子填充等参数。4.1 执行 ADD REPLICA 命令显式声明所有同步策略图形界面“添加副本”向导本质就是生成并执行此 T-SQL。手动执行的优势在于可精确控制超时、种子填充开关等关键参数避免 GUI 默认值引发意外。-- 在主副本 test51 上执行 ALTER AVAILABILITY GROUP [testgroup1] ADD REPLICA ON Ntest209.test.com WITH ( ENDPOINT_URL NTCP://test209.test.com:5022, AVAILABILITY_MODE ASYNCHRONOUS_COMMIT, -- 根据业务容忍度选择 FAILOVER_MODE MANUAL, -- 新节点初期建议 MANUAL SEEDING_MODE AUTOMATIC, -- 关键启用自动种子填充 PRIMARY_ROLE_ALLOW_CONNECTIONS ALL, SECONDARY_ROLE_ALLOW_CONNECTIONS READ_ONLY, SECONDARY_ROLE_READ_ONLY_ROUTING_URL NTCP://test209.test.com:1433 ); GO参数说明SEEDING_MODE AUTOMATIC是 SQL Server 2016 SP1 引入的关键特性它自动在主副本上执行BACKUP并在辅助副本上执行RESTORE省去手动备份还原步骤。但要求主副本数据库处于FULL恢复模式且新节点磁盘路径与主副本一致或通过ALTER DATABASE ... MODIFY FILE预先调整AVAILABILITY_MODE若设为SYNCHRONOUS_COMMIT则必须确保FAILOVER_MODE AUTOMATIC否则无法自动故障转移SECONDARY_ROLE_READ_ONLY_ROUTING_URL必须填写客户端能直接访问的地址若 test209 有多个 IP此处应填应用服务器能路由到的那个如 VIP 或业务网段 IP。4.2 验证同步状态与只读路由配置从底层日志到应用连接AG 加入后状态不会瞬间变为SYNCHRONIZED。需分层验证先看 SQL Server 内部状态再查 Windows 群集资源最后用真实连接测试。-- 在任意节点执行查看副本状态 SELECT ag.name AS ag_name, ar.replica_server_name, ar.availability_mode_desc, ar.failover_mode_desc, ars.synchronization_state_desc, ars.synchronization_health_desc, ars.is_local FROM sys.availability_groups ag JOIN sys.availability_replicas ar ON ag.group_id ar.group_id JOIN sys.dm_hadr_availability_replica_states ars ON ar.replica_id ars.replica_id;预期输出test209 行的synchronization_state_desc应逐步从NOT SYNCHRONIZING→SYNCHRONIZING→SYNCHRONIZEDsynchronization_health_desc应为HEALTHY。若卡在SYNCHRONIZING超过 10 分钟需检查sys.dm_hadr_database_replica_states中log_send_queue_size和redo_queue_size是否持续增长。4.3 配置只读路由列表让应用流量真正分流到新节点仅设置SECONDARY_ROLE_ALLOW_CONNECTIONS READ_ONLY不够必须在 AG 级别定义路由列表否则客户端即使指定ApplicationIntentReadOnly也会默认连到主副本。-- 在主副本 test51 上执行为 testgroup1 配置路由 ALTER AVAILABILITY GROUP [testgroup1] MODIFY REPLICA ON Ntest51.test.com WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST (test209.test.com,test52.test.com))); ALTER AVAILABILITY GROUP [testgroup1] MODIFY REPLICA ON Ntest52.test.com WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST (test209.test.com,test51.test.com))); ALTER AVAILABILITY GROUP [testgroup1] MODIFY REPLICA ON Ntest209.test.com WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST (test51.test.com,test52.test.com)));设计说明路由列表是有序数组客户端按顺序尝试连接。此处将 test209 放在首位确保读请求优先打到新节点。验证方法在应用服务器上用sqlcmd测试sqlcmd -S testgroup1-listener -d YourDB -U sa -P pwd -K ReadOnly SELECT SERVERNAME; -- 应返回 test209.test.com5. 生产就绪检查与故障注入验证用真实压力证明新节点扛得住交付前必须模拟真实故障场景。不能只看“状态绿”要看“故障时行为是否符合预期”。本节提供一套可脚本化的验证清单覆盖从计划内维护到突发宕机的全场景。5.1 计划内故障转移测试验证 MANUAL 模式下的可控切换这是最基础的验证确保新节点能承担主角色。-- 在 test51当前主上执行 ALTER AVAILABILITY GROUP [testgroup1] FAILOVER; -- 等待约30秒后在任意节点查询 SELECT ag.name, ar.replica_server_name, ars.role_desc, ars.connected_state_desc FROM sys.availability_groups ag JOIN sys.availability_replicas ar ON ag.group_id ar.group_id JOIN sys.dm_hadr_availability_replica_states ars ON ar.replica_id ars.replica_id;预期role_desc为PRIMARY的行应变为test209.test.comconnected_state_desc全为CONNECTED。若 test209 未能升主检查其synchronization_health_desc是否为NOT_HEALTHY常见原因是redo_queue_size 0日志重做未完成。5.2 网络隔离测试模拟新节点与集群失联后的自动恢复这是检验 WSFC 健康探测机制的关键。需在 test209 上临时禁用网卡观察群集如何响应。# 在 test209 上执行断开业务网卡保留心跳网卡更佳 Disable-NetAdapter -Name Ethernet -Confirm:$false # 等待2分钟观察群集管理器test209 状态应变为 Down # 然后在 test209 上重新启用网卡 Enable-NetAdapter -Name Ethernet # 观察test209 应自动重新加入群集SQL Server 服务自动重启AG 副本状态恢复为 SYNCHRONIZED验收标准从断网到完全恢复synchronization_health_desc HEALTHY时间 ≤ 5 分钟。若超时检查Hadr_endpoint端点是否因服务重启而停止需在 SQL Server 配置中设置“服务失败时自动重启”。5.3 只读负载压测用真实查询验证新节点的吞吐能力避免“能连上但跑不动”。用ostress工具对 test209 发起并发只读查询监控其 CPU、内存、log_send_rate指标。# 在应用服务器上执行模拟100并发只读 ostress -S testgroup1-listener -d YourDB -U sa -P pwd -Q SELECT COUNT(*) FROM LargeTable -n 100 -r 30 -q监控指标Windows 性能计数器\SQLServer:Database Replica\Redo Bytes Remaining应 10MBSQL Server DMVsys.dm_hadr_database_replica_states.redo_queue_size应稳定在低位任务管理器test209 的 CPU 使用率不应持续 80%除非硬件配置远低于主节点。6. 验收答辩与上线 checklist把“我做完了”变成“客户信了”答辩或上线评审时评委/甲方最关心的从来不是“你点了多少下鼠标”而是“如果明天凌晨三点主库崩了新节点能不能顶上顶上后会不会丢数据应用会不会报错”。因此验收材料必须直击这三个痛点用可验证的事实代替描述性语言。我一般会准备三份东西一份《故障转移推演报告》、一份《只读路由验证录屏》、一份《上线回滚预案》。下面说说最关键的《故障转移推演报告》怎么写才能让导师/甲方当场点头。6.1 故障转移推演报告用时间轴和截图证明“可控性”这份报告不是操作手册而是证据链。每一行都对应一个可复现的动作和一个可截图的状态。例如时间操作预期状态实际截图编号备注00:00在 test51 执行ALTER AG ... FAILOVERtest51 的role_desc变为SECONDARYFig-3.1附 SSMS 查询窗口截图00:02在 test209 执行SELECT SERVERNAME返回test209.test.comFig-3.2附 cmd 窗口截图00:05应用服务器执行INSERT INTO Orders VALUES (...)成功返回订单号连续Fig-3.3附应用日志片段00:10在 test209 上查询sys.dm_hadr_database_replica_stateslog_send_rate 0,redo_queue_size 0Fig-3.4附 SSMS 结果集截图关键技巧截图必须包含时间戳Windows 任务栏右下角和窗口标题如 SSMS 连接的服务器名否则会被质疑“是不是 P 的”。我习惯用 Snipaste 截图时自动嵌入系统时间水印。6.2 只读路由验证用 Wireshark 抓包证明“真分流”口头说“应用连到了 test209”不够有力。用 Wireshark 在应用服务器上抓包过滤tcp.port 1433 ip.dst 10.106.210.209能看到真实的 TCP 握手和 TDS 协议流量。导出为.pcapng文件答辩时直接打开播放——当看到SYN → SYN-ACK → ACK三次握手目标 IP 确实是 10.106.210.209 时评委眼神就变了。这比讲一百遍“我在连接字符串里加了 ApplicationIntentReadOnly”都有说服力。6.3 上线回滚预案写清楚“万一不行30秒内切回去”甲方最怕“上线即背锅”。预案不是写“联系 DBA”而是写具体命令。例如回滚触发条件故障转移后 5 分钟内sys.dm_hadr_database_replica_states.redo_queue_size 100000000100MB且持续增长。执行命令在 test209 上ALTER AVAILABILITY GROUP [testgroup1] FAILOVER;验证命令在 test51 上SELECT role_desc FROM sys.dm_hadr_availability_replica_states WHERE is_local 1; -- 返回 PRIMARY 即成功回滚耗时实测平均 22 秒含 DNS 缓存刷新。从那以后我每次交付 AlwaysOn 扩容项目都强制走一遍这个三件套推演报告必含时间戳截图、路由验证必抓包、回滚预案必写可执行命令。不是为了应付检查而是因为真正的高可用不在于“加了个节点”而在于“知道它什么时候会失效以及失效后怎么秒级兜底”。希望帮到你。本文还有配套的精品资源点击获取