Always ON Failover History

Hi Friends,

Today I am going to share an article which I think would be useful for many of us. Many times we need to find when the availability group fail over has happened. As you know we can find that information from Always ON health session extended events. The problem is we don't have history to track them and it is little difficult to gather that information across the estate.

In my organization we have many Availability groups being deployed hence I thought this can be handy.

The logic is very simple get the data from your central monitoring server using your favorite means (powershell or SSIS) across the estate and load them in to a table.

Below is the output

As you can see from the above image it contains the current replica and the corresponding Listener Name followed by the fail over date and the most important previous replica information. I added collection date as well which can be handy if you want to filter on respective dates.

Below is the code that I made use of to capture this information.

SELECT @FileName = target_data.value('(EventFileTarget/File/@name)[1]', 'nvarchar(4000)')
    FROM (
           SELECT CAST(target_data AS XML) target_data
            FROM sys.dm_xe_sessions s
            JOIN sys.dm_xe_session_targets t
                ON s.address = t.event_session_address
            WHERE = N'AlwaysOn_health'
         ) ft;

WITH    base
          AS (
               SELECT XEData.value('(event/@timestamp)[1]', 'datetime2(3)') AS event_timestamp
                   ,XEData.value('(event/data/text)[1]', 'VARCHAR(255)') AS previous_state
                   ,XEData.value('(event/data/text)[2]', 'VARCHAR(255)') AS current_state
                FROM (
                       SELECT CAST(event_data AS XML) XEData
                        FROM sys.fn_xe_file_target_read_file(@FileName, NULL, NULL, NULL)
                        WHERE object_name = 'availability_replica_state_change'
                     ) event_data
                JOIN sys.availability_replicas ar
                    ON ar.replica_id = XEData.value('(event/data/value)[5]', 'VARCHAR(255)')
    SELECT DATEADD(HOUR, DATEDIFF(HOUR, GETUTCDATE(), GETDATE()), event_timestamp) AS event_timestamp
   ,convert(varchar,getdate(),20) as [current date],
   dns_name as [listner_name]
        FROM base  b inner join sys.availability_group_listeners sag 
on b.group_id=sag.group_id where current_state='PRIMARY_NORMAL'
        ORDER BY event_timestamp DESC;

The above query has been obtained from this LINK and I modified little bit.

once you get this information we can make use of LAG and Partition functions to obtain the data as per our needs. Something like below will help you

create table AlwaysON_servers
   servername varchar(20)

insert AlwaysON_servers values('XXXXXXX')
insert AlwaysON_servers values('YYYYYYY')

create table AlwaysON_servers_history
   hostname varchar(30),
   ListenerName varchar(30),
   restartdate datetime primary key,
   currentdate datetime

select hostname as current_replica,listenername,restartdate,currentdate,
lag(hostname) over(partition by listenername order by restartdate)as previous_replica from  AlwaysON_servers_history

In case if you need any further information please feel free to comment.