SQL Server 2022 AlwaysOn新特性之包含可用性组详解
作者:开始折腾吧 发布时间:2024-01-26 07:51:49
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
![](https://www.aspxhome.com/images/zang.png)
![](https://www.aspxhome.com/images/jiucuo.png)
猜你喜欢
- 目录本文目标如何使用本教程正则表达式到底是什么?入门测试正则表达式元字符字符转义重复字符类反义替换分组后向引用零宽断言负向零宽断言注释贪婪与
- 引言:最近邻插值Nearest Neighbour Interpolate算法是图像处理中普遍使用的图像尺寸缩放算法,由于其实现简单计算速度
- SPAN 和 DIV 的区别在于,DIV(division)是一个块级元素,可以包含段落、标题、表格,乃至诸如章节、摘要和备注等。而SPAN
- 一、变量的定义程序中,数据都是临时存储在内存中,为了更快速的查找或使用这个数据,通常我们把这个数据在内存中存储之后,给整个数据定义一个名称,
- 我想让一片文章,每到3000字就分到下一条插入到数据库,求高手 <%Dim Content Conte
- 本文实例讲述了python实现给微信公众号发送消息的方法。分享给大家供大家参考,具体如下:现在通过发微信公众号信息来做消息通知和告警已经很普
- 1、python大量的库为数据分析提供了完整的工具集2、比起MATLAB、R语言等其他主要用于数据分析语言,python语言功能更加健全3、
- 很多时候我们都会有一个麻烦事,就是打开pycharm或者VScode等的时候,都有可能因为自己电脑上面安装的第三方模块过多,导致电脑很卡,风
- 视图在django中,视图对WEB请求进行回应视图接收reqeust对象作为第一个参数,包含了请求的信息视图就是一个Python函数,被定义
- 继续Mootools常用方法扩展,依然还是String类的扩展。方法:format说明:一个非常简单的format方法,和C#
- 针对之前安装mysql的笔记进行了总结,分享给大家。1.下载下载地址:http://dev.mysql.com/downloads/mysq
- 本文为大家分享的Python工程师面试题主要与Python Web相关,供大家参考,具体内容如下1、解释一下 WSGI 和 FastCGI
- 导语带大家写个微博自动抽奖小程序吧,motivation和之前的B站自动抽奖小程序一样:不想内卷了,整个B站全自动抽奖的小程序吧,万一不小心
- one: declare @s varchar(20) declare @i varchar(20) set @i='' s
- Dreamweaver MX 2004的强大功能以及更加完善的人性化设置已经深受大家喜爱。在此笔者就谈
- 简介显示大量已经按类别(不是很多)排序的数据但没有类别分界线,用户很难找到所需要的类别。例如,数据库中只有9个类别(8个不同的类别和1个nu
- 本文实例讲述了python写日志文件操作类与应用。分享给大家供大家参考,具体如下:项目的开发过程中,日志文件是少不了的,通过写日志文件,可以
- “你不必严格遵守这些原则,违背它们也不会被处以宗教刑罚。但你应当把这些原则看成警铃,若违背了其中的一条,那么警铃就会响起
- 对于每个程序开发者来说,调试几乎是必备技能。代码写到一半卡住了,不知道这个函数执行完的返回结果是怎样的?调试一下看看代码运行到一半报错了,什
- HTTP暴力破解、撞库,有一些惯用的技巧,比如:1. 在扫号人人网时,我遇到单个账号错误两次,强制要求输入验证码,而对方并未实施IP策略。我