Use cross-Region read replicas with Amazon Relational Database Service for SQL Server

August 2023: This post was reviewed and updated to include section for Transparent Data Encryption.

Starting today, you can now create cross-Region read replicas with Amazon Relational Database Service (Amazon RDS) for SQL Server. This feature gives you the ability to deploy a read-only DB instance in a secondary AWS Region. With cross-Region read replica, data is asynchronously sent from your primary database instance to a cross-Region read replica in near-real-time for read scale out. In addition, it enables you to create a disaster recovery solution, and you can also migrate an existing database to a new AWS Region. You can create up to a total of five in-Region and cross-Region replicas combined per source.

In case of disaster in your primary AWS Region, cross-Region read replicas gives you the ability to manually promote your read replicas as a new Single-AZ instance. Note that after the DB instance is promoted as a Single-AZ instance, it can’t become a read replica instance. However, you can convert this promoted Single-AZ instance to a Multi-AZ instance and create new read replicas from the standalone instance. Cross-Region read replicas give you a low Recovery Time Objective (RTO) option for disaster recovery.

In this post, we demonstrate how to use cross-Region read replicas with Amazon RDS for SQL Server.

Solution overview

The following diagram is a high-level view of Amazon RDS for SQL Server in a Multi-AZ configuration using Always On availability groups with 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 different Availability Zones within the same AWS Region. The cross-Region read replica is hosted in a different AWS Region. Data changes against the primary DB instance are replicated to the secondary DB instance synchronously and to the read replicas asynchronously.

If your application users are spread out across the globe, you can utilize cross-Region read replicas to serve read queries from an AWS Region that is close to your user. This helps reduce network latency for your users while interacting with your application, and your application can scale globally.

You can use cross-Region read replicas to reduce the load on your read-heavy database by routing the read queries from your applications to a read replica in another Region. You can use Amazon Route 53 weighted record sets 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 read replicas and give them the same weight. Then you can send direct requests to the individual read replica endpoint of your choosing. Another thing to be aware of while creating read replicas is that the existing logins, custom server roles, and SQL jobs in the primary DB instances are copied over when you create the read replica. You will need to disable or delete any jobs, logins, or server roles from the cross-Region read replica after the cross-Region read replica is created if required. However, if you create any server-level objects on the primary DB instance later, you must recreate them manually on each read replica because server-level objects aren’t replicated to in-Region or cross-Region replicas.

Note the following configuration details to deploy an Amazon RDS for SQL Server cross-Region read replica

The source instance must be Multi-AZ with Enterprise Edition. The following image shows the requirement for your primary Instance to deploy cross-Region read replica.
The source instance and cross-Region read replica must be running on SQL Server Enterprise Edition with 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 and all of its read replicas must be the same. Amazon RDS upgrades the primary immediately after upgrading the read replicas, regardless of the maintenance window. For more information about upgrading the DB engine version, see Upgrading the Microsoft SQL Server DB engine.
Amazon RDS for SQL Server configures cross-Region read replicas using Distributed Availability Groups.
The source and read replica instances types are independent and don’t need to be the same.
Logins are independent between Primary Instance and the cross-Region read replica.
This solution uses asynchronous replication.
If you require Windows Authentication, cross-Region read replicas must be part of an AWS Managed Active Directory or Self Managed Active Directory.

Be aware of the limitations with Amazon RDS for SQL Server cross-Region read replicas

You can’t fail over to an Amazon RDS for SQL Server cross-Region read replicas and fail back.
You must manually promote your Amazon RDS for SQL Server cross-Region read replicas to a standalone single-AZ instance.
You can create up to a total of five read replicas across all supported Regions for one source instance.
Amazon RDS for SQL Server cross-Region read replicas don’t replicate new logins or jobs after the initial replica creation.
You can always delete and recreate your cross-Region read replica if you need.
You must use the same configuration for Service Principal Name (SPNs) and Kerberos as Amazon RDS for SQL Server.

Create an Amazon RDS for SQL Server cross-Region read replica on the AWS Console

You can create an Amazon RDS for SQL Server cross-Region read replica using the AWS Management Console or the AWS Command Line Interface (AWS CLI). Each read replica has its own endpoints to connect.

To create an Amazon RDS for SQL Server cross-Region read replica via the console, complete the following steps:

On the Amazon RDS console, choose Databases.
Select your source instance.
On the Actions menu, choose Create read replica. The following image shows how to deploy a cross-Region read replica.
In the Instance specifications section, choose a DB instance class of your choice.
Select the storage type of your choice.
For Provisioned IOPS, enter an IOPS value.
Under Network & Security, select the desired Region (and optional Availability Zone). The following image shows where to choose the target Region for your read replica.
For Publicly accessible, select No.
Review the encryption, database option (Port 1433), and monitoring and configuration values.
Choose Create read replica. The following image shows options and configurations for your read replica.

