SQL Server 2022 AlwaysOn新特性之包含可用性组详解

SQL Server的容灾功能一直弱于Oracle和MySQL，无法自动同步元数据（用户、登录名、权限、SQL 代理作业、链接服务器），导致在对镜像库或者AlwaysOn执行切换之前，都要手动同步master、msdb里面的元数据。直到2022年11月16日发布2022版本，在AlwaysOn中增加了包含可用性组功能，解决了长久以来“无法自动同步元数据”的问题。

包含可用性组是 Always On 可用性组在SQL Server 2022版本发布的新特性，它支持：

• 在可用性组级别以及实例级别管理元数据对象（用户、登录名、权限、SQL 代理作业等）。

• 可用性组中的专用包含系统数据库，比如master和msdb。

1 如何创建包含可用性组？

包含可用性组是在普通故障转移群集（可以是工作组、也可以是域）搭建好的基础上，创建包含可用性组时，选择"Contained"或“包含”，其它操作与之前版本的一致性组无差别。

也可以使用命令行创建包含一致性组，在WITH中增加了CONTAINED选项

CREATE AVAILABILITY GROUP MRROBOTO  
  WITH (  
     AUTOMATED_BACKUP_PREFERENCE = SECONDARY,  
     FAILURE_CONDITION_LEVEL  =  3,  
     HEALTH_CHECK_TIMEOUT = 600000,
 CLUSTER_TYPE = WSFC,
 CONTAINED   --包含一致性组
      )  
  FOR  
     DATABASE  TEST  
  REPLICA ON  
     '2022-NODE01' WITH  
        (  
        ENDPOINT_URL = 'TCP://2022-NODE01:5022',  
        AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,  
        FAILOVER_MODE = AUTOMATIC,  
        BACKUP_PRIORITY = 30,  
        SECONDARY_ROLE (ALLOW_CONNECTIONS = NO,  
           READ_ONLY_ROUTING_URL = 'TCP://2022-NODE01:1433' ),
        PRIMARY_ROLE (ALLOW_CONNECTIONS = READ_WRITE,  
           READ_ONLY_ROUTING_LIST = ('2022-NODE01') ),  
        SESSION_TIMEOUT = 10,
SEEDING_MODE = AUTOMATIC
        ),  
     '2022-NODE02' WITH  
        (  
        ENDPOINT_URL = 'TCP://2022-NODE02:5022',  
        AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,  
        FAILOVER_MODE = AUTOMATIC,  
        BACKUP_PRIORITY = 30,  
        SECONDARY_ROLE (ALLOW_CONNECTIONS = NO,  
           READ_ONLY_ROUTING_URL = 'TCP://2022-NODE02:1433' ),  
        PRIMARY_ROLE (ALLOW_CONNECTIONS = READ_WRITE,  
           READ_ONLY_ROUTING_LIST = ('2022-NODE01') ),  
        SESSION_TIMEOUT = 10,
SEEDING_MODE = AUTOMATIC
        );
GO  
ALTER AVAILABILITY GROUP MRROBOTO
 ADD LISTENER 'MRROBOTO_LSNR' ( WITH IP ( ('192.168.1.128'),('255.255.255.0') ) , PORT = 1433 );  
GO

创建好包含一致性组后，会为该AG自动创建2个数据库：[AGNAME_master]、[AGNAME_msdb]，还会创建 * ，这看起来比普通的一致性组多了两个数据库：AGNAME_master和AGNAME_msdb。

2 如何使用包含可用性组？

要知道包含可用性组是在元数据的同步层面做了改进，就是我们前面所说的master和msdb两个层面做的改进，所以在使用包含可用性组进行登录名、作业此类的维护时，需要使用 * IP连接到包含可用性组，而不是使用主实例所在的服务器IP地址。如果使用主实例所在的服务器IP地址连接到可用性组创建登录名的话，只会保存在本地的数据库实例上，不会自动同步到包含可用性组中。

3 元数据同步验证

3.1 用户、登录名和权限

1、使用包含可用性组 * 连接到主节点，不管切换到AG01_master还是master，实际上都是使用AG01_master数据库

C:\Users\Administrator>sqlcmd -H 192.168.1.128 -U sa -Y 30
密码:
1> select @@servername;
2> go
------------------------------
2022-NODE01
(1 行受影响)
1> use AG01_master
2> go
已将数据库上下文更改为 "master"。
1> SELECT DB_ID() AS [Database ID],DB_NAME() AS [ Database Name]
2> go
Database ID  Database Name
----------- ------------------------------
         1 master
(1 行受影响)
1> use master
2> go
已将数据库上下文更改为 "master"。
1> SELECT DB_ID() AS [Database ID],DB_NAME() AS [ Database Name]
2> go
Database ID  Database Name
----------- ------------------------------
         1 master
(1 行受影响)

2、创建test数据库的登录名

