sqlserver 分布式可用性组（二）—— 创建、故障转移与元数据信息查看方法

一、 创建规划

准备工作与普通创建两个AG没有区别，以下是DAG规划：

主要步骤如下：

• 创建主AG (AG_DC1)及监听AG_DC1_LISTENER
• 在从AG所有副本创建endpoint
• 创建SQL Server 服务账号并授予连接到endpoint的权限
• 创建从AG (AG_DC2)及监听AG_DC2_LISTENER
• 将从副本加入从AG

以上步骤与创建传统AG没什么区别，应该是可以用图形化工具建的，更简单

• 在主AG (AG_DC1)创建分布式可用性组 (DistAG_DC1_DC2)
• 将从AG (AG_DC2)加入分布式可用性组

二、 创建步骤

1. 创建主AG (AG_DC1)及监听AG_DC1_LISTENER

此步与创建传统AG没有区别，本文默认已建好，不再单独列出步骤。

2. 在从AG所有副本创建endpoint

本文默认主AG中所有副本已在步骤1中创建了endpoint，如果没有需要先建。注意endpoint需监听所有IP地址，即要用LISTENER_IP = ALL参数。

--Create endpoint on all Availability Group replicas
--Run this on the primary replica of the secondary Availability Group
USE [master]
GO

CREATE ENDPOINT [Hadr_endpoint]
   STATE=STARTED-
   AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
   FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = WINDOWS NEGOTIATE
      , ENCRYPTION = REQUIRED ALGORITHM AES)
GO

--Run this on the secondary replica of the secondary Availability Group-
USE [master]
GO
CREATE ENDPOINT [Hadr_endpoint]
   STATE=STARTED
   AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
   FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = WINDOWS NEGOTIATE
       , ENCRYPTION = REQUIRED ALGORITHM AES)
GO

3. 创建SQL Server 服务账号并授予连接到endpoint的权限

在所有副本创建TESTDOMAIN\sqlservice账号，本文中还是默认主AG已建好，以下只在从AG中操作。

如果WSFC中的每个实例使用的是不同的sqlserver服务账号，此步应改为创建sqlserver普通用户而非服务账号。

如果两个WSFC在不同域或未加域（及域独立可用性组），情况将更加复杂，需要创建证书来进行验证。

--Do this on all of the replicas of the secondary Availability Group. 
--Create login and grant CONNECT permissions to the SQL Server service account
USE master
GO

CREATE LOGIN [TESTDOMAIN\sqlservice] FROM WINDOWS;
GO

GRANT CONNECT ON ENDPOINT::Hadr_endpoint 
TO [TESTDOMAIN\sqlservice];  
GO

4. 创建从AG (AG_DC2)及监听AG_DC2_LISTENER

--Create second availability group on second failover cluster with replicas and listener
--Run this on the primary replica of the secondary Availability Group
:CONNECT WSFC-DC2-NODE1
CREATE AVAILABILITY GROUP [AG_DC2] 
FOR  
REPLICA ON
N'WSFC-DC2-NODE1' WITH
(
   ENDPOINT_URL = N'TCP://WSFC-DC2-NODE1.TESTDOMAIN.COM:5022',  
   FAILOVER_MODE = AUTOMATIC,  
   AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,  
   SECONDARY_ROLE(ALLOW_CONNECTIONS = NO),  
   SEEDING_MODE = AUTOMATIC
),  
N'WSFC-DC2-NODE2' WITH
(  ENDPOINT_URL = N'TCP://WSFC-DC2-NODE2.TESTDOMAIN.COM:5022',  
   FAILOVER_MODE = AUTOMATIC,  
   AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,  
   SECONDARY_ROLE(ALLOW_CONNECTIONS = NO),  
   SEEDING_MODE = AUTOMATIC
)
LISTENER 'AG_DC2_LISTENER'
(
   WITH IP ( ('192.168.0.116','255.255.255.0') ) ,
   PORT = 143
);  
GO

参数简介：

• REPLICA：副本名，另外注意此处并没有声明要加入AG的数据库。
• SEEDING_MODE = AUTOMATIC： SQL Server 2016的新参数，用于直接同步从主库同步数据库，而不需进行backup-copy-restore（注意数据库太大时不要用此选项）。
• FAILOVER_MODE = AUTOMATIC：从AG的故障转移模式，注意这并不是分布式可用性组的故障转移模式
• AVAILABILITY_MODE = SYNCHRONOUS_COMMIT：从AG的提交模式，注意这并不是分布式可用性组的提交模式
• SECONDARY_ROLE (ALLOW_CONNECTIONS = NO)：此从AG的从副本不可连接（不可用于只读）只作为灾备，不是必须要设成这样，看实际需求。

5. 将从副本加入从AG

--Join the secondary replicas to the secondary Availability Group
--Run this on the secondary replicas of the secondary Availability Group 
ALTER AVAILABILITY GROUP [AG_DC2] JOIN

--Allow the Availability Group to create databases on behalf of the primary replica
ALTER AVAILABILITY GROUP [AG_DC2] GRANT CREATE ANY DATABASE
GO

第二个语句ALTER AVAILABILITY GROUP [agname] GRANT CREATE ANY DATABASE，意为通过direct seeding 方式初始化从库的AG数据库. 如果希望手动通过 backup-copy-restore 方式进行初始化，应该使用以下语句：

ALTER DATABASE [dbName] SET HADR AVAILABILITY GROUP = [agName];

至此就建好了准备作为分布式可用性组的从AG（目前与传统AG并无区别），此时该AG的从副本状态会是NOT SYNCHRONIZING，因为你还没建AG数据库。AG数据库会在创建分布式可用性组时被加入。

6. 创建分布式可用性组

终于来到了主场——在主AG (AG_DC1)创建分布式可用性组 (DistAG_DC1_DC2)。

--Create Distributed Availability Group
--Run this on the primary replica of the primary Availability Group
:CONNECT WSFC-DC1-NODE1
CREATE AVAILABILITY GROUP [DistAG_DC1_DC2]  
WITH (DISTRIBUTED)   
AVAILABILITY GROUP ON  
'AG_DC1' WITH    
(   
   LISTENER_URL = 'TCP://AG_DC1_LISTENER.TESTDOMAIN.COM:5022',    
   AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,   
   FAILOVER_MODE = MANUAL,   
   SEEDING_MODE = AUTOMATIC   
),   
'AG_DC2' WITH    
(   
   LISTENER_URL = 'TCP://AG_DC2_LISTENER.TESTDOMAIN.COM:5022',   
   AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,   
   FAILOVER_MODE = MANUAL,   
   SEEDING_MODE = AUTOMATIC   
);    
GO

参数简介：

• DISTRIBUTED：声明创建的是分布式可用性组
• LISTENER_URL：后跟各AG的监听名和端口，注意端口号是5022而不是1433（5022端口用于连接endpoint以便进行日志记录同步，如果服务器跨网段，这个端口防火墙必须要开）
• FAILOVER_MODE = MANUAL：分布式可用性组的故障转移模式，只支持MANUAL
• AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT：分布式可用性组的提交模式，同步异步都支持但一般用异步，因为分布式可用性组一般跨各数据中心。

7. 将从AG (AG_DC2)加入分布式可用性组

--Create second availability group on second failover cluster with replicas and listener
--Run this on the primary replica of the secondary Availability Group
:CONNECT WSFC-DC2-NODE1
ALTER AVAILABILITY GROUP [DistAG_DC1_DC2]   
JOIN   
AVAILABILITY GROUP ON  
'AG_DC1' WITH    
(   
   LISTENER_URL = 'TCP://AG_DC1_LISTENER.TESTDOMAIN.COM:5022',    
   AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,   
   FAILOVER_MODE = MANUAL,   
   SEEDING_MODE = AUTOMATIC   
),   
'AG_DC2' WITH    
(   
   LISTENER_URL = 'TCP://AG_DC2_LISTENER.TESTDOMAIN.COM:5022',   
   AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,   
   FAILOVER_MODE = MANUAL,   
   SEEDING_MODE = AUTOMATIC   
);    
GO

此时可以在SSMS中看到新建的分布式可用性组，但是注意在WSFC里是看不到的。

以下sql可查看分布式可用性组信息

--View metadata and status of the Distributed Availability Group
SELECT r.replica_server_name, r.endpoint_url,
rs.connected_state_desc, rs.role_desc, rs.operational_state_desc,
rs.recovery_health_desc,rs.synchronization_health_desc,
r.availability_mode_desc, r.failover_mode_desc
FROM sys.dm_hadr_availability_replica_states rs 
INNER JOIN sys.availability_replicas r
ON rs.replica_id=r.replica_id
ORDER BY r.replica_server_name

三、 分布式可用性组Failover到从AG

注意分布式可用性组仅支持强制故障转移，为了在failover时不丢数据，建议按照以下步骤操作：

• 停止全局主库所有事务操作，并将DAG设为同步提交模式。

-- sets the distributed availability group to synchronous commit 
 ALTER AVAILABILITY GROUP [distributedag] 
 MODIFY 
 AVAILABILITY GROUP ON
 'ag1' WITH 
  ( 
  AVAILABILITY_MODE = SYNCHRONOUS_COMMIT 
  ), 
  'ag2' WITH  
  ( 
  AVAILABILITY_MODE = SYNCHRONOUS_COMMIT 
  );

 -- verifies the commit state of the distributed availability group
 select ag.name, ag.is_distributed, ar.replica_server_name, ar.availability_mode_desc, ars.connected_state_desc, ars.role_desc, 
 ars.operational_state_desc, ars.synchronization_health_desc from sys.availability_groups ag  
 join sys.availability_replicas ar on ag.group_id=ar.group_id
 left join sys.dm_hadr_availability_replica_states ars
 on ars.replica_id=ar.replica_id
 where ag.is_distributed=1
 GO

• 全局主库执行以下sql，等到所有数据库状态变为synchronized时再继续操作。

-- If not rerun the query on both side every 5 seconds until it is the case
SELECT ag.name
       , drs.database_id
       , db_name(drs.database_id) as database_name
       , drs.group_id
       , drs.replica_id
       , drs.synchronization_state_desc
       , drs.last_hardened_lsn  
FROM sys.dm_hadr_database_replica_states drs 
INNER JOIN sys.availability_groups ag on drs.group_id = ag.group_id;

• 在全局主库中设置DAG角色为SECONDARY，注意执行完后DAG是不可用的。

ALTER AVAILABILITY GROUP distributedag SET (ROLE = SECONDARY);

• failover前检查：主从AG的主库均执行以下sql，如果last_hardened_lsn相同，说明主从AG已同步，可以进行failover

-- Run this query on the Global Primary and the forwarder
 -- The availability group is ready to fail over when the last_hardened_lsn is the same for both availability groups per database
 SELECT ag.name, 
     drs.database_id, 
     db_name(drs.database_id) as database_name,
     drs.group_id, 
     drs.replica_id,
     drs.last_hardened_lsn
 FROM sys.dm_hadr_database_replica_states drs
 INNER JOIN sys.availability_groups ag ON drs.group_id = ag.group_id;

• 在从AG的主库执行以下sql，将其转为主AG。

ALTER AVAILABILITY GROUP distributedag FORCE_FAILOVER_ALLOW_DATA_LOSS;

注意：

如果failover前检查发现 last_hardened_lsn 值始终不相同，或者从AG还需要很长时间来追。

此时有两种决断：

1. 放弃failover，把原先的全局AG状态再从SECONDARY切回PRIMARY，继续尝试修复主库。在全局主库执行：

ALTER AVAILABILITY GROUP distributedag FORCE_FAILOVER_ALLOW_DATA_LOSS;

2. 承担丢数据的风险，直接使用强制故障转移。在从AG主库执行：

ALTER AVAILABILITY GROUP distributedag FORCE_FAILOVER_ALLOW_DATA_LOSS;

你会发现执行的命令其实是一样的，区别只是在哪个AG执行而已。

当然如果不介意丢数据，就没必要搞那么复杂的操作，直接使用强制故障转移即可。

在从AG主库执行（其实就是上面第二种情况，一样的）：

ALTER AVAILABILITY GROUP distributedag FORCE_FAILOVER_ALLOW_DATA_LOSS;

四、 查看分布式可用性组元数据信息

目前，SSMS仅在主AG的主副本中显示分布式可用性组信息。分布式可用性组的详细信息均在 SQL Server 中的DMV中。 

如果右键单击分布式可用性组，会发现无选项可用，展开可用性数据库、可用性组侦听器和可用性副本文件夹均为空。

如下图，在SSMS中从副本不显示任何与分布式可用性组相关的内容

1. 查看所有可用性副本名称

以下查询可查看所有可用性组（传统和分布式）及参与其中的节点，需在主副本查询。sys.availability_groups中有一个新列is_distributed，对于分布式可用性组显示为1。

-- shows replicas associated with availability groups
SELECT 
   ag.[name] AS [AG Name], 
   ag.Is_Distributed, 
   ar.replica_server_name AS [Replica Name]
FROM sys.availability_groups AS ag 
INNER JOIN sys.availability_replicas AS ar       
   ON ag.group_id = ar.group_id
GO

输出如下，SPAG1由两个副本DENNIS 和 JY组成。分布式可用性组SPDistAG显示的是两个参与的可用性组名称（SPAG1 和 SPAG2），而不是像传统可用性组那样显示实例的名称。

2. 查看分布式AG运行状况

-- shows sync status of distributed AG
SELECT 
   ag.[name] AS [AG Name], 
   ag.is_distributed, 
   ar.replica_server_name AS [Underlying AG], 
   ars.role_desc AS [Role], 
   ars.synchronization_health_desc AS [Sync Status]
