Read Only Routing Configuration for High Availability: Design Patterns

Best Practices for Read Only Routing Configuration in SQL Server

1. Prerequisites & basic setup

• Listener required: Ensure the availability group has an availability group listener.
• Readable secondaries: Configure one or more secondary replicas with ALLOW_CONNECTIONS = READ_ONLY (or FUNCTIONS) and verify ConnectionModeInSecondaryRole.
• Set READ_ONLY_ROUTING_URL first: Assign each replica a correctly formed read-only routing URL (TCP://host.domain:port) before configuring routing lists.

2. Connection-string and client requirements

• Use the listener: Clients must connect to the AG listener, not directly to instance names.
• Set application intent: Add ApplicationIntent=ReadOnly (or equivalent provider flag) to offload reads.
• Default database consideration: Ensure the login’s default database is in the AG or include Database= in the connection string so routing logic can identify the target DB.

3. Routing lists and priorities

• Primary’s routing list defines order: Configure PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(‘PreferredSecondary’,‘FallbackSecondary’,…)). SQL Server checks the list in order.
• Use multiple entries for failover resilience: Include multiple readable secondaries and the primary as last-resort fallback.
• Keep lists up to date: Update routing lists after topology changes (add/remove replicas, IP/name changes).

4. Network, DNS, and ports

• Use FQDNs and stable DNS: Read-only routing URLs should use fully qualified domain names that resolve correctly from all replicas.
• Open required ports: Ensure TCP ports used by SQL endpoints are reachable between replicas and clients (listener port and instance ports).
• Avoid NAT surprises: Ensure internal routing/dns used by replicas is reachable; public NAT can break routing URLs.

5. Health, monitoring, and failover behavior

• Monitor replica state and read-only status: Track replica role, synchronization state, and whether a secondary is currently readable.
• Understand routing fallback: If the chosen secondary is not readable, SQL Server will route back to the primary (if allowed); plan for that behavior.
• Test failovers: Regularly test planned and unplanned failover scenarios to validate read-only routing and client reconnection behavior.

6. Security and authentication

• Match logins across replicas: Ensure SQL logins (SIDs) and permissions are synchronized across replicas to avoid auth failures on secondaries.
• Prefer Windows auth/kerberos where possible: Validate SPNs and constrained delegation for listener connections if using integrated auth.
• Protect endpoints: Limit access to database mirroring endpoints and use encrypted endpoints if required.

7. Performance and workload placement

• Route heavy read workloads to dedicated secondaries: Use routing lists and resource governance to offload reporting/analytics from primary.
• Consider replica role and commit mode: Prefer synchronous secondaries for HA; use asynchronous secondaries for distant read-only offload but expect potential data lag.
• Use read-only routing for session-based reads carefully: Long-lived sessions pinned to a replica can become stale after failover; design apps to reconnect with ApplicationIntent on reconnect.

8. Automation and configuration management

• Script configuration changes: Use T-SQL or PowerShell (Set-SqlAvailabilityReplica / ALTER AVAILABILITY GROUP) to set READ_ONLY_ROUTING_URL and READ_ONLY_ROUTING_LIST reliably.
• Include routing in deployment pipelines: Treat routing lists and URLs as infrastructure configuration and version them with IaC or runbooks.

9. Troubleshooting checklist

• Verify ApplicationIntent on client connections.
• Check replica READ_ONLY_ROUTING_URL values and listener reachability from each replica.
• Confirm secondary is readable (ALLOW_CONNECTIONS, ConnectionModeInSecondaryRole).
• Review SQL Server error log and Extended Events for routing failures.
• Test DNS resolution and port connectivity from each replica to routing targets.

10. Documentation & operational guidance

• Document routing lists, URLs, ports, and expected failover behavior.
• Train app owners: Ensure application teams know to set ApplicationIntent and how reconnects behave after failover.
• Schedule periodic reviews: Revalidate routing after patches, topology changes, or DNS/AD updates.

If you want, I can generate the exact T-SQL and PowerShell commands to configure READ_ONLY_ROUTING_URL and READ_ONLY_ROUTING_LIST for a sample availability group.