Context
A client used “ApplicationIntent=ReadOnly” in their connection string, expecting queries to execute on the secondary read-only replica. Instead, the queries were executed on the primary database, leading them to suspect an Availability Group misconfiguration.
Diagnostic
To confirm whether the driver sent the read-only intent, we captured the “hadr_read_only_route_preconditions” Extended Event. This event records whether “ReadOnlyIntent = True” arrives at SQL Server:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
CREATE EVENT SESSION [ReadOnlyIntentCheck] ON SERVER ADD EVENT sqlserver.hadr_read_only_route_preconditions ( ACTION( sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.database_name, sqlserver.nt_username ) ); ALTER EVENT SESSION [ReadOnlyIntentCheck] ON SERVER STATE = START; |
When we examined the event data, “ReadOnlyIntent” was set to False, confirming the flag never reached the server.
Initial situation: ReadOnlyIntent = False
What Went Wrong?
In this case, although the third-party driver accepted the option “ApplicationIntent=ReadOnly” it wasn’t send to the SQL Server. After upgrading the driver’s to the latest version, the read-only flag appeared at SQL Server and queries were executed on the replica as intended.
After upgrade: ReadOnlyIntent = True
Key Takeaways
- Audit your stack end-to-end. Availability Group settings are only half the story. Client drivers must support “ApplicationIntent=ReadOnly” to enable read-only routing.
- Monitor with Extended Events: It helps detect misrouted traffic.
- Maintain driver governance: Keep client drivers updated and tested for AG compatibility.
Share via:
