SQL?Server?2022?AlwaysOn新特性之包含可用性組怎么用

這篇文章主要介紹“SQL Server 2022 AlwaysOn新特性之包含可用性組怎么用”的相關(guān)知識，小編通過實際案例向大家展示操作過程，操作方法簡單快捷，實用性強，希望這篇“SQL Server 2022 AlwaysOn新特性之包含可用性組怎么用”文章能幫助大家解決問題。

SQL Server的容災(zāi)功能一直弱于Oracle和MySQL，無法自動同步元數(shù)據(jù)（用戶、登錄名、權(quán)限、SQL 代理作業(yè)、鏈接服務(wù)器），導致在對鏡像庫或者AlwaysOn執(zhí)行切換之前，都要手動同步master、msdb里面的元數(shù)據(jù)。直到2022年11月16日發(fā)布2022版本，在AlwaysOn中增加了包含可用性組功能，解決了長久以來“無法自動同步元數(shù)據(jù)”的問題。

包含可用性組是 Always On 可用性組在SQL Server 2022版本發(fā)布的新特性，它支持：

• 在可用性組級別以及實例級別管理元數(shù)據(jù)對象（用戶、登錄名、權(quán)限、SQL 代理作業(yè)等）。

• 可用性組中的專用包含系統(tǒng)數(shù)據(jù)庫，比如master和msdb。

1 如何創(chuàng)建包含可用性組？

包含可用性組是在普通故障轉(zhuǎn)移群集（可以是工作組、也可以是域）搭建好的基礎(chǔ)上，創(chuàng)建包含可用性組時，選擇"Contained"或“包含”，其它操作與之前版本的一致性組無差別。

也可以使用命令行創(chuàng)建包含一致性組，在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

創(chuàng)建好包含一致性組后，會為該AG自動創(chuàng)建2個數(shù)據(jù)庫：[AGNAME_master]、[AGNAME_msdb]，還會創(chuàng)建偵聽器，這看起來比普通的一致性組多了兩個數(shù)據(jù)庫：AGNAME_master和AGNAME_msdb。

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

要知道包含可用性組是在元數(shù)據(jù)的同步層面做了改進，就是我們前面所說的master和msdb兩個層面做的改進，所以在使用包含可用性組進行登錄名、作業(yè)此類的維護時，需要使用偵聽器IP連接到包含可用性組，而不是使用主實例所在的服務(wù)器IP地址。如果使用主實例所在的服務(wù)器IP地址連接到可用性組創(chuàng)建登錄名的話，只會保存在本地的數(shù)據(jù)庫實例上，不會自動同步到包含可用性組中。

3 元數(shù)據(jù)同步驗證

3.1 用戶、登錄名和權(quán)限

1、使用包含可用性組偵聽器連接到主節(jié)點，不管切換到AG01_master還是master，實際上都是使用AG01_master數(shù)據(jù)庫

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
已將數(shù)據(jù)庫上下文更改為 "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
已將數(shù)據(jù)庫上下文更改為 "master"。
1> SELECT DB_ID() AS [Database ID],DB_NAME() AS [ Database Name]
2> go
Database ID  Database Name
----------- ------------------------------
          1 master
(1 行受影響)

2、創(chuàng)建test數(shù)據(jù)庫的登錄名

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
已將數(shù)據(jù)庫上下文更改為 "test"。
1> CREATE USER [test_admin] FOR LOGIN [test_admin]
2> GO
1> USE [test]
2> GO
已將數(shù)據(jù)庫上下文更改為 "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、連接到輔助節(jié)點，檢查包含一致性組中的元數(shù)據(jù)是否已同步(此處建議使用SSMS工具查詢，sqlcmd需要使用-Q參數(shù)提前寫好語句)

• 使用SSMS工具

• 使用sqlcmd帶-Q參數(shù)

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用戶直接登錄到輔助節(jié)點來驗證元數(shù)據(jù)是否已同步到輔助節(jié)點。

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，狀態(tài) 2，服務(wù)器 2022-NODE02，第 1 行
無法更新數(shù)據(jù)庫 "test"，因為數(shù)據(jù)庫是只讀的。

元數(shù)據(jù)：用戶、登錄名、權(quán)限正常同步

3.2 鏈接服務(wù)器

1、通過ssms工具在包含可用性組的主節(jié)點中創(chuàng)建了一個鏈接服務(wù)器

2、查詢輔助節(jié)點的鏈接服務(wù)器情況，從視圖中看到該鏈接服務(wù)器已經(jīng)同步，并且可用

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、故障轉(zhuǎn)移包含可用性組切換到輔助節(jié)點，使用SSMS工具連接到包含可用性組，確定數(shù)據(jù)是正常同步的。

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

元數(shù)據(jù)：鏈接服務(wù)器正常同步

3.3 SQL代理作業(yè)

將兩個節(jié)點的代理服務(wù)更改為自動啟動模式，在主節(jié)點創(chuàng)建作業(yè)，驗證是否自動同步作業(yè)到輔助節(jié)點

1、主節(jié)點創(chuàng)建作業(yè)，每間隔10秒鐘往test數(shù)據(jù)庫的msdb_test表插入當前服務(wù)器名和時間戳。

2、輔助節(jié)點檢查作業(yè)同步情況：輔助節(jié)點自動同步作業(yè)，作業(yè)id一致，但其originating_server（初始服務(wù)器）為所在節(jié)點的主機名

3、測試主節(jié)點故障轉(zhuǎn)移到輔助節(jié)點，驗證作業(yè)能否正常執(zhí)行，如果能夠正常執(zhí)行，msdb_test表的server列會改為故障轉(zhuǎn)移后的節(jié)點主機名：此處由2022-NODE01自動更改為2022-NODE02

4、將AlwaysOn主節(jié)點切換后，作業(yè)僅在主節(jié)點運行，不會在輔助節(jié)點執(zhí)行。

元數(shù)據(jù)：SQL代理作業(yè)正常同步，切換后作業(yè)僅在主節(jié)點執(zhí)行

關(guān)于“SQL Server 2022 AlwaysOn新特性之包含可用性組怎么用”的內(nèi)容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識，可以關(guān)注億速云行業(yè)資訊頻道，小編每天都會為大家更新不同的知識點。