August 24, 2021 at 6:01 pm
I have setup the availability group.
Primary Replica - Full, Diff and Log backups & Maintenance Jobs etc.
Secondary Replica 1(Synchronous commit) - For HA and Automatic Failover
Secondary Replica 2 - (Asynchronous commit for Reporting purpose only)
Secondary Replica 3 (Asynchronous commit) - For DR
Once the databases in primary is up and running, I will plan on synchronization to secondary replica's so thinking of automatic seeding option in that case I have to ensure when the sync is running across all secondary replica's at same time the full and diff backup should not be running right? Is there any better option than this during the go live?
August 24, 2021 at 6:11 pm
Automatic seeding has a number of limitations.
Depending on the size of database(s), network speed between sites, distance between sites, number of databases to seed etc it can take a considerable amount of time to complete.
Auto Seeding is a single threaded operation, can only handle 5 database syncs at a time.
Auto Seeding also prevents the log from being truncated while seeding is running so think about the impact to RTO/RPO.
If you already want have fulls/diffs/logs, then the better option is to just use your existing backups as the starting points.
They should all be going to a central location anyways so all replicas should be able to see the backups so there should be no issues there.
August 25, 2021 at 3:00 pm
Thanks! During the seeding process if I use the option of backups then I would not be doing log backup until the seeding is finished right? Or then I have to restore those logs until that point?
This raised another question, since backup is single threaded, so if I stripe the backup to multiple files will it provide mult-threaded write? Is my understanding is correct?
August 25, 2021 at 3:34 pm
If you use Automatic Seeding, the log is unable to truncate, thus you CANNOT backup the log while seeding is happening, resulting in an impacted RTO/RPO, so you need to check if seeding can complete without you impacting your recovery objectives to be able to use seeding.
If you sync from backups (so using your FULL/DIFF/LOG) you CAN still take log backups, you just have to account for any drift and ensure you apply any new logs to the secondaries, think of it as a manual logshipping, then when you are ready you can just join the replicas to the group and its working.
Writing to multiple bak files will make it multi threaded yes, but you need to ensure you don't backup to to many bak files as you could then saturate all the IO paths into the device.
BACKUP DATABASE MyDB TO DISK = 'Z:\Backups\MyDB_Part1.bak', DISK = 'Z:\Backups\MyDB_Part2.bak'
Thats a 2 thread write, but if the device can only have 1 thread then I haven't saved any time, if anything probably increased backup time a little.
If your paralleling backups then again thats something to consider, if you backup 10 databases in parallel thats 10 threads to disk, splitting that is then an exponential growth of threads so you need to ensure the infrastructure can handle that many threads.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply