Azure SQL Database Failover Group: GracePeriodWithDataLossHours

Question

What's the actual use case of GracePeriodWithDataLossHours ?

Grace period with data loss: Because the primary and secondary databases are synchronized using asynchronous replication, the failover may result in data loss. You can customize the automatic failover policy to reflect your application’s tolerance to data loss. By configuring GracePeriodWithDataLossHours, you can control how long the system waits before initiating the failover that is likely to result data loss.

if I set 1 hour . if after 1 hour there are still data loss , will that be still auto failover happen ?

Answer 1

@sakuraime The grace period is the time you choose before we do the auto failover. If you choose minimum that is 1hr, that means that we won't do the auto failover until that hour has pass. The Data loss in a healthy replica should be 5 sec or less. In that hour we try to fix the primary, so you don't lose any data. But if can't fix it in that 1hr that you choose as your grace period. we'll do the auto fail over and you could lose up to 5 sec of data.

Regards,
Oury

Answer 2

Yes. Auto-failover will still happen with possible data loss after 1 hour.

https://sqlworldwide.com/auto-failover-groups-graceperiodwithdatalosshours/
<<<
Manual failover is a better option for customers who need certainty on failover timing. The automatic failover policy is only “automatic” in the sense that a customer doesn’t have to do anything. The implementation on Microsoft’s side includes manual steps to arrive at the optimal compromise between maximizing availability and minimizing data loss across multiple customer databases. With manual steps, there is always some uncertainty.

At the same time, someone can make an argument to keep the setting automatic failover. Depending on the situation and impact on business, customers can initiate manual failover (before Microsoft triggers automatic failover).

>>

Answer 3

@sakuraime Please see answers below

1. What's the actual use case of GracePeriodWithDataLossHours ?
   This is when you select Automatic failover, let’s say we have power, network, cooling issue… that causes the primary to go down, we won’t failover to the secondary until the grace period has finished.
   In that Hour we will try to fix the issue to bring the primary back online.
2. if I set 1 hour . if after 1 hour there are still data loss , will that be still auto failover happen ?
   Yes, the replication is asynchronous, in the case of unplanned failover transactions committed to the primary but not replicated to the secondary will be lost.
   So if we bring back the primary in that hour there is no data loss, but if we can’t bring it back in 1hr (GracePeriod you chose) we’ll do the failover and it could have data loss.
3. if the primary server fails , if there are data loss , it will wait 1 hour before failover ??
   Correct.

Hope that answer your questions.

Regards,
Oury