Backup a large VM mssql server

[JosueM]

Good day everyone.

This is not a support question, what would be the best way to backup on site and then offsite a 2.5TB mssql 2008 r2 vm.

Currently we have this:
1. incremental backup once a day to physical windows server on gigabit ethernet network. average transfered data is 60GB and average duration is 1.5 hours.
2. backup copy once a day to offsite physical windows server on 40Mbps wlan network. average transfered data is 60GB and average duration is 4 hours.
3. replication job once a day on offsite from the backup copy on gigabit ethernet network. average transfered data is 7GB and average duration is 0.5 hours.

How could this be improved to get better times. I discarded backing up the VM on production hours cuz the timeouts when creating/erasing snapshots causes the users to disconnect.

I was considering using mssql mirroring or log shipping for the data and veeam just for the OS. still on failure manual work most be done at remote site in case of disaster.

Thanks in advance for your comments

[PTide]

Hi,

Do you equally want to get better times in all three cases, or there is a priority? Also, what do you mean by "better times"? Is is that you want backup job to finish faster, or you'd like to have a better RPO?

Thanks

[JosueM]

Hello PTide, ideally we would like to improve backup time and better RPO. in the real life is pretty hard to accomplish because of hardware limits. So we are looking to improve any of them to have in case of a disaster the least ipossible information lost .

Is there any recommendation based on similar escenarios?

Thanks iin advance.

[Anders]

To throw my experiences in:

Snapshot issues:
Design the SQL Server VM with few disks. The more disks, the more issues you will get with removing snapshots.
(Yes, all best practices talk about having a ton of disks. They just never consider snapshot based backups.)
Dont use reverse incremental, as it causes the snapshot to be open longer.
Backup from SAN Snapshots will reduce the active time of the vmware snapshot
Pure SSD Storage will help alot on snapshot removal.
Newer versions of VMWare handle snapshots better. 5.5 can be a nightmare. 6.0+ is quite the improvement.

Speeding up the backup
Direct SAN Backup (if iSCSI or FC based storage) can help alot on performance.
WAN Accelerator will help you speed up the transfer over the 40 Mbps link. (Enterprise Plus licens + SSD for cache in both ends needed)

What does the setup look like? (Storage, Network, hypervisor version?

[JosueM]

thanks for reply Anders,

-The mssql vm has 9 disk total , 3 for db data, 1 for db log, 1 for tempdb and the rest for OS,apps and swap.
-Onsite backup uses incremental type.
-unable to backup from storage snapshots.
-mssql vm is running on SSD volume.
-vmware version is 5.0
-wan accelarator not available.

Attached is a diagram of the current setup.

[Anders]

If you connect the Dell R330 backup server to the iSCSI Network, you could do Direct SAN backup to speed up the backup/replication speed part.
(I'm assuming you are using hot-add on the veeam VM right now)
Also be sure to check your Storage policy (I guess the optimal would be Round Robin on an ALUA profile)

But most important, consider upgrading to a VMWare 6.x - My guess is it will solve all your snapshot removal issues.

I cant see how to improve your remote site speeds without more bandwidth or a WAN accelerator.

[JosueM]

Thanks for your suggestions Anders, will try to upgrade vmware.

[PTide]

Josue,

I was considering using mssql mirroring or log shipping for the data and veeam just for the OS. still on failure manual work most be done at remote site in case of disaster.

As a workaround I can suggest you to use File Copy Job to have an identical copy of your primary backup chain + transaction log backups (.VLBs) on the DR site. Some people will find it not very convenient though, but the workaround allows you to perform point-in-time restores on DR site almost in the same manner as on the PR.

Thanks

[JosueM]

Thanks PTide I did not know that can be done, it seems a good idea I will try it too.