SQLserver AlwaysOn 提交模式与节点的可用性

select 
    r.replica_server_name,
    r.availability_mode,
    r.availability_mode_desc,
    r.failover_mode_desc,
    r.session_timeout,
    s.connected_state_desc
from sys.availability_replicas r inner join sys.dm_hadr_availability_replica_states s on s.replica_id = r.replica_id

数据已同步到SQL1和SQL2两个节点

目前SQL1和SQL2两个节点是同步模式，现在简单粗暴，直接关闭SQL2节点，模拟同步模式的辅助副本宕机的情况

此时会发现，SQL1节点仍旧可以正常读写，这就是同步模式的退化机制，就是同步模式的辅助副本，不管是同步模式（这里SQL1和SQL2就是同步模式）或者异步模式，都不会影响主副本的读写。

其实此时主副本已经处于裸奔了，因为与他同步模式的辅助副本实际上已经下线。

这种情况下，主副本和辅助副本之间所谓的“同步提交”模式实际上形同虚设，实际上完整的数据实际上只有SQL1上的一份（SQL3是一步提交模式），极端情况下如果SQL1再宕机，存在数据丢失的风险。

AlwaysOn节点中Session Timeout的作用

参考这里：https://learn.microsoft.com/zh-cn/sql/database-engine/availability-groups/windows/availability-modes-always-on-availability-groups?view=sql-server-ver15&redirectedfrom=MSDN

备注里提到的“如果某一辅助副本超过了主副本的会话超时期限，则主副本将暂时切换到该辅助副本的异步提交模式。 在该辅助副本重新与主副本连接后，它们将恢复同步提交模式。”

如果同步提交模式在祝福本会话超时之前，主节点在写入数据时时什么情况？再次实验，把SQL2节点正常启动，恢复同步提交模式，同时修改Session Timeout为一个较大的值，这里修改为600（默认是10秒）

此时再次关闭SQL2节点，然后在SQL1节点上写入数据，会出现什么情况？参考截图，实际上这个insert语句的执行会一直等下去，直到超出上面设置的session timeout的值。

因此这里就有一个选择，在某些高安全的模式下，如果跟主副本同步提交的辅助副本宕机，主副本可选选择一会等待，直到辅助副本上线

实际上这个SQL会一直执行到上面设置的Session Timeout，也就是600秒（读操作不受影响），这里执行到9分52秒是因为设置完这个时间，从关闭SQL2节点的MSSQL服务到SQL开始执行有几秒钟时间，超时时间是SQL1最早探测到SQL2下线开始计算

令笔者意外的是，在上面这个SQL执行期间，启动SQL2上的服务，发现主副本（SQL1）上可以正常连接到SQL2，但是SQL2无法加入AG group，直至这个insert语句执行完成，SQL2才正常加入到AG Group中来。

REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT

正如这个参数名字上的含义，主副本在写入数据时，要求同步提交到（N个）辅助副本，联系上面提交模式的选择，如果节点之间本身是异步提交模式，那么设置同步提交到N个节点是没有意义的，这里尝试将所有节点设置为异步提交模式，同时REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT设置为1，尝试保持会发现直接报错：

“The Alter operation is not allowed by the current availability-group configuration. The required_synchronized_secondaries_to_commit 1 is greater than the 0 possible secondary synchronous-commit availability replicas in availability group 'ag1'. Change one of the existing asynchronous-commit replicas to the synchronous-commit availability mode, and retry the operation. (.Net SqlClient Data Provider)”

其实不难理解：既然要求同步提交打一个节点，那么节点之间必然是同步模式，如果是异步提交模式，就相互矛盾了。

按照正常模式，设置SQL1和SQL2之间为同步提交模式，设置REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT为1，可以正常保存。

此时AG节点正常同步数据，如果目前再次关闭SQL2节点，会发生什么？

同样地，SQL1上的写操作会等待SQL2响应（SQL1上的读操作不受影响），此时SQL2已经宕机，然后等待超时（session timeout）之后会发生什么？

可以看到此时SQL1上并没有像第二个case的“自动退化”为异步模式，而是给出了一个插入失败的错误提示

Remote harden of transaction 'INSERT' (ID 0x000000000000ddc6 0000:000003c4) started at Dec 18 2023  9:34PM in database 'DB01' at LSN (37:2080:3) failed.

(1 row affected)
Msg 596, Level 21, State 1, Line 35
Cannot continue the execution because the session is in the kill state.
Msg 0, Level 20, State 0, Line 35
A severe error occurred on the current command.  The results, if any, should be discarded.

此时再尝试访问DB01会给出一个如下错误提示：

Msg 988, Level 14, State 1, Line 34
Unable to access database 'DB01' because it lacks a quorum of nodes for high availability. Try the operation again later.

也就是DB01连读操作都不允许了，参考这里：https://learn.microsoft.com/zh-cn/sql/linux/sql-server-linux-availability-group-ha?view=sql-server-ver15

在完成故障转移之前，主要副本拒绝所有连接。

可见，AlwaysOn节点之间，如果在忽略REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT参数的情况下，因为主副本存在一个退化为异步的情况，

不管是同步或者异步模式，实际上都是不完全“安全”的。只有在要求强一致（两个或者多个节点同步提交）且设置REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT大于1的时候，主节点才能完全安全模式。