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).
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 Directory Service for Microsoft Active Directory (Managed Active Directory) and Managed Active Directory must be replicated cross-Region using Enterprise edition.
Please 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 Managed Active Directory if you need Windows Authentication.
You must use the same configuration for Service Principal Name (SPNs) and Kerberos as Amazon RDS for SQL Server.
Amazon RDS for SQL Server cross-Region read replicas can’t be part of an on-premises AD nor use AD Connector.
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:
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):
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.
Read MoreAWS Database Blog
