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

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

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 行受影响)

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

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 行受影响)

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"，因为数据库是只读的。

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 行受影响)

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