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

There are two ways to generate backups for recovering MS SQL databases on EC2 instances. The type of backup you can generate depends on the recovery model selected for your MS SQL databases set by your Database Administrator.

• Point-in-time (PITR) recovery allows you to recover the database to a specific point in time. This is not available for databases using the Simple Recovery Model.
• Full database recovery backup allows you to recover a database to the last complete backup or to a specific point-in-time, provided the backup is complete to that specific time. This requires transaction log backups and the Full Recovery Model.

Backup storage requirements

VSS

You must ensure VSS storage limit is set to 25-30% 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.