Sql（VIP）：
-- group info
SELECT
g.name as ag_name,
rgs.primary_replica,
rgs.primary_recovery_health_desc as recovery_health,
rgs.synchronization_health_desc as sync_health
From sys.dm_hadr_availability_group_states as rgs
JOIN sys.availability_groups AS g
ON rgs.group_id = g.group_id
--replicas info
SELECT
g.name as ag_name,
r.replica_server_name,
rs.is_local,
rs.role_desc as role,
rs.operational_state_desc as op_state,
rs.connected_state_desc as connect_state,
rs.synchronization_health_desc as sync_state,
rs.last_connect_error_number,
rs.last_connect_error_description
From sys.dm_hadr_availability_replica_states AS rs
JOIN sys.availability_replicas AS r
ON rs.replica_id = r.replica_id
JOIN sys.availability_groups AS g
ON g.group_id = r.group_id
--DB level
SElECT
g.name as ag_name,
r.replica_server_name,
DB_NAME(drs.database_id) as [database_name],
drs.is_local,
drs.is_primary_replica,
synchronization_state_desc as sync_state,
synchronization_health_desc as sync_health,
database_state_desc as db_state
FROM sys.dm_hadr_database_replica_states AS drs
JOIN sys.availability_replicas AS r
ON r.replica_id = drs.replica_id
JOIN sys.availability_groups AS g
ON g.group_id = drs.group_id
ORDER BY g.name, drs.is_primary_replica DESC;
GO
-
14 模拟故障转移使用命令停掉主节点的sql server服务（我们期望它能发生自动故障转移，并且希望在本例中主节点能转移到m191或m192上面，同时又保证VIP能继续使用，等待少许时间后/故障转移需要一定的时间，特别是繁忙的大型系统，可能需要数秒到几分钟/）
14.1 故障转移（手动）Sql（主）：
将m192转换为主数据库
sudo pcs resource move ag_cluster-master m192 --master
[ge1] Only readable by root