0

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

2

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.

  • You are assuming the .bak can be read by both restores at the same time without interfering with each other. Why do you believe this is possible? – James Jenkins Feb 28 '18 at 11:02
  • Have you tried it ? Do you get errors? – Kin Shah Feb 28 '18 at 11:33
  • I did try it, but I didn't check if there were waits. It could be that the two restores where taking turns reading the file. Thus taking the same (or longer) then doing the restores in sequential order. – James Jenkins Feb 28 '18 at 13:17
  • If you use the same file to do restores on different servers what you are saying does not add up. – Kin Shah Feb 28 '18 at 14:41
  • Accepting answer, I tried to do a test with a 750GB database, but my two destination servers have different quality storage (DR is slower) I can't really compare. Overall the time for restore on the faster storage was essentially what it would have been, for a single restore. – James Jenkins Jun 14 '18 at 12:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.