Partager via

End to End - Using a Listener to Connect to a Secondary Replica (Read-Only Routing)

  • Article

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:

  1. Configure availability group replicas to accept read-only connections while in the secondary role.
  2. Configure Read-Only Routing for each availability replica. Each secondary replica must
    1. Define a read-only routing URL
    2. Specify a read-only routing list
  3. 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

  1. Define a read-only routing URL address.
  2. 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.

 

Comments

  • 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..