Read-only routing refers to the ability of SQL Server to route incoming connections to an availability group listener to a secondary replica that is configured to allow read-only workloads.
The following end to end steps demonstrate how to configure and use read-only routing to route read-intent listener connections to the secondary replica.
This demo performs the following steps:
- Configure availability group replicas to accept read-only connections while in the secondary role.
- Configure Read-Only Routing for each availability replica. Each secondary replica must
- Define a read-only routing URL
- Specify a read-only routing list
- Use SQLCMD to connect to SQL Server using the availability group listener and the Application Intent connection property to confirm read-only routing is working.
In this demo, we have the following defined objects:
- Availability group named ag
- Listener named aglisten
- Replicas SQLNODE1 (primary) and SQLNODE2 (secondary)
1 Configure Availability Group Replicas to Accept Read-Only Connections
Configure your availability group replicas to allow for read-only connection requests when in the secondary role. The following script configures both SQLNODE1 and SQLNODE2 replicas, when in in the secondary role, to accept read-only connections through the listener.
ALTER AVAILABILITY GROUP [ag]
MODIFY REPLICA ON N'SQLNODE1' WITH (SECONDARY_ROLE(ALLOW_CONNECTIONS = READ_ONLY))
GO
ALTER AVAILABILITY GROUP [ag]
MODIFY REPLICA ON N'SQLNODE2' WITH (SECONDARY_ROLE(ALLOW_CONNECTIONS = READ_ONLY))
GO
NOTE: Read-only routing can support ALLOW_CONNECTIONS property set to READ_ONLY or ALL.
Alternatively, use SQL Server Management Studio to pull up the availability group properties using Object Explorer, and set the Readable Secondary property to 'Read-intent only.'
2 Configure Read-Only Routing
Each availability replica that will accept these read-only connections must be defined with a read-only routing URL and a routing list. In this step you will
- Define a read-only routing URL address.
- Specify a read-only routing list for each availability replica while in the primary role.
NOTE: Configuring the read-only routing URL and the routing list can be performed through Transact-SQL. SQL Server Management Studio does not offer these availability group properties for modification.
First, define the read-only URL for each replica. This designates the address for each availability replica you wish to accept read-only connection requests when in the secondary role. For example, define a URL SQLNODE2, so that when SQLNODE2 is in the secondary role, it can accept read-only connections.
ALTER AVAILABILITY GROUP ag MODIFY REPLICA ON N'SQLNODE1' WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://SQLNODE1:1433'));
GO
ALTER AVAILABILITY GROUP ag MODIFY REPLICA ON N'SQLNODE2' WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://SQLNODE2:1433'));
GO
Next, define a routing list for each replica. When the replica is in the primary role, this designates where to route read-only connection requests to. For example, when SQLNODE1 is in the primary role, define our routing list to consist of SQLNODE2 which is where read-only connection requests will be routed.
ALTER AVAILABILITY GROUP ag MODIFY REPLICA ON N'SQLNODE1' WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('SQLNODE2')));
GO
ALTER AVAILABILITY GROUP ag MODIFY REPLICA ON N'SQLNODE2' WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('SQLNODE1')));
GO
3 Confirm read-only routing using SQLCMD
SQLCMD ships with SQL Server 2012, supporting the latest SQL Server connection parameters for AlwaysOn availability groups including the new Application Intent connection property.
To test your newly configured read-only routing, use SQLCMD to specify the application intent option (-K).
NOTE: You must specify one availability database from the availability group using the database option (-d). If this option is not specified your connection will not be successfully routed to the secondary replica.
