当SQL Server爱上Linux：AVAILABILITY_MODE 和 DataGuard 的实践差距

作者 | 张乐奕：Oracle ACE 总监，ACOUG （中国 Oracle 用户组）联合发起人。Oracle 数据库高可用解决方案与 Exadata 一体机专家。长于数据库故障诊断，性能调优。作为多家知名论坛版主，热衷社区技术分享，同时也是 Exadata 用户组的发起人，组织策划并作为技术分享者的活动已超过百场。

在上一篇文章中，我们描述了 配置 SQL Server 2017 上的可用性组初体验 。在测试可用性模式时，Linux下的某些行为显得匪夷所思。在 SQL on Linux 中如果设置了 availability_mode 为 SYNCHRONOUS_COMMIT，那么必须至少有一个 secondary replica（或者一个config node）是存活的，否则 priamry replica 中就不再允许任何 DML 操作。

请看以下一些有趣的验证过程。

关于 AVAILABILITY_MODE

需要注意的是，与 Oracle DataGuard 不尽相同的概念是，在 Always On AG 每个replica 上都可以设置自己的 AVAILABILITY_MODE。

AVAILABILITY_MODE 参数有三个可选值，分别是：

• SYNCHRONOUS_COMMIT
• ASYNCHRONOUS_COMMIT
• CONFIGURATION_ONLY。

SYNCHRONOUS_COMMIT：同步提交，意味着主 replica 的事务必须等到备 replica 将变更日志写入磁盘中才可以提交。可以设置包括主 replica 在内的最多三个replica 处于同步提交状态。

ASYNCHRONOUS_COMMIT：异步提交，意味着 replica 无需等待备 replica 的动作而可以直接提交成功。

CONFIGURATION_ONLY：仅同步 AG 配置元数据。设置为该值的 replica 仅会从主 replica 中将 AF 配置的元数据同步过来，不会同步任何用户表数据。

在一个多节点 replica 的 AG 环境中，如果：

主库和其中任何一个备库设置为 SYNCHRONOUS_COMMIT，则主库的日志提交必须等待该备库完成日志写入；

主库设置为 SYNCHRONOUS_COMMIT，而所有备库都设置为ASYNCHRONOUS_COMMIT，则主库无需等待；

主库设置为 ASYNCHRONOUS_COMMIT，则无视备库上该参数的设置，主库均无需等待。

在多节点的 AG 环境中，假设一个主库配置了两个同步的 secondary，那么是不是要等待这两个secondary都完成日志写入才能提交事务呢？此时又引入了required_synchronized_secondaries_to_commit参数。

关于required_synchronized_secondaries_to_commit

required_synchronized_secondaries_to_commit 参数是在 SQL Server 2017中引入的，这个参数从直观意义上就可以看得出是指定当 commit 的时候需要有个几个同步的 secondaryreplica 存活。

这个参数在三节点的AG集群中，默认值为1，也就是如果至少要存活一个secondaryreplica，主库上的事务才可以提交，否则 commit 就会一直等待。这很好理解。

但是不好理解的是，该参数可以手工修改为 0，从字面上看应该是说，即使所有secondary replica 都不同步了，也是可以允许 commit 的。

但是实际情况却并非如此，修改为 0 是不起作用的。通过以下测试可以知道。

首先，设置 required_synchronized_secondaries_to_commit 参数为 0

sudo pcs resourceupdate ag_cluster required_synchronized_secondaries_to_commit=0 [Kamus@centos1~]$ sql 1> selectname,required_synchronized_secondaries_to_commit from sys.availability_groups; 2> GO name required_synchronized_secondaries_to_commit ------------------------------------------------------------------------- ag1 0 (1 rows affected)

现在三个节点都是正常状态。

1> selectr.replica_server_name,r.availability_mode_desc,r.session_timeout,rs.connected_state_desc 2> from sys.availability_replicas r,sys.dm_hadr_availability_replica_statesrs 3> where r.replica_id=rs.replica_id; 4> GO replica_server_name availability_mode_desc session_timeout connected_state_desc ------------------------------------------------------------ --------------- ------------------------------ centos1 SYNCHRONOUS_COMMIT 10 CONNECTED centos2 SYNCHRONOUS_COMMIT 10 CONNECTED centos3 SYNCHRONOUS_COMMIT 10 CONNECTED (3 rows affected)

在主节点上进行 Insert，可以成功，这很好。

1> insert intot1 select * from sys.databases; 2> GO (6 rows affected)

停掉一个 secodary replica。显示第二个节点已经 DISCONNECTED。

1> selectr.replica_server_name,r.availability_mode_desc,r.session_timeout,rs.connected_state_desc 2> from sys.availability_replicasr,sys.dm_hadr_availability_replica_states rs 3> where r.replica_id=rs.replica_id; 4> GO replica_server_name availability_mode_desc session_timeout connected_state_desc ------------------------------------------------------------ --------------- ------------------------------ centos1 SYNCHRONOUS_COMMIT 10 CONNECTED centos2 SYNCHRONOUS_COMMIT 10 DISCONNECTED centos3 SYNCHRONOUS_COMMIT 10 CONNECTED (3 rows affected)

在主库上进行 Insert，还是可以成功，这很好。

1> insert intot1 select * from sys.databases; 2> GO (6 rows affected)

再停掉一个 secodary replica。显示2、3节点都已经 DISCONNECTED。

1> selectr.replica_server_name,r.availability_mode_desc,r.session_timeout,rs.connected_state_desc 2> from sys.availability_replicasr,sys.dm_hadr_availability_replica_states rs 3> where r.replica_id=rs.replica_id; 4> GO replica_server_name availability_mode_desc session_timeout connected_state_desc ------------------------------------------------------------ --------------- ------------------------------ centos1 SYNCHRONOUS_COMMIT 10 CONNECTED centos2 SYNCHRONOUS_COMMIT 10 DISCONNECTED centos3 SYNCHRONOUS_COMMIT 10 DISCONNECTED (3 rows affected)

在主库上执行 Insert，此时 hang 住，这很不好。

1> insert intot1 select * from sys.databases; 2> GO

更讨厌的是，对于该表的查询也会 hang 住，这就更不好了。

2> selectcount(*) from t1; 3> GO

现在数据库中的等待是什么呢？确实是 HADR_SYNC_COMMIT。

1> selectSTATUS,COMMAND,DATABASE_ID,WAIT_TYPE,WAIT_TIME from sys.dm_exec_requests wherecommand='INSERT'; 2> GO STATUS COMMAND DATABASE_IDWAIT_TYPE WAIT_TIME ------------------------------------------------------------ ----------- ----------------------------------------- suspended INSERT 5HADR_SYNC_COMMIT 1670 (1 rows affected)

如果我们更进一步做一个session的xevent trace，可以看到等待的是 WaitForHarden，而 Harden 的意思即是 remote replica 的日志写入。现在主库在等待一个备库的日志完成写入，然后自己才能提交成功。在正常情况下，当主库不再需要等待备库而可以自行 commit 的情况下，在 xevent trace 中应该出现将备库的 commit_policy标志为donothing状态，也就是在xevent中应该要出现hadr_db_partner_set_policy 事件才是正常的，然而这里并没有出现。

但是我们明明把 required_synchronized_secondaries_to_commit 参数设置为0了。所以也许微软需要更新一下文档，明确说明在多个 sync 的 secondary 存在的情况下，该参数即使修改为 0 也仍然按照 1 来处理。

结论

在 SQL on Linux 中如果设置了 availability_mode 为 SYNCHRONOUS_COMMIT，那么必须至少有一个 secondary replica（或者一个config node）是存活的，否则 priamry replica 中就不再允许任何 DML 操作，而尝试对于某表进行 DML 之后，还会进一步阻塞对于该表的查询，即使设置了required_synchronized_secondaries_to_commit=0 也是无效的。

这是一个很奇怪的 design，因为这强制去掉了当一个集群中所有备库都崩溃时，主库能够自动转为异步提交模式的功能，从而造成了所有备库失效则会影响主库业务正常进行这样一个大问题。

实际上这个 design 是在 SQL on Linux 2017 CU1 之后才修改的，在 CU1 之前还是允许当所有备库都失效以后，主库仍然是可以正常读写的。甚至在现在的文档中仍然保留了这样的描述。

https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-availability-group-ha?view=sql-server-linux-2017#two-synchronous-replicas

也就是说在 SQL Server 2017CU 1 之后，不再支持单纯的 read-scale 功能的 AG 了。虽然不太理解微软的 SQL Server 程序员是怎么考虑这个问题的，但是现状就是如此。

相比起 Oracle 的 Data Guard 而言，也就是现在 SQL Server 的 AG 只有同步（等同于 DG 的 Maximum protection），异步（等同于 DG 的 Maximum performance）这样两种方式，而缺少了 DG 的 Maximum availability 模式。

我只能认为这是一个设计理念的问题，微软的程序员更倾向于关注数据一致性，但是我期望在未来 SQL Server 可以对此进行改进。