14

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?

  • 3
    You need to restore to find out how long this will take. Look at this answer. ` migrating it in a cloud service ` --you need to be more specific, options will depend on the service you chose. FYI there is not SQL 2014 R2 version. – SqlWorldWide May 9 '18 at 13:40
  • 1
    The backup and the restore operations are very different, so you can't use findings about one operation to come to sensible estimates for the other - internal and external factors will come into play. The best thing to do is to try it out, you may want to try out with a small backup first and then see where that takes you. – Bertrand Leroy May 9 '18 at 13:47
  • Are you backing up to multiple files? I've found that it can greatly speed up backup and restore times. mssqltips.com/sqlservertip/935/… – Jonathan Fite May 9 '18 at 14:39
  • "each 10 minutes ~ 70 files per day." Every 10 minutes for 24 hours is 144 files. – RonJohn May 9 '18 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. – yrushka May 10 '18 at 9:19
21

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.

0

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.

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.