info@techdevops.com | 437-991-3573 | Data Engineering Services
TechDevOps.com
Resources Tools
Experts in Microsoft SQL Server on Windows, Linux, Containers | Clusters, Always On, FCI | Migrations, Cloud, Performance



SQL Server High Availability - Monitor AlwaysON Availability Groups using Powershell, DMVs and TSQL
by BF (Principal Consultant; Architecture; Engineering)
2017-02-27









Powershell:

Get-ClusterLog

Get-Cluster

Get-ClusterNode

Get-ClusterAccess

Get-ClusterQuorum

Get-ClusterResource

Get-ClusterGroup

Get-ClusterNetwork

Get-ClusterNetworkInterface

Test-SqlAvailabilitygroup -Path "SQLSERVER:\Sql\localhost\DEFAULT\AvailabilityGroups\agXYZ"

Test-SqlAvailabilityReplica -Path "SQLSERVER:\Sql\localhost\DEFAULT\AvailabilityGroups\agXYZ\AvailabilityReplicas\VMSQ01"

Test-SqlDatabaseReplicaState -Path "SQLSERVER:\Sql\localhost\DEFAULT\AvailabilityGroups\agXYZ\DatabaseReplicaStates\VMSQ01.DB"


DMV's:

SELECT * FROM sys.dm_hadr_cluster;

SELECT * FROM sys.dm_hadr_cluster_members;

SELECT * FROM sys.dm_hadr_cluster_networks;

SELECT * FROM sys.dm_hadr_database_replica_states;

SELECT * FROM sys.dm_hadr_database_replica_cluster_states;

SELECT * FROM sys.dm_tcp_listener_states;

SELECT * FROM sys.availability_replicas;

SELECT * FROM sys.availability_groups;

SELECT * FROM sys.availability_groups_cluster;

SELECT * FROM sys.availability_group_listeners;

SELECT * FROM sys.availability_group_listener_ip_addresses;

SELECT * FROM sys.dm_hadr_availability_group_states;

SELECT * FROM sys.dm_hadr_availability_replica_cluster_nodes;

SELECT * FROM sys.dm_hadr_availability_replica_cluster_states;

SELECT * FROM sys.dm_hadr_availability_replica_states;


TSQL:

SELECT
ar.replica_server_name as 'AlwaysON Node',
ag.name as 'Availability Group',
adc.database_name as 'Database',
--ISNULL(drs.database_state_desc,'') as 'DatabaseState',
drs.synchronization_state_desc 'Synchronization State',
drs.synchronization_health_desc as 'Synchronization Health',
ISNULL(DATEDIFF(MS,LAG(drs.last_commit_time) OVER(PARTITION BY drs.database_id ORDER BY drs.database_id DESC, is_primary_replica DESC),drs.last_commit_time),'') AS 'RPO(ms)',
ISNULL(drs.log_send_queue_size,'') as 'PRI-LogSendQueueSize(RecordsUnsent(KB))', --Amount of log records of the primary database that has not been sent to the secondary databases, in kilobytes (KB).
ISNULL(drs.log_send_rate,'') as 'PRI-LogSendRate(KB/s)', --Average rate at which primary replica instance sent data during last active period, in kilobytes (KB)/second.
ISNULL(drs.redo_queue_size,'') as 'SEC-RedoQueueSize(RecordsNotDone(KB))', --Amount of log records in the log files of the secondary replica that has not yet been redone, in kilobytes (KB)
ISNULL(drs.redo_rate,'') as 'SEC-RedoRate(KB/s)', --Rate at which the log records are being redone on a given secondary database, in kilobytes (KB)/second.
CASE drs.is_primary_replica WHEN '1' Then 'Y' Else 'N' End as 'IsPrimary',
GetDate() as 'InsertedDate'
/*drs.is_local,
drs.is_commit_participant,
drs.recovery_lsn,
drs.truncation_lsn,
drs.last_sent_lsn,
drs.last_sent_time,
drs.last_received_lsn,
drs.last_received_time,
drs.last_hardened_lsn,
drs.last_hardened_time,
drs.last_redone_lsn,
drs.last_redone_time,
drs.filestream_send_rate,
drs.end_of_log_lsn,
drs.last_commit_lsn,
drs.last_commit_time*/
FROM
sys.availability_replicas AS ar with (nolock)
INNER JOIN sys.dm_hadr_database_replica_states AS drs with (nolock) ON drs.group_id = ar.group_id AND drs.replica_id = ar.replica_id
INNER JOIN sys.availability_databases_cluster AS adc with (nolock) ON drs.group_id = adc.group_id AND drs.group_database_id = adc.group_database_id
INNER JOIN sys.availability_groups AS ag with (nolock) ON ag.group_id = drs.group_id
ORDER BY
ag.name, ar.replica_server_name, adc.database_name;