July 21, 2021 at 1:38 am
When performing Full Backup and Log Backup of an Availability Group database. How is the log chain not broken when the Availability Group fails over to the Secondary node in a two node Cluster?
As I see it we have a 2 node cluster with Full Backup and Log Backup running on Node A. Then SQL fails over to Node B. Log backups continue to run on Node B as it is now the Primary. However no Full Backup has been performed on Node B for months. So in the event of a disaster am I able to restore the Full Backup and Logs from Node A and the other remaining Logs from Node B?
We use Commvault so maybe this is seemless but it does not make sense to me that it will work..
July 21, 2021 at 11:40 am
Months of log restores? Oh no. That will be a nightmare, regardless of the failover question. Speaking of, because the secondary has the same transaction id values and will be able to sort which have been committed and which have not. However, OMG, you really need to take more full backups! Now. Paul Randal told me a story once. He was brought in on a consulting gig because someone had a full backup and then, I forget, 3 months of logs. Estimate for the time to restore was something like 18 days. You simply can't restore months worth of logs in a timely manner. Multiple weeks worth are probably too much too. I would never let it go over a week. Look to differential backups, something to supplement one backup months old.
Have you tested a restore? How about a restore to a point in time? Validating your backup strategies through a restore test is vital. In fact, most people nowadays talk about a restore strategy, not a backup strategy.
"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
July 21, 2021 at 1:23 pm
The log chain doesn't break as the way availability groups work is to ensure the LSN's match between all replicas, so there is never any drift in the chain.
With that being said as long as you have all the logs since the full be that from A or from B then yes you can restore to a log backup.
So if the last full from B was back in January, but on A it was July, then you would take the July backup and replay all logs taken from A and then any from B after it was failed over.
But remember backups are worthless, it's the restores that will save your bacon, so you really should be testing all this out and ensuring you can continually meet RTO/RPO deadlines etc.
I personally HATE CommVault and really any 3rd party backup software (with a few exceptions), I would much rather backup the databases to a UNC path common to all replica's using something like OLA, then CommVault would go and do a file level backup instead of that server and secure the BAK/TRN files and not do it's VSS snaps that it does as that just causes more issues than it solves in our environment.
That way I control the backups, and I control the restores and I know what I can achieve to meet the company RTO/RPO's without relying on other teams getting files out of vaults etc.
July 21, 2021 at 1:28 pm
Quick note regarding any 3rd party solution:
I'm game to use any of them. Show me a restore to a point in time that meets our SLA, RTO & RPO. Now do it a second time. Did it all work? Great. We use the 3rd party solution. Did it tank? Hell no, we're not using that. It's that simple. Just validate you can meet the recovery requirements. If not, do not use it.
"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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply