SQL Server 2016 Always On 扩容只读副本与路由配置-北京尧图网络

简介面向需要在 SQL Server 2016 Always On 高可用集群中扩容数据库节点的运维与数据库管理员这份文档围绕新增副本节点的完整落地过程展开。内容结合读写分离环境中的角色、地址与主机名规划依次说明故障转移群集功能开启、主机解析文件互信配置、新节点加入现有 Windows 故障转移群集、在新节点启用高可用功能以及把副本加入可用性组等关键环节并配有详细截图辅助理解。文档按环境准备、节点加入与副本配置的顺序组织重点信息标注明确。压缩包共 1 个 docx 文件约 1.92MB以图文步骤说明为主便于按章节对照现有集群环境逐步操作。已有 102 人学习适合作为集群扩容时的参考手册也可用于梳理节点加入前后的配置检查项、读写分离策略与群集联动关系帮助读者减少试错成本、提升实施效率。1. 三个副本的账先算清这次扩容到底在扩什么半夜被叫起来原因是报表库的只读查询全压在主副本上有人提议「给 sqlserver 的 Always On 组再加一个副本节点不就行了」。听上去是装个 SQL Server、点两下「添加副本」的事真动手才发现前面还有一串活新节点得先成为 WSFC 的成员服务账户得换成域账户5022 端点得通可用性组之外还得配只读路由否则读流量根本不会往新节点上走。这篇记录拆的就是 SQL Server 2016 Always On 集群里新增一个数据库副本节点的完整链路版本红线核对、加故障转移群集、开 Always On、加入可用性组、配只读路由、上线前验证。适合正在做读扩展、准备从两副本扩到三副本又不想停机的 DBA 和运维如果手上只有标准版第二章那几条红线得先看不然装完才发现只能干瞪眼。2. 前置核对副本上限、提交模式和只读路由的选型账扩容最容易翻车的地方不在安装而在动手前的账没算。新节点是当可读副本用还是只当个热备同步提交还是异步提交只读路由列表按什么顺序排这三个问题决定了后面每一处配置的参数值。先把它们定下来再开服务器能省掉大量返工。2.1 副本数量与版本红线怎么核对SQL Server 2016 的 Always On 在不同版本上能力差得很远这是第一个必须确认的事。企业版支持一个主副本加最多八个辅助副本辅助副本可以被配置成可读只读路由、备份优先级、自动种子设定这些能力都在标准版走的是基本可用性组Basic Availability Groups只允许两个副本辅助副本不能读、不能承担备份也不支持把读流量路由过去。所以「加第三副本做读写分离」这件事标准版从根上就做不了不是配置问题。版本之外还有补丁级别。可用性组要求各副本的 SQL Server 主版本和 Service Pack / 累积更新保持一致补丁不同的节点在加入时会直接报错或者加入成功后日志同步卡在某个状态不动。稳妥的做法是在新节点装完之后先在旧节点上跑一遍版本查询再在新节点上跑一遍逐字段对比。-- 在每个节点上分别执行把结果贴到一起比对 SELECT SERVERNAME AS node_name, SERVERPROPERTY(ProductVersion) AS product_version, -- 形如 13.0.5026.0 SERVERPROPERTY(ProductLevel) AS patch_level, -- RTM / SP1 / SP2 / CU 编号 SERVERPROPERTY(Edition) AS edition, -- Enterprise / Standard SERVERPROPERTY(Collation) AS server_collation, -- 必须全组一致 SERVERPROPERTY(IsHadrEnabled) AS hadr_enabled; -- 实例级 Always On 开关ProductVersion前两段是主版本13 对应 2016第三段是补丁序列号这个数字必须一致。Collation是服务器级排序规则可用性组内的数据库排序列必须能对上跨排序规则虽然部分场景能跑但临时表和跨库查询会带来一堆隐性麻烦我一般要求新节点安装时就选和现有节点完全相同的排序规则。IsHadrEnabled在装完还没开 Always On 时是 0这个后面单独处理。安装 SQL Server 时几个容易漏的点实例名要和现有节点保持一致都用默认实例或都用同名命名实例侦听器配置会依赖这个服务账户先填域账户别图省事用本地系统账户后面切账户要重启服务身份验证模式跟现有节点对齐纯域环境用 Windows 身份验证就够但很多团队留了混合模式做应急入口那就保持一致。2.2 同步提交还是异步提交按机房和延迟定提交模式决定了主副本上的一次写入要等多少个副本确认才算完成。同步提交的副本主库事务提交前必须收到它的日志硬化确认异步提交则是主库写完就返回辅助副本自己追。这个差别直接体现在业务写入延迟上。新加的第三个节点如果是同机房、同机架、网络延迟在亚毫秒级可以上同步提交如果是跨机房、跨可用区老老实实异步。血泪经验是跨机房配同步提交某天专线抖一下主库写入全部挂起业务方看到的不是慢是彻底卡死。同步副本越多写入路径上的确认环节越多这个风险是线性叠加的。-- 新副本以异步提交加入避免拖慢主库写入机房内网延迟稳定再考虑改同步 ALTER AVAILABILITY GROUP [AG_SALES] ADD REPLICA ON NSQLNODE03 WITH ( ENDPOINT_URL NTCP://SQLNODE03.corp.local:5022, AVAILABILITY_MODE ASYNCHRONOUS_COMMIT, -- 跨机房或延迟不稳时用异步 FAILOVER_MODE MANUAL, -- 异步副本只能是手动故障转移 SEEDING_MODE AUTOMATIC, SESSION_TIMEOUT 10 -- 秒健康检查超时默认 10 );AVAILABILITY_MODE和FAILOVER_MODE是绑定的同步提交才能配自动故障转移异步提交只能手动。SESSION_TIMEOUT控制主副本等不到辅助副本心跳多久就判定它失联默认 10 秒在高延迟链路上可以适当放宽到 15但别调太大否则真出问题时切不过来。还有个约束值得提前知道一个可用性组里最多只有两个副本能配自动故障转移第三个副本即使同步提交也只能手动切。这不是配置错误是产品设计。2.3 只读路由表与备份优先级的预先设计只读路由是这次扩容的核心目的。它的工作机制分两半每个副本要在自己的SECONDARY_ROLE里声明一个READ_ONLY_ROUTING_URL告诉别人「连我读走这个地址」主副本的SECONDARY_ROLE里维护一份READ_ONLY_ROUTING_LIST按优先级排列读请求该去哪些副本。客户端连接串带上ApplicationIntentReadOnly连侦听器侦听器把连接转给列表里的副本。两个硬性要求路由 URL 必须用 FQDN 加端口写 IP 直接不生效这是最常见的路由失败原因路由列表的最后一层必须把主副本自己放进去兜底否则所有辅助副本都不可用时读请求会直接失败而不是回落到主库。设计阶段把这张表填好后面照着配就行角色主机名IP提交模式可读只读路由优先级备份优先级主副本SQLNODE0110.10.10.51同步是3兜底50辅助副本SQLNODE0210.10.10.52同步只读140新增副本SQLNODE0310.10.10.53异步只读230侦听器AG-LSN10.10.10.60----备份优先级数值越大优先级越高0 表示该副本不参与备份。给新节点配 30 而不是 40是为了让备份任务优先落在原有副本上新节点先只承担读流量等稳定运行一段时间再调整。这种循序渐进的做法比一次性把职责全压上去要安全得多。3. 把新节点塞进 WSFC功能安装、hosts 与 Add-ClusterNodeAlways On 的底座是 Windows 故障转移群集新节点必须先成为群集的正式成员SQL Server 才能在实例级别开启高可用。这一步在图形界面里点起来很顺但几个前置依赖漏一个就卡住。3.1 故障转移群集功能的安装与前置依赖打开服务器管理器添加角色和功能勾选「故障转移群集」并把它附带的管理工具一起装上。命令行等价操作如下适合批量或远程执行# 安装故障转移群集功能-IncludeManagementTools 会一并装好群集管理器 Install-WindowsFeature -Name Failover-Clustering -IncludeManagementTools -Restart:$false # 装完确认状态State 应为 Installed Get-WindowsFeature -Name Failover-Clustering | Select-Object Name, InstallState-Restart:$false表示先不重启但实际经验是装完这个功能重启一次最稳很多「加节点时报找不到群集服务」的问题都是没重启导致的。关于「DNS 服务器」这个角色原文步骤里勾了它但我的做法是能不装就不装。域环境里节点名解析走域控 DNS 就够了额外在 SQL 节点上装 DNS 角色会占用 53 端口在排错时凭空多出一层变量出问题时你很难判断到底是解析问题还是群集问题。真需要本地解析用 hosts 文件解决就够轻量且可控。其他前置项节点必须是域成员且和现有节点在同一个域操作系统版本和补丁级别与现有节点一致节点上的网卡建议至少两块一块业务一块心跳心跳网段不要配默认网关时间同步要开群集对节点间时间偏差敏感。这些条件不满足时加节点那一步会给出很含糊的错误后面第五章会展开。在正式加节点之前官方推荐先跑一次群集验证# 对现有节点和新节点一起做验证报告输出到本地 Test-Cluster -Node SQLNODE01,SQLNODE02,SQLNODE03 -ReportName C:\temp\cluster-validate报告里重点关注网络、存储、系统配置三部分。存储警告在纯 Always On 场景不用群集共享磁盘一般可以忽略网络和系统配置的红色项必须清掉。这份报告的价值在于它会把问题提前暴露在一个不影响生产的窗口里而不是等到加节点失败时再去逐个猜。3.2 hosts 文件的双向补齐原文把这一步单独拎出来是对的因为它是整个流程里最容易被做一半的地方。集群节点之间的名称解析必须是双向的新节点要能解析所有老节点所有老节点也要能解析新节点。在每台现有节点上编辑C:\Windows\System32\drivers\etc\hosts追加新节点的记录10.10.10.51 SQLNODE01.corp.local SQLNODE01 10.10.10.52 SQLNODE02.corp.local SQLNODE02 10.10.10.53 SQLNODE03.corp.local SQLNODE03 10.10.10.60 AG-LSN.corp.local AG-LSN新节点上则要把全部节点加侦听器的记录都补齐。注意每行同时写 FQDN 和短名只写短名在某些场景下群集服务解析会失败只写 FQDN 则会让一些老应用连不上。侦听器名字也建议一并写进去省得后面配只读路由时还要回头改。改完之后必须逐台验证别只 ping 通就算完# 在每台节点上对其它所有节点和侦听器名做解析验证 $targets SQLNODE01,SQLNODE02,SQLNODE03,AG-LSN foreach ($t in $targets) { $r Resolve-DnsName -Name $t.corp.local -ErrorAction SilentlyContinue if ($r) { {0} - {1} -f $t, ($r.IPAddress -join ,) } else { FAILED: $t } }这段脚本要返回每个名字对应的正确 IP出现 FAILED 就说明 hosts 或 DNS 还有问题。Resolve-DnsName走的是系统解析链路比 ping 更能反映群集服务的真实视角。改 hosts 之后不需要重启但群集服务如果已经启动建议重启一次群集服务让它重新读取。3.3 加入集群与端口放行在主节点上打开故障转移群集管理器连接到现有群集右侧操作栏点「添加节点」把新节点主机名加进去一路下一步。命令行版本更清晰# 先确认当前群集和现有节点状态 Get-Cluster -Name SQLCLUSTER01 | Select-Object Name, Domain Get-ClusterNode -Cluster SQLCLUSTER01 | Select-Object Name, State # 把新节点加入群集 Add-ClusterNode -Name SQLNODE03 -Cluster SQLCLUSTER01 # 加入后再次确认三个节点 State 都应为 Up Get-ClusterNode -Cluster SQLCLUSTER01 | Format-Table Name, State, NodeWeightNodeWeight是投票权重默认 1。三个节点时全部参与投票没问题如果后续扩到四个以上要考虑配置见证磁盘或云见证避免出现投票平局。加节点这一步如果报「无法访问节点」九成是 3.2 的解析没做全或者防火墙没放行。端口方面Windows 故障转移群集需要的不止一个端口这段经常被漏端口/协议用途说明TCP 135RPC 终结点映射群集管理必需TCP 445SMB自动种子设定、文件共享UDP 3343群集内部通信群集服务心跳与状态同步TCP 5022数据库镜像端点Always On 日志传输TCP 1433SQL Server 引擎业务与侦听器连接TCP 49152-65535动态 RPC 端口可固定范围后统一放行# 新节点上一次性放行 Always On 相关端口 $rules ( { NameSQL-AO-Endpoint; Port5022; ProtoTCP }, { NameSQL-Engine; Port1433; ProtoTCP }, { NameSMB-In; Port445; ProtoTCP } ) foreach ($r in $rules) { New-NetFirewallRule -DisplayName $r.Name -Direction Inbound -Protocol $r.Proto -LocalPort $r.Port -Action Allow -Profile Any }-Profile Any是为了同时覆盖域、专用、公用三种网络配置文件生产环境里节点网卡如果被识别成「公用网络」只放行域配置是无效的。放行后用Test-NetConnection -ComputerName SQLNODE03 -Port 5022从对端验证比在本机看规则是否生效更直接。4. SQL 侧收尾服务账户、AlwaysOn 开关与 5022 端点群集成员身份拿到之后剩下的是 SQL Server 实例侧的配置。这部分步骤不多但每一步都有一处容易踩空的地方。4.1 服务账户切换与实例级检查新节点装完 SQL Server 后如果安装时用的是本地账户或虚拟账户必须先换成域账户。可用性组在建立副本间连接时会用服务账户的身份做认证本地账户在域里没有身份端点握手会失败。切换服务账户要在 SQL Server 配置管理器里做别直接改服务属性配置管理器会顺带处理权限授予。命令行方式如下# 停服务后改账户obj 后面有一个空格这是 sc.exe 的语法要求 Stop-Service -Name MSSQLSERVER -Force sc.exe config MSSQLSERVER obj CORP\svc_sql password 填入实际密码 sc.exe config SQLSERVERAGENT obj CORP\svc_sql password 填入实际密码 Start-Service -Name MSSQLSERVER Start-Service -Name SQLSERVERAGENT # 确认服务已用新账户运行 Get-CimInstance Win32_Service -Filter NameMSSQLSERVER | Select-Object Name, StartName, Stateobj和值之间那个空格是老语法遗留漏了会报「参数无效」。代理服务账户建议和引擎用同一个域账户避免作业执行时出现权限割裂。改完账户后群集管理器里这个 SQL Server 资源的账户信息也要同步更新否则故障转移时资源起不来。服务账户需要在域里有以下权限对新节点本地管理员组、对 WSFC 群集对象的「读取」权限、对可用性组相关注册表项的访问权限。安装 SQL Server 时如果已经加入群集安装程序会自动授予一部分手动加节点的情况则要自己补。这一步没有做后面开 Always On 时服务会启动失败事件日志里给的是权限相关的错误码。顺带检查几个实例级配置max degree of parallelism、max server memory、remote login timeout建议和现有节点对齐。可用性组不会同步这些实例级设置一台机器上并行度是 0另一台是 8同一个查询在两边的执行计划可能完全不同读流量切过去就会出现性能落差。4.2 启用 AlwaysOn 并确认端点就绪实例级 Always On 开关默认是关的新节点必须手动打开。图形界面在配置管理器里SQL Server 服务 → 右键实例 → 属性 → AlwaysOn 高可用性 → 勾选「启用 AlwaysOn 可用性组」。# 用 SqlServer 模块打开开关-Force 跳过确认提示 Enable-SqlAlwaysOn -ServerInstance SQLNODE03 -Force -Verbose # 开关生效需要重启引擎服务 Restart-Service -Name MSSQLSERVER -ForceEnable-SqlAlwaysOn依赖 SqlServer PowerShell 模块没装的话用配置管理器操作也一样。这个开关的底层是写注册表并通知群集资源所以必须在节点已经是群集成员之后才能打开顺序反了会报错。重启后验证开关状态和端点-- IsHadrEnabled 应为 1否则说明开关没生效或服务没重启 SELECT SERVERPROPERTY(IsHadrEnabled) AS hadr_enabled, SERVERPROPERTY(ComputerNamePhysicalNetBIOS) AS node_name; -- 检查镜像端点正常应存在一个 state 为 STARTED 的端点端口 5022 SELECT name, type_desc, state_desc, port, is_encryption_enabled, encryption_algorithm_desc, connection_auth_desc FROM sys.database_mirroring_endpoints;如果端点不存在说明可用性组从未在这个实例上创建过端点。正常情况下向可用性组添加副本时向导或 T-SQL 会自动创建端点但如果你看到端点存在却state_desc是 STOPPED说明端口被占用或服务账户没有启动端点的权限用netstat -ano | findstr 5022查一下占用进程。端点加密算法在 SQL Server 2016 里默认是 AES这个不用改全组保持一致即可。如果是从更老的版本迁移过来端点可能用的是 RC4加密算法不一致会导致副本之间连不上报的是「连接超时」这种完全没有指向性的错误这类隐性不一致最消耗排查时间。4.3 ADD REPLICA把新副本挂进可用性组到这里新节点在实例层面已经准备好了回到主副本上执行加入操作。图形界面的路径是连侦听器 → AlwaysOn 高可用性 → 可用性组 → 右键目标组 → 添加副本。命令行更可控参数能一次写全-- 在主副本上执行把 SQLNODE03 挂进 AG_SALES ALTER AVAILABILITY GROUP [AG_SALES] ADD REPLICA ON NSQLNODE03 WITH ( ENDPOINT_URL NTCP://SQLNODE03.corp.local:5022, AVAILABILITY_MODE ASYNCHRONOUS_COMMIT, FAILOVER_MODE MANUAL, SEEDING_MODE AUTOMATIC, SESSION_TIMEOUT 10, BACKUP_PRIORITY 30, SECONDARY_ROLE ( ALLOW_CONNECTIONS READ_ONLY, READ_ONLY_ROUTING_URL NTCP://SQLNODE03.corp.local:1433 ) );几个参数逐个说。ENDPOINT_URL必须是新节点自己的 FQDN 加镜像端点端口 5022写侦听器地址是错的。SEEDING_MODE AUTOMATIC启用自动种子设定主副本会通过网络直接把数据库文件和日志推送到新副本省去手工备份还原这个能力要求 SQL Server 2016 及以上且要打通 445。ALLOW_CONNECTIONS READ_ONLY允许辅助副本接受只读连接这是只读路由的前提。READ_ONLY_ROUTING_URL用的是引擎端口 1433不是镜像端点端口这两个端口写反是很高频的错误。执行完这条之后新节点上的可用性组还处于「未加入」状态需要在新节点上执行加入-- 在新节点 SQLNODE03 上执行 ALTER AVAILABILITY GROUP [AG_SALES] JOIN; -- 授予可用性组自动创建数据库的权限自动种子设定必须 ALTER AVAILABILITY GROUP [AG_SALES] GRANT CREATE ANY DATABASE;JOIN让本地实例正式成为该可用性组的成员GRANT CREATE ANY DATABASE是自动种子设定的前置条件漏了这一步种子状态会一直卡在等待数据库永远建不出来。这个权限范围看起来吓人但实际只有可用性组本身能使用它创建数据库。自动种子设定之外还有一条传统路径在主副本做一次完整备份加日志备份在新节点用RESTORE ... WITH NORECOVERY还原再把数据库加进可用性组。这条路径慢但可控网络带宽紧张或数据库特别大时更稳。两条路的核心差别在于日志链的连续性自动种子设定由引擎自己管理手工还原则要确保还原的日志链正好接上中间断一环就要重来。4.4 只读路由与读流量切换副本挂上去只完成了「能读」还没完成「会被读」。只读路由列表要配在主副本的SECONDARY_ROLE上因为路由决策是主副本做的。-- 在主副本上配置路由列表内层括号代表优先级组先试 SQLNODE02再试 SQLNODE03最后回落主副本 ALTER AVAILABILITY GROUP [AG_SALES] MODIFY REPLICA ON NSQLNODE01 WITH ( SECONDARY_ROLE ( READ_ONLY_ROUTING_LIST ((SQLNODE02,SQLNODE03),(SQLNODE01)) ) ); -- 确认各副本的路由 URL 和列表都写对了 SELECT ar.replica_server_name, ar.read_only_routing_url, ar.secondary_role_allow_connections_desc, ar.backup_priority FROM sys.availability_replicas ar JOIN sys.availability_groups ag ON ar.group_id ag.group_id WHERE ag.name AG_SALES;READ_ONLY_ROUTING_LIST的嵌套括号是优先级层级同一层内的副本按顺序尝试整层都不可用才进下一层。把 SQLNODE02 和 SQLNODE03 放在同一层意味着读请求优先走 0202 挂了才走 0303 也不行才回主库。如果你希望读请求在两个辅助副本间分摊就把它们放在最外层各自单独一层——但实际经验是 SQL Server 的只读路由不做负载均衡它只按优先级找第一个可用的所以真正的流量分摊要靠客户端侧的多连接串或中间件。路由生效还需要一个条件副本的ALLOW_CONNECTIONS必须是READ_ONLY或ALL。如果显示NO路由列表里写了也没用连接会被拒绝而不是重定向。5. 排错清单五类翻车现场的定位路径下面这五条都是实际处理过的现场按「现象 → 原因 → 解决」记录方便下次照着对号入座。5.1 添加副本时报版本不兼容现象向导执行到一半弹出「可用性副本的版本不受支持」或加入后同步状态长期停在NOT SYNCHRONIZING。原因新节点补丁级别低于现有节点或者主版本不同。可用性组要求所有副本的 SQL Server 版本和补丁完全一致低版本加入高版本组是不被允许的。解决先在主节点执行SELECT VERSION记下完整版本号在新节点上装同一版本的累积更新。装完重启服务再重新执行ADD REPLICA。如果副本已经加进去了但状态异常先REMOVE REPLICA再重新加不要试图在原地修状态。5.2 标准版加第三副本后发现读不了现象副本加入成功sys.dm_hadr_availability_replica_states显示一切正常但把ALLOW_CONNECTIONS设成READ_ONLY时报错或者连接上来直接拒绝。原因标准版的基本可用性组不支持可读辅助副本ALLOW_CONNECTIONS只能配NO只读路由 URL 也无法配置。解决这个没有绕过去的办法要么升级到企业版要么换方案——用日志传送或事务复制把数据同步到一个独立的只读实例上。选型阶段核对SERVERPROPERTY(Edition)能避免整个流程白做。5.3 加集群节点时报无法访问现象Add-ClusterNode卡在验证阶段报「无法访问节点 SQLNODE03」或「群集服务无法启动」。原因三个常见来源——hosts 只改了老节点没改新节点、新节点网卡被识别为公用网络导致防火墙规则不生效、新节点上的群集服务没有启动。解决先在老节点和新节点上互相Resolve-DnsName验证双向解析再用Get-NetConnectionProfile看网络类别是 Public 就改成 DomainAuthenticated最后Get-Service ClusSvc确认群集服务是 Running。三步走完基本都能定位。5.4 启用 AlwaysOn 后服务起不来现象勾选 AlwaysOn 重启服务SQL Server 服务启动失败事件日志里是权限或端点相关错误。原因服务账户对 WSFC 群集对象没有足够权限或者 5022 端口已被其它进程占用或者节点还不是群集成员就打开了开关。解决先确认节点在Get-ClusterNode列表里且状态为 Up再netstat -ano | findstr 5022查端口占用服务账户权限用群集管理器里的「授予对群集对象的访问权限」补。顺序上永远是先进群集、再开开关。5.5 自动种子设定长期卡在等待现象副本加进去了sys.dm_hadr_database_replica_states里查不到数据库sys.dm_hadr_physical_seeding_stats里状态一直是 PENDING 或 FAILED。原因新节点上没有执行GRANT CREATE ANY DATABASE445 端口不通新节点的数据库默认路径下没有足够空间或目录不存在主副本上的数据库处于恢复模式。解决补授权、放行 SMB、在两个节点上确认默认数据目录存在且权限正确。种子过程可以在sys.dm_hadr_physical_seeding_stats里看到传输速率和剩余字节如果速率长期是 0就是链路问题而不是数据问题。另外提一个第五点五只读路由配完不生效。现象是连接串带了ApplicationIntentReadOnly但SELECT SERVERNAME还是返回主副本。原因通常是READ_ONLY_ROUTING_URL写成了 IP、端口写成了 5022、或者路由列表最后一层没放主副本导致回落失败。用sys.availability_replicas逐条核对最直接。6. 上线前的验证把只读路由和同步延迟都跑一遍配置齐了不等于能上线。我习惯在切流量前跑一轮固定检查把「以为配好了」变成「确认配好了」。6.1 用三个查询确认副本与数据库真的就位第一层看副本角色和状态第二层看数据库同步进度第三层看种子传输有没有残留。-- 副本层面角色、连接状态、同步健康度 SELECT ag.name AS ag_name, ar.replica_server_name, ars.role_desc, ars.operational_state_desc, ars.connected_state_desc, ars.synchronization_health_desc, ar.availability_mode_desc, ar.secondary_role_allow_connections_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 ORDER BY ars.role_desc DESC, ar.replica_server_name;三个副本的connected_state_desc都应该是CONNECTEDsynchronization_health_desc是HEALTHY。异步副本的synchronization_state_desc显示SYNCHRONIZING是正常的只有同步副本才要求SYNCHRONIZED。-- 数据库层面日志发送队列和重做队列数字越小越健康 SELECT ar.replica_server_name, DB_NAME(drs.database_id) AS db_name, drs.synchronization_state_desc, drs.log_send_queue_size AS log_send_queue_kb, drs.redo_queue_size AS redo_queue_kb, drs.last_redone_time, drs.secondary_lag_seconds FROM sys.dm_hadr_database_replica_states drs JOIN sys.availability_replicas ar ON drs.replica_id ar.replica_id WHERE drs.is_local 0;log_send_queue_size持续增长说明网络或主库日志生成速度超过传输能力redo_queue_size持续增长说明新节点的磁盘重做跟不上。新节点刚上线时这两个值有一次爬升是正常的稳定后应该回落到接近 0。secondary_lag_seconds是延迟秒数跨机房异步副本在几十秒内可以接受同机房应该是个位数。6.2 用 ApplicationIntent 验证读流量真的落到新节点这一步最容易被跳过也最容易出问题。用 sqlcmd 显式带ApplicationIntentReadOnly连侦听器sqlcmd -S AG-LSN,1433 -d SalesDB -K ReadOnly -Q SELECT SERVERNAME AS node, SERVERPROPERTY(IsReadOnly) AS is_read_only-K ReadOnly就是设置应用意向。返回的node应该是 SQLNODE02 或 SQLNODE03is_read_only为 1。如果返回主副本名字且is_read_only为 0说明路由没生效回到 4.4 逐条核。多跑几次可以观察优先级行为是否符合预期把 SQLNODE02 的服务停掉再跑读请求应该自动落到 SQLNODE03两个都停才回主库。这个验证有个细节连接串必须用侦听器名称不能直连节点名。直连节点时客户端根本不经过路由决策-K参数被忽略你会得到一个「看起来正常」的假象。6.3 灰度切流与回退动作验证通过后我不会一次性把读流量全切过去。通常的做法是先让一两个非核心报表应用指向带ApplicationIntentReadOnly的连接串跑一天看慢查询和延迟曲线确认没问题再逐步扩大。切的过程中重点盯secondary_lag_seconds和redo_queue_size新节点的磁盘 IO 扛不扛得住读负载这两个指标最直观。回退动作要提前想好而不是出事时现找。最快的回退是把新副本的ALLOW_CONNECTIONS改回NO读请求会按路由列表自动跳过它-- 出问题时的紧急回退读流量会绕开该副本 ALTER AVAILABILITY GROUP [AG_SALES] MODIFY REPLICA ON NSQLNODE03 WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS NO));这个操作是瞬时的不影响日志同步等排查完再改回READ_ONLY即可。相比之下REMOVE REPLICA就重了移除后要重新走一遍种子流程大库上耗时会很长非必要不用。从那以后我每次扩副本都会在切换到生产前强制走一遍 6.1 的三个查询和 6.2 的-K ReadOnly验证哪怕前面每一步都自认为配得没问题——因为只读路由这类东西配错了不会报错只会安静地把流量留在主库上等到主库被压垮才发现那时候已经没有后悔药了。希望帮到你。本文还有配套的精品资源点击获取

SQL Server 2016 Always On 扩容只读副本与路由配置

相关推荐

SAP MM供应商寄售全链路：主数据、411 K与MRKO结算

SAP MM供应商寄售全链路：F/10配置、411 K转自有与MRKO结算避坑

VS Code 插件管理指南：分层配置、冲突排查与工作区推荐

更多资讯

CST仿真实现编码超表面分束器设计与优化

象棋炮碾丹砂杀法：肋道施压与子力效率实战解析

旅游大数据推荐系统架构与实现

getsockopt函数详解：网络编程中的套接字选项获取

线性回归在病毒传播预测中的应用与实践

无锁编程核心技术解析与性能优化实践

今日更新

猫狗目标检测数据集：VOC/COCO/YOLO三格式一体化实战包

HDU2017多校标程复现：算法竞赛工程化真题沙盒

Codex Sandbox 隔离环境拆解：AI Agent 的临时实验室如何运作

本周精选

防幻觉智能体架构：电源硬件设计中的大模型编排与工具化实践

OpenGL面试核心考点：渲染管线、矩阵变换与性能排查全解析

全国计算机二级C语言真题PDF高效刷题指南

本月热门

我发现了一个新思路：用 Remotion + Claude Code 像写代码一样自动化生成短视频

Windows下 Codex 中 Chrome 和 Computer Use 插件不可用问题排查及解决参考方式：TaoToken 统一 Key 配置与验证

2026 大模型集体涨价：用 Python 做企业 Token 成本测算与选型避坑（附配置）

读完文章，想聊聊您的网站？