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:
In this demo, we have the following defined objects:
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.'
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
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
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.