Use Single-AZ read replicas in Amazon RDS for SQL Server

In-Region read replicas and cross-Region read replicas are two popular read-scale availability options available in Amazon Relational Database Service (Amazon RDS) for SQL Server. Amazon RDS customers use read replicas to offload their analytical or read-intended transactional workloads from a primary database instance. Previously, read replicas required the primary database instance to be in a Multi-AZ configuration. However, some customers are less concerned about the high availability for their primary database instance, but require near real-time data replication to serve their analytical applications with lower costs. Running analytical queries against the primary instance tends to consume extended periods of time, which can lead to blocking and timeouts for other transactional (OLTP) types of queries. By offloading the read-intended analytical queries off the primary instance, you can reduce locking and blocking, query timeouts, and so on, thereby improving the performance for your primary instance. Considering such need of customers, Amazon RDS for SQL Server announced availability of read replicas for Single-AZ instances on April 01, 2024.

Similar to the existing read replica for Multi-AZ feature, the new read replica for Single-AZ feature also enables you to create a readable copy of the Single-AZ instance—however, with the absence of the secondary instance at the primary Availability Zone. The read replica for Single-AZ can be created within the same AWS Region or across Regions. You can also use a cross-Region read replica for Single-AZ as a cross-Region disaster recovery instance.

In this post, we explain the architecture, creation, and monitoring of read replicas for Single-AZ.

Advantages of using read replicas for Single-AZ

Single-AZ read replicas offer the following advantages:

You can use the read replica to offload analytical or read-intended workloads from the Single-AZ primary instance.
You can promote the read replica to a Single-AZ instance for in-Region or cross-Region disaster recovery purposes.
The total cost of a read replica instance with Single-AZ is lower compared to the cost of a read replica instance with Multi-AZ.
You can create up to 15 read replicas for the same Single-AZ instance.

Limitations of using read replicas for Single-AZ

Keep in mind the following limitations when using Single-AZ read replicas:

Although it is possible to create multiple read replicas, only the primary Single-AZ instance can accept inserts, updates, and deletes.
Automatic failover to a read replica is not possible. However, you can promote a read replica to a standalone Single-AZ instance when required. Once a read replica is promoted, it cannot be paired back as a read replica for the original primary Single-AZ instance.
Since the Availability Group data synchronization mode is asynchronous, there is a possibility of synchronization latency in the read replica, especially in under-provisioned instances with resource contention. Due to SQL Server’s design, data changes will not be reflected in the read replica until the redo thread applies the corresponding log records to the read replica.
It is not possible to take a backup of the databases in the read replica.
Database-level objects, such as tables and database users, created in the primary Single-AZ instance will be automatically replicated to the read replica. However, server or instance-level objects, like logins and SQL Agent jobs, added to the primary Single-AZ instance after creating the read replica instance must be manually created in the read replica instance.

Solution overview

A Single-AZ instance with a read replica comprises one node in the primary Availability Zone and a second node in a secondary Availability Zone. Each of these nodes has its own Always On availability groups. A distributed availability group is created that spans across the availability groups among these instances. Transactions committed in the primary Single-AZ instance are replicated to the read replica in an asynchronous manner. The primary Single-AZ instance and the read replica instance have their own RDS endpoints that can be used in the connection string of the frontend applications.

The following diagram illustrates the architecture of a read replica for a Single-AZ instance within the same Region.

You can also create the read replica in another Region that is different from the Region of the Single-AZ instance. The following diagram illustrates the architecture of a Single-AZ instance with a cross-Region read replica.

It is possible to create up to 15 read replica instances for the same Single-AZ instance. If you have a business reason to create more read replicas, be aware that it is very important to monitor the resource utilization in primary instance as background threads which are responsible for reading the log records, compressing the log stream and transmitting to each replica can encounter resource contention (CPU, threads, network, etc.) and there by performance degradation. The following example shows a Single-AZ instance with two read replicas. RDS automation creates a distributed availability group for each read replica instance that you create.

With this architecture, OLTP-type transactional workloads can be directed to the Single-AZ endpoint, whereas analytical or read-intended workloads can be directed to the read replica endpoint.

In unforeseen situations where the Single-AZ instance of the read replica goes down and becomes irrecoverably damaged or inaccessible, you can promote the read replica to a Single-AZ instance to reduce downtime for your applications. You may then create a new read replica instance from your Single-AZ instance (promoted read replica) and reconfigure your analytical applications to point to the new read replica.

If your application has multiple connection strings that point to the same RDS Single-AZ or read replica instance, you may consider creating a DNS record pointing to the corresponding RDS endpoint. In the event of a failure, you can update the DNS record to point to the appropriate RDS endpoint, thereby avoiding the overhead of updating multiple connection strings on one or more application servers.

Although it’s possible to create multiple read replicas for a Single-AZ instance, it is crucial to appropriately size the instance (instance class, storage IOPS and throughput, and so on) to avoid resource contention, because synchronization latency in one or more read replicas can cause SQL Server to retain the log records in the log file of the primary database replica. The periodic transaction log backup can’t truncate the log records until they are transmitted and committed in the read replica. If the log can’t be truncated, the log file will automatically grow (if set) and can eventually fill up the disk space, resulting in an outage for the primary instance.

In the following sections, we show you how to create a read replica for a Single-AZ instance using either the AWS Management Console or the AWS Command Line Interface (AWS CLI).

Prerequisites

To set up this solution, you should have the following prerequisites:

Basic understanding about distributed availability groups in Microsoft SQL Server.
An active Single-AZ instance running a supported version of SQL Server Enterprise edition (for details about supported minor versions, refer to Cross-Region read replicas with RDS for SQL Server). The Standard edition is not currently supported for read replicas. For instructions, see Create a DB instance.
Automatic backup enabled in the primary Single-AZ instance with a backup retention period greater than 0 days.

Create a read replica for Single-AZ using the console

To create your read replica using the console, complete the following steps:

On the Amazon RDS console, chose Databases in the navigation pane.
Select your source Single-AZ instance.
On the Actions menu, choose Create read replica.
Note: The DB instance must be a Single-AZ instance running SQL Server version 2016 Enterprise Edition (or higher version. See details about supported minor versions under link).
For DB instance identifier, enter a name for the new read replica instance.
In the Instance Configuration section, choose an appropriate instance class for the read replica (for example, db.m5.xlarge).
For a given availability group, all replicas should run on a comparable instance class and volume type that can handle identical workloads to avoid any resource contention or synchronization latency. For more information, refer to Recommendations for computers that host availability replicas (Windows system).
For AWS Region, choose an appropriate Region based on the application business requirement.
For Storage type, choose an appropriate storage type (GP2/GP3/IO1/IO2). You may also choose to enable/disable storage auto scaling to avoid a storage full situation.
1. If you choose IO1/IO2, provide an appropriate value for Provisioned IOPS as well.
2. If you choose GP3, provide an appropriate value for Provisioned IOPS and Storage throughput.
It is recommended to use a storage configuration similar (or higher) to that of source instance to avoid I/O latency causing query runs or synchronization latency.
In the Connectivity section, choose appropriate values for DB subnet group public accessibility VPC security groups and Availability Zone. You may choose the default or an existing one based on the requirement. The default port number is 1433. If you want to use a different port number, you can change it under the Additional configuration.
Choose Microsoft Windows Authentication for the instance as appropriate (for more details, see Working with AWS Managed Active Directory with RDS for SQL Server). If the source Single-AZ instance is already part of a Windows Active Directory domain, you may choose the appropriate directory.
If your Single-AZ instance is encrypted, choose to Enable encryption for your read replica instance. You can’t create an encrypted read replica instance from an unencrypted Single-AZ instance.
Under Performance Insights, you can enable Amazon RDS Performance Insights at the time of creating new read replica or enable it at a later time.
Performance Insights for 7 days of retention are free. For more information, see Monitoring DB load with Performance Insights on Amazon RDS.
You can select Enable enhanced monitoring now or at a later time.
Enhanced Monitoring captures OS-related metrics and can be used to analyze server resource consumption, capacity planning, and more. For more details, see Overview of Enhanced Monitoring. Enhanced Monitoring for up to 7 days of retention is free.
There may be a business reason to retain the log records for extended period. Under Log exports, select to choose Agent Log and Error log so that the logs will be exported to Amazon CloudWatch.
For IAM role, enter the AWS Identity and Access Management (IAM) role you use to publish logs to CloudWatch.
Review the configuration values and choose Create read replica.

Create a read replica for Single-AZ using the AWS CLI

You can also create a read replica for Single-AZ instance using the AWS CLI create-db-instance-read-replica command. The following is an example of the syntax:

aws rds create-db-instance-read-replica 
—db-instance-identifier <RR_INSTANCE_NAME> \ 
—source-db-instance-identifier <SOURCE_SINGLEAZ_INSTANCE_NAME> \ 
—region us-west-2

For example, for an in-Region Single-AZ read replica, use the following code:

aws rds create-db-instance-read-replica 
--db-instance-identifier singleaz-inst1-rr1 \ 
--source-db-instance-identifier singleaz-inst1 \ 
--region us-west-2

For a cross-Region Single-AZ read replica, use the following code:

