— 监控集群节点
SELECT
node_name,
status_description
FROM sys.dm_hadr_cluster_members;
GO

— 监控集群资源
SELECT
resource_id,
resource_name,
resource_type,
status_description
FROM sys.dm_hadr_cluster_resources;
GO

— 监控SQLServer实例状态
SELECT
server_name,
instance_name,
status,
cluster_nodename
FROM sys.dm_os_cluster_nodes;
GO

— 监控集群网络
SELECT
network_name,
network_address,
is_public,
is_cluster_network
FROM sys.dm_os_cluster_networks;
GO

— 创建监控作业
USE msdb;
GO

EXEC dbo.sp_add_job
@job_name = N’MonitorCluster’,
@enabled = 1,
@description = N’监控SQLServer故障转移集群状态’;
GO

EXEC dbo.sp_add_jobstep
@job_name = N’MonitorCluster’,
@step_name = N’Check Cluster Status’,
@subsystem = N’TSQL’,
@command = N’
DECLARE @status VARCHAR(MAX);

— 检查集群状态
SELECT @status = COALESCE(@status + CHAR(13), ”) +
”Cluster: ” + cluster_name + ”, Quorum: ” + quorum_type_desc + ”, State: ” + quorum_state_desc
FROM sys.dm_hadr_cluster;

— 检查节点状态
SELECT @status = COALESCE(@status + CHAR(13), ”) +
”Node: ” + node_name + ”, Status: ” + status_description
FROM sys.dm_hadr_cluster_members;

— 检查资源状态
SELECT @status = COALESCE(@status + CHAR(13), ”) +
”Resource: ” + resource_name + ”, Type: ” + resource_type + ”, Status: ” + status_description
FROM sys.dm_hadr_cluster_resources;

— 如果有问题，发送警报
IF EXISTS (
SELECT 1
FROM sys.dm_hadr_cluster_members
WHERE status_description <> ”UP”
)
BEGIN
RAISERROR(”集群状态异常: %s”, 16, 1, @status);
END;
‘,
@database_name = N’master’;
GO

EXEC dbo.sp_add_jobschedule
@job_name = N’MonitorCluster’,
@name = N’Every 5 Minutes’,
@freq_type = 4,
@freq_interval = 1,
@freq_subday_type = 4,
@freq_subday_interval = 5,
@active_start_time = 000000,
@active_end_time = 235959;
GO

执行结果：