SQL?Server?2022?AlwaysOn新特性之包含可用性組詳解
SQL Server的容災(zāi)功能一直弱于Oracle和MySQL，無法自動(dòng)同步元數(shù)據(jù)（用戶、登錄名、權(quán)限、SQL 代理作業(yè)、鏈接服務(wù)器），導(dǎo)致在對(duì)鏡像庫或者AlwaysOn執(zhí)行切換之前，都要手動(dòng)同步master、msdb里面的元數(shù)據(jù)。直到2022年11月16日發(fā)布2022版本，在AlwaysOn中增加了包含可用性組功能，解決了長久以來“無法自動(dòng)同步元數(shù)據(jù)”的問題。
包含可用性組是 Always On 可用性組在SQL Server 2022版本發(fā)布的新特性，它支持：
- 在可用性組級(jí)別以及實(shí)例級(jí)別管理元數(shù)據(jù)對(duì)象（用戶、登錄名、權(quán)限、SQL 代理作業(yè)等）。
- 可用性組中的專用包含系統(tǒng)數(shù)據(jù)庫，比如master和msdb。
1 如何創(chuàng)建包含可用性組？
包含可用性組是在普通故障轉(zhuǎn)移群集（可以是工作組、也可以是域）搭建好的基礎(chǔ)上，創(chuàng)建包含可用性組時(shí)，選擇"Contained"或“包含”，其它操作與之前版本的一致性組無差別。
也可以使用命令行創(chuàng)建包含一致性組，在WITH中增加了CONTAINED選項(xiàng)
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)建好包含一致性組后，會(huì)為該AG自動(dòng)創(chuàng)建2個(gè)數(shù)據(jù)庫：[AGNAME_master]、[AGNAME_msdb]，還會(huì)創(chuàng)建偵聽器，這看起來比普通的一致性組多了兩個(gè)數(shù)據(jù)庫：AGNAME_master和AGNAME_msdb。
2 如何使用包含可用性組？
要知道包含可用性組是在元數(shù)據(jù)的同步層面做了改進(jìn)，就是我們前面所說的master和msdb兩個(gè)層面做的改進(jìn)，所以在使用包含可用性組進(jìn)行登錄名、作業(yè)此類的維護(hù)時(shí)，需要使用偵聽器IP連接到包含可用性組，而不是使用主實(shí)例所在的服務(wù)器IP地址。如果使用主實(shí)例所在的服務(wù)器IP地址連接到可用性組創(chuàng)建登錄名的話，只會(huì)保存在本地的數(shù)據(jù)庫實(shí)例上，不會(huì)自動(dòng)同步到包含可用性組中。
3 元數(shù)據(jù)同步驗(yàn)證
3.1 用戶、登錄名和權(quán)限
1、使用包含可用性組偵聽器連接到主節(jié)點(diǎn)，不管切換到AG01_master還是master，實(shí)際上都是使用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é)點(diǎn)，檢查包含一致性組中的元數(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é)點(diǎn)來驗(yàn)證元數(shù)據(jù)是否已同步到輔助節(jié)點(diǎn)。
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，級(jí)別 16，狀態(tài) 2，服務(wù)器 2022-NODE02，第 1 行
無法更新數(shù)據(jù)庫 "test"，因?yàn)閿?shù)據(jù)庫是只讀的。元數(shù)據(jù)：用戶、登錄名、權(quán)限正常同步
3.2 鏈接服務(wù)器
1、通過ssms工具在包含可用性組的主節(jié)點(diǎn)中創(chuàng)建了一個(gè)鏈接服務(wù)器
2、查詢輔助節(jié)點(diǎn)的鏈接服務(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é)點(diǎn)，使用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è)
將兩個(gè)節(jié)點(diǎn)的代理服務(wù)更改為自動(dòng)啟動(dòng)模式，在主節(jié)點(diǎn)創(chuàng)建作業(yè)，驗(yàn)證是否自動(dòng)同步作業(yè)到輔助節(jié)點(diǎn)
1、主節(jié)點(diǎn)創(chuàng)建作業(yè)，每間隔10秒鐘往test數(shù)據(jù)庫的msdb_test表插入當(dāng)前服務(wù)器名和時(shí)間戳。
2、輔助節(jié)點(diǎn)檢查作業(yè)同步情況：輔助節(jié)點(diǎn)自動(dòng)同步作業(yè)，作業(yè)id一致，但其originating_server（初始服務(wù)器）為所在節(jié)點(diǎn)的主機(jī)名
3、測試主節(jié)點(diǎn)故障轉(zhuǎn)移到輔助節(jié)點(diǎn)，驗(yàn)證作業(yè)能否正常執(zhí)行，如果能夠正常執(zhí)行，msdb_test表的server列會(huì)改為故障轉(zhuǎn)移后的節(jié)點(diǎn)主機(jī)名：此處由2022-NODE01自動(dòng)更改為2022-NODE02
4、將AlwaysOn主節(jié)點(diǎn)切換后，作業(yè)僅在主節(jié)點(diǎn)運(yùn)行，不會(huì)在輔助節(jié)點(diǎn)執(zhí)行。
元數(shù)據(jù)：SQL代理作業(yè)正常同步，切換后作業(yè)僅在主節(jié)點(diǎn)執(zhí)行
4 包含可用性組總結(jié)
1、對(duì)于包含可用性組來說，它的元數(shù)據(jù)全部都存放在?可用性組名稱_master??和?可用性組名稱_msdb??這兩個(gè)數(shù)據(jù)庫中。
2、對(duì)于數(shù)據(jù)庫實(shí)例來說，包含可用性組的?可用性組名稱_master??和?可用性組名稱_msdb??這兩個(gè)數(shù)據(jù)庫就是普通的用戶數(shù)據(jù)庫，可讀可寫，但不建議存放用戶的生產(chǎn)數(shù)據(jù)；
3、要連接到包含可用性組，必須使用?包含可用性組的偵聽器??連接，其他連接參數(shù)與舊版本一樣。
這種設(shè)計(jì)就解決了SQL Server一直以來被詬病的元數(shù)據(jù)對(duì)象同步問題。使得用戶、登錄名、鏈接服務(wù)器和作業(yè)都能夠在包含一致性組中的主節(jié)點(diǎn)和輔助節(jié)點(diǎn)同步。?
到此這篇關(guān)于SQL Server 2022 AlwaysOn新特性之包含可用性組介紹的文章就介紹到這了,更多相關(guān)SQL Server 2022 AlwaysOn內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
很多同學(xué)和園友都遇到過sql注入的，其中大部分都是代碼的不嚴(yán)謹(jǐn)造成的，都是犯過很多錯(cuò)誤才學(xué)會(huì)認(rèn)真起來2011-09-09
SQLServer中匯總功能的使用GROUPING,ROLLUP和CUBE
查看SQL Server的幫助才發(fā)現(xiàn),厲害啊,原來還有這么厲害的東西,不由的想起以前做水晶報(bào)表的時(shí)候,原來在SQL Server中就可以實(shí)現(xiàn)這樣的功能.2010-07-07
SQL 實(shí)現(xiàn)某時(shí)間段的統(tǒng)計(jì)業(yè)務(wù)
有一張錯(cuò)誤上報(bào)表，現(xiàn)在要做的是統(tǒng)計(jì)在某個(gè)時(shí)間段[beginTime,endTime]（其中beginTime，endTime由前臺(tái)進(jìn)行傳入）內(nèi)，每個(gè)上報(bào)人上報(bào)錯(cuò)誤點(diǎn)的總數(shù)以及已解決錯(cuò)誤的總數(shù),閑話不說，看代碼2013-01-01
sql?server自動(dòng)生成拼音首字母的函數(shù)
建立一個(gè)查詢，執(zhí)行語句生成函數(shù)fn_GetPy，下面是具體的實(shí)現(xiàn)，需要的朋友可以參考下2014-01-01
SQL JOIN 連接詳細(xì)介紹及簡單使用實(shí)例
這篇文章主要介紹了SQL JOIN 連接詳細(xì)介紹及簡單使用實(shí)例的相關(guān)資料,需要的朋友可以參考下2017-01-01
SqlServer高版本數(shù)據(jù)備份還原到低版本
這篇文章主要為大家詳細(xì)介紹了SqlServer高版本數(shù)據(jù)備份還原到低版本的方法,具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2016-11-11
MSSQLSERVER跨服務(wù)器連接(遠(yuǎn)程登錄)的示例代碼
這篇文章主要介紹了MSSQLSERVER跨服務(wù)器鏈接服務(wù)器的方法，大家參考使用2013-11-11
在SQL查詢中根據(jù)已知ID的集合來查詢結(jié)果我們通常會(huì)用到IN，直接在IN后面給出ID的集合或是在IN后面跟一個(gè)子查詢。2011-08-08
這篇文章主要介紹了SQL Server表分區(qū)刪除，刪除分區(qū)又稱為合并分區(qū)，簡單地講就是將多個(gè)分區(qū)的數(shù)據(jù)進(jìn)行合并。現(xiàn)以表Sales.SalesOrderHeader作為示例，演示如何進(jìn)行表分區(qū)刪除。下面就和小編一起進(jìn)入文章學(xué)習(xí)該內(nèi)容吧2021-10-10
使用SQL實(shí)現(xiàn)車流量的計(jì)算的示例代碼
本文主要介紹了使用SQL實(shí)現(xiàn)車流量的計(jì)算的示例代碼，文中通過示例代碼介紹的非常詳細(xì)，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2022-02-02