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

此时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的时候,主节点才能完全安全模式。

 

热门相关:陆鸣至尊神殿   魔葫   我有一张均富卡   仙王   万道龙皇