1

Setup:

  • 2 Availability Groups
  • 2 Replicas on Synchronous Commits.
  • 1 Replica on Asynchronous Commit.
  • Automatic Failover configured for the two replicas on Synchronous Commits.
  • Manual Failover configured for the single replica on Asynchronous Commit.

Problem:

  • ESX Server for the primary replica went down.
  • Automatic failover didn't happen.

  • Manual forced failover was performed using the command:

    ALTER AVAILABILITY GROUP AccountsAG FORCE_FAILOVER_ALLOW_DATA_LOSS;

  • This resulted in a lot of data loss.

Question:

  • What am I missing here ?
  • Why did the data loss happen? My understanding is that since two replicas was on synchronous commit, there would be no data loss when either one of them goes down.
  • Is it the data in the Log transactions that was lost. Would periodic log shrink prevented this data loss ?
Improve this question

1 Answer 1

Reset to default
2

What am I missing here

You should first read How It Works: Always On–When Is My Secondary Failover Ready?. This would tell you about scenarios when your secondary is not failover ready and what could lead to "no automatic" failover

Why did the data loss happen? My understanding is that since two replicas was on synchronous commit, there would be no data loss when either one of them goes down

The data loss happened because you forced failover over to replica which was not synchronized. Look at the word "FORCE_FAILOVER_ALLOW_DATA_LOSS" it says itself that if you use me you "might" face data loss. What this command does is this beings online database discarding any transaction log blocks which originated on primary but somehow did not made on secondary and since secondary has no information about it it cannot replay it and hence you might face data loss when database comes online.

Is it the data in the Log transactions that was lost. Would periodic log shrink prevented this data loss ?

Let us call it transaction log block not data. This log block was on primary but due to unexpected shutdown it was not delivered to secondary and secondary was not synchronized. If you are able to bring primary online after some time without doing any forced failover you might have been able to save data loss but since you forced failover it is not possible. May be later if you bring primary replica online and the information is there in transaction log you can get it in primary replica. Shrinking has NO business here at all.

Improve this answer
3
  • The secondary replica with Synchronous Commit was showing synchronized. Also, wanted to add that the data loss was close to around 2M rows across many tables. The forced fail over was performed with the data loss option as it was disaster recovery. However, it doesn't explain the amount of data loss.The 2M rows is around 3 weeks of data. We are sure that the replicas were in sync before the disaster. Can there be any other reason for the data loss? Thank you for your detailed answer on my previous question.
    – Hemanth Sundareshan
    Commented May 17, 2019 at 2:08
  • @HemanthSundareshan The answer to your question lies in the link I have shared in the answer. Read the last part "Don’t Kill SQL" it talks about registries not being updated on sudden SQl Server termination. Regarding huge data loss are you sure the transaction committed on primary ?. Were you using explicit transaction ?
    – Shanky
    Commented May 17, 2019 at 6:20
  • Yes. The transactions have been committed on the primary. In my above set up related info, i have referred to a Replica on Asynchronous Commit. We use this replica to pull data into ETL database to run ETL. We have all the data in the ETL db. Also, we take weekly backups, the data is present in the last weekly backup.
    – Hemanth Sundareshan
    Commented May 27, 2019 at 1:05

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.