How to Set Up Read-Only Routing for High Availability

How to Set Up Read-Only Routing for High AvailabilityRead-only routing is a powerful technique for scaling read workloads and improving availability in database systems that support routing of read-intent connections (for example, SQL Server Always On Availability Groups, Azure SQL Database, or some distributed SQL systems). This article explains the concept, benefits, prerequisites, and a step‑by‑step configuration procedure, plus testing, monitoring, and best practices to ensure a resilient, high-performance read-only routing setup.

What is Read-Only Routing?

Read-only routing directs client connections that are flagged as read-intent to secondary (replica) databases rather than the primary (read-write) replica. This offloads read traffic from the primary, improving overall throughput and reducing contention. In high-availability (HA) architectures, read-only routing helps maintain performance during failover and supports scaling out read workloads.

Benefits

• Improved read scalability by distributing SELECT queries across replicas.
• Reduced primary load, allowing faster writes and lower replication lag.
• Better resource utilization across the cluster.
• Faster recovery and failover behavior when clients can transparently connect to readable replicas.

Prerequisites and Supported Environments

Before configuring read-only routing, verify that your environment supports it and meets requirements:

• A database technology that supports read-only routing (e.g., SQL Server Always On Availability Groups, Azure SQL Database active geo-replication variants, some cloud-managed distributed SQL offerings).
• At least one readable secondary replica that is configured to allow read access.
• Network connectivity and firewall rules allowing client connections to replicas and the listener/endpoint.
• Properly configured listeners, connection strings, and client drivers that honor read-intent connection attributes (for example, ApplicationIntent=ReadOnly for SQL Server).
• Appropriate permissions to modify availability group properties and to create endpoints or listeners.

Architecture Overview

Typical components in a read-only routing configuration:

• Primary replica (read-write).
• One or more secondary replicas (readable secondaries).
• Availability group listener or connection routing mechanism (virtual network name/IP or DNS).
• Client applications configured to request read-only intent when issuing read queries.
• Monitoring and failover orchestrator (cluster manager, availability group automatic failover settings).

Step-by-Step Configuration (SQL Server Always On example)

The following steps describe configuring read-only routing for SQL Server Always On Availability Groups. Adapt specifics to your platform if different.

1. Prepare replicas

   • Ensure each replica is set up in the availability group.
   • Configure each secondary replica’s readable secondary property:
     • For SQL Server Management Studio (SSMS): Right-click the replica → Properties → Availability Replica → set “Readable secondary” to “Yes” or “Read-intent only” depending on desired behavior.
     • Using T-SQL:

       
       ALTER AVAILABILITY GROUP [YourAG] MODIFY REPLICA ON N'YourSecondaryServer' WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY)); 

2. Configure endpoints and listener (if not already configured)

   • Ensure database mirroring endpoints are created and endpoints are functioning across replicas.
   • Create and configure the availability group listener with a network name and IP address to be used by clients for connectivity.

3. Set read-only routing URLs for each replica

   • Each replica must have a routing URL that points to the network address clients should use to connect to that replica.
   • T-SQL example: “`sql ALTER AVAILABILITY GROUP [YourAG] MODIFY REPLICA ON N’PrimaryServer’ WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_URL = ‘TCP://PrimaryServer.domain:1433’));

   ALTER AVAILABILITY GROUP [YourAG] MODIFY REPLICA ON

    N'SecondaryServer1' 

   WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = ‘TCP://Secondary1.domain:1433’));

   ALTER AVAILABILITY GROUP [YourAG] MODIFY REPLICA ON

    N'SecondaryServer2' 

   WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = ‘TCP://Secondary2.domain:1433’)); “`

4. Configure the read-only routing list

   • Define the order in which secondaries should receive read-intent connections when the primary routes them.
   • T-SQL example (on primary replica):

     
     ALTER AVAILABILITY GROUP [YourAG] SET (READ_ONLY_ROUTING_LIST = (N'SecondaryServer1', N'SecondaryServer2')); 

   • For more granular control, use ALTER AVAILABILITY GROUP … MODIFY REPLICA … (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = …)) and specify read-only routing lists per primary.

5. Ensure client connection strings specify read intent

   • Clients must declare they intend to perform read operations. For ADO.NET/SQL Server clients, include:
     • ApplicationIntent=ReadOnly
   • Example connection string:

     
     Server=YourAGListener.domain; Database=YourDB; Integrated Security=true; ApplicationIntent=ReadOnly; 

6. DNS and network considerations

   • Ensure the listener DNS name resolves and that client traffic can reach the IP/ports of the routed replicas.
   • Configure load balancers, firewalls, and network security groups to allow appropriate traffic.

Testing Read-Only Routing

• Connect a client with ApplicationIntent=ReadOnly to the listener and verify which server handles the session:

  
  SELECT @@SERVERNAME AS CurrentServer,       sys.fn_hadr_backup_is_preferred_replica('YourDB') AS IsPreferredReplica; 

• On secondaries, run:

  
  SELECT CONNECTIONPROPERTY('net_transport') AS Transport,       SESSIONPROPERTY('IsReadOnly') AS IsReadOnly; 

• Test failover scenarios:
  • Force failover to a secondary and confirm that read-intent connections still route correctly.
  • Simulate network partitions and verify client reconnect behavior.

Monitoring and Troubleshooting

• Monitor replica health, synchronization state, and session distribution.
• Check SQL Server error logs and Windows Event Viewer for routing and endpoint errors.
• Common issues:
  • Clients not sending ApplicationIntent=ReadOnly → connections land on primary.
  • Readable secondary not configured → routing fails or sends to another replica.
  • Networking/firewall blocks preventing routed connections from reaching replicas.

Best Practices

• Use read-intent connection strings from application code only for truly read-only workloads.
• Prefer read-intent only on secondaries to avoid accidental writes.
• Keep replicas geographically/latency-aware; prefer low-latency secondaries for read routing.
• Automate testing of read routing after deployments and failovers.
• Monitor replication lag and avoid sending heavy, latency-sensitive reads to lagging secondaries.
• Document routing lists and maintain them alongside configuration management.

Security Considerations

• Limit permissions on secondary replicas; avoid unnecessary elevated privileges.
• Secure endpoints and listener traffic with TLS.
• Ensure authentication and authorization checks still enforce data access rules for read sessions.

Example: Typical Troubleshooting Steps

• Verify replica properties:
  • Confirm SECONDARY_ROLE.ALLOW_CONNECTIONS = READ_ONLY or READ_INTENT.
• Confirm routing URLs:
  • Check READ_ONLY_ROUTING_URL values for each replica.
• Confirm routing list:
  • Ensure the primary has an ordered list of readable replicas.
• Check client connection string:
  • Ensure ApplicationIntent=ReadOnly is present.
• Verify network reachability:
  • Telnet or Test-NetConnection to replica host:port from client.

Conclusion

Read-only routing, when configured and used correctly, significantly enhances availability and scalability by directing read workloads to readable replicas. The keys are proper replica configuration, accurate routing URLs, correct client connection strings declaring read intent, and routine testing/monitoring. With those pieces in place, applications can achieve higher throughput, better resilience during failover, and more efficient use of resources across primary and secondary replicas.