SQL always on manual failover RRS feed

  • Question

  • Hello,

    I want to ask you how to perform the manual failover of availability group, does my primary server should be on to perform the failover what if my entire datacenter got down?? what will happen if my only running server was  the DR server and asynch commit is configured between them ??

    how to configure the failover in that case ?? and for the failback stage what will happen ??

    The only form of failover supported by asynchronous-commit mode is forced failover (with possible data loss).



    Saturday, June 25, 2016 9:37 AM


  • For a manual failover there are two things to take into consideration:

    1) The Windows Server Cluster needs to be online. In a typical DR scenario you would need to do this with a ForceQuorum startup of the cluster service. But it depends on your setup.

    2) The SQL AG needs to be switched. This is done from the secondary. (ALTER AVAILABILITY GROUP <AGName> FAILOVER) If the AG was not in sync at that time you need to force failover, and live with potential data loss due to that fact. Normally in this scenario your way back is to resynch the primary datacenter once it is back online and then do a (non-forced) failover back.

    One thing on the sideline: Beware of Split-Brain scenarios. Once you force the secondary online it can happen that the primary system also comes online, if the two datacenters can't reach each other. If that happens your potential for data loss is significantly higher...

    Saturday, June 25, 2016 11:58 PM