AWS Database Blog

Load balancing strategies for Amazon RDS for SQL Server read replicas to scale read workloads and reduce latency

Amazon Relational Database Service (Amazon RDS) for SQL Server makes it straightforward to set up, operate, and scale SQL Server deployments in the AWS Cloud. The service allows DBAs to focus on high-value tasks such as query optimization, query construction, and schema design rather than time-consuming database administration tasks including provisioning, backups, software patching, monitoring, and hardware scaling.

Amazon RDS read replicas enhance performance and durability for RDS database instances. Data from the primary DB instance is asynchronously replicated to read replicas in near-real time, enabling you to scale out beyond the capacity constraints of a single DB instance for read-heavy workloads. You can create one or more replicas of a given source DB instance and serve high-volume application read traffic from multiple copies of your data. Additionally, you can create up to 15 in-Region and cross-Region (limitations) read replicas to scale Amazon RDS for SQL Server.

In this post, we demonstrate how to load balance and elastically scale out your read workloads using read replicas with Amazon RDS for SQL Server and Amazon Route 53.

Solution overview

The following diagram provides a high-level overview 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 instance and in-Region read replica are hosted in different Availability Zones within the Region , while the cross Region Cross-Region read replica is hosted in a different Region. Data changes to the primary DB instance are replicated synchronously to the standby DB instance and asynchronously to the read replicas.

To manage traffic distribution between the in-Region and cross-Region read replicas, we use a Route 53 private hosted zone with a routing policy. Additionally, we use Route 53 private hosted zone CNAME records to store RDS for SQL Server endpoints. The application connects to the database using these CNAME record endpoints.

If your application users are geographically dispersed, you can utilize cross-Region read replicas to reduce network latency. By routing users to the application endpoint closest to them, their queries are served from a Region that is nearer to their location. This helps minimize latency for users interacting with your application, while also allowing your application to scale globally.

You can use Route 53 weighted policy to distribute read requests across your read replicas. Within a Route 53 hosted Availability Zone, you can create individual record sets (such as CNAME) for each DNS read replica endpoint and give them the same weight. This enables you to send direct requests to the individual read replica endpoint of your choice.

When creating read replicas the existing logins, custom server roles, and SQL jobs in the primary DB instances are copied over. This is a one-time operation during the creation of read replica; any subsequent changes or new server-level objects must be manually replicated as they are not replicated to the in-Region or cross-Region replicas. If any of the copied server-level objects are not required on the read replicas, you should disable or delete them.

Let’s review three different use cases, each using a different routing policy (weighted routing, latency routing, and geolocation routing) to achieve load balancing of a read-only workload.

We use the following configuration details to deploy this load balancing solution using Amazon RDS for SQL Server and Route 53 routing:

  • The primary instance must be Multi-AZ with Enterprise Edition in order to create read replicas, and read replicas are Enterprise Edition as well.

  • We need at least two read replicas, which can be in-Region, cross-Region, or a combination. For our example, we have an in-Region read replica in us-east-1 and a cross-Region read replica in ap-south-1.

We use the following RDS for SQL Server endpoints in this example:

  • Primary – crrr-primary01.ckwu95yj2jxd.us-east-1.rds.amazonaws.com
  • In-Region replica – crrr-irreplica01.ckwu95yj2jxd.us-east-1.rds.amazonaws.com
  • Cross-Region replica – crrr-xrreplica02.cxfvmitq9dpk.ap-south-1.rds.amazonaws.com

Create a Route 53 hosted zone for your application

To create a Route 53 hosted zone via the AWS Management Console, complete the following steps:

  1. On the Route 53 console, choose Hosted zones in the navigation pane.
  2. Choose Create hosted zone.
  3. Enter a domain name (for example, com).
  4. Specify the type as Private hosted zone.
  5. Add the Regions and corresponding VPCs containing the replicas.
  6. Choose Create hosted zone.

Use case 1: Load balancing using Route 53 private hosted CNAME records with weighted routing

For our first use case, we want to scale out beyond the capacity constraints of a single DB instance for read-heavy database workloads. You can use multiple read replicas of a given source DB instance and serve high-volume application read traffic from multiple copies of your data, thereby increasing aggregate read throughput.

