In my environment, I have SQL 2014 with Always On Availability Group for databases in asynchronous with manual fail over.

We have 1 primary and 2 secondary replicas--secondary1 and sec2.

I have to do failover from primary to secondary1. I know some data loss will happen as it is a forced failover.

My questions:

  • What will be prerequisite for this?
  • Can we do in busy hours or after busy hours?
  • Is there any risk involved during failover.

Appreciate your suggestions as first time I will do failover.

  • 2
    If this is a planned failover, then there's no reason to have data loss. Just switch it to synch, let it catch up and then do the manual failover.
    – Tara Kizer
    Feb 9, 2017 at 20:15

3 Answers 3


Do yo have a test environment? If you don't, get a $200 free Azure credit and set your environment up there (same service pack level) and try it, it is one of the best way to gain confidence and find edge cases. Nothing can really beat testing a like to like environment except experience. Also check out the official support docs from Microsoft.

Per the doc you'll want to check out several items, including:

To determine the failover readiness of an secondary replica, query the is_failover_ready column in the sys.dm_hadr_database_cluster_states dynamic management view, or look at the Failover Readiness column of the Always On Group Dashboard.

To answer the question though, AlwaysOn failovers are very fast as the service is running on both instances and the listener then points to the new host. Ensure the hosts are connecting to the listener instead of the IP of the machine.

Thus, if tested and running properly in your environment it is much faster than FCIs (Failover Cluster Instances) during busy hours. Note that AlwaysOn only works per database and not per server so you will want to ensure jobs are setup right on the new primary replica, users, permissions/sids, and if you need to maintain databases in sync you will want to ensure that you do that somehow before failing one of them over and not the other. Also, if you use MSDTC for cross database transactions you'll want to be very careful as it can cause irreparable data corruption in many cases where you use cross db transactions within the same instance.

Perhaps Sean will be able to give you more of the issues you might face if he sees your thread.

  • Strangely, I get Invalid object name for dm_hadr_database_cluster_states on SQL Server 2016 running an AG. Aug 9, 2019 at 0:06

If you fail over to a secondary replica that is not synchronized, you will experience data loss. The amount of lost data would vary depending on how much latency you have at that exact minute. There is not guaranteed data loss--just the potential for it. For example, if the databases are idle, your asynchronous secondary replica might be in sync, and you would have no data loss.

If there is data loss in any of your AG databases (yes, it's possible for only some DBs in an AG to see data loss), those databases will not be able to resume data movement between replicas after the failover with data loss. You will need to tear down from & re-initialize those databases back into the AG in order to resume data movement.

If you are doing a planned failover, simply do the following steps:

  1. switch the secondary replica to be synchronous
  2. wait for data movement to catch up
  3. fail over with zero data loss
  4. switch back to asynchronous mode

Derik Hammer has a blog on using PowerShell to perform (and automate) this process: http://www.sqlhammer.com/availability-group-fail-over-test-with-powershell/


There are some recommendations when it comes to performing a forced manual failover of an availability group. Please see below.

Do not force failover while the primary replica is still running. Before you force failover, it is strongly recommend that you prevent clients from accessing the original primary replica. Otherwise, after failover is forced, the original primary databases and the current primary databases could be updated independently of the other.

  • This is not possible in Availability Groups: the original primary databases and the current primary databases could be updated independently of the other. A secondary replica is read-only at most, you cannot write to them.
    – user507
    Feb 9, 2017 at 20:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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