AWS Database Blog

Using in-Region read replicas in Amazon RDS for SQL Server

Amazon RDS for SQL Server now supports in-Region read replicas, which give you the ability to offload your read workloads from your primary database instance to a replica. Read replicas use the built-in distributed availability groups feature and are available for Enterprise Edition. A distributed availability group is an availability group that spans two separate availability groups. The members of the distributed availability group are the availability groups themselves. Amazon RDS uses a domain-independent Windows Server Failover Cluster (WSFC) for this architecture. This post goes over the architecture of read replicas, how to create a read replica, and how to monitor it.

Read replicas are supported on Enterprise Edition in the Multi-AZ configuration on SQL Server 2016 Service Pack 2 Cumulative Update 3 (13.00.5216.0.v1) or higher.

Multi-AZ configuration with read replicas

In a Multi-AZ configuration with a read replica, transactions committed on the primary DB instance are synchronously replicated to a secondary replica for high availability purposes and asynchronously sent to the read replica for read scale out. Read replicas contain near-real-time data from the primary DB instance, and you can use them to offload the read-only workload from the primary DB instance, such as analytical or reporting type of queries that can tolerate some latency in data updates. Additionally, you can use read replicas as a warm standby solution in a different Availability Zone than your primary or secondary DB instance, where you can promote the replica to a Single-AZ instance based on your business needs. You can secure data at rest by using Transparent Data Encryption (TDE) or AWS KMS. For more information, see How Amazon Relational Database Service (Amazon RDS) uses AWS KMS. You can secure data in transit by Secure Sockets Layer (SSL) or Native Network Encryption (NNE).

The following diagram is a high-level view of RDS SQL Server in the Multi-AZ configuration using Always On Availability Groups. The primary and secondary DB instances are hosted in different Availability Zones within the same AWS Region, in case of an outage the primary DB instance will automatically failover to the secondary DB instance. Your databases are part of the Always On Availability Group called ‘RDSAG0’. Data changes against the primary DB instance are replicated to the secondary DB instance in synchronous fashion. You can connect to the primary DB instance using the listener endpoint.

When you create a read replica, Amazon RDS takes a snapshot of the primary DB instance and creates a new read-only instance from the snapshot. Creating or deleting the read replica doesn’t require any downtime on the primary DB instance. You can create up to five read replicas.

The following diagram shows the Multi-AZ configuration adding one read replica. This distributed availability group setup has three DB instances hosted in different Availability Zones within the same AWS Region. The primary and secondary DB instances are part of availability group called ‘RDSAG0’. The read replica has its own availability group called ‘READ REPLICA AG’. These availability groups are members of a single distributed availability group called ‘DISTRIBUTED AG’. Data changes are committed asynchronously to the read replica. The primary DB instance and the read replica have their own unique endpoints.

Note: Please be aware that the listener endpoint pointing to the primary DB instance cannot be used as a read-intended connection string to route the traffic to the read replica.

The following diagram shows the Multi-AZ configuration with two read replicas. There are two distributed availability groups created, one for each read replica. The availability group ‘RDSAG0’ is member of each of these distributed availability groups along with the availability group from the corresponding read replica. The primary DB instance and each of the read replicas has its own unique endpoint.

The primary DB instance is the only copy of the database that can accept both read/write traffic; the read replica can only accept read-only traffic. When you use Always On availability groups in Amazon RDS, an application cannot use the listener endpoint pointing to the primary DB instance as a read-intended connection string to route the traffic to the read replicas.

Prerequisites

Before you get started with this walkthrough, make sure you meet the following prerequisites:

  • Read replicas are in the same Region as the primary DB instance.
  • Your DB instance is on 2016 Service Pack 2 Cumulative Update 3 or higher.
  • Your DB instance uses Enterprise Edition.
  • Your DB instance is in the Multi-AZ configuration using Always On availability groups.
  • For the primary DB instance, you must enable automatic backups (for example, the backup retention period must be greater than 0 days).
  • 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 source DB instance immediately after upgrading the read replicas, regardless of a read replica’s maintenance window. For more information about upgrading the DB engine version, see Upgrading the Microsoft SQL Server DB Engine.

Additionally, you should be aware of the following:

  • Your read replica can be a different DB instance family or size than the primary DB instance (for example, your primary DB instance is an r5.2xlarge your read replica could be a t3.xlarge).
  • You cannot create a new database on a read replica. Any new database you create on the primary DB instance is automatically created on the read replicas.
  • Logins, custom server roles, and SQL jobs that exist on the primary DB instance when you create the read replica are copied to the read replica. If you create any such server-level objects on the primary DB instance later, you must recreate them manually on each read replica.
  • You can create additional logins and SQL jobs on the read replicas. Those changes are lost when the read replica is deleted and recreated from the primary DB instance.
  • Read replicas start with the same storage size as the primary DB instance. However, when you scale storage on the primary DB instance, you also must do so on the replica.

Creating an RDS SQL Server read replica

You can create an RDS SQL Server read replica via the AWS Management Console or AWS CLI.

Creating a SQL Server read replica doesn’t cause an outage for the primary DB instance. You can create more than one read replica (up to five) from the same primary DB instance. Each read replica has its own endpoints to connect.

AWS Management Console

