Managed disaster recovery with Amazon RDS for SQL Server using cross-Region automated backups

Amazon Relational Database Service (Amazon RDS) for SQL Server now supports managed disaster recovery (DR) using Amazon RDS cross-Region automated backups. This feature extends the existing Amazon RDS backup functionality, giving you the ability to set up automatic replication of system snapshots and transaction logs from a primary AWS Region to a secondary Region. The new feature provides a cross-Region DR capability for mission-critical databases, allowing you to restore your database to a specific point in time within your backup retention period. With this feature, database and backup administrators can reduce their Recovery Point Objective (RPO) to minutes.

In this post, we discuss this new feature in detail and demonstrate how you can enable cross-Region automated backups and restore of your database to a point in time.

Solution overview

Before we start, we should discuss some important considerations when planning a cross-Region DR strategy using this method.

Recovery Point Objective (RPO), Recovery Time Objective (RTO), and cost are three key metrics that dictate your DR solution when developing your DR strategy. Based upon these three metrics, you can decide whether you want to choose an in-Region hot DR (active-active) or cross-Region cold DR (backup and restore) strategy.

Before you consider a cross-Region DR strategy, you need to evaluate whether an in-Region DR solution meets your needs or not. Amazon RDS for SQL Server provides a Multi-AZ deployment, which replicates data synchronously across Availability Zones. This highly available Multi-AZ deployment is usually sufficient for a DR strategy within one Region in an AWS Cloud environment. In addition, with the in-Region read replica capability of Amazon RDS for SQL Server, you can use read replicas as a warm standby solution in a different Availability Zone. This provides another in-Region DR solution.

Previously, building a low-cost cross-Region DR solution involved using a self-managed script, AWS Backup, or AWS Lambda to create a database snapshot, copy the snapshot to a different Region, and restore that database to a new instance. Now, you no longer need to create additional processes to achieve this. The new DR capability utilizes the existing RDS backup functionality to set up automatic replication of system snapshots and transaction logs from a primary Region to a secondary Region, lowering the RPO down to a few minutes. The actual RTO of this DR strategy depends on how quickly it takes to restore a DB instance from the copied snapshot.

While an instance-level full backup runs once a day during your preferred backup window, transaction log backups are initiated every 5 minutes. Because additional time is required to copy transaction logs to remote Regions, the RPO for the cross-Region backup may run around 5 or more minutes behind the source Region.

The following table showcases the average RPO and RTO metrics you can attain with various HA and DR capabilities of Amazon RDS for SQL Server.

Amazon RDS for SQL Server supports cross-Region automated backup for all editions of SQL Server 2014 and higher. It’s currently available to replicate automated backups between us-east-1 and us-west-2, eu-central-1 and eu-west-1, and from ap-northeast-1 to ap-northeast-3.

When cross-Region automated backup is enabled on your DB instance, Amazon RDS automatically performs a full daily snapshot of your data, which occurs during your preferred backup window. In addition, it captures transaction logs to Amazon S3 every 5 minutes.

The following diagram illustrates our solution architecture.

Enable cross-Region automated backup replication

To use cross-Region automated backups, the primary Region instance that you want to protect must have automated backups enabled.

You can configure cross-Region automated backups on new and existing RDS for SQL Server instances using the AWS Management Console or AWS Command Line Interface (AWS CLI). To use the console, complete the following steps:

1. On the Amazon RDS console, choose Databases.
2. Under DB Identifier, select the instance you want to enable cross-Region DR for and choose Modify.
3. In the Additional Configuration section, for Backup replication, select Enable replication in another AWS Region.
4. For Destination Region, choose the target Region you want your backup to be replicated to.
5. For Replicated backup retention period, choose the retention period (up to 35 days) for the destination Region. For this post, we choose 7 days.

6. Scroll down to the bottom of the page and choose Continue.
7. Verify your changes and choose Modify DB instance.

Amazon RDS automatically creates a snapshot in the local Region and starts replicating the data. You can view the snapshot progress on the Current Region tab on the Automated backups page.

In the destination Region, choose the Replicated tab to view the automated backup. While the initial snapshot is being replicated to the destination Region, you can see the replicated backup with a blank restorable time value. The restorable time is displayed after the setup is complete.

Perform cross-Region point-in-time recovery

Cross-Region point-in-time recovery (PITR) restores the entire snapshot as a separate instance. When you initiate PITR, transactional logs are applied to the most appropriate daily backup to restore your DB instance to the specific requested time.

To restore your RDS instance using the console, complete the following steps:

1. In the destination Region, navigate to the Amazon RDS console.
2. In the navigation pane, choose Automated backups.
3. On the Replicated tab, select the instance you want to recover.
4. On the Actions menu, choose Restore to point in time.

5. Select Latest restorable time.

You can also select a custom point in time by selecting Custom. When you select Custom, the Restore to point in time window appears with your available restorable range, which is in-between Earliest restorable time and Latest restorable time.

6. You must specify instance specifications such as DB engine, DB instance identifier, DB instance class, Single-AZ or Multi-AZ, storage option, connectivity, and more options in the Additional configuration section of the instance to be restored.
7. Scroll down to the bottom of the page and choose Restore to point in time.

On the Databases page, you can see the backup getting restored to a new instance with the provided name.

When the restore is complete, the status changes from Creating to Available. You can connect to the restored instance using the instance’s endpoint.

The restoration time greatly depends on the number of transaction logs to be applied. The lesser the number of transactions and the smaller the transaction logs between snapshots are, the faster the recovery time it takes to restore the database.

Considerations for cross-Region automated backup

When using cross-Region automated backup, consider the following:

• When you restore from a cross-Region automated backup, a copy of the options group from the source Region named xrab-<source Region>-<source options group name>-… is created as a default selection for the options group of the restored instance. This option group doesn’t copy the options from the source Region. You can specify a different option to preserve the same options as in the source Region.
• If your instance uses a custom parameter group and option group, you should create a new parameter group and option group that contain any persistent or permanent options that are included in the source instance.
• The cost of this DR strategy is charged for the storage cost of the snapshots and data transfer cost to copy these snapshots from your primary Region to DR Region. See Amazon RDS for SQL Server Pricing for pricing details.

Summary

In this post, we discussed some key considerations and various options to provide disaster recovery within Amazon RDS for SQL Server. We also provided details on how to configure cross-Region automated backups and perform PITR in the target Region.

Cross-Region automated backups are ideal if you need a cost-effective cross-Region DR capability that helps save on compute and licensing costs. You don’t have to run any live RDS instances until you need to perform PITR in another Region following the unlikely event of a disaster taking down an entire primary Region. This significantly reduces management overhead, enabling database administrators to focus on other tasks, in addition to ensuring compliance and data integrity with a low RPO. For more information about enabling and working with cross-Region automated backups, see Replicating automated backups to another AWS Region.

About the Author

Mesgana Gormley is a Senior Database Specialist Solution Architect at Amazon Web Services. She works on the Amazon RDS team providing technical guidance to AWS customers and helping them migrate, design, deploy, and optimize relational database workloads on AWS.