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;
|
|
|
|
|
|