Backup requirements for SQL on EC2

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

For general prerequisites and requirements for MS 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 the last complete backup. Transaction log backups are not required to perform this type of recovery.

Backup storage requirements

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

• For Transaction Log backups, Microsoft SQL Log backup commands output the backup file to a disk volume available locally on the host and does not offer an option for direct upload to remote targets like Amazon S3. This requires a temporary or staging file location to be provisioned with enough free disk space before the files are uploaded to Clumio Cloud. The space required for this temporary file location depends on the size of the database transaction log which can be determined from this Microsoft article.
• 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.
• Clumio also recommends adding separate drives (to be configured as "Temp File Path" during Clumio installation, see step 5 in the section Adding SQL Server hosts to Clumio), with enough disk space to contain the temporary space needed to backup transaction logs so that backups can be successfully performed.

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.