Configuring Read-Only Routing for an Always On Availability Group

One of the main benefits of an AlwaysOn Availability Group is being able to read off of the secondary replicas. However, Read-Only Routing is not automatically configured when you first build your AlwaysOn Availability Group. To fully utilize an AlwaysOn Availability Group and take full advantage of having read-only connections connect to your secondary database, you will have to configure Read-Only Routing.

Read-Only Routing refers to the functionality of SQL Server to route incoming read-only connection requests to the secondary replica of an AlwaysOn Availability Group. Connections are made to the Availability Group Listener, which is used in the clients’ connection string. The connection string must also specify the application intent as “read-only.

In the following steps, I will show how to configure Read-Only Routing in SQL Server 2012 and above using Transact-SQL. In the following examples, I am using a 3 node Windows Cluster where the node names are “SQLNODE01”, “SQLNODE02”, and “SQLNODE03”. My Availability Group’s name is “AG1” and the listener is “AGListenerName”. The database I am configuring for the Availability Group is “AGdb1”.

Step 1 Configure your read-only routing URL for each replica. Change all the red text to match your environment. You can also change the secondary connection to “All” instead of “READ_ONLY” depending on your needs.

ALTER AVAILABILITY GROUP[AG1]MODIFY REPLICA ONN'SQLNODE01'WITH(SECONDARY_ROLE(ALLOW_CONNECTIONS=READ_ONLY));

ALTER AVAILABILITY GROUP[AG1]MODIFY REPLICA ONN'SQLNODE01'WITH(SECONDARY_ROLE(READ_ONLY_ROUTING_URL=N'TCP://SQLNODE01.mydomain.com:1433'));

ALTER AVAILABILITY GROUP[AG1]MODIFY REPLICA ONN'SQLNODE02'WITH(SECONDARY_ROLE(ALLOW_CONNECTIONS=READ_ONLY));

ALTER AVAILABILITY GROUP[AG1]MODIFY REPLICA ONN'SQLNODE02'WITH(SECONDARY_ROLE(READ_ONLY_ROUTING_URL=N'TCP://SQLNODE02.mydomain.com:1433'));

ALTER AVAILABILITY GROUP [AG1] MODIFY REPLICA ONN'SQLNODE03'WITH(SECONDARY_ROLE(ALLOW_CONNECTIONS=READ_ONLY));

ALTER AVAILABILITY GROUP[AG1]MODIFY REPLICA ONN'SQLNODE03'WITH(SECONDARY_ROLE(READ_ONLY_ROUTING_URL=N'TCP://SQLNODE03.mydomain.com:1433'));

 Step 2 Configure your read-only routing list. This is the route the read-only connection uses when you are making an incoming read-only connection to each server instance when it is the primary replica. It will try to make a connection to the first node in the routing list followed by the second if it cannot make a connection to the first. For example, when “SQLNODE01” is the primary replica, the read-only connection will first try to make the connection on “SQLNODE03”.

ALTER AVAILABILITY GROUP[AG1]MODIFY REPLICA ONN'SQLNODE01'WITH(PRIMARY_ROLE(READ_ONLY_ROUTING_LIST=('SQLNODE03','SQLNODE02')));

ALTER AVAILABILITY GROUP[AG1]MODIFY REPLICA ONN'SQLNODE02'WITH(PRIMARY_ROLE(READ_ONLY_ROUTING_LIST=('SQLNODE03','SQLNODE01')));

ALTER AVAILABILITY GROUP[AG1]MODIFY REPLICA ONN'SQLNODE03'WITH(PRIMARY_ROLE(READ_ONLY_ROUTING_LIST=('SQLNODE02','SQLNODE01')));

NOTE: SQL Server 2016 introduces load-balancing across the secondary replicas in your AlwaysOn Availability Group. To utilize this feature, the above routing list will need to be modified so that SQL Server directs incoming read-only connections across the servers you choose to be load-balanced. You will have to use nested parentheses around the server instances you want to be part of the load-balanced group. In the below example, I have 7 servers in my Availability Group below and when “SQLNODE01” is the primary replica, SQL Server will load-balance all read-only connections to only “SQLNODE02” and “SQLNODE03”. If those servers are unavailable, SQL Server will load-balance the incoming read-only connections to “SQLNODE04”, “SQLNODE05” and “SQLNODE06”.

ALTER AVAILABILITY GROUP[AG1]MODIFY REPLICA ONN'SQLNODE01'WITH(PRIMARY_ROLE(READ_ONLY_ROUTING_LIST=(('SQLNODE02','SQLNODE03'),('SQLNODE04','SQLNODE05','SQLNODE06'),'SQLNODE07')));

At this point, your secondary database is now available to receive incoming read-only connections. Let’s test this out by using sqlcmd. When using sqlcmd, you must use the –K ReadOnly parameter as part of your connection string to the database. You must also use the –d parameter and specify the AG database. I am on SQLNode01 for these examples.

BEFORE configuring read-only routing.

AFTER configuring read-only routing.

As you can see, after we configure Read-Only Routing, our connection now uses SQLNode03. That will indicate that Read-Only Routing is configured properly. Any connection string in your application that has the “ApplicationIntent-ReadOnly” parameter will now read off of the secondary replica. Now what if one of your users wanted to run a SELECT statement in SSMS that can take a long time to pull results? You don’t want them running it on the primary replica and consuming its resources, but if you try and open a database on the secondary replica in SSMS, you receive the below error.

You can inform the user to include the “ApplicationIntent=ReadOnly” parameter when you first make a connection in SSMS. To do this, you must hit the Options button and go to the Additional Connection Parameters tab and use the read-only parameter as shown below. After you hit connect, you will be able to expand the databases and run queries against the database.

To fully understand the different options you have as a secondary role in your Availability Group, I’ve included the roles as provided by MSDB. I also have how the connection access configuration affects the client connectivity in the chart below.

Types of Connection Access Supported by the Secondary Role

The secondary role supports three alternatives for client connections, as follows:

• No connections
  • No user connections are allowed. Secondary databases are not available for read access. This is the default behavior in the secondary role.
    
• Only read-intent connections
  • The secondary database(s) are available only for connection for which the Application Intent connection property is set to ReadOnly (read-intent connections).
    
• Allow any read-only connection
  • The secondary database(s) are all available for read access connections. This option allows lower versioned clients to connect.

I hope this helps you configure your Read-Only Routing! Thanks.