Improve the Restore Performance on AWS
Posted by Rob Risetto on January 20, 2014
Following on from my backup performance post, the next challenge was trying to restore a backup on the mirrored server within a reasonable time. In this case, a reasonable time was 3 to 4 hours as opposed to 12+ hours. Again the issue we faced on AWS was the 39 MB/sec throughput limit per high
IO disk. Originally the database files resided on one disk and the backup was also on the same disk, so really we had no chance of meeting any reasonable timeframe for the restore. Also in addition the AWS server was a much lower spec than production.
So here’s what I did to get the restore to 2.75 hours.
- Got the customer to upgrade the server to a large instance. This ensured the VM wasn’t throttled by AWS due to it being a low spec VM.
- Got the customer to create 4 x data disk and 1 x transaction log file disk. This would then match the primary SQL Server.
- Got the customer to create 2 x backup disks. The backup from the primary server was striped to two backup files, so each file was copied to its respective backup disk partition.
- Configured the SQL Server service account for Instant File Initialisation. The premise here was to avoid zeroing out of data files during the restore process. To enable Instant File Initialisation you have to assign the SQL Server service account the “Perform volume maintenance tasks” right via the local security policy