Backup and Restore Strategies for Amazon RDS for SQL Server

More Info

As businesses increasingly rely on Amazon Relational Database Service (Amazon RDS) for SQL Server, they seek effective solutions to protect their data while meeting specific Recovery Point Objectives (RPO) and Recovery Time Objectives (RTO). RPO refers to the maximum time since the last backup that is acceptable, while RTO indicates the maximum delay acceptable between service disruption and restoration. Database administrators play a crucial role in ensuring data protection, and Amazon RDS for SQL Server provides several options for backup and restore.

When formulating a backup strategy for Amazon RDS for SQL Server, consider the following factors:

• What type of encryption is in use? Are you utilizing Transparent Data Encryption (TDE) or Amazon Elastic Block Store (Amazon EBS) volume-level encryption with AWS Key Management Service (AWS KMS)? Is encryption required for backup files in Amazon Simple Storage Service (Amazon S3)?
• Do you prefer restoring individual databases or the entire RDS cluster?
• What is your disaster recovery plan? Do you need cross-Region backups? Will you opt for a Multi-AZ or a Single-AZ setup?

Various strategies can be employed to secure data on Amazon RDS for SQL Server, including automated snapshots, manual snapshots, AWS Backup, native backup and restore, or a combination of these approaches. This article evaluates these strategies, addressing their respective options and limitations. We will also demonstrate how to leverage AWS Backup for centralized management of automated and manual snapshots across your organization.

Amazon RDS for SQL Server Automated Snapshots

Amazon RDS automatically generates snapshots of your DB instance during its designated backup window. These snapshots provide a complete backup of the entire DB instance rather than just individual databases. Automated backups are retained based on the backup retention period defined by you, allowing restoration to any point in time within that timeframe.

The following diagram illustrates the architecture of a backup and restore solution using automated snapshots.

Advantages of this approach include:

• Snapshot retention can range from 0 to 35 days, with a default of 7 days established during the creation of your RDS for SQL Server instance via the console.
• Log backups can occur as frequently as every 5 minutes, depending on workload, allowing for an RPO as short as 5 minutes upon restoration.
• Snapshots can be used to create a new RDS for SQL Server instance.
• Encrypted instances and databases maintain their encryption during restoration.
• Cross-Region automated backups can be copied.
• The initial snapshot of a DB instance provides a full backup, while subsequent snapshots are incremental, capturing only the changes since the last snapshot.

However, automated snapshots do have limitations:

• You cannot create an encrypted snapshot from an unencrypted instance.
• Individual databases within the instance cannot be restored separately.
• Restoration to the same instance is not possible, meaning endpoint changes occur during Multi-AZ restores.
• The DB instance snapshot must utilize the same customer-managed key (CMK) as the original instance.
• There is a brief suspension of storage I/O (approximately 10 milliseconds) during the snapshot process.

Amazon RDS for SQL Server Manual Snapshots

Manual snapshots are created on-demand, allowing for backups based on specific needs. This process results in a storage volume snapshot of the entire DB instance and is not limited to individual databases. Creating a manual DB snapshot on a Single-AZ instance may cause a brief I/O suspension, with duration varying based on the instance size and class. In Multi-AZ deployments, I/O activity may experience a brief suspension during backup.

The architecture of a backup and restore solution utilizing manual snapshots is illustrated in the following diagram.

Key benefits of manual snapshots include:

• You can create snapshots at any time, free from the 35-day retention limit.
• Snapshots persist indefinitely, allowing restoration even after the instance has been stopped.
• They can be used to create new instances and, in some cases, to change SQL Server Editions.
• A manual snapshot can be taken prior to a release to enable easy rollback, with all snapshots created post-launch being incremental.

Nevertheless, manual snapshots share the same limitations as automated snapshots and have additional restrictions:

• Manual backups cannot be taken while an automated snapshot is in progress.

AWS Backup

AWS Backup allows you to centralize and automate data protection across AWS services. This fully managed, cost-effective, policy-driven service simplifies data protection on a larger scale. It ensures compliance with regulatory requirements and enhances business continuity strategies. When used with AWS Organizations, AWS Backup facilitates the centralized deployment of backup policies, enabling configuration and governance of backup activities across all AWS accounts and resources, including Amazon EC2 instances, EBS volumes, Amazon RDS databases (including Amazon Aurora clusters), and more.

The following diagram outlines the architecture of a backup and restore solution using AWS Backup.

This approach offers several advantages:

• Automate scheduling, retention management, and lifecycle management of backups.
• Centralize your backup strategy across multiple accounts and Regions.
• Monitor backup activities and receive alerts across AWS services.
• Implement cross-Region backups for disaster recovery.
• Support for cross-account backups.
• Ensure secure backups with secondary encryption.
• All backups are encrypted using KMS keys.
• Works seamlessly with TDE.
• Restore to a specific recovery point from the AWS Backup console.

However, AWS Backup has its own limitations:

• Native SQL backup and restore capabilities are not supported.
• Cross-Region point-in-time recovery (PITR) is not available.

For more information on utilizing AWS Backup for Amazon RDS for SQL Server, refer to this informative tutorial here.

Native SQL Backup and Restore

Amazon RDS also supports native backup and restore for SQL Server databases. You can create a full backup of your RDS for SQL Server database and store it in Amazon S3. This backup can then be restored to an existing RDS DB instance running SQL Server or even to an on-premises server or a different RDS instance running SQL Server. Enabling native SQL backup and restore for Amazon RDS for SQL Server requires adding the SQL_Server_Backup_Restore option, which is critical for effective data management. For further insights on this topic, check out this authority site here. Additionally, for an excellent resource on training and support, visit this page.

In summary, choosing the right backup and restore strategy for Amazon RDS for SQL Server is essential for data protection and business continuity. By evaluating various options and understanding their limitations, organizations can develop a robust strategy that meets their specific needs.