August 10, 2023 at 6:17 am
Hi All,
We are having 2 Data center.
For application A failover SQL instance been used and application B two node AG been used.
Whenever there is a failover in AG the database in DR will not become online, it is going in recovery and take time to be online.
But for FCI application B in failover SQL cluster database become online in same two done cluster configuration.
Did the failover SQL cluster will not do the recovery phase sql server - Analysis, Redo & Undo for SAN? Maybe, Did it just switch over the instances?
August 10, 2023 at 2:27 pm
Failover will always include recovery. It has to. It's likely that the one that is going really slow simply has a lot more transactions to deal with.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 10, 2023 at 7:20 pm
Need more information - first, I have to assume the FCI is configured with hosts in the same data center since that will be using shared storage. Failover for the FCI will take as long as it takes start SQL Server and perform recovery.
Assuming the AG is across data centers - that would normally be configured in asynchronous mode which would not allow for automatic failover. If the AG is configured using synchronous mode - and automatic failover, then I would expect recovery on the secondary to be almost instantaneous.
If the secondary is set to asynchronous - once the primary is no longer available it would transition to recovering and wait for a forced failover to bring it online and make it the new primary.
You cannot really compare the recovery of an FCI to the recovery of an AG. In an FCI - SQL Server is NOT running on the secondary, so in a failover situation SQL Server has to be started and then each database is recovered - performing the roll forward and roll back operations based on the data in the transaction log.
In an AG - since SQL Server is already up and running then recovery is much shorter because the transactions have already been applied. If there is a large redo queue, that would delay recovery - but that would only occur if the AG is set to asynchronous.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 11, 2023 at 9:57 am
Thanks both of you. I will check the AG setting etc.
Thanks Jeffrey Williams for detailed answer, I have new learning.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply