Backup requirements for SQL on EC2

Both backup and recovery of SQL on EC2 have specific storage requirements.

For general prerequisites and requirements for SQL on EC2, see Getting Started with Clumio for Microsoft SQL on EC2.

For more information about data recovery requirements, see Recovery Requirements for MSSQL on EC2.

Recovery model requirements for MS SQL databases

A SQL database can be configured with one of the three recovery models: simple recovery model, full recovery model and bulk-logged recovery model. While full and incremental database backups are supported with all recovery models, transaction log backups can only be taken when the database is in full recovery model or bulk-logged recovery model.

Clumio provides two types of recovery operations:

• Point-in-time (PITR) recovery allows you to recover the database to a specific point in time. This requires transaction log backups and is only available for databases with full recovery model or bulk-logged recovery model.
• Full database recovery allows you to recover a database to a selected database backup. Transaction log backups are not required to perform this type of recovery.

Backup storage requirements

Clumio application logs and changed block records

Clumio stores its application logs and change block records in the Clumio temp file directory. These log files generally do not exceed 1GB in size. The application logs are uploaded to Clumio SecureVault every 15 minutes and then truncated after that. Clumio produces changed block records in order to perform incremental forever database backups. Changed block records are very small in size and Clumio automatically purges the older records to make sure they do not exceed 1 GB in size.

VSS

You must ensure VSS storage limit is set to 10-20% of the database disk/drive size. Refer to the following Microsoft article for more information about how to configure this setting. Clumio recommends using a different drive on the host as the VSS storage location rather than using the database drive.

Transaction Log Backups

• Clumio takes transaction log backup using native SQL commands and directly streams the backup content into Clumio SecureVault. There is no additional storage required. If you wish to retain a copy of the log backup on your host, please contact Clumio support for additional space requirements.
• For Transaction Logs, it is recommended to set the initial size and auto-growth of the Transaction Log file to reasonable values. Although there is no one optimal value for Transaction Log File initial size and auto-growth that fits all situations, the following may be considered a good starting point for a normal workload:
  • set the initial size of the SQL Server Transaction Log file to 20-30% of the database data file size.
  • set auto-growth to a large amount, based on your database growth plan.

Best practices and recommendations

• VSS snapshots can fail if you create a snapshot of more than 35 databases at the same time. Refer to the following Microsoft article for more information about this issue.
• If there are any native SQL based backup scripts on the SQL host, this could adversely affect Clumio transaction log backups. Log backups may become full and there may be broken log chain issues. Clumio recommends that you avoid running Clumio and other SQL backups in parallel.