Ask Learn
Preview
Ask Learn is an AI assistant that can answer questions, clarify concepts, and define terms using trusted Microsoft documentation.
Please sign in to use Ask Learn.
Sign inThis browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
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.
Anonymous
November 26, 2013
You really need to add load balancing of the servers on the read only list to make this more useful
Anonymous
May 14, 2014
Awesome article...Enjoyed reading roughly..!!
Anonymous
February 10, 2016
Liked it.
Anonymous
December 06, 2018
I've configured read only routing, after that when I try sqlcmd to confirm it's working I'm getting below error, any help will be highly appreciated:Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : TCP Provider: No connection could be made because the target machine actively refused it..Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Login timeout expired.Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..
Ask Learn is an AI assistant that can answer questions, clarify concepts, and define terms using trusted Microsoft documentation.
Please sign in to use Ask Learn.
Sign in