4

I'd just like to know if there is any way to query when the AG group failed over.

eg - this is the Primary Replica now, but I'm pretty sure it was the secondary yesterday? How can i find when the failover took place

is there something specific in the Logs I should be looking for, or is there a tsql script to use?

  • Couple of good talks available on Pass.org. Recommend seeing few from Trayce Jordon. Eg "Finding root cause for unexplained Availability Group failover" – jesijesi Feb 12 at 21:56
8

You can use following PowerShell script to get last failover time

    Get-winEvent -ComputerName ListnerName -filterHashTable @{logname ='Microsoft-Windows-FailoverClustering/Operational'; id=1641}| ft -AutoSize -Wrap 
  • This works and gives a complete history of failovers of the AG (it appears) Note: it didn't work from my workstation - (error the RPC server is unavailable...) but logging into a server in the same dmz gave me access to all other SQL servers in the DMZ. – Trubs Nov 18 '16 at 4:21
  • 1
    Works great. But as @Trubs pointed out - not working (or working very slow) when running from local pc, but using etsn ListenerName and running command there (without ComputerName parameter ofc) works great. I've added reverse sort (cause I'm that lazy for scrolling) and it's looks like this: Get-WinEvent -filterHashTable @{logname ='Microsoft-Windows-FailoverClustering/Operational'; id=1641}| sort TimeCreated | ft -AutoSize - – Michael Logutov Aug 5 '19 at 14:26
  • What does id=1641 mean? – youcantryreachingme Sep 17 '19 at 1:43
4

You can set up a monitoring script (or a 3rd party SQL monitoring solution), to query the various columns of the sys.dm_hadr_availability_replica_states DMV on a regular schedule (e.g. every 30 seconds) and put a timestamp on it of when you've collected the values.

The particular columns of interest related to failover events are:

  • last_connect_error_number
  • last_connect_error_description
  • last_connect_error_timestamp

Additional columns that are worth tracking:

  • role
  • operational_state
  • connected_state

More detailed description for the above columns:

sys.dm_hadr_availability_replica_states (Transact-SQL)

An overview of all DMVs for AlwaysOn Availability Groups with links to more details is available on MSDN:

Monitor Availability Groups (Transact-SQL)

  • can we get for past how many times we have had failover ? – kudlatiger Jul 20 '16 at 2:45
3

Although its difficult to find out when the last fail-over occurred, you can get a notification (alert) when the fail-over does occur... (you can then search your email?)

You can create an alert to let you (or your team) know when a fail-over occurred using Error Number 1480

1]

Note: This will send an email for every database in the AG (on each server), which in my case was 12 databases on 2 servers. On the options tab you can set Delay between responses: to something like 1 minute, so that only one email a minute is sent.

  • The reason I marked my own response as the answer was I wanted something within SQL to take care of this and this is a complete answer (imo). And its very useful to know if you've just failed over... If you come to SO looking to find historical failovers, after the fact, I think @mashrur answer is the most complete answer – Trubs Nov 18 '16 at 4:26
  • 1
    There are useful directions for setting this up here. – David Jul 26 '17 at 15:25
3

Following lists failover time and direction for each database for all failover events on the server on which this is run, using T-SQL as requested.

-- Script to determine failover times in Availability Group 

;WITH cte_HADR AS (SELECT object_name, CONVERT(XML, event_data) AS data
FROM sys.fn_xe_file_target_read_file('AlwaysOn*.xel', null, null, null)
WHERE object_name = 'error_reported'
)

SELECT data.value('(/event/@timestamp)[1]','datetime') AS [timestamp],
       data.value('(/event/data[@name=''error_number''])[1]','int') AS [error_number],
       data.value('(/event/data[@name=''message''])[1]','varchar(max)') AS [message]
FROM cte_HADR
WHERE data.value('(/event/data[@name=''error_number''])[1]','int') = 1480
2

If the failover is successful, then windows event logs would have an entry

The state of the local availability replica in availability group '' has changed from 'RESOLVING_NORMAL' to 'PRIMARY_PENDING'

The state of the local availability replica in availability group '' has changed from 'PRIMARY_PENDING' to 'PRIMARY_NORMAL'

This MS KB article has details about the error message and you can use PowerShell script to send you email based on specific events.

Apart from the windows event log, you can look at Cluster Diagnostic Extended Event Log with a format that resembles - ServerName_InstanceName_SQLDIAG_*.xel

  • Thanks Kin, so there is nothing in SQL i can use - I have to use windows logs? – Trubs Mar 9 '16 at 21:05
  • @trubs You can using XEvents which can fire alerts. But thats something that you have to code yourself and implement. Also, you can use PowerShell script to alert you for particular events. Are you concerned with planned failover or unplanned ? – Kin Shah Mar 9 '16 at 21:08
0
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 s.name = N'AlwaysOn_health'
) ft

SELECT 
XEData.value('(event/@timestamp)[1]','datetime2(3)') AS event_timestamp,
XEData.value('(event/data[@name="previous_state"]/text)[1]', 'varchar(255)') AS previous_state,
XEData.value('(event/data[@name="current_state"]/text)[1]', 'varchar(255)') AS current_state,
XEData.value('(event/data[@name="availability_replica_name"]/value)[1]', 'varchar(255)') AS availability_replica_name,
XEData.value('(event/data[@name="availability_group_name"]/value)[1]', 'varchar(255)') AS availability_group_name
INTO #Temp 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
WHERE XEData.value('(event/data[@name="current_state"]/text)[1]', 'varchar(255)') = 'PRIMARY_NORMAL'
ORDER BY event_timestamp DESC;
  • 1
    While this wall of code might answer the question, your answer would be much more valuable if you added a bit of a description to it to explain why this is a good solution. – mustaccio Feb 12 at 21:40
  • XEData.value('(event/@timestamp)[1]','datetime2(3)') AS event_timestamp column will give the exact failover time and this what we use in our current systems and its working as expected. – Santhosh Kanneganti Feb 13 at 7:18
-2

This the code I use to determine which server is the Primary node and which one is the secondary node. I use this in every job on my server.

NOTE: this is for a 2-node AOAG. Adjust it as needed to handle multiple-server AOAGs.

declare @PrimaryServer varchar(50),
        @SecondaryServer varchar(50)

set @SecondaryServer = (select ar.replica_server_name
                from master.sys.availability_replicas ar
                join master.sys.dm_hadr_availability_replica_states ars
                on ar.replica_id = ars.replica_id
                where role_desc = 'SECONDARY')

set @PrimaryServer = (select ar.replica_server_name
                from master.sys.availability_replicas ar
                join master.sys.dm_hadr_availability_replica_states ars
                on ar.replica_id = ars.replica_id
                where role_desc = 'PRIMARY')

--If this is the secondary server, do whatever
If @@SERVERNAME = @SecondaryServer 
    begin
        --Put your code here for Secondary Server actions
    end
Else
    begin
        --Put code here for Primary Server actions
    end

--End If
  • 3
    Welcome Pilot Gal! Thanks for answering, but the question is asking for failover time, not which node is primary/secondary. – LowlyDBA Jan 30 '18 at 15:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.