Double Concurrent Restore from one backup for 3 node AG?

Posted on

Question :

I have a 400GB database on SQL 2014 Enterprise Edition Instance. I took a full backup as a single file (not striped), it restores to 8 datafiles and 1 log file. I put the backup in a shared folder that all instances have access to.

I need to restore without recovery (NORECOVERY) on two instance, then follow through with the other steps to join them in the AG.

Using one SSMS I used 2 query windows to, restore the database on the two different instances (different VM servers in different locations) at the same time. I used sp_whoisactive to monitor progress as both restored.

Everything worked fine, the final t-log and join with the wizard gave me a stable synchronized database in the AG.

About 30 minutes in I got wondering if, this was counterproductive. Clearly it worked, the time to completion was similar (or a bit longer) then similar single restores. But there are multiple distractions and ongoing process, so my perception may be wrong. From watching the Wizard I know it only restores to one instance at at time.

All things being equal is doing a double concurrent restore from one backup to two instances an optimal timing choice? (is it faster or slower then one after the other?)

I found 2 related posts that do not seem to address my question

Answer :

All things being equal is doing a double concurrent restore from one backup to two instances an optimal timing choice?

It is definitely faster, since the wizard is doing it in sequential order.

You can use sqlcmd or powershell runspace or (possibly coming in dbatools cmdlets) to do parallel restores.

To even speed up that, split the full backup into stripes and enable instant file initialization.

note: make sure that there is no adhoc log backup happening since it can break your lsn and your restore.

Leave a Reply

Your email address will not be published. Required fields are marked *