Configure read-only routing in an Always On availability group in SQL Server on AWS

Created by Subhani Shaik (AWS)

Environment: PoC or pilot	Technologies: Databases; Infrastructure	Workload: Microsoft
AWS services: AWS Managed Microsoft AD; Amazon EC2

Summary

This pattern covers how to use the standby secondary replica in SQL Server Always On by offloading the read-only workloads from the primary replica to the secondary replica.

Database mirroring has one-to-one mapping. You can’t read the secondary database directly, so you must create snapshots. The Always On availability group feature was introduced in Microsoft SQL Server 2012. In later versions, major functionalities have been introduced, including read-only routing. In Always On availability groups, you can read the data directly from the secondary replica by changing the replica mode to read-only.

The Always On availability groups solution supports high availability (HA), disaster recovery (DR), and an alternative to database mirroring. Always On availability groups work at the database level and maximize the availability of a set of user databases.

SQL Server uses the read-only routing mechanism to redirect the incoming read-only connections to the secondary read replica. To achieve this, you should add the following parameters and values in the connection string:

• ApplicationIntent=ReadOnly

• Initial Catalog=<database name>

Prerequisites and limitations

Prerequisites

• An active AWS account with a virtual private cloud (VPC), two Availability Zones, private subnets, and a security group

• Two Amazon Elastic Compute Cloud (Amazon EC2) machines with SQL Server 2019 Enterprise Edition Amazon Machine Image with Windows Server Failover Clustering (WSFC) configured at the instance level and an Always On availability group configured at the SQL Server level between the primary node (WSFCNODE1) and the secondary node (WSFCNODE2), which are part of the AWS Directory Service for Microsoft Active Directory directory named tagechtalk.com

• One or more nodes configured to accept read-only in the secondary replica

• A listener named SQLAG1 for the Always On availability group

• SQL Server Database Engine running with the same service account on two nodes

• SQL Server Management Studio (SSMS)

• A test database named test

Product Versions

• SQL Server 2014 and later

Architecture

Target technology stack

• Amazon EC2

• AWS Managed Microsoft AD

• Amazon FSx

Target architecture

The following diagram shows how the Always On availability group (AG) listener redirects queries that contain the ApplicationIntent parameter in the connection to the appropriate secondary node.

1. A request is sent to the Always On availability group listener.

2. If the connection string does not have the ApplicationIntent parameter, the request is sent to the primary instance.

3. If the connection string contains ApplicationIntent=ReadOnly, the request is sent to the secondary instance with read-only routing configuration , which is WSFC with an Always On availability group.

Tools

AWS services

• AWS Directory Service for Microsoft Active Directory enables your directory-aware workloads and AWS resources to use Microsoft Active Directory in the AWS Cloud.

• Amazon Elastic Compute Cloud (Amazon EC2) provides scalable computing capacity in the AWS Cloud. You can launch as many virtual servers as you need and quickly scale them up or down.

• Amazon FSx provides file systems that support industry-standard connectivity protocols and offer high availability and replication across AWS Regions.

Other services

• SQL Server Management Studio (SSMS) is a tool for connecting, managing, and administering the SQL Server instances.

• sqlcmd is a command-line utility.

Best practices

For more information about Always On availability groups, see the SQL Server documentation.

Epics

Set up read-only routing

Task	Description	Skills required
Update the replicas to read-only.	To update both the primary and the secondary replica to read-only, connect to the primary replica from SSMS, and run the Step 1 code from the Additional information section.	DBA
Create the routing URL.	To create routing URL for both replicas, run the Step 2 code from the Additional information section. In this code, tagechtalk.com is the name of the AWS Managed Microsoft AD directory.	DBA
Create the routing list.	To create the routing list for both replicas, run the Step 3 code from the Additional information section.	DBA
Validate the routing list.	Connect to the primary instance from SQL Server Management Studio, and run the Step 4 code from the Additional information section to validate the routing list.	DBA

