暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / SQLServer AlwaysON 跨域部署和切换实践

SQLServer AlwaysON 跨域部署和切换实践

数据库技术闲谈 2024-06-18
821

上次研究 SQLServer 高可用还是在 RDS SQLServer 数据库刚上线时，那时候用的主要是 SQLServer 2008 的数据库镜像复制（Mirror）技术。SQLServer 的镜像就是物理备库，在见证服务器的支持下可以自动切换主备。缺点是切换是数据库级别的，并且还只支持一个镜像库。后来 SQLServer 2012 推出 AlwaysON（全面的高可用性和灾难恢复解决方案），支持可用性组的概念。一个可用性组里每个数据库可以包括多个副本（ 1 个主副本和多个辅助副本，即一主多备），此外可用性组可以支持多个数据库集中做高可用和容灾管理。

SQLServer AlwaysON 实现是在数据库镜像技术基础上加上 Windows 故障转移集群的能力实现了SQLServer 主实例故障时自动切换到备实例的功能。
有关 Windows 故障转移集群的部署依赖 Windows 域控技术，其部署过程这里就略去不提。在本文中线下机房和云上机房由于网络架构的独立，分别使用不同的域控管理数据库Windows 服务器。SQLServer 部署好后还要启用 HADR 支持，这个部署配置过程请参考微软官网。
本文重点描述如何在不同域之间部署 SQLServer AlwaysON 分布式可用性组，实现数据库跨域的容灾和切换。这个方案可以用于 SQLServer 服务器机房搬迁、数据库批量上云等场景。
文章中关于 SQLServer 产品功能的理解属于个人观点，如果有误，欢迎留言指出。


一、分布式可用性组部署

1.   数据库可用性组架构规划

数据库资源和架构规划整理如下。

其中底色为绿色的为客户线下当前 SQLServer AlwaysON 基础可用性组部署架构，镜像节点之间使用的是域账户认证方式。底色为蓝色的为计划部署的云上 SQLServer AlwaysON 基础可用性组。在线下和云上两个基础可用性组之间将部署一个分布式可用性组，用于将线下数据库同步到云上。


要构建跨域的分布式可用性组，就没有办法使用统一的域账户在多个可用性组之间做镜像连接认证，所以需要调整为证书认证。按官方推荐的证书部署方法，每个实例都要配置一个证书，并复制到其他实例节点且创建对应的登录名和用户名。在上面这个规划里一共有 4 个实例，则一共会产生 4 份证书。客户真实环境里每个SQLServer AlwaysON 集群是有三个实例（一主两备）。如果选择这个方法，则一共会产生 6 份证书，且每个实例要额外创建 5 个登录名和用户名。这个会明显增加实际部署复杂度和后期运维难度（容易出错）。
实际上在整个部署架构里也可以只使用同一份证书就行，这个证书可以由任意实例创建都行，当然选择当前主实例会表面上更合理一些（全局只有一个主实例）。不过这样过分的精简也给后期留下一个风险，云上和云下的SQLServer 集群毕竟都是独立的环境，共用一份证书后期时间久了运维可能也说不清楚这个证书的作用。

于是，这里我们就折衷一下，线下的SQLServer 共用一份证书，云上的共用另外一份证书，分别由对应的主实例创建这个证书。这里还要注意的是，备可用性组的主实例实际上也是主可用性组的主实例的备副本（镜像实例），所以它并不能进行读写操作。不过SQLServer 支持将备实例打开只读查询功能，这里就不研究测试了。一般在集群有 3 个及以上实例节点的时候，会开放一个备实例的读功能，实现业务访问数据库的读写分离需求。


2.   创建统一的可用性组认证方式

可用性组的认证具体就是指数据库镜像端点的连接认证，前面已经描述过，统一调整为证书认证。所以这一节就是要配置统一的实例登录名、数据库用户名和证书，并设置证书的所有者为统一的用户名。

这里先挑选线下机房的主实例节点WIN-71 创建实例证书。

  • 线下机房主可用性组主实例WIN-71 创建实例证书并备份。

