SQL Server Monitoring - Always On configuration

This blog is part of the blog series named SQL Server Monitoring.

Purpose

To get a quick overview of the Always On configuration.

Report

The report are showing 6 tables where each are showing selected information about the Windows Server Failover Clustering (WSFC) and Always On configurations.

WSFC quorum state

The output here is based on the DMV sys.dm_hadr_cluster which returns a row for the WSFC cluster used by the instance.

WSFC quorum members

The output here is based on the DMV sys.dm_hadr_cluster_members which returns rows for every member of the WSFC cluster.

Always On group states

The output here is based on the DMV sys.dm_hadr_availability_group_states which returns rows for all Always On groups and the servers belonging to the groups.

Always On read only routing

The output here is based on the DMV sys.availability_read_only_routing_lists which returns rows for the Always On read-only configuration..

Always On replicas

The output here is based on the DMV sys.availability_replicas which return rows for all the configured Always On replicas.

Always On database states

The output here is based on the DMV sys.dm_hadr_database_replica_states which returns rows for every database configured to be member of Always On groups.

Sources

This feature queries the 7 DMV’s sys.availability_groups, sys.availability_read_only_routing_lists, sys.availability_replicas, sys.dm_hadr_availability_group_states, sys.dm_hadr_cluster, sys.dm_hadr_cluster_members and sys.dm_hadr_database_replica_states.

Model

The model for the report uses the facts [Always On database states], [Always on group states], [Always On read only routing], [Always on replica], [WSFC quorum members] and [WSFC quorum state], and the dimension [Always On group-replica].

Service setup

The feature uses the service ‘fhsmSPDatabaseState’ which can be configured using the stored procedure fhsmSPControl.

Parameter

There is no parameter to be configured for this feature.

Schedule

The default schedule for the feature is once every 1 hour (3.600 seconds).

EXEC fhsmSPControl
	@Type = 'Schedule',
	@Command = 'list',
	@Task = 'fhsmSPDatabaseState',
	@Name = 'Database state',
	@Wildcard = 0;

EXEC fhsmSPControl
	@Type = 'Schedule',
	@Command = 'set',
	@Task = 'fhsmSPDatabaseState',
	@Name = 'Database state',
	@ScheduleType = 0,
	@Enabled = 1,
	@ExecutionDelaySec = 3600,
	@FromTime = '00:00:00',
	@ToTime = '23:59:59',
	@Monday = 1,
	@Tuesday = 1,
	@Wednesday = 1,
	@Thursday = 1,
	@Friday = 1,
	@Saturday = 1,
	@Sunday = 1;

Retention

The default retention for the table dbo.fhsmAlwaysOnState is 1.825 days (5 years).

EXEC fhsmSPControl
	@Type = 'Retention',
	@Command = 'list',
	@TableName = 'fhsmAlwaysOnState',
	@Wildcard = 0;

EXEC fhsmSPControl
	@Type = 'Retention',
	@Command = 'set',
	@TableName = 'fhsmAlwaysOnState',
	@Enabled = 1,
	@Sequence = 1,
	@Days = 1825;

SQL Server Monitoring – Always On configuration