Create a hosted CNAME record with weighted routing by completing the following steps:

  1. On the Route 53 console, navigate to the hosted zone you created.
  2. Choose Create record.
  3. For Record name, enter a name (for example, weightedread).
  4. Specify CNAME as the record type.
  5. Enter one of your RDS read replica endpoint.
  6. For Routing policy, select Weighted.
  7. For TTL, enter 0.
  8. For equal distribution of sessions, you can divide 100 by the number of read replicas (for example, enter one of the RDS read replica endpoints).
  9. For Record ID, enter a unique identifier (for example, crrr-weightedread01).
  10. Choose Create record.
  11. Repeat these steps for each read replica (keep the same record name and settings and provide the replica endpoint name in the value field).

You should see the weighted records as shown in the following screenshot.

When configuring the connection string in your application, you can use the CNAME you created earlier as your server name. In the following code, we run a SQL query to select servername and the current date/time and wait for 1 minute to simulate an application workload using sqlcmd:

sqlcmd -S weightedread.myapplication.com -d master -M -U admin -P <SQLLogin_Password> -Q "set nocount on Select @@Servername as [ServerName],Getdate() as [CollectionDate]; waitfor delay '00:01:00';" -W

Now when you run multiple sessions of your workload, the sessions are distributed between the read replicas. For example, you can run a total of 15 sqlcmd commands using the weightedread DNS, following code snippet via a batch file:

@echo off
set loopcount=15
:loop
sqlcmd -S weightedread.myapplication.com -d master -M -U admin -P “<SQLLogin_Password>” -Q “SET NOCOUNT ON SELECT @@Servername AS [ServerName], GETDATE() AS [CollectionDate]; WAITFOR delay ‘00:00:05’;” -W
set /a loopcount=loopcount-1
if %loopcount%==0 goto exitloop
goto loop
:exitloop
pause

You can perform connection count tracking by running the following query on each replica or running a multi-server query against registered servers for all read replicas:

--Connection Count Tracking 
select count(1) as [ConnectionCount] from sys.dm_exec_sessions where login_name='admin' and program_name='SQLCMD';

We can see that the read workload is distributed across multiple read replicas.

Use case 2: Load balancing using Route 53 private hosted CNAME records with latency routing

If your application users are spread across the globe, you can utilize cross-Region read replicas to serve read queries for any of the Regions hosting a read replica based on the network latency of that end-user to the Region.

To create a hosted CNAME record with the latency routing policy, complete the following steps:

  1. On the Route 53 console, navigate to the hosted zone you created.
  2. Choose Create record.
  3. For Record name, enter a name (for example, latencyread).
  4. Specify CNAME as the record type.
  5. Enter your RDS read replica endpoint.
  6. For Routing policy, select Latency.
  7. For TTL, enter 0.
  8. For Region, choose the Region corresponding to the read replica.
  9. For Record ID, enter a unique identifier (for example, crrr-latencyread01).
  10. Choose Create record.
  11. Repeat these steps for each read replica (keep the same record name and settings and provide the replica endpoint name in the value field).

When configuring the connection string in your application, you can now use the CNAME you created earlier as your server name. In the following example, we run a SQL query to select servername and the current date/time and wait for 1 minute to simulate an application workload using sqlcmd:

sqlcmd -S latencyread.myapplication.com -d master -M -U admin -P <SQLLogin_Password> -Q "set nocount on Select @@Servername as [ServerName],Getdate() as [CollectionDate]; waitfor delay '00:01:00';" -W

Now let’s run multiple sessions of the read workload from the application server in different Regions, each closer to a different read replica. In the following code, you can run a total of 15 sqlcmd commands (we can adjust the variable for more executions) using the weightedread DNS :

@echo off
set loopcount=15
:loop
sqlcmd -S latencyread.myapplication.com -d master -M -U admin -P “<SQLLogin_Password>” -Q “SET NOCOUNT ON SELECT @@Servername AS [ServerName], GETDATE() AS [CollectionDate]; WAITFOR delay ‘00:00:05’;” -W
set /a loopcount=loopcount-1
if %loopcount%==0 goto exitloop
goto loop
:exitloop
pause

For connection count tracking, run the following query on each replica or run a multi-server query against registered servers for all read replicas:

--Connection Count Tracking 
select count(1) as [ConnectionCount] from sys.dm_exec_sessions where login_name='admin' and program_name='SQLCMD';

We observe that the sessions get served by the read replica close to the application server running the read workload.

1) Executing read workload from application server in US-East-1

2) Executing workload from application server in AP-South-1