To create an RDS SQL Server via the console, complete the following steps:

  1. On the Amazon RDS console, choose Databases.
  2. Search for your source instance.
    The DB instance must be in a Multi-AZ configuration using Always On availability groups.
  3. Select your source instance.
  4. For Actions, choose Create read replica.
  5. In the Instance specifications section, for DB instance class, choose db.m5.xlarge.
  6. For Storage type, choose Provisioned IOPS (SSD).
  7. For Provisioned IOPS, enter 5000.
    If you choose an IOPS value greater than that of source instance, it may impact the performance until the operation is complete.
  8. Under Network & Security, for Availability zone, choose No preference.
  9. For Publicly accessible, select No.
  10. Under Encryption, choose Disable encryption.
    You cannot create an encrypted read replica from an unencrypted primary DB instance.
  11. Under Settings, for Read replica source, choose rds-sql-prod-inst-01.
  12. For DB instance identifier, enter rds-sql-prod-inst-01-replica-01.
  13. Under Database options, for Database port, keep the default value (1433).
  14. Under Microsoft SQL Server Windows Authentication, for Directory, choose None.
  15. Under Monitoring, choose Disable enhanced monitoring.
  16. Under Log exports, choose Error log.
  17. For IAM role, enter the IAM role you use to publish logs to Amazon CloudWatch Logs, if it is not already chosen.
  18. Under Performance Insights, choose Enable Performance Insights.
    You do not have to enable Performance Insights, but it is free for up to 7 days of retention, so it is recommended for this walkthrough.
  19. Review the configuration values and choose Create read replica.

AWS CLI

You can also create a read replica via AWS CLI. See the following code:

aws rds create-db-instance-read-replica 
      --db-instance-identifier <SOURCE INSTANCE NAME>
      --source-db-instance-identifier <READ REPLICA NAME>
      --region us-west-2 
      --publicly-accessible

Connecting to the newly created read replica

You can connect to the primary DB instance and the read replica instances using the endpoint and port number. Amazon RDS provides two sets of endpoints for read replicas: one points to the primary DB instance and the other points to the read replica. The primary DB instance is the only copy of the database that can accept read-write traffic. The read replica can only accept read-only traffic.

The following screenshot shows the endpoint information and how you can use it in SQL Server Management Studio (SSMS) to connect to the DB instance.

Promoting an RDS SQL Server read replica to a stand-alone instance

You can promote your read replica to a stand-alone DB instance. Promoting a read replica to a Single-AZ instance does not have any effect on any other read replicas (if any exist), nor does it affect the primary DB instance or its synchronous copy in the Multi-AZ configuration.

You can promote a read replica to a Single-AZ DB instance using the console or AWS CLI.

AWS Management Console

To promote a read replica using the console, complete the following steps:

  1. On the Amazon RDS console, choose Databases.
  2. Search for your read replica instance.
  3. Select the read replica instance.
  4. For Actions, choose Promote.
  5. Under Preferences, for Enable automatic backups, choose Yes.
  6. For Backup retention period, choose 7 days.
  7. For Backup window, choose No preference.
  8. Choose Continue.

AWS CLI

You can also promote a read replica to a Single-AZ instance via AWS CLI. See the following code example:

aws rds promote-read-replica 
   --db-instance-identifier <READ REPLICA NAME>

Monitoring the read replica

The replication status of a read replica can be one of the following:

  • Replicating – The read replica is replicating as expected.
  • Replication degraded – Replicas are receiving data from the primary DB instance, but one or more databases might be not be getting updates. This can occur, for example, when a replica is in the process of setting up newly created databases. The status doesn’t transition from Replication degraded to Error unless an error occurs during the degraded state.
  • Error – An error has occurred with the replication. To determine the exact error, check the Replication Error field in the Amazon RDS console or the event log.

To view the replication status, complete the following steps:

  1. On the Amazon RDS console, choose Databases.
  2. Search for your read replica instance.
  3. Select your read replica instance.
  4. View the status under the Replication status column.

Information about the replication lag between the primary DB instance and the read replica is available in Amazon CloudWatch. You can access this information via the console or AWS CLI. You can also run an SQL query against the primary DB instance to determine the current replication lag.

The replication lag is valid only when the replication state is either Replicating or Replication degraded.

AWS Management Console

To monitor the read replica lag metric via the console, complete the following steps:

  1. On the Amazon RDS console, choose Databases.
  2. Search for your read replica instance.
  3. Select your read replica instance.
  4. Choose the Monitoring tab.
  5. Locate the graph with the title Replica Lag.

AWS CLI

You can also query the replica lag from CloudWatch using the AWS CLI, for example:

aws cloudwatch get-metric-statistics 
   --namespace "AWS/RDS" 
   --metric-name ReplicaLag 
   --start-time 2020-03-30T00:00:00Z 
   --end-time 2020-04-01T00:00:00Z 
   --period 3600 
   --dimensions Name=DBInstanceIdentifier,Value=<READ REPLICA NAME>
   --statistics Maximum

For more information about available commands, see CloudWatch.

SQL query

You can also monitor the read replica lag with an SQL query against the primary DB instance, for example:

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;

Summary

With read replicas on Amazon RDS for SQL Server, you can create up to five replicas to scale out the read-intended workloads. This post explained how to create read replicas and monitor them. To learn more about read replicas, see Working with Read Replicas. Try out Amazon RDS for SQL Server read replicas today. If you have any thoughts or questions, leave them in the comments.

 

About the Author

 

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