aws rds create-db-instance-read-replica \
--db-instance-identifier singleaz-inst1-rr2 \
--source-db-instance-identifier arn:aws:rds:us-west-2:<CUSTOMER_ID>:db:singleaz-inst1 \
--endpoint-url https://rds-siteb.us-east-1.amazonaws.com \
--region us-east-1 
--source-region us-west-2

Monitor a read replica instance for Single-AZ instance

Similar to the other types of read replicas, it is important to monitor the resource consumption (CPU, memory, I/O) and right-size the resources of the instance. You may use the historical baseline consumption and future workload prediction for this capacity planning effort. You can use tools like Enhanced Monitoring and Performance Insights for this purpose, or use a third-party tool or build custom monitoring scripts using SQL Server’s built-in dynamic management views and dynamic management functions. In addition to monitoring the resource utilization, we also suggest periodically monitoring the synchronization latency. You can monitor the replication lag using CloudWatch by viewing the Amazon RDS ReplicaLag metric or periodically collecting the lag information using the T-SQL query:

SELECT AR.replica_server_name
     , DB_NAME (ARS.database_id) 'database_name'
     , AR.availability_mode_desc
     , ARS.synchronization_health_desc
     , ARS.last_commit_time
     , ARS.last_hardened_lsn
     , ARS.last_redone_lsn
     , ARS.secondary_lag_seconds
     , ARS.redo_queue_size
FROM sys.dm_hadr_database_replica_states ARS
INNER JOIN sys.availability_replicas AR ON ARS.replica_id = AR.replica_id
WHERE is_local <> 1
     -- AND DB_NAME(ARS.database_id) = '<REPLACE WITH SPECIFIC DB NAME>'
ORDER BY AR.replica_server_name;

Common operational considerations about read replicas

Keep in mind the following when using read replicas:

You can use different instance classes and volume types for the Single-AZ instance and its read replica instances. Although it’s possible to under-provision the read replica instance, it is crucial to periodically monitor resource consumption and appropriately size the instance class and storage type to avoid any query performance degradation or data synchronization issues due to CPU or memory pressure or I/O latency.
When creating a read replica for the first time, the RDS automation takes a snapshot backup of the Amazon Elastic Block Store (Amazon EBS) volume attached to the Single-AZ instance. A new volume is created from this snapshot and attached to the read replica instance. A newly created read replica is expected to have slightly higher I/O latency and, consequently, higher Always On replication lag and query runtime latency for the initial few hours, as the underlying storage goes through a process called S3 hydration, which is a background process to warm up the storage blocks from a snapshot backup in Amazon Simple Storage Service (Amazon S3) to the newly created EBS volume. If the volume is accessed where the data is not loaded, the transactions accessing the volume encounter a higher latency than normal while the data gets loaded. The larger the volume size, the longer it will take to complete the hydration.
You can create or restore databases in the Single-AZ instance with read replicas. Amazon RDS automation automatically restores and pairs it in the read replicas. However, any server-level objects that you create in the Single-AZ instance, such as logins or SQL Agent jobs, will not be automatically replicated to existing read replicas.
Read replicas are commonly used for offloading analytical types of workloads. Analytical workloads tend to consume high tempdb database resources. It is crucial to monitor and appropriately size the tempdb database in the read replica to avoid query performance degradation due to tempdbcontention.
When you initiate an upgrade (major or minor version) for your Single-AZ instance, Amazon RDS automation upgrades all the read replicas along with upgrading the Single-AZ instance. Because the instances and databases aren’t accessible during the upgrade, you must perform the upgrade during a scheduled maintenance window. Additionally, you can’t have different versions of SQL Server for the Single-AZ and read replica instances.
Volume breaching the IOPS and throughput can be a bottleneck for data synchronization from the Single-AZ instance to the read replica instance. It is essential to periodically monitor and appropriately size the volume associated with the Single-AZ and read replica instances.
Read replica instances can be deleted at any time. When you delete a Single-AZ instance that has one or more read replica instances, the read replica instances are automatically promoted to Single-AZ instances. You may delete the promoted Single-AZ instance as appropriate.

Conclusion

In this post, we discussed how read replicas for an RDS for SQL Server Single-AZ instance can help with scaling the Single-AZ instance and offloading read-intensive workloads from the source Single-AZ instance. Additionally, you can use it for business continuity in the event that the source Single-AZ instance becoming unavailable. It is crucial to appropriately size the read replica instance (CPU, memory, I/O) based on historical resource consumption and future transactional resource forecasting.

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

About the author

Junu Thankappan is a Senior Database Engineer with Amazon Web Services. He works with the AWS RDS team, focusing on commercial database engines and SQL Server.