SQL Restore Data Flow Optimization

[StoopidMonkey]

I'm hoping somebody can explain the finer points of exporting databases (in BAK format if it matters) using Veeam SQL Explorer. I opted to restore a BAK file to the same SQL VM that it was backed up from, which also happens to be the SQL Mount Server for the recovery operation. What I'm seeing is that it first sends data in a one-way stream from the VBR server to the SQL server, which stored the data in a BAK file in a TMP directory. THEN once that finished it created ANOTHER BAK file (so I need twice the size of the BAK file available) and started copying the data from the TMP BAK to the restored BAK. Worse yet, I don' think that data copy is direct between those two files. I'm now seeing a maxed-out bidirectional data flow from the NICs, almost indicating that the SQL server reading the TMP BAK data, sending it to VBR for verification, and VBR is sending it back to SQL to write to the new BAK file. Did I completely botch how these restores are supposed to happen? Here I thought I was saving time and network bandwidth...

[PetrM]

Hi Chris,

I guess the SQL VM is not a mount server but a staging one? Basically, this is the behavior by design as export consists of the following steps: it copies .bak to a temp folder on the staging server from a mounted backup, attaches a database and applies logs, creates backup (the second .bak) to a temp folder, copies the second .bak to the target. I asked my colleagues to reproduce this behavior in lab because we have an idea to drop the first copy from export algorithm. But so far, I cannot explain bidirectional data flow through Veeam B&R that you're seeing, we'll dig deeper into it.

Thanks!

[StoopidMonkey]

Thanks for the explanation! What would you say is the optimal configuration for a restore that gets the BAK file finalized as fast as possible? Assume that networking is the bottleneck @ 1Gbps.

[PetrM]

Hi Chris,

I was on vacation and could not update the topic. To be honest, I don't think that there is some sort of "fine-tuning" in VESQL to speed up data transmission over the network as the processing rate fully depends on network channel performance. However, why don't use Instant Database Recovery if you need to fire up a database ASAP?

By the way, the question about bi-directional traffic is still open but I did not hear back from my colleagues yet.

Thanks!

[PetrM]

Hi Chris,

Please let me to correct one statement from my first post: I said that export is started by copying .bak from backup to a temp folder. In fact, there is no .bak file in backup but .mdf and .ldf files are copied to to the staging server and stored in .bak file in the temp folder. All further parts of the algorithm work as described.

Returning to the question of bi-directional traffic, it is by design as copy operation is performed by VESQL itself which copies data to the target from the staging server using UNC paths.

Basically, I see two possible optimizations:
1. We can drop the first copy operation of .mdf/.ldf to .bak and attach database with logs directly from mount cache similar to how it works in Publish/IR. Initially, we didn't want to use mount cache due to slow read issues but we've managed to accelerate read in v10 release. Therefore, I think we can safely get rid of this first copy to .bak in temp folder.
2. We can make direct copy from from the staging server to a target without sending data over Veeam B&R where VESQL is running.

Both optimizations are not easy to implement, thus I cannot comment on ETA but I want to thank you for paying our attention to the export workflow!

Thanks!

[ibarizz]

Hello,

I'm just revisiting this old thread to ask a question related to this. Is there any way to understand how much space the /tmp/ folder in the server's target is going to occupy? Is there any kind of ratio I should take into consideration for this? I'm facing an issue where I have very limited space on my target disks, for example, 500 GB, and when I try to restore a 600 GB database, I'm unsure if adding an additional 100 or 200 GBs to that disk will be enough, as the /tmp/ folder will take up 'X' amount of space that I won't be aware of.

Thank you in advance!