FROM  sys.availability_groups AS ag
INNER JOIN sys.availability_replicas AS ar 
   ON  ag.group_id = ar.group_id        
INNER JOIN sys.dm_hadr_availability_replica_states AS ars       
   ON  ar.replica_id = ars.replica_id
WHERE ag.is_distributed = 1
GO

3. 通过 DMV 查看基础性能

在主AG中执行以下查询

-- shows underlying performance of distributed AG
SELECT 
   ag.[name] AS [Distributed AG Name], 
   ar.replica_server_name AS [Underlying AG], 
   dbs.[name] AS [Database],
   ars.role_desc AS [Role],
   drs.synchronization_health_desc AS [Sync Status],
   drs.log_send_queue_size,
   drs.log_send_rate, 
   drs.redo_queue_size, 
   drs.redo_rate
FROM sys.databases AS dbs
INNER JOIN sys.dm_hadr_database_replica_states AS drs
   ON dbs.database_id = drs.database_id
INNER JOIN sys.availability_groups AS ag
   ON drs.group_id = ag.group_id
INNER JOIN sys.dm_hadr_availability_replica_states AS ars
   ON ars.replica_id = drs.replica_id
INNER JOIN sys.availability_replicas AS ar
   ON ar.replica_id = ars.replica_id
WHERE ag.is_distributed = 1
GO

4. 查看分布式可用性组的性能计数器

-- displays OS performance counters related to the distributed ag named 'distributedag'
SELECT * FROM sys.dm_os_performance_counters WHERE instance_name LIKE '%distributed%'

5. 查看所有可用性组的运行状况

以下查询显示有关传统可用性组和分布式可用性组运行状况的大量信息

-- displays sync status, send rate, and redo rate of availability groups, including distributed AG
SELECT 
       ag.name AS 'AG Name', 
       ag.is_distributed, 
       ar.replica_server_name AS 'AG', 
       dbs.name AS 'Database',
   ars.role_desc, 
   drs.synchronization_health_desc, 
   drs.log_send_queue_size, 
   drs.log_send_rate, 
   drs.redo_queue_size, 
   drs.redo_rate,
   drs.suspend_reason_desc,
   drs.last_sent_time,
   drs.last_received_time,
   drs.last_hardened_time,
   drs.last_redone_time,
   drs.last_commit_time,
   drs.secondary_lag_seconds
FROM sys.databases dbs 
INNER JOIN sys.dm_hadr_database_replica_states drs 
   ON dbs.database_id = drs.database_id
INNER JOIN sys.availability_groups ag 
   ON drs.group_id = ag.group_id
INNER JOIN sys.dm_hadr_availability_replica_states ars 
   ON ars.replica_id = drs.replica_id
INNER JOIN sys.availability_replicas ar 
   ON ar.replica_id =  ars.replica_id
--WHERE ag.is_distributed = 1
GO

6. 查看所有可用性组的元数据

以下查询显示有关传统可用性组和分布式可用性组使用的endpoint和URL信息

-- shows endpoint url and sync state for ag, and dag
SELECT
   ag.name AS group_name,
   ag.is_distributed,
   ar.replica_server_name AS replica_name,
   ar.endpoint_url,
   ar.availability_mode_desc,
   ar.failover_mode_desc,
   ar.primary_role_allow_connections_desc AS allow_connections_primary,
   ar.secondary_role_allow_connections_desc AS allow_connections_secondary,
   ar.seeding_mode_desc AS seeding_mode
FROM sys.availability_replicas AS ar
JOIN sys.availability_groups AS ag
   ON ar.group_id = ag.group_id
GO

7. 查看种子设定的当前状态

以下查询显示有关种子设定当前状态的信息。 这对于解决副本之间的同步错误非常有用。

-- shows current_state of seeding 
SELECT
   ag.name AS aag_name,
   ar.replica_server_name,
   d.name AS database_name,
   has.current_state,
   has.failure_state_desc AS failure_state,
   has.error_code,
   has.performed_seeding,
   has.start_time,
   has.completion_time,
   has.number_of_attempts
FROM sys.dm_hadr_automatic_seeding AS has
JOIN sys.availability_groups AS ag
   ON ag.group_id = has.ag_id
JOIN sys.availability_replicas AS ar
   ON ar.replica_id = has.ag_remote_replica_id
JOIN sys.databases AS d
   ON d.group_database_id = has.ag_db_id
GO

参考

https://www.mssqltips.com/sqlservertip/5053/setup-and-implement-sql-server-2016-always-on-distributed-availability-groups/

https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-distributed-availability-groups?view=sql-server-2016

https://docs.microsoft.com/zh-cn/sql/database-engine/availability-groups/windows/distributed-availability-groups?view=sql-server-ver15