SQL Backup Performance on Amazon AWS
Posted by Rob Risetto on January 05, 2014
Up until now my exposure to Amazon cloud services was purely article based, however recently I was engaged by a client to assist in improving the backup and restore performance for a 700GB SQL Server database residing on a SQL Server VM provisioned in the Amazon AWS cloud service.
The problem
The 700 GB database backup was taking 6 to 7 hours and a restore around 8 to 9 hours. The restore time frame was also important since a Database Mirroring was corrupting due to a SQL Bug and therefore a mirroring refresh would take 14 to 16 hours.
The 700 GB database backup was taking 6 to 7 hours and a restore around 8 to 9 hours. The restore time frame was also important since a Database Mirroring was corrupting due to a SQL Bug and therefore a mirroring refresh would take 14 to 16 hours.
The initial environment configuration consisted of
1. 700GB database had 3000 data files one for each table partition (don’t ask)
2. All data files and transaction log file resided on one Provisioned IOPS (1000 iops) disk.
3. There was a disk for the OS and 1 disk for backups.
4. A full backup was using the Database Maintenance Plan
5. The primary server had 8 CPUs and 64 GB RAM
6. The mirror server had 2 CPUs and 16 GB RAM (not ideal) and same disk setup.The standard backup was getting a throughput of 30 to 40 MB/sec, further tests suggested that I would not get much more than this and seemed to be a limitation per disk (FastIO).The solution consisted of
1. Assign 4 x FastIO disks and spread the data files evenly over the 4 disks
2. Assign a 5th disk for the transaction log
3. Assign two disks for backups to allow a stripe backup
4. Run a backup writing to two backup files, each backup file on their own backup diskThe result was a throughput of around 130 MB/sec, backup time of 2.5 hours.
1. 700GB database had 3000 data files one for each table partition (don’t ask)
2. All data files and transaction log file resided on one Provisioned IOPS (1000 iops) disk.
3. There was a disk for the OS and 1 disk for backups.
4. A full backup was using the Database Maintenance Plan
5. The primary server had 8 CPUs and 64 GB RAM
6. The mirror server had 2 CPUs and 16 GB RAM (not ideal) and same disk setup.The standard backup was getting a throughput of 30 to 40 MB/sec, further tests suggested that I would not get much more than this and seemed to be a limitation per disk (FastIO).The solution consisted of
1. Assign 4 x FastIO disks and spread the data files evenly over the 4 disks
2. Assign a 5th disk for the transaction log
3. Assign two disks for backups to allow a stripe backup
4. Run a backup writing to two backup files, each backup file on their own backup diskThe result was a throughput of around 130 MB/sec, backup time of 2.5 hours.
The 4 x Disks enabled 4 x backup threads and two backup files gave 2 x write threads
I did play with Backup options like BufferCount, MaxTransferSize and Blocksize but I got acceptable performance without changing the defaults.
Note at this stage, the client was not interested investigating the option combining the 4 disks into a logical volume – I’m assuming this can be done on a AWS VM.
Other items configured, were 64KB allocation unit for each disk and Anti Virus exceptions for SQL Server folders (including backup).
I used the backup procedure from http://ola.hallengren.com/sql-server-backup.htmlto automate the backup, the backup command looked like
EXECUTEmaster.dbo.DatabaseBackup @Databases= ‘USER_DATABASES’, @Directory = N’F:\SQLBackup, G:\SQLBackup’,@BackupType= ‘FULL’, @Verify = ‘N’, @Compress = ‘Y’, @CleanupTime = 72, @CheckSum = ‘Y’
In my next post I will outline how I got the restore timeframe to drop.
Spread the love
Leave a Reply