July 24, 2019 at 3:59 pm
I'm trying to understand how the primary / secondary relationship works in AOAG. I don't have a test rig right now & it's hard to explain!
Short question: What does SQL server check to ensure the primary & secondary in a AOAG relationship are in sync correctly & can be joined.
Background: I'm ultimately moving from 2008 mirrored from siteA to siteB > 2012 AOAG to siteA to siteB. My link between A & B will struggle to cope with the volumes of data & I need to do this for a lot of dbs which is why I might not set it to manual data synchronization.
create DB1 on primaryServer
configure AOAG
Do a full & log backup
Select initialisation manual prepare.
Copy to 2ndaryServer via physical transfer to site B or slowly down the network, restore full & log, leave in norecovery.
Join the restored DB2 on 2ndaryServer
So there may be a big delay between 3 & 6. At which stage do these get out of sync enough for me to not join? What can I check to see if they are in sync (as in logSequenceNumbers or some such). Under which conditions would the join fail here. I'm asking because of the volumes & timings and I want to minimize failures.
I'm also wondering if I could use my existing mirror pair here at all. Could I:
stop activity.
ensure all dbs are synchronized.
backup the dbs & logs & restore at site A.
failover.
copy the databases while they are online at DR.
restore at site B.
failback.
establish AOAG
I'm not sure if the failover would mess up the - whatever the sequnce numbers here are.
Any other plans gratefully received!
Thank you
July 24, 2019 at 7:45 pm
In order for a database to be joined - it must have the latest transactions that have been backed up applied to the destination. To shorten the number of transaction log backups to be applied, you can perform these steps:
You can avoid the databases getting out of synch - if you stop backing up the transaction log on the primary until after you have restored and joined the database to the group. However - since that process will take a long time that could present a very high risk. The above steps will reduce the amount of time you would need to stop the transaction log backups...so, to expand on the above:
These steps all depend on how long it takes for the differential - and copying the files across the network. Since the differential and transaction log backups should be very small - it should not take a long time for those steps to be completed.
What you really need to avoid is having another transaction log backup performed on the primary - after you have copied the other files across and restore. If that happens, you need to copy that file and restore - and if another one occurs you run into the same issue.
For smaller databases - you do the same thing but you may not need a differential, just perform a new backup - copy - restore, then apply transaction logs from that point...again, depending on how long it takes to copy the files across the network.
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply