MSSQL AlwaysOn Diagnostics

SSMS

You can tell if a database is a read-only replica in SSMS because it has (Synchronized) appended to the database name in Object Explorer

Cluster Log

Get-ClusterLog -Node MYSERVER -TimeSpan 15

Causes Powershell to generate Cluster.log for MYSERVER covering the last 15 minutes
Mode                LastWriteTime         Length Name----                -------------         ------ -----a----         1/2/2025   1:31 PM         413460 Cluster.log

Open Cluster.log in Notepad.

Useful search terms can be things like...

Check

USE MASTER 

GO  

WHILE 1=1 

  BEGIN 

    PRINT CONVERT(VARCHAR(20), GETDATE(),120) 

  DECLARE @max INT; 

    SELECT @max = max_workers_count 

      FROM sys.dm_os_sys_info; 

    SELECT GETDATE() AS 'CurrentDate',  

           @max AS 'TotalThreads',  

           SUM(active_Workers_count) AS 'CurrentThreads',  

           @max - SUM(active_Workers_count) AS 'AvailableThreads',  

           SUM(runnable_tasks_count) AS 'WorkersWaitingForCpu',  

           SUM(work_queue_count) AS 'RequestWaitingForThreads' 

         --SUM(current_workers_count) AS 'AssociatedWorkers' 

      FROM sys.dm_os_Schedulers 

     WHERE STATUS = 'VISIBLE ONLINE'; 

    WAIT FOR DELAY '0:0:15' 

  END

Quorum Model

To view the quorum model...

SELECT cluster_name,

       quorum_type_desc,

       quorum_state_desc

  FROM sys.dm_hadr_cluster;

To view the node votes...

SELECT member_name,

       number_of_quorum_votes

  FROM sys.dm_hadr_cluster_members;

Maximum Failures in the Specified Period

Be careful of breaching the "Maximum failures in the specified period" (MFSP). For example, if the MFSP is set to 3 and the period is set to 6 then more than 3 "failures" will prevent automatic failover from happening. 

The following are seen as "failures" (1)

Use the Failover Cluster Manager tool to check/change the values for MFSP and Period.

NT Authority\SYSTEM

This login account should have the following permssions.

(these are granted by default but may have been revoked)

Check Failover Readiness

SELECT database_name, is_failover_ready 

  FROM sys.dm_hadr_database_replica_cluster_states 

 WHERE replica_id IN (SELECT replica_id 

                      FROM   sys.dm_hadr_availability_replica_states)

Bibliography