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

接上文：https://www.cnblogs.com/wy123/p/17905118.html，关于AlwaysOn主副本与辅助副本之间提交模式与安全故障转移的话题

参考AlwaysOn属性面板中的信息

1，主节点异步提交模式：如果主要副本配置为“异步提交模式” ，则从节点不管是同步或者异步，主节点提交事务都无须等待从节点（永远是异步模式）。 

2，主节点同步提交模式：需要分两种情况

　　2.1 如果从节点是同步模式，则主节点与从节点同步提交（同步模式）

　　2.2 如果从节点是异步模式，则主节点提交事务都无须等待从节点（异步模式）

3，上面截图备注中的备注：

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

　　上述第2.1中的情况，也就是同步提交模式，主结点提交事务等从提交同步提交，如果对应的从超时或者从节点自身宕机，同步模式将会自动退化为异步模式。

　　此时同步提交模式退化为异步提交(这一点跟MySQL的半同步复制如出一辙，都存在主从同步模式退化的问题)。

 

那么这里先引出几个问题：

1，即便是同步模式，在某些条件下会自动“退化”为异步模式，此时主节点其实是在裸奔，最严重的是从节点直接宕机，主节点还可以正常读写，在某些高安全模式下是不允许单点运行的，这是可以被接受的吗，又如何破解？

2，上面截图中的Session Timeout是什么作用，可以理解成一个普通的连接超时时间吗？

3，截图中的参数required_synchronized_secondaries_to_commit，与节点之间的同步和异步模式有什么关系，该参数该如何理解？忽略此参数的情况下，所谓“自动故障转移”真的可靠吗？

同步提交模式退化为异步模式

如上截图，将SQL1和SQL2两个节点设置为同步模式，SQL1上执行的事务性操作，将会等待日志传送到SQL2节点之后再反馈给客户端提交成功的信息，这一点没有问题

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，可以正常保存。

ALTER AVAILABILITY GROUP [ag1] 
     SET (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

如果是两个节点的同步副本&REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT为1 ，在次要副本中断的恢复之前，主要副本拒绝所有连接。

以下是其他情况下主副本的可用性情况

 

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

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