Utilizing Cross-Region Read Replicas with Amazon Relational Database Service for SQL Server

More Info

As of August 2023, this post has been updated to include a section on Transparent Data Encryption.

Starting today, you can create cross-Region read replicas using Amazon Relational Database Service (Amazon RDS) for SQL Server. This new feature allows you to establish a read-only database instance in a secondary AWS Region. With cross-Region read replicas, data is asynchronously transmitted from your primary database instance to the cross-Region read replica in near-real-time, facilitating read scaling. Additionally, this functionality enables the creation of a disaster recovery solution and allows for the migration of an existing database to a new AWS Region. You can create a maximum of five combined in-Region and cross-Region replicas for each source instance.

In the event of a disaster in your primary AWS Region, cross-Region read replicas provide the capability to manually promote your read replicas to standalone Single-AZ instances. Be aware that once a DB instance is elevated to a Single-AZ instance, it cannot revert to being a read replica. However, you can convert the promoted Single-AZ instance to a Multi-AZ instance and generate new read replicas from it. Cross-Region read replicas offer a low Recovery Time Objective (RTO) for disaster recovery.

In this post, we will illustrate how to implement cross-Region read replicas with Amazon RDS for SQL Server.

Overview of the Solution

The diagram below presents a high-level view of Amazon RDS for SQL Server configured in a Multi-AZ setup using Always On availability groups, featuring one in-Region read replica and one cross-Region read replica. The primary and secondary instances, along with the in-Region read replica DB instance, are hosted in distinct Availability Zones within the same AWS Region, while the cross-Region read replica resides in a different AWS Region. Data modifications made to the primary DB instance are replicated synchronously to the secondary DB instance and asynchronously to the read replicas.

If your application users are geographically dispersed, you can leverage cross-Region read replicas to handle read queries from an AWS Region closer to your users. This approach minimizes network latency during interactions with your application, allowing for global scalability.

Cross-Region read replicas can also alleviate the load on your read-heavy database by directing read queries from your applications to a read replica located in another Region. Amazon Route 53 weighted record sets can be utilized to distribute read requests across your read replicas. Within a Route 53 hosted Availability Zone, you can create individual record sets for each DNS read replica endpoint associated with your replicas, assigning them the same weight. Subsequently, you can send direct requests to the specific read replica endpoint of your choice.

It’s important to note that when creating read replicas, existing logins, custom server roles, and SQL jobs from the primary DB instance are carried over. You may need to disable or remove any jobs, logins, or server roles from the cross-Region read replica if necessary. Additionally, any server-level objects created on the primary DB instance after the fact must be manually recreated on each read replica, as server-level objects are not replicated to either in-Region or cross-Region replicas.

Key Configuration Details for Deploying Cross-Region Read Replicas

• The source instance must be configured as Multi-AZ and utilize the Enterprise Edition.
• Both the source instance and the cross-Region read replica must run on SQL Server Enterprise Edition with the following versions:
  • RDS for SQL Server 2019 (Version 15.00.4073.23 and higher).
  • RDS for SQL Server 2017 (Version 14.00.3049.1 and higher).
  • RDS for SQL Server 2016 (Version 13.00.5216.0 and higher).
• The SQL Server DB engine version of the source DB instance must match that of all its read replicas. Amazon RDS performs an immediate upgrade of the primary instance following the upgrade of the read replicas, regardless of the maintenance window. For further information on upgrading the DB engine version, refer to their guidelines.
• Cross-Region read replicas are configured using Distributed Availability Groups.
• The instance types of the source and read replicas can be different.
• Logins are managed independently between the primary instance and the cross-Region read replica.
• This solution employs asynchronous replication.
• If Windows Authentication is required, cross-Region read replicas must be integrated with either an AWS Managed Active Directory or a Self Managed Active Directory.

Limitations of Cross-Region Read Replicas

• You cannot failover to an Amazon RDS for SQL Server cross-Region read replica and then revert back.
• You must manually promote your Amazon RDS for SQL Server cross-Region read replicas to a standalone single-AZ instance.
• A total of five read replicas can be created across all supported Regions for a single source instance.
• New logins or jobs created after the initial replica setup will not replicate.
• You can always delete and recreate your cross-Region read replica as needed.
• Maintain consistent configurations for Service Principal Name (SPNs) and Kerberos as per Amazon RDS for SQL Server requirements.

Creating an Amazon RDS for SQL Server Cross-Region Read Replica via AWS Console

You can establish an Amazon RDS for SQL Server cross-Region read replica using the AWS Management Console or the AWS Command Line Interface (CLI). Each read replica will have its own endpoints for connection.

To create a cross-Region read replica through the console, follow these steps:

1. On the Amazon RDS console, select Databases.
2. Choose your source instance.
3. From the Actions menu, click on Create read replica.
4. In the Instance specifications section, select your preferred DB instance class.
5. Choose the desired storage type.
6. For Provisioned IOPS, enter the IOPS value.
7. Under Network & Security, select the target Region (and optional Availability Zone).
8. Set Publicly accessible to No.
9. Review the encryption, database option (Port 1433), and monitoring configurations.
10. Click on Create read replica.

Creating a Cross-Region Read Replica with AWS CLI

To create a cross-Region read replica using the AWS CLI, execute the following command from a machine with the AWS CLI installed:

aws rds create-db-instance-read-replica 
  --db-instance-identifier <READ REPLICA NAME> 
  --source-db-instance-identifier <ARN of the Source DB instance> 
  --region <target-region-name> 
  --source-region <source-region-name> 
  --db-subnet-group-name <db-subnet-group-name>

For instance:

aws rds create-db-instance-read-replica 
  --db-instance-identifier mydbinstancecfreplica 
  --source-db-instance-identifier mysourcearn 
  --region us-west-2 
  --source-region us-east-1 
  --db-subnet-group-name mysubnetgroup

For more insightful information on this topic, refer to this authoritative resource or check out this excellent resource on employee training at Fast Company.