方法跟官网文档基本一样，首先实例要创建主密钥（一个就够），然后创建实例证书。不同之处在备份导出证书的时候，将证书的私钥也备份出去（为了密钥安全，加密备份）。

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'abc@KEY#123';

GO

USE master;

CREATE CERTIFICATE idc_a_Cert

WITH SUBJECT = 'IDC A Certificate',  

EXPIRY_DATE = '12/30/2099';  

GO

BACKUP CERTIFICATE idc_a_Cert

TO FILE = N'\\win-75\backup\idc_a_Cert.cer'

WITH PRIVATE KEY(

FILE = N'\\win-75\backup\idc_a_privkey',

ENCRYPTION BY PASSWORD = 'P@ssw0rd');

这里证书备份私钥时的加密密码一定要记得，后面还原证书时会用到。
证书备份成功后再删除这个证书。这一步不是必须的，是为了后面步骤操作统一。此外也可以有助于理解证书的作用时机。

DROP CERTIFICATE idc_a_Cert;

GO

  • 云上机房备可用性组主实例WIN-75 创建实例证书并备份。

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'abc@KEY#123';
GO
USE master;
CREATE CERTIFICATE idc_b_Cert
WITH SUBJECT = 'IDC B Certificate',  
EXPIRY_DATE = '12/30/2099';  
GO
BACKUP CERTIFICATE idc_b_Cert
TO FILE = N'\\win-75\backup\idc_b_Cert.cer'
WITH PRIVATE KEY(
FILE = N'\\win-75\backup\idc_b_privkey',
ENCRYPTION BY PASSWORD = 'P@ssw0rd');
DROP CERTIFICATE idc_b_Cert;
GO

  • 在线下机房和云上机房所有实例里创建两个机房的登录名、用户名和还原两个机房的证书。

以下操作在所有实例里执行：WIN-71、WIN-72、WIN-76、WIN-77 。
USE master;
CREATE LOGIN idc_a_login
WITH PASSWORD = 'xxx@YYY#ZZZ'; 
GO
USE master;
CREATE USER idc_a_user FOR LOGIN idc_a_login;
GO
USE master;
CREATE CERTIFICATE idc_a_cert
AUTHORIZATION idc_a_user 
FROM FILE = N'\\win-75\backup\idc_a_cert.cer' 
WITH PRIVATE KEY (FILE = N'\\win-75\backup\idc_a_privkey',
DECRYPTION BY PASSWORD = 'P@ssw0rd');
GO
USE master;
CREATE LOGIN idc_b_login
WITH PASSWORD = 'xxx@YYY#ZZZ'; 
GO
USE master;
CREATE USER idc_b_user FOR LOGIN idc_b_login;
GO
USE master;
CREATE CERTIFICATE idc_b_cert
AUTHORIZATION idc_b_user 
FROM FILE = N'\\win-75\backup\idc_b_cert.cer' 
WITH PRIVATE KEY (FILE = N'\\win-75\backup\idc_b_privkey',
DECRYPTION BY PASSWORD = 'P@ssw0rd');
GO
此时查看实例上的证书信息如下。
SELECT c.name cert_name, p.name user_name ,c.pvt_key_encryption_type_desc ,c.subject,c.expiry_date ,c.start_date, p.type_desc
FROM sys.certificates c  JOIN sys.database_principals p ON (c.principal_id=p.principal_id)
WHERE expiry_date > getdate();

WIN-71 :

db_71_cert 和 db_72_cert 是前面按官网证书部署可用性组步骤的产物，没有删除放在这里是方便对比观察。可以看到，在WIN-71 上删除证书 idc_a_cert 再还原后，其证书所有者是可以指定为统一的用户名idc_a_user ，并且证书的私钥是加密的。

WIN-76：


3.   部署SQLServer备可用性组

前面可用性组认证方式都准备好了，接下来就先部署备可用性组，再去调整主可用性组。这样能最大程度的减少业务停机时间。
  • 创建数据库镜像端点并指定连接证书。

以下操作在WIN-76、WIN-77 顺序执行。

步骤包含创建镜像端点、指定连接认证证书、授权用户连接端点。
DROP ENDPOINT EP_IDC_B;
CREATE ENDPOINT EP_IDC_B
STATE = STARTED
AS TCP (
LISTENER_PORT = 5022,
LISTENER_IP = ALL
)
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE idc_a_cert,
ROLE = ALL
);
USE master;
GRANT CONNECT ON ENDPOINT::EP_IDC_B TO [idc_a_login] ;
GRANT CONNECT ON ENDPOINT::EP_IDC_B TO [idc_b_login] ;
GO
查看实例上的镜像信息如下。
SELECT e.name endpoint_name, p.name login_name , protocol_desc ,e.type_desc , state_desc , role_desc ,connection_auth_desc ,c.name cert_name ,encryption_algorithm_desc ,p.type_desc 
FROM sys.database_mirroring_endpoints e JOIN sys.server_principals p ON (e.principal_id =p.principal_id )
LEFT JOIN sys.certificates c ON (e.certificate_id=c.certificate_id) ;


此时还不会有镜像连接。

  • 主实例WIN-76创建可用性组。

实际上只有这个实例了创建了可用性组，它才是真正意义上的主实例。
CREATE AVAILABILITY GROUP [sql2016_ag_2]
FOR
REPLICA ON N'WIN-76' WITH (ENDPOINT_URL = N'TCP://10.0.0.76:5022',
FAILOVER_MODE = MANUAL,
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
BACKUP_PRIORITY = 50,
SECONDARY_ROLE(ALLOW_CONNECTIONS = NO),
SEEDING_MODE = MANUAL),
N'WIN-77' WITH (ENDPOINT_URL = N'TCP://10.0.0.77:5022',
FAILOVER_MODE = MANUAL,
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
BACKUP_PRIORITY = 50,
SECONDARY_ROLE(ALLOW_CONNECTIONS = NO),
SEEDING_MODE = MANUAL);
GO

  • 备实例WIN-77 加入到本地可用性组。

ALTER AVAILABILITY GROUP [sql2016_ag_2] JOIN
ALTER AVAILABILITY GROUP [sql2016_ag_2] GRANT CREATE ANY DATABASE
GO
这一步成功后，就可以查看实例的镜像连接信息了，如下。

WIN-76：

WIN-77：

  • 数据库备份并还原。

这时候的情形就跟前面证书部署可用性组有点不一样，因为这个主实例 WIN-76 并没有数据库。在规划上，它也只是主可用性组主实例的备实例。在分布式可用性组里，这个备可用性组的主实例是“转发实例”的角色。并且这个角色并不固定是这个实例。
所以这里数据库的备份用的是主可用性组上的数据库备份。如果前面备份过可以复用数据备份，只是要注意该数据备份后的所有日志备份都是需要的。如果做不到这点，那就重新发起新的备份。
以下操作在备可用性组的所有实例上顺序执行。
-- On the server instance that hosts the secondary replica,
-- Restore database backups using the WITH NORECOVERY option:
RESTORE DATABASE MyDb1
FROM DISK = N'\\win-75\backup\MyDb1.bak'  
WITH NORECOVERY, REPLACE, STATS=10; 
GO
RESTORE DATABASE MyDb2
FROM DISK = N'\\win-75\backup\MyDb2.bak'  
WITH NORECOVERY, REPLACE, STATS=10;   
GO
-- Restore the transaction log on each secondary database,
-- using the WITH NORECOVERY option:
RESTORE LOG MyDb1
FROM DISK = N'\\win-75\backup\MyDb1.trn'  
WITH FILE=1, NORECOVERY; 
GO
RESTORE LOG MyDb2
FROM DISK = N'\\win-75\backup\MyDb2.trn'  
WITH FILE=1, NORECOVERY; 
GO
执行完后，备可用性组的每个实例上数据库的状态都是“正在还原”。

  • 将数据库加入到备可用性组中。

正常操作来说，最后要将数据库加入到可用性组中。SQL 如下。
ALTER DATABASE MyDb1 SET HADR AVAILABILITY GROUP = [sql2016_ag_2];
GO
ALTER DATABASE MyDb2 SET HADR AVAILABILITY GROUP = [sql2016_ag_2];
GO
但实际上由于这个可用性组自己都是个备的角色，并没有可以读写的主库，所以这个数据库不符合加入可用性组这个条件。
这个命令执行会报错。这一步就不需要执行了。

图形化界面操作一样，也是不允许在可用性组里增加这个数据库。

  • 为可用性组增加监听IP。

这个监听IP就是可用性组的 VIP，会调用 Windows 故障转移集群创建一个IP，挂在备可用性组的主实例节点 WIN-76 上。如果后面 WIN-76 发生故障，这个 VIP 会迁移到另外一个实例 WIN-77 上。
ALTER AVAILABILITY GROUP [sql2016_ag_2]
ADD LISTENER 'ag2-listener'(
WITH IP (('10.0.0.82','255.255.255.0')),
PORT = 1433);
这个端口是可以自定义的，为了方便业务使用，就使用默认端口 1433 。命令成功后查看故障转移集群管理器也能看到这个IP。


3.    修改SQLServer主可用性组的镜像端点认证方式。

这一步是最关键的一步。这一步不要求业务停止读写主实例。
这一步操作后主可用性组的主备实例之间同步链路（也就是数据库镜像链路）会中断。中断不要紧，此时不要轻易对主实例发起数据备份和日志备份，那么后续恢复数据库镜像链路后，备实例数据会自动恢复同步。但是如果操作失误（如截断了主实例的事务日志流），导致最坏的情形就是备实例的数据库要重新还原主实例数据库的备份。当数据库容量非常大的时候，这个就有点耽误时间。

  • 调整主可用性组多个备实例副本的同步模式为异步提交。

主可用性有备实例是同步提交模式。为了避免操作失误导致主实例出现短暂性性能问题，将主可用性组的备实例提交模式都改为异步。在主实例 WIN-71 上执行下面SQL。
ALTER AVAILABILITY GROUP [sql2016_ag_1] MODIFY REPLICA ON 'WIN-72'
WITH ( FAILOVER_MODE = MANUAL );
ALTER AVAILABILITY GROUP [sql2016_ag_1] MODIFY REPLICA ON 'WIN-72'
WITH ( AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT);
GO
可用性组的副本的这两个参数彼此有内部逻辑的限制。如果同步模式是异步的，切换模式就只能是手动的；反之不一定。

  • 挂起主可用性组备实例下的数据库复制。

注意，这个只在备实例上 WIN-72 上执行。
ALTER DATABASE MyDb1 SET HADR SUSPEND;
GO
ALTER DATABASE MyDb2 SET HADR SUSPEND;
GO
查看实例可用性组同步状况更新如下。

WIN-71：

WIN-72：

如果执行错了，就执行恢复命令。
ALTER DATABASE MyDb1 SET HADR RESUME;
GO
ALTER DATABASE MyDb2 SET HADR RESUME;
GO
此时，可用性组面板信息更新如下。


  • 重建主可用性组上每个实例的镜像端点。

由于SQLServer 数据库镜像不支持修改连接认证方法，只支持创建的时候指定连接认证方法。所以需要先删除镜像端点。
以下操作在主可用性组的每个实例上执行。先从备实例开始。
DROP ENDPOINT Endpoint_Mirroring ;
GO
CREATE ENDPOINT Endpoint_Mirroring
STATE = STARTED
AS TCP (
LISTENER_PORT = 5022,
LISTENER_IP = ALL
)
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE idc_a_cert,
ROLE = ALL
);
GO
USE master;
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [idc_a_login];
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [idc_b_login];
GO
重建了数据库镜像端点后，首先查看镜像连接信息。

WIN-71：

WIN-72：

  • 恢复主可用性组的数据库复制和同步模式。

在备实例WIN-72 上执行下面SQL恢复数据同步。

ALTER DATABASE MyDb1 SET HADR RESUME;

GO

ALTER DATABASE MyDb2 SET HADR RESUME;

GO

在主实例WIN-71上执行下面SQL修改备实例同步模式。
ALTER AVAILABILITY GROUP [sql2016_ag_1] MODIFY REPLICA ON 'WIN-72'
WITH ( AVAILABILITY_MODE = SYNCHRONOUS_COMMIT);
GO
ALTER AVAILABILITY GROUP [sql2016_ag_1] MODIFY REPLICA ON 'WIN-72'
WITH ( FAILOVER_MODE = AUTOMATIC );
GO
检查可用性状态信息已经恢复如下。


至此，所有实例的镜像端点都已经创建好并且支持证书认证连接。接下来就是创建分布式可用性组了。

3.    创建跨域的分布式可用性组

前面两个可用性组都有自己的VIP，分布式可用性组跟普通可用性组原理基本一样，不一样的地方就是分布式可用性组的成员是前面两个可用性组，实际成员连接方式就体现在各自的VIP 上。所以，下面的操作都是在各个可用性组的主实例上执行（因为VIP在主实例上）。

  • 主可用性组主实例上创建分布式可用性组。

CREATE AVAILABILITY GROUP [sql2016_dist_ag]
WITH (DISTRIBUTED)
AVAILABILITY GROUP ON
'sql2016_ag_1' WITH
(
LISTENER_URL = 'tcp://10.0.0.81:5022',
AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
FAILOVER_MODE = MANUAL,
SEEDING_MODE = MANUAL
),
'sql2016_ag_2' WITH
(
LISTENER_URL = 'tcp://10.0.0.82:5022',
AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
FAILOVER_MODE = MANUAL,
SEEDING_MODE = MANUAL
);
GO
注意上面的参数，说明如下：

  • 每个成员（可用性组）的同步模式（AVAILABILITY_MODE）都是异步提交（ASYNCHRONOUS_COMMIT）。这是因为线下机房跟云上机房的网络条件并不是很好，异步提交能做到线下机房可用性组的读写性能不受分布式可用性组同步影响。

  • 每个成员（可用性组）的切换模式（FAILOVER_MODE）都是手动模式（MANUAL）。

  • 每个成员（可用性组）的种子模式（SEEDING_MODE）都是手动模式（MANUAL），即备可用性组的数据库采取手动还原主可用性组主实例数据库备份的方式。在网络条件不好的情况下，必须这样才能精确控制时间和提高成功的概率。

  • 备可用性组加入到分布式可用性组中。

ALTER AVAILABILITY GROUP [sql2016_dist_ag]
JOIN
AVAILABILITY GROUP ON
'sql2016_ag_1' WITH
(
LISTENER_URL = 'tcp://10.0.0.81:5022',
AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
FAILOVER_MODE = MANUAL,
SEEDING_MODE = MANUAL
),
'sql2016_ag_2' WITH
(
LISTENER_URL = 'tcp://10.0.0.82:5022',
AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
FAILOVER_MODE = MANUAL,
SEEDING_MODE = MANUAL
);
GO
这里参数说明跟上一步保持一致。
这一步命令成功后，就可以在主备可用性组的主实例上查看到分布式可用性组相关的镜像连接了。

WIN-71：

新增的两个镜像连接，一个是本地发起（is_accept=0），对端 IP 是 10.0.0.82 ，正式备可用性组的监听IP（VIP）；另外一个连接是备可用性组发起的，对端IP是 10.0.0.76 （WIN-76），表示目前 VIP（82）在WIN-76 这个节点上。

WIN-72：

  • 将数据库加入到分布式可用性组中。

还剩最后一步将数据库加入到分布式可用性组中。这个需要在备可用性组的主实例（转发实例WIN-76）上执行。
ALTER DATABASE MyDb1 SET HADR AVAILABILITY GROUP = [sql2016_dist_ag];
GO
ALTER DATABASE MyDb2 SET HADR AVAILABILITY GROUP = [sql2016_dist_ag];
GO
这一步成功后，备可用组主实例上数据库同步状态变更为“已同步”。

查看主可用性组的主实例上的可用性组信息如下。

WIN-71：

WIN-72：

备实例上信息记录只有2条，因为WIN-72 只跟WIN-71有镜像复制关系。

  • 将数据库加入到备可用性组中。

前面在部署备可用性组的最后一步并没有将数据库加入到备可用性组中，当时是条件不具备。现在可以加入了。

在备可用性组的备实例WIN-77上执行。

ALTER DATABASE MyDb1 SET HADR AVAILABILITY GROUP = [sql2016_ag_2];
GO
ALTER DATABASE MyDb2 SET HADR AVAILABILITY GROUP = [sql2016_ag_2];
GO

查看备可用性组的实例上可用性组信息如下。

WIN-76：

WIN-77：

至此，分布式数据库可用性部署成功。

不过是否算完全成功，还需要做一些切换演练才能证明。


二、分布式可用性组切换演练

分布式可用性组由于是在两个可用性组之间进行数据同步，两个可用性组内部又有主备实例角色，可以相应的做切换。这个就导致分布式可用性组的同步链路方向有很多种组合情形。

我们定义一种状态语法，如 (主, 备) 表示可用性组下的主备同步方向。（主，备，（主，备））表示分布式可用性组下的同步链路。那么当两个可用性组分别只有两个成员的时候，它会有下列8种组合状态。WIN-71简化为71。

定义三种运维操作，如 A1表示主可用性组的主备切换、A2表示备可用性组的主备切换、A3表示分布式可用性组的主备切换。


状态名
同步链路
S1
（71,72，（76,77））
S2
（71,72，（77,76））
S3
（72,71，（76,77））
S4
（72,71，（77,76））
S5
（76,77，（71,72））
S6
（76,77，（72,71））
S7
（77,76，（71,72））
S8
（77,76，（72,71））


这8种状态经过三种运维操作中任意一种或多种变换后多可以转变到另外一种状态。其规律就如左图所示。每个顶点代表一种状态，两个顶点间的棱代表转换操作。Y 轴代表 A1 操作，X 轴代表 A2 操作，Z 轴代表 A3 操作。

一共12 条棱，代表有 12 种转换场景。不过完整的测试并不需要做 12 次，而是从 S1 顶点出发通过最少的边将所有顶点走遍就行。这是数学上的一笔画问题，需要经过 8 条棱，具体经过的顶点如下：S1, S2, S4, S3, S7, S5, S6, S8 。所以做 8 次测试就可以覆盖掉所有场景。

像客户场景里每个分布式可用性组有三个实例节点，这个会导致有 3x3x2=18 种状态。这个完整性覆盖测试路径就非常多了。到时候我们就从 2 个备里随机挑选一个备用于测试验证，按上面 2 节点可用性组情况处理，总共 8 个测试场景。

不过由于我们在证书上全局层面只用了两个证书，所以只需要验证两个证书在各自可用性组内部的切换、以及分布式可用性组内部（即可用性组之间）的切换能成功通过，也能很大程度的确信全部场景都能通过。

所以，在本方案文档里，仅做三个方向的切换验证。具体路线可以随机定，下面选择的是S1 -> S2 -> S6 -> S8 。


1.   云上机房可用性组主备切换演练

由于后面切换后主备角色会变化，为了避免概念混淆，这里将备可用性组改称为云上机房可用性组。这里要做的是云上可用性组内部的主备实例切换，需要在当前的备实例WIN-77上执行下面SQL。

ALTER AVAILABILITY GROUP [sql2016_ag_2] FAILOVER;
GO

成功后查看线下机房可用性组主实例上的可用性组信息更新如下。

WIN-71：

主实例上的镜像链接也更新如下。

WIN-71：

可以看到主实例上的镜像链接里出现了WIN-77 的客户端IP，表示当前的云上机房可用性组的主实例切换到WIN-77 上了。

云上机房可用性组主实例上可用性组信息如下。

WIN-77：

镜像连接信息如下。


2.    跨域分布式可用性组主备切换演练

接下来要做跨域分布式可用性组的主备切换，这一步应用连接会断开。所以需要应用停机，修改连接方式中的原VIP 到云上可用性组的VIP 。

  • 检查业务已经完全停止读写线下机房可用性组的主实例。
  • 将分布式可用性组的可用性组成员的同步模式改为“同步提交”。

在线下机房可用性组主实例上运行下面SQL。

ALTER AVAILABILITY GROUP [sql2016_dist_ag]
MODIFY
AVAILABILITY GROUP ON
'sql2016_ag_1' WITH
(
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT
),
'sql2016_ag_2' WITH 
(
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT
);
  • 检查分布式可用性组的云上可用性组的实例同步没有延时。

WIN-71：

看最后一列同步的LSN数据都一样。

为了保险起见，云上机房可用性组的主实例也查询确认一下。

WIN-77：

看最后一列的LSN数据是否跟线下机房可用性组的LSN是否一致。

一致后继续做下面切换。

  • 将分布式可用性组的当前主可用性组角色变更为备（辅助副本）。

在线下机房可用性组的主实例上执行下面SQL。

ALTER AVAILABILITY GROUP [sql2016_dist_ag] SET (ROLE = SECONDARY);
GO

此时主实例上的可用性组信息变化如下。

WIN-71：

跟前面对比，发现少了分布式可用性组的记录，这是正常的。

WIN-77 上可用性信息保持不变。

  • 将分布式可用性组的当前备可用性组角色变更为主。

ALTER AVAILABILITY GROUP [sql2016_dist_ag] FORCE_FAILOVER_ALLOW_DATA_LOSS;
GO

变更成功后云上机房可用性组变更为主可用性组，主实例节点是WIN-77。

查看主实例的可用性组记录信息如下。

WIN-77：

查看各个实例的镜像链接信息。

WIN-77：

WIN-71：

  • 将分布式可用性组的可用性组成员的同步模式改为“异步提交”。

在分布式可用性组新的主可用性组主实例WIN-77上执行。

ALTER AVAILABILITY GROUP [sql2016_dist_ag]
MODIFY
AVAILABILITY GROUP ON
'sql2016_ag_1' WITH
(
AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT
),
'sql2016_ag_2' WITH
(
AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT
);
GO

至此数据库正式切换到云上机房。业务验证读写。


3.   线下机房可用性组主备切换演练

线下机房可用性组此时在分布式可用性组里是备副本角色，在该可用性组的备实例WIN-72 上执行下面切换SQL。

ALTER AVAILABILITY GROUP [sql2016_ag_1] FAILOVER;
GO

在分布式可用性组的主可用性组（线上机房）的主实例WIN-77 上查看可用性组当前信息变化如下。

WIN-77：

在 WIN-77上能看到分布式可用性组的备可用性组新的IP（WIN-72）已经建立连接了。此时还能看到老的连接（WIN-71）在。但一段时间后这个连接会自动释放。

WIN-72：

确认线下机房可用性组的主实例已经切换到WIN-72上。


三、SQLServer AlwaysON集群其他操作

1.   撤销 SQLServer AlwaysON 可用性组步骤

  • 在分布式可用性组实例上删除分布式可用性组。

只支持SQL删除，无法图形化界面操作。

DROP AVAILABILITY GROUP [sql2016_dist_ag]
GO

  • 在任意成员实例的可用性组里删除数据库。

ALTER AVAILABILITY GROUP sql2016_ag_1 REMOVE DATABASE MyDb1;
ALTER AVAILABILITY GROUP sql2016_ag_1 REMOVE DATABASE MyDb2;

  • 在任意成员实例上删除可用性组。

路径：Always ON高可用性 - 可用性组 。

2.   撤销 SQLServer 实例镜像相关对象

在数据库镜像实例的每个SQLServe实例上操作：

  • 删除镜像端点。

路径：服务器对象 - 端点 - 数据库镜像 。

  • 删除镜像的认证证书。

路径：数据库 - 系统数据库 - master - 安全性 - 证书

  • 删除镜像的认证证书的所有者（用户）。

路径：数据库 - 系统数据库 - master - 安全性 - 用户 。

默认用户 dbo不能删除。

  • 删除镜像的认证证书的所有者对应的登录名。

路径：安全性 - 登录名。

默认用户 sa 不能删除。


3. 配置新增数据库自动同步

前面迁移已有数据库将分布式可用性组的同步模式里种子模式设置为手动，这个导致后续新增数据库都要备份并还原到每个可用性组的实例中，并且还要加到可用性组中。这个操作比较繁琐，需要改为自动同步新增数据库。

  • 在分布式可用性组的主可用性组的主实例上配置成员实例的种子模式为自动且授权创建数据库。

 

ALTER AVAILABILITY GROUP [sql2016_ag_2]

MODIFY REPLICA ON N'WIN-76' WITH (SEEDING_MODE = AUTOMATIC);

GO

ALTER AVAILABILITY GROUP [sql2016_ag_2]

MODIFY REPLICA ON N'WIN-77' WITH (SEEDING_MODE = AUTOMATIC);

GO

ALTER AVAILABILITY GROUP [sql2016_ag_2] GRANT CREATE ANY DATABASE;

GO

 

 

  • 在分布式可用性组的主可用性组的备实例上配置成员实例的创建数据库权限。

 

ALTER AVAILABILITY GROUP [sql2016_ag_2] GRANT CREATE ANY DATABASE;

GO

 

  • 在分布式可用性组的主可用性组的主实例上配置主可用性组的种子模式为自动。

 

ALTER AVAILABILITY GROUP [sql2016_dist_ag]

MODIFY

AVAILABILITY GROUP ON

'sql2016_ag_2' WITH

(

SEEDING_MODE = AUTOMATIC

);

GO

 

  • 在分布式可用性组的备可用性组的主实例上配置备可用性组的种子模式为自动。

 

ALTER AVAILABILITY GROUP [sql2016_dist_ag]

MODIFY

AVAILABILITY GROUP ON

'sql2016_ag_1' WITH

(

SEEDING_MODE = AUTOMATIC

);

GO

 

  • 在分布式可用性组的备可用性组的主实例上配置成员实例的种子模式为自动。

 

ALTER AVAILABILITY GROUP [sql2016_ag_1]

MODIFY REPLICA ON N'WIN-71' WITH (SEEDING_MODE = AUTOMATIC);

GO

 

ALTER AVAILABILITY GROUP [sql2016_ag_1]

MODIFY REPLICA ON N'WIN-72' WITH (SEEDING_MODE = AUTOMATIC);

GO

 

ALTER AVAILABILITY GROUP [sql2016_ag_1] GRANT CREATE ANY DATABASE;

GO

 

 

  • 在分布式可用性组的备可用性组的备实例上授权自动创建数据库。

 

ALTER AVAILABILITY GROUP [sql2016_ag_1] GRANT CREATE ANY DATABASE;

GO

 

  • 在分布式可用性组的主可用性组的主实例上新增数据库并备份。

 

CREATE DATABASE MyDB7;

GO

 

ALTER DATABASE MyDB7 set RECOVERY FULL

GO

 

BACKUP DATABASE MyDb7  

TO DISK = N'\\win-75\backup\MyDb7.bak'  

    WITH FORMAT, STATS=10; 

GO

 

BACKUP LOG MyDb7

TO DISK = N'\\win-75\backup\MyDb7.trn'  

    WITH NOFORMAT; 

GO 

 

  • 在分布式可用性组的主可用性组的主实例上将新数据库加入到主可用性组。

 

ALTER AVAILABILITY GROUP [sql2016_ag_2]

ADD DATABASE [MyDb7];

GO

 

总结

SQLServer AlwaysON 的数据库高可用和容灾切换能力非常契合传统客户对数据库容灾的需求。

  • 比如说指定哪些备库是同步模式，哪些备库是异步模式，同时又有自动和手动切换能力。腾讯的 TDSQL 的数据库高可用和容灾能力就跟这个非常像。

  • SQLServer AlwaysON 的可用性组里多个数据库一起同步和切换的能力。MySQL 的主从复制就跟这个很接近。不同之处是一个 SQLServer 里如果有很多数据库，是可以划分为多个可用性组。架构上理论来说不同可用性组可以选择不同的实例作为同步载体（也就意味着同步方向不同）。

  • SQLServer AlwaysON 的数据同步链路是基于数据库镜像技术实现，连接可以加密和配置证书，安全性非常高。分布式可用性组更适合异地容灾和数据库上云场景。



可用性测试分布式处理数据库镜像分布式架构分布式技术
文章转载自数据库技术闲谈，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论