none
Distributed availability group Failover issue. RRS feed

  • Question

  • Hi All,

    i have created Distributed Availability group set up as below

    in Data center A, created 2 node AG1 setup (Synchronous)   in Datacenter B, created 2 node AG2 setup (asynchronous).

    created Distributed AG setup (synchronous) on top of these two AGs and it is working fine.

    when i am executing Failover command (on Primari of DC A) to failover manually it is not failing over properly, it changes the role of AG1 as secondary but shows disconnected from AG2.

    please help to clarify if any config problem is there or i need to follow certain steps to failover.


    SQL Server DBA

    Thursday, October 26, 2017 2:33 PM

All replies

  • Are you doing the manual failover from within AG1? Or are you performing a manual failover between AG1 and AG2?

    Edwin M Sarmiento Microsoft Data Platform MVP | Microsoft Certified Master/Solutions Master
    Blog | Twitter | LinkedIn
    Learn SQL Server High Availability and Disaster Recovery


    Thursday, October 26, 2017 3:34 PM
    Moderator
  • Hi Edwin,

    thank you so much for the response,

    i executed ALTER Availability group DistAG FAIlOVER_ALLOW_DATA_LOSS command on Primary of AG1.


    SQL Server DBA

    Friday, October 27, 2017 5:28 AM
  • The command you used

    • Disconnected AG1 from AG2 and got the distributed AG in suspended state
    • Forced potential data loss

    The proper way to manually failover a distributed AG is to first configure all of the replicas to synchronous, then, configure configure the distributed AG to synchronous before attempting to failover. Do not use FAILOVER_ALLOW_DATA_LOSS unless you really are sure that you want to invoke that command.


    Edwin M Sarmiento Microsoft Data Platform MVP | Microsoft Certified Master/Solutions Master
    Blog | Twitter | LinkedIn
    Learn SQL Server High Availability and Disaster Recovery


    Friday, October 27, 2017 4:12 PM
    Moderator
  • So Edwin, in my current setup i have 

    AG1(Synchronous)

    DAG (Synchronous)

    AG2(Asynchronous)

    so i'll initiate failover manually with below steps:

    1) i have to change AG2 from Asynchronous to synchronous

    2) then execute Failover command on primary of AG1 with below command

    ALTER Availability group DistAG FAIlOVER

    kindly suggest steps seems ok or need to add or edit any steps

    thanks Edwin for the great help.

    regards

    sachin


    SQL Server DBA

    Friday, October 27, 2017 5:35 PM
  • That is correct. Be sure to do this during a maintenance window when there is minimal amount of transaction logs generated on the primary replica of AG1

    Edwin M Sarmiento Microsoft Data Platform MVP | Microsoft Certified Master/Solutions Master
    Blog | Twitter | LinkedIn
    Learn SQL Server High Availability and Disaster Recovery


    Saturday, October 28, 2017 12:51 AM
    Moderator