Use case 3: Load balancing using Route 53 private hosted CNAME records with geolocation routing

If your application users are spread out across the globe, you may want to load balance across endpoints in a predictable way that’s straightforward to manage, so that each user request is consistently routed to the same endpoint and ensures that data is processed and stored within the desired geographic boundaries, helping to comply with data sovereignty requirements. You can use the geolocation routing policy to serve read requests from the RDS read replica that’s within the same Region.

Create a hosted CNAME record with the geolocation routing policy using the following steps:

  1. On the Route 53 console, navigate to the hosted zone you created.
  2. Choose Create record.
  3. For Record name, enter a name (for example, georead).
  4. Specify CNAME as the record type.
  5. Enter your RDS read replica endpoint.
  6. For Routing policy, select Geolocation.
  7. For TTL, enter 0.
  8. For Location, choose an appropriate location corresponding to the read replica (for example, North America for us-east-1).
  9. For Record ID, enter a unique identifier (for example, crrr-georead01).
  10. Choose Create record.
  11. Repeat these steps for each read replica (keep the same record name and settings and provide the replica endpoint name in the value field).

When configuring the connection string in your application, you can now use the CNAME you created earlier as your server name. In the following code, we run a SQL query to select servername and the current date/time and wait for 1 minute to simulate an application workload using sqlcmd:

sqlcmd -S georead.myapplication.com -d master -M -U admin -P <SQLLogin_Password> -Q "set nocount on Select @@Servername as [ServerName],Getdate() as [CollectionDate]; waitfor delay '00:01:00';" -W

Now let’s run multiple sessions of the read workload from the application server in different Regions, each in the same geolocation as the different read replica. In the following code, you can run a total of 15 sqlcmd commands (we can adjust the variable for more executions) using the weightedread DNS:

@echo off
set loopcount=15
:loop
sqlcmd -S georead.myapplication.com -d master -M -U admin -P “<SQLLogin_Password>” -Q “SET NOCOUNT ON SELECT @@Servername AS [ServerName], GETDATE() AS [CollectionDate]; WAITFOR delay ‘00:00:05’;” -W
set /a loopcount=loopcount-1
if %loopcount%==0 goto exitloop
goto loop
:exitloop
pause

For connection count tracking, you can run the following query on each replica or run a multi-server query against registered servers for all read replicas:

--Connection Count Tracking 
select count(1) as [ConnectionCount] from sys.dm_exec_sessions where login_name='admin' and program_name='SQLCMD';

We observe that the sessions get served by the read replica within the same geolocation as the application server running the read workload.

1) Executing workload from application server in United States

2) Executing workload from application server in India

The following screenshot shows the records for all three routing policies explained in the example (weighted routing, latency routing, and geolocation routing) to achieve load balancing of a read-only workload.

Cleanup

To avoid incurring future charges, delete the resources you created as part of this post:

  1. To delete the Amazon RDS for SQL Server, refer to Terminate your RDS instance.
  2. To delete the EC2 instance, refer to Terminate your EC2 instance
  3. To remove the Route 53 , refer to Terminate private hosted zone

Summary

In this post, we described strategies for load balancing application read workloads using Route 53 and RDS for SQL Server read replicas. We discussed use cases for each strategy, demonstrating how to leverage different Route 53 private hosted zone routing policies to route traffic to the appropriate database endpoints. This solution provides a consistent, common endpoint for applications to use without requiring changes to the application configuration based on the end-user’s location.

Try out this solution in your AWS account. If you have any comments or questions, leave them in the comments section.

About the authors

Alvaro Costa-Neto is a Database Specialist Solutions Architect for AWS, where he helps customers design and implement database solutions on the cloud. He has a passion for database technologies and has been working with them for more than 19 years, mostly with Microsoft SQL Server. He resides in Clermont, FL with his wife and two children, who share his love for aviation and traveling. When he is not working, he likes to host cookouts with his family and friends and explore new places.

Jeril Jose is Database Specialist Consultant with over 16 years of experience in Microsoft SQL Server and other database technologies. He helps customers architect, migrate and optimize their database solutions to AWS. Prior to joining AWS, he supported production and mission-critical database implementation across financial and retail segments.

Rakesh Ramanukolanu is Senior Database Specialist Solutions Architect at Amazon Web Services. He helps customers across different industry verticals design, migrate, and optimize their SQL Server workloads to a managed database platform like Amazon RDS and Amazon RDS Custom.