SQL Server restore speed up

[md@gmx.net]

Hi there,
is it planned to use instant file initialization for Veeam SQL Server databases?
it would speed up significantly the restore time.
Right now we do some testing and can confirm that in our scenario the restore with veeam is three times slower than using the SQL Server restore command
and instant file initialization enabled for the SQL Server Service account.

Regards
Marcus

[foggy]

Hi Marcus, if I'm getting it right, then it should work transparently for Veeam B&R, since it is configured on the SQL Server itself and Veeam B&R doesn't control these settings when restoring databases.

[Gostev]

Most likely, this is an "apple to oranges comparison" issue, as opposed to anything else.

There will always be some performance difference, since you're performing a restore from different backup formats: native SQL database backup vs. deduplicated image-level backup of the entire SQL Server VM that has the required database file fragmented across the VM image. Plus, when doing such tests you should always make sure both backups are sitting on the same storage volume.

[md@gmx.net]

Hi Marcus, if I'm getting it right, then it should work transparently for Veeam B&R, since it is configured on the SQL Server itself and Veeam B&R doesn't control these settings when restoring databases.

It's not a SQL Server option. It's a operating system permission.

[md@gmx.net]

Most likely, this is an "apple to oranges comparison" issue, as opposed to anything else.

There will always be some performance difference, since you're performing a restore from different backup formats: native SQL database backup vs. deduplicated image-level backup of the entire SQL Server VM that has the required database file fragmented across the VM image. Plus, when doing such tests you should always make sure both backups are sitting on the same storage volume.

Most likely, it's a real world comparision and we don't talk about vms here.
A three times slower restore with Veeam B&R is not some performance difference.
The reason why Veeam B&R is so slow with a restore is the fact that they do an ordenary file copy over iSCSI and don't use instant file initialisation as recommended by Microsoft.

BTW. How do I restore dbs with Veeam B&R if the iSCSI protocol is not permitted in the LAN?

[Gostev]

Sorry, it doesn't actually matter if it's a VM or not.

What matters the most is that you're comparing restore speed from an image-level backup of the entire SQL Server machine vs. application-level backup of the specific SQL Server database. The issue that Instant File Initialization is intended to solve doesn't exist for Veeam in the first place, because image-level backups force us to perform database file restore in a certain way, which is absolutely different than restore from a native SQL backup into the newly provisioned database file.

If iSCSI protocol is not permitted in the LAN, you could install Veeam console on the SQL Server itself, and import backups from the remote repository (this will not use iSCSI).