We have a very big production database which is 10 TB. We are considering migrating it to a cloud service, but I have to understand the risks and estimate the duration of the whole process. I backup the database once per month using Native SQL Server 2014 SP2 backup solution. The duration is almost 720 minutes - 12 h. The compression ratio is 5, meaning the backup size is almost 2 TB. We can't afford differential backups because accumulated, these take as much time and space as the Full, so we do T-log backups (~200 Mb each) instead each 10 minutes ~ 144 files per day. The BIG question is having all these stats, does anyone have any idea how long it would take to restore a 2 TB backup containing 10 TB data? Would it be much longer than the Full backup duration?
-
"each 10 minutes ~ 70 files per day." Every 10 minutes for 24 hours is 144 files.– RonJohnMay 9, 2018 at 16:38
-
you are right about the version and the nr of files. Microsoft SQL Server 2014 (SP2-CU11) (KB4077063) - 12.0.5579.0 (X64) Feb 21 2018 12:19:47 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows and 144 files. my bad.– yrushkaMay 10, 2018 at 9:19
2 Answers
This is simple and quick to test.
Take that 2TB backup file, copy it to Azure using AzCopy. Provision a SQL Server using the "Free License: SQL Server 2017 Developer on Windows Server 2016" image configured with 12-15 TB of SSD storage. Remote desktop to the SQL VM and restore the backup directly from Blob Storage, or copy it to a local disk and then restore it.
Just remember to shut down the VM when you're not testing, and destroy it and the disks when you're done to minimize the charges.
Thank you all for answers. Here is what I have done to estimate the restore time. We can't afford currently a real test scenario of restoring the 2 TB backup and I don't know yet the final configuration for the migration. It would be close to what we have now. And it will be in Alibaba Cloud. We will be using Classic Virtual machines (ECS). I tested 2 smaller backup restores on the 4'th node in 4 node SQL cluster which is identical in configuration with the Production SQL Server instance. The restores revealed an increase in restore time of 20% to 50% from the backup time. Taking the 50% and applying to the 720 minutes backup time - I got a restore time of 1080 minutes (18 hours). I also tested the case with the backups for both tested databases split to 5 files and the restore time didn't decrease at all. It was very close to the one backup file restore case.