等保测评命令——SQL server

六、高可用与容灾（8.1.4.9）

6.1 集群与镜像 配置

-- 61. 查看故障转移群集实例
SELECT
cluster_name,
type_desc,
quorum_state_desc,
quorum_type_desc
FROM sys.dm_hadr_cluster;

-- 62. 查看AlwaysOn可用性组详细状态
SELECT
ag.name AS[可用性组],
ar.replica_server_name AS[副本服务器],
ar.availability_mode_desc AS[可用性模式],
ar.failover_mode_desc AS[故障转移模式],
rs.role_desc AS[当前角色],
rs.synchronization_health_desc AS[同步健康],
rs.last_hardened_lsn
FROM sys.availability_groups ag
JOIN sys.availability_replicas ar ON ag.group_id = ar.group_id
JOIN sys.dm_hadr_availability_replica_states rs ON ar.replica_id = rs.replica_id;

-- 63. 查看数据库镜像（传统方式）
SELECT
database_id,
db.name,
mirroring_state_desc AS[镜像状态],
mirroring_role_desc AS[镜像角色],
mirroring_partner_name,
mirroring_witness_name,
mirroring_connection_timeout
FROM sys.database_mirroring dm
JOIN sys.databases db ON dm.database_id = db.database_id
WHERE mirroring_guid ISNOTNULL;

-- 64. 查看复制配置（数据分发）
SELECT
name AS[发布名],
database_id,
is_snapshot_in_defaultfolder,
compress_snapshot_files
FROM distribution.dbo.MSpublications;

-- 65. 查看事务日志状态（恢复关键）
SELECT
db.name,
db.log_reuse_wait_desc,
ls.cntr_value/1024.0AS[日志大小MB],
lu.cntr_value/1024.0AS[已用日志MB]
FROM sys.databases db
JOIN sys.dm_os_performance_counters ls ON db.name = ls.instance_name AND ls.counter_name ='Log File(s) Size (KB)'
JOIN sys.dm_os_performance_counters lu ON db.name = lu.instance_name AND lu.counter_name ='Log File(s) Used Size (KB)'
WHERE ls.object_name LIKE'%Databases%';