How can I check the failover history of the nodes in Always On availability groups ?

 

SQL Server Availability Groups (AG) provide high availability and disaster recovery solutions for critical databases. Understanding the history of failovers in an AG is crucial for tracking changes in primary and secondary nodes, ensuring system stability, and diagnosing potential issues. In this blog post, we'll share a handy script that allows you to access and analyze the failover history of the primary node within a SQL Server cluster.

Please find the gitHub Link for the same.

Copied!

 /* 
Script origin :https://dba.stackexchange.com/questions/76016/how-to-check-history-of-primary-node-in-an-availability-group
Reshared @ : https://dbascrolls.com

Full Credit to auther. 
*/
declare @xel_path varchar(1024);
declare @utc_adjustment int = datediff(hour, getutcdate(), getdate());

-------------------------------------------------------------------------------
------------------- target event_file path retrieval --------------------------
-------------------------------------------------------------------------------
;with
    target_data_cte
    as
    (
        select
            target_data = 
            convert(xml, target_data)
        from sys.dm_xe_sessions s
            inner join sys.dm_xe_session_targets st
            on s.address = st.event_session_address
        where s.name = 'alwayson_health'
            and st.target_name = 'event_file'
    ),
    full_path_cte
    as
    (
        select
            full_path = 
            target_data.value('(EventFileTarget/File/@name)[1]', 'varchar(1024)')
        from target_data_cte
    )
select
    @xel_path = 
        left(full_path, len(full_path) - charindex('\', reverse(full_path))) + 
        '\AlwaysOn_health*.xel'
from full_path_cte;

-------------------------------------------------------------------------------
------------------- replica state change events -------------------------------
-------------------------------------------------------------------------------
;with
    state_change_data
    as
    (
        select
            object_name,
            event_data = 
            convert(xml, event_data)
        from sys.fn_xe_file_target_read_file(@xel_path, null, null, null)
    )
select
    object_name,
    event_timestamp = 
        dateadd(hour, @utc_adjustment, event_data.value('(event/@timestamp)[1]', 'datetime')),
    ag_name = 
        event_data.value('(event/data[@name = "availability_group_name"]/value)[1]', 'varchar(64)'),
    previous_state = 
        event_data.value('(event/data[@name = "previous_state"]/text)[1]', 'varchar(64)'),
    current_state = 
        event_data.value('(event/data[@name = "current_state"]/text)[1]', 'varchar(64)')
from state_change_data
where object_name = 'availability_replica_state_change'
order by event_timestamp desc;

Monitoring the history of failovers in a SQL Server Availability Group is an essential task for database administrators. The script shared in this blog post provides a convenient method to access and analyze replica state change events, helping you pinpoint the exact moment a secondary node assumed the role of the primary node.