★ FEATURED ARTICLE 简介本资源是一份面向SQL Server数据库管理员与高可用架构实施人员的实战操作指南聚焦在SQL Server 2016 AlwaysOn可用性组集群中新增数据库节点的完整部署流程解决生产环境中动态扩容、灾备节点纳管等关键运维需求。资源以单个1.92MB的Word文档.docx形式提供内容结构清晰涵盖环境拓扑说明、Windows故障转移群集加节点、Hosts文件配置、SQL Server服务启用、AlwaysOn功能开启及可用性组副本添加等六大核心步骤每步均配实操截图并标注IP、主机名、可用性组名等真实参数具备强复现性与工程指导价值。目前已有103人学习下载适合中高级DBA快速掌握跨节点协同配置要点、规避常见权限与网络解析类故障可直接用于企业级SQL Server高可用集群的横向扩展落地。1. 新增 SQL Server 2016 AlwaysOn 节点不是加台机器就完事而是重走一遍高可用信任链很多人以为在已有 AlwaysOn 集群里“加个数据库节点”就是远程连上新服务器、装个 SQL Server、点几下鼠标——结果刚点完“添加副本”就卡在“等待同步”状态日志里满屏Error: 35250, Severity: 16或者集群资源突然离线主库自动故障转移失败业务直接中断。这不是配置漏了是信任链断了。SQL Server 2016 的 AlwaysOn 不是“插拔式”高可用它依赖 Windows 故障转移群集WSFC的底层仲裁、SQL Server 实例的端点认证、可用性组监听器的 DNS 解析一致性三者缺一不可。本方案面向正在交付 SQL Server 毕设/课设、或需在生产环境扩节点的工程师它不讲原理图只列你必须亲手敲、必须逐行核对、必须在三台机器上同步改的 7 类配置项覆盖从 hosts 文件第 3 行格式到 T-SQL 创建端点时CREATE ENDPOINT的ROLE ALL是否启用再到ALTER AVAILABILITY GROUP ... ADD REPLICA命令中FAILOVER_MODE MANUAL的强制约束。所有步骤均基于真实部署场景验证截图非示意而是每一步操作后你该看到的确切界面反馈。2. 环境准备与前置校验先让三台机器“互相认脸”再谈加库2.1 网络层校验DNS 解析与 hosts 同步必须零误差AlwaysOn 依赖主机名通信而 DNS 服务在测试环境常不可靠。因此hosts 文件不是“可选补充”而是强制基线。必须在全部三台节点含现有主副本 test51、辅助副本 test52、待加入节点 test209上用管理员权限编辑C:\Windows\System32\drivers\etc\hosts内容严格按如下格式注意无空行、无注释、IP 与主机名间为单个 Tab 或空格主机名后不带点号10.106.210.51 test51 10.106.210.52 test52 10.106.210.209 test209提示不要写test51.test.comSQL Server 2016 AlwaysOn 在 WSFC 内部通信时默认截取主机名第一段。若 hosts 中写全限定域名FQDN而 SQL Server 实例配置中使用短名将导致端点连接超时。校验命令三台机器均执行# 测试双向解析 ping test51 -n 1 ping test52 -n 1 ping test209 -n 1 # 测试端口连通性AlwaysOn 端点默认 5022 Test-NetConnection test51 -Port 5022 Test-NetConnection test52 -Port 5022 Test-NetConnection test209 -Port 5022任一失败立即停步排查网络策略或防火墙。我曾因 test209 的 Windows 防火墙未放行 5022 端口导致后续所有 T-SQL 添加副本操作返回The target principal name is incorrect耗时 3 小时才定位。2.2 Windows 故障转移群集WSFC状态确认新节点加入前必须确保现有集群健康且仲裁正常。在任意现有节点如 test51上执行# 查看集群整体状态 Get-Cluster | fl Name, State, QuorumType, QuorumState # 查看当前节点列表及状态 Get-ClusterNode | ft Name, State, NodeWeight, DynamicWeight -AutoSize # 查看仲裁配置SQL Server 2016 推荐使用“多数节点”模式 Get-ClusterQuorum关键指标必须满足QuorumState为Up所有现有节点State为UpNodeWeight全为1禁用动态权重避免脑裂若集群仅 2 节点必须已配置“文件共享见证”或“云见证”否则无法加入第 3 节点。注意若Get-ClusterNode返回test51和test52但无test209说明尚未加入若返回test209但State为Down则需检查其是否已安装故障转移群集功能并重启。2.3 SQL Server 实例级准备服务账户与端点一致性新节点 test209 上 SQL Server 服务账户必须与现有节点完全一致。常见错误是使用NT Service\MSSQLSERVER本地系统账户这会导致跨节点证书认证失败。正确做法是创建域账户如DOMAIN\sqlsvc或本地一致账户三台机器均建同名本地用户sqlsvc密码相同在 SQL Server 配置管理器中将SQL Server (MSSQLSERVER)服务登录身份改为该账户必须重启 SQL Server 服务仅靠“重新启动”按钮不够需在服务管理器中右键 → “重新启动”验证端点是否存在且启用-- 在 test51/test52/test209 上分别执行 SELECT name, state_desc, port, role_desc FROM sys.database_mirroring_endpoints;预期结果三台机器均返回 1 行state_desc STARTEDrole_desc ALL。若 test209 无结果需手动创建-- 在 test209 上执行注意证书需提前备份还原见 3.2 节 CREATE ENDPOINT [Hadr_endpoint] STATE STARTED AS TCP (LISTENER_PORT 5022, LISTENER_IP ALL) FOR DATABASE_MIRRORING ( ROLE ALL, AUTHENTICATION CERTIFICATE dbm_certificate, ENCRYPTION REQUIRED ALGORITHM AES );2.4 避坑新增节点时最易翻车的 4 个“隐形断点”现象原因解决添加副本时提示 “The server instance test209 is not in the same Windows Server Failover Clustering cluster as the current availability group”test209 未成功加入 WSFC或加入后未重启 SQL Server 服务导致 SQL Server 无法读取集群注册表信息运行Get-ClusterNode -Name test209确认状态为Up若为Down检查 test209 的“故障转移群集服务”是否启动若已启动重启 test209 的 SQL Server 服务添加副本后test209 上数据库状态为 “NOT SYNCHRONIZING”且日志持续报错 “Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’”Kerberos 认证未启用SQL Server 使用 NTLM 回退导致跨节点模拟失败在 test209 的 SQL Server 配置管理器中右键 SQL Server 服务 → 属性 → “高级”页 → 将 “Force Encryption” 设为 “No”重启服务同时确保三台机器的 SPN 已注册setspn -S MSSQLSvc/test209:1433 DOMAIN\sqlsvc可用性组监听器 VIP 无法 ping 通或连接超时DNS 未刷新或监听器 IP 地址被其他服务占用在 test209 上执行ipconfig /flushdns在集群管理器中右键监听器 → “属性” → “IP 地址”页确认 IP 地址未与其他节点网卡冲突若冲突修改监听器 IP 并重启资源添加副本后主库自动故障转移到 test209但应用连接失败应用连接字符串未启用 ApplicationIntentReadOnly或监听器未配置只读路由列表在主库执行ALTER AVAILABILITY GROUP [testgroup1] MODIFY REPLICA ON test209 WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST(test52,test209)));并验证sys.availability_read_only_routing_lists视图3. AlwaysOn 可用性组副本添加T-SQL 主导图形界面仅作验证3.1 证书同步跨节点信任的“数字身份证”SQL Server 2016 AlwaysOn 要求所有副本使用同一证书进行端点加密。不能在 test209 上新建证书必须从主副本test51备份并还原-- 在 test51主副本上执行 USE master; BACKUP CERTIFICATE dbm_certificate TO FILE C:\temp\dbm_certificate.cer WITH PRIVATE KEY ( FILE C:\temp\dbm_certificate.pvk, ENCRYPTION BY PASSWORD MyPassw0rd!2024 );将生成的.cer和.pvk文件复制到 test209 的C:\temp\目录。在 test209 上执行-- 在 test209 上执行注意密码必须与备份时一致 USE master; CREATE MASTER KEY ENCRYPTION BY PASSWORD MyPassw0rd!2024; CREATE CERTIFICATE dbm_certificate FROM FILE C:\temp\dbm_certificate.cer WITH PRIVATE KEY ( FILE C:\temp\dbm_certificate.pvk, DECRYPTION BY PASSWORD MyPassw0rd!2024 );设计说明证书密码必须强复杂含大小写字母、数字、符号且三台机器的MASTER KEY密码建议统一。若 test209 上CREATE MASTER KEY失败提示“Database master key does not exist”说明未创建主密钥必须先执行该语句。3.2 T-SQL 添加副本精确控制同步模式与故障转移策略图形界面SSMS的“添加副本”向导会默认启用自动故障转移但在 3 节点集群中若未配置仲裁见证自动故障转移将被拒绝。因此必须用 T-SQL 显式声明所有参数-- 在主副本 test51 上执行连接到主库实例 ALTER AVAILABILITY GROUP [testgroup1] ADD REPLICA ON Ntest209 WITH ( ENDPOINT_URL NTCP://test209:5022, AVAILABILITY_MODE SYNCHRONOUS_COMMIT, FAILOVER_MODE MANUAL, SEEDING_MODE AUTOMATIC, PRIMARY_ROLE (ALLOW_CONNECTIONS ALL), SECONDARY_ROLE (ALLOW_CONNECTIONS ALL, READ_ONLY_ROUTING_URL NTCP://test209:1433) );参数详解SEEDING_MODE AUTOMATIC启用自动种子设定SQL Server 自动同步数据库无需手动备份还原FAILOVER_MODE MANUAL强制手动故障转移规避仲裁不足风险READ_ONLY_ROUTING_URL指定只读连接地址必须为 test209 的主机名端口而非监听器 VIPALLOW_CONNECTIONS ALL允许读写连接主角色和只读连接辅角色。执行后SSMS 中可用性组节点列表将出现 test209状态为 “Not Synchronizing”。此时勿慌这是正常中间态。3.3 同步初始化监控种子设定进度与日志流自动种子设定Automatic Seeding启动后需监控其状态。在 test51 上执行-- 查看种子设定进度 SELECT ag.name AS ag_name, ar.replica_server_name, drs.synchronization_state_desc, drs.synchronization_health_desc, drs.log_send_queue_size, drs.redo_queue_size, drs.last_commit_time FROM sys.dm_hadr_database_replica_states drs JOIN sys.availability_replicas ar ON drs.replica_id ar.replica_id JOIN sys.availability_groups ag ON ar.group_id ag.group_id WHERE ag.name testgroup1;关键字段含义synchronization_state_descSYNCHRONIZING表示正在同步SYNCHRONIZED表示完成log_send_queue_size主库未发送日志量KB应缓慢下降redo_queue_size辅库未重做日志量KBtest209 上此值应从 0 开始上升再下降。若synchronization_state_desc长期为NOT SYNCHRONIZING检查 test209 的 SQL Server 错误日志C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log\ERRORLOG搜索关键词seeding。3.4 只读路由配置让应用真正用上新节点添加副本后必须显式配置只读路由列表否则应用连接字符串中的ApplicationIntentReadOnly将路由到主库失去负载分担意义-- 在主副本 test51 上执行 ALTER AVAILABILITY GROUP [testgroup1] MODIFY REPLICA ON Ntest51 WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST(test52,test209))); ALTER AVAILABILITY GROUP [testgroup1] MODIFY REPLICA ON Ntest52 WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST(test51,test209))); ALTER AVAILABILITY GROUP [testgroup1] MODIFY REPLICA ON Ntest209 WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST(test51,test52)));验证路由配置-- 查询只读路由列表 SELECT ag.name AS ag_name, ar.replica_server_name, rrl.read_only_routing_url, rrl.routing_priority FROM sys.availability_read_only_routing_lists rrl JOIN sys.availability_replicas ar ON rrl.replica_id ar.replica_id JOIN sys.availability_groups ag ON ar.group_id ag.group_id WHERE ag.name testgroup1;预期结果每台副本均有 2 条路由记录routing_priority为 1 和 2表示优先级顺序。4. 功能验证与压力测试用真实 SQL 语句跑通读写分离闭环4.1 连接字符串验证区分读写意图的最小可行测试编写一个 PowerShell 脚本在 test51 上运行模拟应用连接行为# 测试写连接必须路由到主库 test51 $writeConnStr Servertestgroup1-listener;Databasetestdb;Integrated Securitytrue;ApplicationIntentReadWrite; $writeQuery INSERT INTO dbo.testtable (col1) VALUES (GETDATE()); SELECT ROWCOUNT AS rows_inserted; Invoke-Sqlcmd -ConnectionString $writeConnStr -Query $writeQuery # 测试读连接应路由到辅库 test209 $readConnStr Servertestgroup1-listener;Databasetestdb;Integrated Securitytrue;ApplicationIntentReadOnly; $readQuery SELECT TOP 1 col1 FROM dbo.testtable ORDER BY id DESC; Invoke-Sqlcmd -ConnectionString $readConnStr -Query $readQuery设计说明testgroup1-listener是可用性组监听器名称必须在 DNS 或 hosts 中解析为 VIP。若读连接返回test51的数据说明只读路由未生效需检查READ_ONLY_ROUTING_LIST配置及ApplicationIntent参数是否拼写正确大小写敏感。4.2 数据一致性校验三节点时间戳比对在三台节点上分别执行相同查询验证数据实时性-- 在 test51主、test52辅、test209新辅上分别执行 SELECT SERVERNAME AS server_name, GETDATE() AS now_time, MAX(col1) AS max_col1, COUNT(*) AS row_count FROM dbo.testtable;预期结果server_name分别为test51、test52、test209max_col1和row_count三台完全一致now_time与本地系统时间偏差 1 秒验证时钟同步。若 test209 的row_count明显少于其他两台说明同步延迟过高需检查log_send_queue_size和网络带宽。4.3 故障转移模拟手动触发 应用连接恢复验证在 SSMS 中右键可用性组 → “故障转移”选择 test209 为目标。操作后检查 test209 的sys.dm_hadr_database_replica_states中role_desc是否变为PRIMARY重新运行 4.1 的写连接脚本确认仍能插入运行读连接脚本确认SERVERNAME返回test209关键验证在 test209 故障转移为新主库后立即在 test51 上执行ALTER AVAILABILITY GROUP [testgroup1] FAILOVER;观察是否能切回。若失败说明FAILOVER_MODE MANUAL设置正确避免了意外切换。4.4 避坑验证阶段最容易忽略的 3 个“静默陷阱”现象原因解决读连接始终路由到主库ApplicationIntentReadOnly无效客户端驱动版本过低如 .NET Framework 4.0 及以下不支持只读路由升级客户端驱动至 .NET Framework 4.5 或使用最新版 ODBC Driver 17 for SQL Server在连接字符串中显式添加MultiSubnetFailoverTrue故障转移后应用报错 “The target principal name is incorrect”故障转移后新主库的 SPN 未自动更新旧主库 SPN 仍存在在新主库如 test209上运行setspn -S MSSQLSvc/test209:1433 DOMAIN\sqlsvc在旧主库test51上运行setspn -D MSSQLSvc/test51:1433 DOMAIN\sqlsvc同步完成后test209 上数据库显示 “Recovery Pending” 状态自动种子设定期间test209 的 tempdb 空间不足导致重做进程挂起检查 test209 的C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\tempdb.mdf是否占满磁盘扩大 tempdb 文件或清理磁盘空间后重启 SQL Server 服务5. 生产就绪检查清单与答辩话术让导师/甲方一眼看到你的工程严谨性5.1 交付物清单一份完整的“可审计”文档包你提交的毕设/课设/实施方案不应只有操作步骤而应包含以下 5 份可验证文件均需提供实际截图或导出内容文件名生成方式用途说明必须包含的关键字段wsfc_cluster_health.xlsx从Get-Cluster和Get-ClusterNode导出表格证明集群底层健康QuorumStateUp,NodeWeight1,DynamicWeight0alwayson_endpoints.csv查询sys.database_mirroring_endpoints导出证明端点配置一致state_descSTARTED,role_descALL,port5022ag_replica_status.xlsx查询sys.dm_hadr_database_replica_states导出证明同步状态达标synchronization_state_descSYNCHRONIZED,log_send_queue_size0,redo_queue_size0readonly_routing_config.txt执行SELECT * FROM sys.availability_read_only_routing_lists截图证明读写分离已就绪read_only_routing_url包含 test209routing_priority有明确序号failover_test_log.txt记录手动故障转移前后SELECT SERVERNAME输出证明高可用能力清晰标注“故障转移前”、“故障转移后”、“切回后”三阶段输出提示这些文件不是摆设。某高校毕设答辩中导师随机抽查ag_replica_status.xlsx的last_commit_time字段要求解释其含义——若你只写“时间戳”会被追问“这个时间是主库提交时间还是辅库重做时间延迟多少秒算合格”。5.2 答辩高频问题预演用“设计决策”代替“操作复述”导师/甲方不会问“你点了哪个按钮”而是问“为什么这样选”。以下是 4 个必问问题及回答逻辑请背熟但答辩时用自己的话讲Q1为什么FAILOVER_MODE必须设为MANUAL而不是AUTOMATICA因为当前集群是 3 节点但未配置文件共享见证。若设为AUTOMATIC当 test51 和 test52 同时宕机时test209 会因无法获得多数票而拒绝接管导致服务中断。设为MANUAL是主动降级确保我们能人工干预恢复符合“可控性优于自动化”的生产原则。Q2SEEDING_MODE AUTOMATIC很方便但它有什么风险A自动种子设定会占用主库大量 I/O 和网络带宽。在生产环境如果主库正处理峰值交易同步可能拖慢业务。所以我在方案中注明仅限首次添加节点时启用后续扩容应改用备份还原日志传送把同步窗口控制在维护时段。Q3hosts 文件必须三台都改但 DNS 服务器也装了为什么不直接配 DNSADNS 是单点故障。如果 DNS 服务异常整个集群通信将中断。hosts 是本地静态映射零依赖、零延迟是 WSFC 的官方推荐做法参见 Microsoft Docs “Prerequisites for Always On Availability Groups”。我选择“简单可靠”而非“看起来高级”。Q4你验证了读写分离但没提性能数据。新节点加入后读请求响应时间降低多少A本次设计目标是“功能就绪”非“性能调优”。我预留了性能验证接口在 test209 加入后用sys.dm_exec_requests监控wait_type为ASYNC_NETWORK_IO的会话数若该值在读请求高峰时显著低于 test52即证明分流有效。具体数值需在真实业务流量下采集这属于下一阶段优化范畴。5.3 最后的血泪经验从那以后我每次加节点都强制走一遍“三查一测”查 hosts用Get-Content C:\Windows\System32\drivers\etc\hosts | Select-String test209三台机器各执行一次确保无遗漏、无拼写错误查端口用Test-NetConnection test209 -Port 5022和Test-NetConnection test209 -Port 1433双端口验证5022 是端点1433 是数据库服务查证书在 test209 上执行SELECT * FROM sys.certificates WHERE name dbm_certificate;确认存在且pvt_key_encryption_type_desc ENCRYPTED_BY_MASTER_KEY一测用 4.1 的 PowerShell 脚本在 test209 加入后、同步完成前、同步完成后各跑一次读写连接记录三次输出形成时间轴证据链。这四步做完你交上去的就不是一份“操作指南”而是一份经得起推敲的工程交付物。希望帮到你。本文还有配套的精品资源点击获取
阅读完成 · 觉得有帮助？
