none
How do I determine an Availability Group Failover Time?

    Question

  • I am using SQL Server 2012 and have Availability Groups set up using two replicas. I need to be able to use TSQL to determine if a replicas failed over and switched between primary and secondary. Is there a DMV I could query or any other method I could use to get this information?

    Thursday, June 27, 2013 10:05 PM

Answers

All replies

  • Hello,

    The SQL Server Error Log reports events affecting AlwaysOn Availability Groups, for example, the connectivity state of availability databases between primary and secondary replicas. You can refer to the sql server error log for the failover information. What's more, the WSFC log and CLUSTER.LOG can also used to diagnose issues in the WSFC cluster or in the SQL Server resource DLL.
    Reference:

    http://social.technet.microsoft.com/Forums/en-US/8cbbd8e6-431c-4ea2-8cc2-41916d1a90f2/determining-cluster-failover-time-from-sql-log
    http://msdn.microsoft.com/en-us/library/dn135314.aspx

    Regards,
    Fanny Liu

    If you have any feedback on our support, please click  here.


    Fanny Liu
    TechNet Community Support

    Friday, June 28, 2013 6:16 AM
    Moderator
  • You may also check extended events.

    yup

    Sunday, June 30, 2013 10:49 AM
  • Thank you for your help and advice. I have a job that checks that all the database backups ran successfully. As we are using Availability Groups I need to determine that there was no failover between a specific time. If there was I have to adjust for it in my code.

    I could read from the error log with this code:

    xp_readerrorlog 0, 1, N'The NETBIOS name of the local node that is running the server is', N'', N'2013-07-02 11:00:00.000', N'2013-07-02 12:00:00.000', N'desc

    This will read the error log. However, how would I be able to flag it is a condition? If I use "If Exists" it fails.

    I need to be able to determine if a failover happened during a specific time built into TSQL code.

    Wednesday, July 03, 2013 5:44 PM