Create an Amazon RDS for SQL Server cross-Region read replica with AWS CLI

To create cross-Region read replica using AWS CLI, run the following code below from a machine that has 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 example:

aws rds create-db-instance-read-replica 
         --db-instance-identifier mydbinstancecfreplica
         --source-db-instance-identifier arn:aws:rds:us-east-1:123456789:db:mydbinstancecf 
         --region us-west-2
         --source-region us-east-1 
         --db-subnet-group-name default

Transparent Data Encryption

Amazon RDS supports Transparent Data Encryption (TDE) for Microsoft SQL Server, automatically encrypting and decrypting stored data for enhanced security. TDE protects data at rest, which includes data and log files. For more information, refer Support for Transparent Data Encryption in SQL Server.

When a database is encrypted with TDE on the source instance, RDS automation automatically encrypts the database on the cross-region read replica without any manual intervention on your end. In the screenshot below, the ‘sample’ database is encrypted with TDE on cross region read replica.

Monitoring an Amazon RDS for SQL Server read replica

You can use Amazon CloudWatch to monitor the replication lag. You can monitor the replication status via the console or AWS CLI. You can also run a SQL query against the primary DB instance to determine the current replication lag. The replication status is either Replicating or Replication degraded, unless an error occurs with the replication, which can be determined by the Replication Error field on the Amazon RDS console or the event log. You can also see the status of a read replica using the AWS CLI describe-db-instances command or the Amazon RDS API DescribeDBInstances operation.

The following image shows the replication status of a read replica.

You can run the following SQL query on the primary instance to monitor the lag for all read replicas (cross-Region and in-Region):

SELECT AR.replica_server_name
	, DB_NAME (ARS.database_id) '<DATABASE NAME>'
	, AR.availability_mode_desc
	, ARS.synchronization_health_desc
	, ARS.last_hardened_lsn
	, ARS.last_redone_lsn
	, ARS.secondary_lag_seconds
FROM sys.dm_hadr_database_replica_states ARS
INNER JOIN sys.availability_replicas AR ON ARS.replica_id = AR.replica_id
WHERE DB_NAME(ARS.database_id) = '<DATABASE NAME>'
ORDER BY AR.replica_server_name;

The following image shows the latency between all the replicas.

Promoting cross-Region read-replicas

You can promote your cross-Region read replica as a standalone Single-AZ instance based on your business needs and you can change your application configuration to connect to the promoted instance.

The following image shows how to promote a cross-Region read replica.

Note that after you promote your cross-Region read replica, your cross-Region read replica is removed from the replication chain and is now a Amazon RDS for SQL Server single-AZ instance with read and write access.

The below diagrams show the promotion process of your cross-region read replica to a single-AZ Instance:

This diagram shows the current state before promotion
This diagram shows the new single-AZ instance after promotion

Clean up

If a real disaster occurs and you promote your read replica to become your primary instance, please make sure to terminate the previous primary instance if it’s no longer being used to avoid incurring unwanted charges. Please make sure you clean up all the AWS resources after following this blog post in your test environment.

Conclusion

With Cross-Region read replicas on Amazon RDS for SQL Server, you can scale out read-intensive workloads across five different Regions. In this post, we walked through the steps to create Amazon RDS for SQL Server Cross-Region read replicas.

If you have any thoughts or questions, leave them in the comments.

About the Authors

Gene Mays is a Senior Microsoft Specialist Solutions Architect at AWS. He has worked with SQL Server for over 15 years and has extensive experience in various industries. He has helped many customers architect high availability and disaster recovery solutions for SQL Server as well as orchestrate large-scale migrations in AWS. Prior to working with AWS, Gene has experience supporting enterprise customers in the financial and health industries.

Alex Pawvathil is a Technical Account Manager at AWS. Alex’s focus is Amazon RDS for SQL Server. He has worked with SQL Server for over 12 years. As part of the AWS Enterprise support team, he helps customers plan and build solutions using best practices and keep their environments operationally healthy.

Swarndeep Singh is a Senior Solutions Architect at AWS. He works with the Amazon RDS team, focusing on commercial database engines and SQL Server. He enjoys taking on technical challenges in Amazon RDS and is passionate about working with AWS Customers, building customized solutions and sharing knowledge with his teammates.

Sid Vantair is a Solutions Architect with AWS covering Strategic accounts. With over a decade of experience in working with relational databases, he thrives on resolving complex technical issues to overcome customer hurdles. Outside of work, he cherishes spending time with his family and fostering inquisitiveness in his children.

AWS Database Blog