0

I have 2-node SQL Server 2019 FCI cluster, which takes up to 4 minutes to failover from one node to another.

The cluster has 3 user databases, migrated from the older SQL server versions:

  • 2 databases with higher load have SIMPLE recovery model (and compatibility level 100)
  • 1 database has FULL recovery model (and compatibility level 130)

Would changing recovery model and/or increasing compatibility level reduce failover time of the cluster?

Unfortunately, I have no access to the server, but checking logs is definitely the first thing I requested already. Also, I understand that compatibility level might be the last thing to affect failover time. But I was not able to find any data on relation of recovery model to failover time of a FCI cluster.

I'm afraid using ADR yet having read somewhere on Microsoft that it was introduced on SQL Server 2019 and improved in SQL Server 2022. Maybe when I get next environment upgrade in a few years.

Paul White
  • 94,921
  • 30
  • 437
  • 687
Sergey Nudnov
  • 103
  • 2

2 Answers2

2

Would changing recovery model and/or increasing compatibility level reduce failover time of the cluster?

Generically, no, not really. Recovery has to start from the oldest checkpoint (some changes with ADR/CTR), which must be in the log regardless of recovery model.

While ADR/CTR may help (only helps if there are ADR compatible transactions wherein the log has a checkpoint very far in the past or has many open transactions), we don't know where the bulk of the time is spent and thus it is unsure what may help.

I have 2-node SQL Server 2019 FCI cluster, which takes up to 4 minutes to failover from one node to another.

Where is the time spent on this? How long does it take the cluster to move the resources and online them? How long does it take SQL to start? How long is the database recovery (the database in question)? What stage in database recovery takes the longest amount of time?

Once you have a timeline of events and know how much is spent at each stage, an investigation into possibly reducing the time spent on that stage will be possible. Until then, it's random guessing.

Paul White
  • 94,921
  • 30
  • 437
  • 687
Sean Gallardy
  • 38,135
  • 3
  • 49
  • 91
1

If the long failover is due to excessive restore time, consider changing the target recovery time of the database or configuring Accelerated Database Recovery. - David Browne - Microsoft

comment-answers
  • 1
  • 2