Test the read-only routing

Task	Description	Skills required
Connect by using the ApplicationIntent parameter.	From SSMS, connect to the Always On availability group listener name with ApplicationIntent=ReadOnly;Initial Catalog=test. The connection is established with the secondary replica. To test this, run the following command to show the connected server name. SELECT SERVERPROPERTY('ComputernamePhysicalNetBios') The output will show the current secondary replica name (WSFCNODE2).	DBA
Perform a failover.	From SSMS, connect to the Always On availability group listener name. Verify that the primary and secondary database are in sync, with no data loss. Perform a failover so that the current primary replica becomes the secondary replica, and the secondary replica becomes the primary replica. From SSMS, connect to the Always On availability group listener name with ApplicationIntent=ReadOnly;Initial Catalog=test. The connection is established with the secondary replica. To test this, show the connected server name by running the following command. SELECT SERVERPROPERTY('ComputernamePhysicalNetBios') It will display the current secondary replica name (WSFCNODE1).	DBA

Connect by using the sqlcmd command-line utility

Task	Description	Skills required
Connect by using sqlcmd.	To connect from sqlcmd, run the Step 5 code from the Additional information section at the command prompt. After you are connected, run the following command to show the connected server name. SELECT SERVERPROPERTY('ComputernamePhysicalNetBios') . The output will display the current secondary replica name (WSFCNODE1).	DBA

Troubleshooting

Issue	Solution
Creating the listener fails with the message 'The WSFC cluster could not bring the Network Name resource online'.	For information, see the Microsoft blog post Create Listener Fails with Message 'The WSFC cluster could not bring the Network Name resource online'.
Potential issues, including other listener issues or network access issues.	See Troubleshoot Always On Availability Groups Configuration (SQL Server) in the Microsoft documentation.

Related resources

• Configure read-only routing for an Always On availability group

• Troubleshoot Always On Availability Groups Configuration (SQL Server)

Additional information

Step 1. Update the replicas to read-only

ALTER AVAILABILITY GROUP [SQLAG1] MODIFY REPLICA ON N'WSFCNODE1' WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY))
GO
ALTER AVAILABILITY GROUP [SQLAG1] MODIFY REPLICA ON N'WSFCNODE2' WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY))
GO

Step 2. Create the routing URL

ALTER AVAILABILITY GROUP [SQLAG1] MODIFY REPLICA ON N'WSFCNODE1' WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://WSFCNode1.tagechtalk.com:1433'))
GO
ALTER AVAILABILITY GROUP [SQLAG1] MODIFY REPLICA ON N'WSFCNODE2' WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://WSFCNode2.tagechtalk.com:1433'))
GO

Step 3. Create the routing list

ALTER AVAILABILITY GROUP [SQLAG1] MODIFY REPLICA ON N'WSFCNODE1' WITH (PRIMARY_ROLE(READ_ONLY_ROUTING_LIST=('WSFCNODE2','WSFCNODE1')));
GO
ALTER AVAILABILITY GROUP [SQLAG1] MODIFY REPLICA ON N'WSFCNODE2' WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('WSFCNODE1','WSFCNODE2')));
GO

Step 4. Validate the routing list

SELECT AGSrc.replica_server_name AS PrimaryReplica, AGRepl.replica_server_name AS ReadOnlyReplica, AGRepl.read_only_routing_url AS RoutingURL , AGRL.routing_priority AS RoutingPriority FROM sys.availability_read_only_routing_lists AGRL INNER JOIN sys.availability_replicas AGSrc ON AGRL.replica_id = AGSrc.replica_id INNER JOIN sys.availability_replicas AGRepl ON AGRL.read_only_replica_id = AGRepl.replica_id INNER JOIN sys.availability_groups AV ON AV.group_id = AGSrc.group_id ORDER BY PrimaryReplica

Step 5. SQL Command Utility

sqlcmd -S SQLAG1,1433 -E -d test -K ReadOnly