DB-유

Read-Only Routing in Always On Availability Groups

· mssql, high-availability, always-on

I once pointed a reporting tool at the secondary replica by name, and after the next failover the reports were quietly hammering the primary. Hard-coding a replica is a trap. Read-only routing is the fix: clients connect to the listener with ApplicationIntent=ReadOnly, and the primary redirects them to a readable secondary according to a routing list you define. SQL Server 2016 added load balancing across several secondaries.

Below is a two-replica setup and a way to test that the redirection really happens. You need an AG with a listener on SQL Server 2014 or later (2016 for load-balanced routing), and Enterprise Edition, since Basic Availability Groups in Standard don't allow readable secondaries. Clients need a driver that knows ApplicationIntent: Native Client 11, ODBC Driver 11 or later, .NET 4.5 or later, or JDBC 4.0 or later.

Allow read connections on the secondary

Each replica's SECONDARY_ROLE setting controls whether it accepts reads when it is a secondary:

ALTER AVAILABILITY GROUP [AG1]
MODIFY REPLICA ON N'SQL01' WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));
ALTER AVAILABILITY GROUP [AG1]
MODIFY REPLICA ON N'SQL02' WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));

READ_ONLY means only connections with ApplicationIntent=ReadOnly are accepted on the secondary; ALL accepts any connection. READ_ONLY is the safer setting because it stops people connecting directly to the secondary by name and being surprised when it becomes the primary.

Set it on every replica, because any of them may be a secondary after a failover.

Routing URL for each replica

The routing URL is the endpoint the primary hands back to the client. It is the replica's own TDS endpoint, in the form TCP://fqdn:port:

ALTER AVAILABILITY GROUP [AG1]
MODIFY REPLICA ON N'SQL01' WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://sql01.contoso.local:1433'));
ALTER AVAILABILITY GROUP [AG1]
MODIFY REPLICA ON N'SQL02' WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://sql02.contoso.local:1433'));

Use the FQDN and the actual port of the instance, not the listener and not the mirroring endpoint port (5022). Clients must be able to resolve and reach that address directly.

Routing list for each replica as primary

The routing list says: "when this replica is primary, send read-only connections to these replicas, in this order". Define it for every replica:

ALTER AVAILABILITY GROUP [AG1]
MODIFY REPLICA ON N'SQL01' WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST = (N'SQL02', N'SQL01')));
ALTER AVAILABILITY GROUP [AG1]
MODIFY REPLICA ON N'SQL02' WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST = (N'SQL01', N'SQL02')));

Including the primary itself at the end of the list means read-only connections still succeed, on the primary, when the secondary is down. Without it they would fail.

With three or more replicas on SQL Server 2016, group replicas in nested parentheses to load-balance between them round-robin:

ALTER AVAILABILITY GROUP [AG1]
MODIFY REPLICA ON N'SQL01' WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST = ((N'SQL02', N'SQL03'), N'SQL01')));

The listener

Routing only works through the listener. Confirm it exists and note its port:

SELECT agl.dns_name, agl.port, agl.ip_configuration_string_from_cluster
FROM sys.availability_group_listeners agl;

Testing the redirect

First the configuration:

SELECT ar.replica_server_name, ar.secondary_role_allow_connections_desc, ar.read_only_routing_url,
       rl.routing_priority, ar2.replica_server_name AS routed_to
FROM sys.availability_replicas ar
LEFT JOIN sys.availability_read_only_routing_lists rl ON rl.replica_id = ar.replica_id
LEFT JOIN sys.availability_replicas ar2 ON ar2.replica_id = rl.read_only_replica_id
ORDER BY ar.replica_server_name, rl.routing_priority;

Then test the redirection with sqlcmd. The -K ReadOnly switch sets the application intent, and -d must name a database in the AG, because routing decisions are made per database:

sqlcmd -S ag1-listener.contoso.local -d Sales -K ReadOnly -E -Q "SELECT @@SERVERNAME"
sqlcmd -S ag1-listener.contoso.local -d Sales -E -Q "SELECT @@SERVERNAME"

The first should return the secondary's name, the second the primary's. From an application, the connection string equivalent is:

Server=tcp:ag1-listener.contoso.local,1433;Database=Sales;Integrated Security=SSPI;ApplicationIntent=ReadOnly;MultiSubnetFailover=True;

On the secondary you can watch read-intent sessions arrive:

SELECT session_id, login_name, host_name, program_name FROM sys.dm_exec_sessions WHERE is_user_process = 1;

Gotchas

  • Read-only connections land on the primary. The connection string is missing Initial Catalog/Database, so no routing decision can be made. Routing requires a database name that belongs to the AG.
  • The target database ('Sales') is in an availability group and is currently accessible for connections when the application intent is set to read only. (Error 978) A connection without ApplicationIntent=ReadOnly reached the secondary directly by name, and ALLOW_CONNECTIONS = READ_ONLY rejected it. Connect through the listener instead.
  • Login timeout after the redirect. The client resolved the listener fine but cannot reach the READ_ONLY_ROUTING_URL. Check DNS for the replica FQDN and firewall on port 1433 from the client network to the secondary.
  • After a failover, reads go nowhere. The new primary has no READ_ONLY_ROUTING_LIST. Define the list for every replica, not just the current primary.
  • Old driver ignores ApplicationIntent. SQL Server Native Client 10 and .NET 4.0 do not know the keyword and silently connect to the primary. Upgrade the driver.

Every replica needs all three settings, and I re-run the sqlcmd -K ReadOnly test after every failover test. It takes ten seconds and has caught a missing routing list more than once.