1> CREATE LOGIN [test_admin] WITH PASSWORD=N'test.123', DEFAULT_DATABASE=[test], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
2> GO
1> USE [test]
2> GO
已将数据库上下文更改为 "test"。
1> CREATE USER [test_admin] FOR LOGIN [test_admin]
2> GO
1> USE [test]
2> GO
已将数据库上下文更改为 "test"。
1> ALTER ROLE [db_owner] ADD MEMBER [test_admin]
2> GO
1> select name,dbname,sid,createdate from sys.syslogins where loginname='test_admin'
2> go
name                           dbname                         sid                            createdate
------------------------------ ------------------------------ ------------------------------ -----------------------
test_admin                     test                           0x8CEFB4D480A8E54F97C86ADF9E6934FD 2022-10-18 14:40:02.913

3、连接到辅助节点，检查包含一致性组中的元数据是否已同步(此处建议使用SSMS工具查询，sqlcmd需要使用-Q参数提前写好语句)

• 使用SSMS工具

• 使用sqlcmd带-Q参数

C:\Users\Administrator>sqlcmd -S 192.168.1.128 -U sa -d AG01_master -Y 30 -K ReadOnly -Q "select @@servername;select name,dbname,sid,createdate from AG01_master.sys.syslogins where loginname='test_admin'"
密码:
------------------------------
2022-NODE02
(1 行受影响)
name                           dbname                         sid                            createdate
------------------------------ ------------------------------ ------------------------------ -----------------------
test_admin                     test                           0x8CEFB4D480A8E54F97C86ADF9E6934FD 2022-10-18 14:40:02.913
(1 行受影响)

也可以使用test_admin用户直接登录到辅助节点来验证元数据是否已同步到辅助节点。

C:\Users\Administrator>sqlcmd -S 192.168.1.128 -U test_admin -d test -Y 30 -K readonly
密码:
1> select @@servername
2> go
------------------------------
2022-NODE02
(1 行受影响)
1> select * from Foo
2> go
Bar
-----------
         1
(1 行受影响)
1> insert into Foo values(2)
2> go
消息 3906，级别 16，状态 2，服务器 2022-NODE02，第 1 行
无法更新数据库 "test"，因为数据库是只读的。

元数据：用户、登录名、权限正常同步

3.2 链接服务器

1、通过ssms工具在包含可用性组的主节点中创建了一个链接服务器

2、查询辅助节点的链接服务器情况，从视图中看到该链接服务器已经同步，并且可用

C:\Users\Administrator>sqlcmd -S 192.168.1.128 -U sa -Y 30 -d AG01_master  -K ReadOnly -Q "select @@servername;select name,data_source from AG01_master.sys.servers where is_linked=1;SELECT Bar FROM [NODE02-FROM125].test.dbo.Foo"
密码:
------------------------------
2022-NODE02
(1 行受影响)
name                           data_source
------------------------------ ------------------------------
NODE02-FROM125                 192.168.1.126
(1 行受影响)
Bar
-----------
         1
(1 行受影响)

3、故障转移包含可用性组切换到辅助节点，使用SSMS工具连接到包含可用性组，确定数据是正常同步的。

C:\Users\Administrator>sqlcmd -S 192.168.1.126 -U sa -Y 30 -Q "ALTER AVAILABILITY GROUP [AG01] FAILOVER"
密码:输入密码

元数据：链接服务器正常同步

3.3 SQL代理作业

将两个节点的代理服务更改为自动启动模式，在主节点创建作业，验证是否自动同步作业到辅助节点

1、主节点创建作业，每间隔10秒钟往test数据库的msdb_test表插入当前服务器名和时间戳。

2、辅助节点检查作业同步情况：辅助节点自动同步作业，作业id一致，但其originating_server（初始服务器）为所在节点的主机名

3、测试主节点故障转移到辅助节点，验证作业能否正常执行，如果能够正常执行，msdb_test表的server列会改为故障转移后的节点主机名：此处由2022-NODE01自动更改为2022-NODE02

4、将AlwaysOn主节点切换后，作业仅在主节点运行，不会在辅助节点执行。

元数据：SQL代理作业正常同步，切换后作业仅在主节点执行

4 包含可用性组总结

1、对于包含可用性组来说，它的元数据全部都存放在可用性组名称_master和可用性组名称_msdb这两个数据库中。

2、对于数据库实例来说，包含可用性组的可用性组名称_master和可用性组名称_msdb这两个数据库就是普通的用户数据库，可读可写，但不建议存放用户的生产数据；

3、要连接到包含可用性组，必须使用包含可用性组的 * 连接，其他连接参数与旧版本一样。

这种设计就解决了SQL Server一直以来被诟病的元数据对象同步问题。使得用户、登录名、链接服务器和作业都能够在包含一致性组中的主节点和辅助节点同步。

来源：https://www.cnblogs.com/kernelry/p/17359501.html