One of the perks of a SQL Server AlwaysOn* Availability Group (AG) is that the secondary nodes can handle read-only traffic. If this is configured correctly, the AG Listener will direct traffic marked “Read-Intent” to the secondary node.
But we have to make sure we’ve configured things properly, both on setup, and when failing over, adding a new node, or removing a node. After you have your AG and listener set up, the basic steps are:
- Configure read-only for secondary roles,
- set the read-only routing URL,
- set the read-only routing list for each node, and
- verify that it is working.
Configure read-only connectivity
Reference: Microsoft.com.
For the three-node XYZ (where XYZ01 is primary, 02 is secondary, and 03 is a non-readable standby), we would run the following script from the primary node:
ALTER AVAILABILITY GROUP [AG_APP]
MODIFY REPLICA ON N’XYZ01′ WITH (SECONDARY_ROLE(ALLOW_CONNECTIONS = READ_ONLY))
GO
ALTER AVAILABILITY GROUP [AG_APP]
MODIFY REPLICA ON N’XYZ02′ WITH (SECONDARY_ROLE(ALLOW_CONNECTIONS = READ_ONLY))
Set read-only routing URL
Reference: Microsoft.com.
For the three-node XYZ (where XYZ01 is primary), we would run the following script from the primary node:
ALTER AVAILABILITY GROUP [AG_APP]
MODIFY REPLICA ON N’XYZ01′ WITH
(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));
ALTER AVAILABILITY GROUP [AG_APP]
MODIFY REPLICA ON
N’XYZ01′ WITH
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N’TCP://XYZ01.irving.multiview:1433′));
ALTER AVAILABILITY GROUP [AG_APP]
MODIFY REPLICA ON N’XYZ02′ WITH
(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));
ALTER AVAILABILITY GROUP [AG_APP]
MODIFY REPLICA ON
N’XYZ02′ WITH
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N’TCP://XYZ02.irving.multiview:1433′));
ALTER AVAILABILITY GROUP [AG_APP]
MODIFY REPLICA ON N’XYZ03′ WITH
(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));
ALTER AVAILABILITY GROUP [AG_APP]
MODIFY REPLICA ON
N’XYZ03′ WITH
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N’TCP://XYZ03.irving.multiview:1433′));
Set read-only routing list
Reference: Microsoft.com
The read-only routing list tells SQL Server which node should be read-only for a given primary node. For example, the following code says,
- if 01 is primary, the read-only order of preference is 02, 01, 03.
- if 02 is primary, the read-only order of preference is 01, 02, 03.
- if 03 is primary, the read-only order of preference is 02, 01, 03.
ALTER AVAILABILITY GROUP [AG_APP]
MODIFY REPLICA ON N’XYZ03′ WITH
(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));
ALTER AVAILABILITY GROUP [AG_APP]
MODIFY REPLICA ON
N’XYZ03′ WITH
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N’TCP://XYZ03.irving.multiview:1433′));
ALTER AVAILABILITY GROUP [AG_APP]
MODIFY REPLICA ON
N’XYZ01′ WITH
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(‘XYZ02′,’XYZ01′,’XYZ03’)));
ALTER AVAILABILITY GROUP [AG_APP]
MODIFY REPLICA ON
N’XYZ02′ WITH
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(‘XYZ01′,’XYZ02′,’XYZ03’)));
ALTER AVAILABILITY GROUP [AG_APP]
MODIFY REPLICA ON
N’XYZ03′ WITH
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(‘XYZ02′,’XYZ01′,’XYZ03’)));
Verify
To use SQL Management Studio to test whether you have configured read-intent routing correctly:
- Use Ctrl-N to open a new query window.
- Use CTRL-K-O (or the toolbar) to change connections.
- Enter the name of the AG listener, (for example, XYZ). Do not click “Connect” yet.
- Click “Options” in the connect dialogue, and select the Advanced Connection Parameters tab.
- Type “ApplicationIntent=ReadOnly;Initial Catalog=<DBName>” in the dialogue, where <DBName> is the name of a database in the AG.
- Click Connect.
- Verify the server you connected to by running “SELECT @@SERVERNAME”.
And there you go!
Happy days,
Jen
*I don’t care whether it has a space between “Always” and “On”, or not. Don’t @ me, folks.