Configure SQL Server 2012 AlwaysOn Availability Groups Read-Only Routing using T-SQL

By:   |   Comments (32)   |   Related: > Availability Groups


Problem

In a previous tip on New SQL Server AlwaysOn Feature - Part 1 configuration, we have seen how we can configure AlwaysOn Availability Groups. AlwaysOn Availability Groups promise to maximize your IT investments by allowing read-only workloads on the secondary replicas. How can we configure the secondary replicas to allow read-only workloads? And, how do we configure SQL Server to automatically redirect the read-only workloads after a failover?  Check out this tip to learn more.

Solution

SQL Server 2012 AlwaysOn Availability Groups help organizations maximize IT investments by making full use of standby hardware for read-only workloads. Not only that, it also improves performance on the primary database by offloading read-only workloads and redirecting them to a secondary replica. In this tip, we will configure databases in an Availability Group to allow read-only workloads when acting as a secondary replica. We will also configure SQL Server to automatically redirect read-only workloads after an Availability Group failover.

 

Enable Readable Secondary's in SQL Server 2012 AlwaysOn Availability Groups

The first thing that we need to do is configure the secondary replica to allow read-only workload. This can be done by modifying the Readable Secondary option in the Availability Group Properties window.

SQL Server 2012 Availability Group Properties window

There are three different options under the Readable Secondary drop-down menu.

  • No. This is the default value. This means that the database does not allow read-only workloads when running as a secondary role. Any attempts to run SELECT statements in this database will return an error similar to that of when running queries against a database mirror.
  • Read-intent only. This setting allows read-only workloads when running as a secondary role only if the application connection string contains the parameter: Application Intent=Read-only.
  • Yes. This setting allows read-only workloads regardless of whether or not the application connection string contains the parameter: Application Intent=Read-only.

For the purpose of this tip, we will configure the replicas' Readable Secondary option to Read-intent only. This will allow us to appreciate how read-only routing works when configured later in this tip.

Configure the readable secondary option to read-intent only for SQL Server 2012 AlwaysOn Availability Groups

Once the Readable Secondary option has been configured, you can test this by running your read-only applications against the secondary replica. Be sure to add the connection string parameter Application Intent=Read-only in your applications.

Verify applications connecting to readable secondaries in SQL Server 2012 AlwaysOn Availability Groups

In the screenshot above, my applications are connected to both the primary (ALWAYSON-AG1) and the secondary replica (ALWAYSON-AG2.) Since we configured the replicas' Readable Secondary option to Read-intent only, I've added the connection string parameter Application Intent=Read-only in one of the applications.

Enable Read-Only Routing using T-SQL in SQL Server 2012 AlwaysOn Availability Groups

So far, I've only used the Availability Group Listener Name on the application connecting to the primary replica whereas I've still used the SQL Server instance name to connect to my readable secondary. This is because if I use the Listener Name to connect to my readable secondary, it will automatically be redirected to my primary replica even if I specify the Application Intent=Read-only parameter in the connection string. The Listener Name will always redirect to the primary replica unless we configure a read-only routing list. A read-only routing list is a list of all Availability Group Replicas that are configured to accept read-only workloads. SQL Server will redirect client connections that have the Application Intent=Read-only parameter in the connection string to one of the replicas in the read-only routing list. A more comprehensive explanation of what is happening in the background is described in this blog post by Matt Neerincx, a developer in the SQL Server Engine Team. We want to configure all of our applications to use the Listener Name instead of the instance names for simplicity's sake and possibly for standardization. However, we also want our read-only workloads to be redirected not to the primary replica, but to the secondary replica and failed over accordingly should the Availability Group fail over. To configure read-only routing, we need to do the following on our Availability Group.

  1. Specify a read_only_routing_url. A read_only_routing_url is the entry point of an application to connect to a readable secondary. It contains the system address and the port number that identifies the replica when acting as a readable secondary. This is similar to the endpoint URL we specify when configuring database mirroring.
  2. Specify a read-only routing list on all replicas. For each replica that will act as primary, we need to define the corresponding secondary replicas that will act as the routing target for read-only workloads. This means that if the replica is acting as a primary, all read-only workloads will be redirected to the replicas in the read-only routing list

Unfortunately, there is no graphical user interface to perform these tasks in SQL Server Management Studio. Therefore, we will need to do these using T-SQL. Let's first specify a read_only_routing_url for all of the replicas in our Availability Group. Since we already have an existing Availability Group, we will simply use the ALTER AVAILABILITY GROUP command for these tasks. 

ALTER AVAILABILITY 
GROUP AlwaysOnAGProd
MODIFY REPLICA 
ON 
'ALWAYSON-AG1'
WITH
(
   SECONDARY_ROLE
   
    (
       READ_ONLY_ROUTING_URL='TCP://ALWAYSON-AG1.TESTDOMAIN.local:1433'

    )
)

The code simply modifies the Availability Group named AlwaysOnAGProd and assigns the read_only_routing_url to the replica ALWAYSON-AG1 when it is acting as a secondary replica. This needs to be done on all replicas in your Availability Group.

Next, we need to specify a read-only routing list. 

ALTER AVAILABILITY GROUP AlwaysOnAGProd
MODIFY REPLICA ON 
'ALWAYSON-AG1'
WITH
(
   PRIMARY_ROLE
   (
       READ_ONLY_ROUTING_LIST =('ALWAYSON-AG2')
   )
)

The code simply lists the replica ALWAYSON-AG2 in the read-only routing list for ALWAYSON-AG1 if it is acting as the primary. This also needs to be done on all replicas in your Availability Group.

A complete T-SQL script for my 2-node Availability Group configuration is shown below. 

--Specify a read_only_routing_url
ALTER AVAILABILITY GROUP AlwaysOnAGProd
MODIFY REPLICA ON 
'ALWAYSON-AG1'
WITH
(
   SECONDARY_ROLE
   (
       READ_ONLY_ROUTING_URL='TCP://ALWAYSON-AG1.TESTDOMAIN.local:1433'
   )
)


ALTER AVAILABILITY GROUP AlwaysOnAGProd
MODIFY REPLICA ON 
'ALWAYSON-AG2'
WITH
(
   SECONDARY_ROLE
   (
       READ_ONLY_ROUTING_URL='TCP://ALWAYSON-AG2.TESTDOMAIN.local:1433'
   )
)

--Specify a read-only routing list
ALTER AVAILABILITY GROUP AlwaysOnAGProd
MODIFY REPLICA ON 
'ALWAYSON-AG1'
WITH
(
   PRIMARY_ROLE
   (
       READ_ONLY_ROUTING_LIST =('ALWAYSON-AG2')
   )
)


ALTER AVAILABILITY GROUP AlwaysOnAGProd
MODIFY REPLICA ON 
'ALWAYSON-AG2'
WITH
(
   PRIMARY_ROLE
   (
       READ_ONLY_ROUTING_LIST =('ALWAYSON-AG1')
   )
)

Of course, the more replicas you have in your Availability Group, the more configuration has to be made.

Testing Read-Only Routing in SQL Server 2012 AlwaysOn Availability Groups

Now that we've enabled Readable Secondary replicas and configured read-only routing, let's test this with our applications. We will use the Availability Group Listener Name on all of our application connection strings. Also, on the application that we want to connect to the secondary replica for read-only workload, we will specify the Application Intent=Read-only parameter in the connection string. Let's see how our applications behave.

Read-only routing in applications with SQL Server 2012 AlwaysOn Availability Groups

Note that I am using the same connection string values for both application except for the additional Application Intent=Read-only parameter in one of them. Because of this parameter, the application gets redirected to the replica defined in the read-only routing list - ALWAYSON-AG2 - when ALWAYSON-AG1 acts as my primary replica. When you failover the Availability Group, the application automatically gets redirected to the new secondary replica without making changes to the application. This makes the failover and failback process very seamless with the applications.

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Edwin Sarmiento Edwin M Sarmiento is a Microsoft SQL Server MVP and Microsoft Certified Master from Ottawa, Canada specializing in high availability, disaster recovery and system infrastructures.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Wednesday, February 13, 2019 - 2:50:53 AM - Ashif shaikh Back To Top (79023)

Hey,

Your ROR configuration will not work for read intent connections if the primary server node1 goes down (on a 2 node cluster Node1 and Node2).

The following code can help us in this scenario.

ALTER AVAILABILITY GROUP AlwaysOnAGProd
MODIFY REPLICA ON 
'ALWAYSON-AG1'
WITH
(
   PRIMARY_ROLE
   (
       READ_ONLY_ROUTING_LIST =('ALWAYSON-AG2','ALWAYSON-AG1')
   )
)
 
 
ALTER AVAILABILITY GROUP AlwaysOnAGProd
MODIFY REPLICA ON 
'ALWAYSON-AG2'
WITH
(
   PRIMARY_ROLE
   (
       READ_ONLY_ROUTING_LIST =('ALWAYSON-AG1','ALWAYSON-AG2')
   )
)

Monday, July 3, 2017 - 10:28:06 AM - vazo Back To Top (58776)

 

 What is the best practice to update the windows or mssql under an AG? 

Cluster Aware Update not working as seconrady DB is read only with assync connection and manual failover.

Shutdown is enabled during patch time window.

I think first i need to stop both database service. Then upgr the secondary - then primary. Then start Primary DB service - then secondary? 

Am i think right?

Wednesday, June 29, 2016 - 11:10:28 AM - bass_player Back To Top (41786)

 Hi Phil_H,

The Application Intent = Read only parameter in the connection string tells the database engine to route the connections to the read-only secondary replicas. That assumes that you have defined a read-only routing list. Without tthat connection string parameter, the database engine will simply accept the connection as it is on the primary replica.

Friday, June 24, 2016 - 10:54:16 AM - Phil_H Back To Top (41758)

Thank you for the great article made me finally understand theses concepts and get my AG set up correctly with Read Only Routing. 

Can you help with this though?

I'm not a developer so I don't understand how an application can make an read only interaction with the database use the connection string with the 'Application Intent = Read only clause', while interactive database calls from the same applcaition use a connection string without this. - What do I need to be telling the developers to do? 

Thanks in advance

 

Wednesday, March 23, 2016 - 7:52:00 PM - bass_player Back To Top (41042)

 

That is correct. Any time you run production workloads on a SQL Server instance - backup, read-only reports, database maintenance, etc. - you need to pay for license. But keep in mind that your licenses are only covered on the secondary if you have software assurance. Contact your Microsoft account manager for more details on this

Wednesday, March 23, 2016 - 3:03:19 PM - Jared Back To Top (41040)

 Edwin -

I have Primary and secondary replica on WSFC and AAG configuration. Both nodes are physical and sync with automatic failover. The secondary replica is readable and we run maintenance job on it but now we want to keep the "Readable Secondary" setting to "NO" instead of "Readable" and move the maintenance jobs to Primary replica so the secondary replica becomes pure passive node instead of active which saves licensing cost. My question to you is - Does changing the readable secondary replica setting to "NO" will make it passive so we save licensing cost for the secondary please? 

 

Tuesday, June 16, 2015 - 10:12:28 AM - Rafael Dontal Goncalez Back To Top (37934)

Amazing, Edwin, I just saved me hours of work. Thanks a lot. 

Friday, August 1, 2014 - 12:46:16 PM - Mike Back To Top (33981)

Edwin - After working through these steps multiple times, all of my connections are only going to the primary instance, regardless of whether I use Application Intent=ReadOnly in the connection string.  I realize this is a broad question, but any feedback you can provide on common mistakes I could have made would be much appreciated.  Thanks in advance.

Sunday, July 27, 2014 - 2:26:26 AM - Vu Pham Back To Top (33897)

Dear Edwin Sarmien,


Thanks so much!

It's help me alot.


Vu Pham

Thursday, June 19, 2014 - 2:46:44 AM - Stevemenzies Back To Top (32303)

Thanks you Bass_Player for this information!

Steve

Wednesday, June 18, 2014 - 1:57:50 PM - bass_player Back To Top (32298)

Hi Stevemenzies,

If you remove the Availability group, it also removes the listener name. However, there's nothing stopping you from creating a DNS alias because that's what the listener basically is. Once the cleanup process is completed and DNS has replicated the deletion of the entry across your network, you can create the same DNS alias and point it to the IP address of the machine running the SQL Server instance.

On the other hand, re-evaluate your HA/DR strategy with your virtualization strategy. Just because you have everythng virtualized doesn't mean you have to treat every VM in the same virtual host the same. Each one of them have a specific recovery objective and service level agreement. Make sure that those requirments are still met.

Wednesday, June 18, 2014 - 9:58:16 AM - Stevemenzies Back To Top (32294)

HI! Great Post!

I have a question about AAG Listeners on SQL 2012! We recently virtualised our SQL estate and no longer require to have our 2 node SQL AAG. I was going to drop the AAG as it's not required any longer but i undertsand this will remove the listener also? Does this mean the clients will lose connectivty to the Databases? I craeted the AAG (quite straight forward) but as to removing it I am not too sure!

thanks

Steve

Tuesday, June 3, 2014 - 4:10:26 PM - abeku66 Back To Top (32067)

Tried all the suggestions above but still no joy yet. What i am going to do is provision a CCI in the same domain as the Database servers and migrate the application to this new server.Once his is done, i will try and use an Intergrated Security connection from the application server to the Database server via the listener .Hopefully this will work. I will let you know how this goes!

 

Cheers!

Tuesday, June 3, 2014 - 12:07:22 PM - bass_player Back To Top (32061)

Here's some troubleshooting steps to perform on the application server

1) Run a PING test to the Availability Group listener name. Verify that you are getting a response

2) Failover the Availability Group to one of the secondary replicas

3) Flush the DNS cache on the application server using ipconfig /flushdns

4) Run another PING test to the Availability Group listener name. Verify that you are getting a response. If you are not, there may be some DNS or routing issue that is preventing communication between the application server and the secondary replica. This isn't a database problem but a network problem so you need to get your network administrators involved.

5) If you are getting a response from the PING test, run a SQL Profiler trace to validate that you can indeed connect (or not) to the databases via the application. Capture both successful and failed logins

Monday, June 2, 2014 - 6:08:34 PM - abeku66 Back To Top (32050)

No it doesn't look like the Application can connect successfully to the Databases on the second replica via the listener. Can you suggest a sure way of testing if the Application can successfully connect to the replica ? I believe that if the Application can successfully connect , we shouldn't have a problem with the web interface>

Cheers mate!

Monday, June 2, 2014 - 5:26:12 PM - abeku66 Back To Top (32048)

Actually, the replica's don't exist in a mult-subnet environment. They are all on the same VLAN and the ip's are all within the same subnet. I only added the milti subnet clause just in case . In either case the failover seems very successful each time  i have tested it but then the website Data doesn't load when you try to select from one or two drop downs until i fail back to the original primary replica. Yes the Application server can connect to the server that hosts the replica database.

Monday, June 2, 2014 - 3:48:40 PM - bass_player Back To Top (32047)

You can still use SQL Server authentication with Availability Groups. You just cannot have replicas in an Availability Group be in different Active Directory domains. For example, if your application server is on domainA and your SQL Server instances are on domainB, the cluster nodes in the WSFC running the Availability Group replicas should all be on domainB. Your application can use mixed mode authentication to connect to the Availability Group replica databases. 

For troubleshooting purposes, first validate that the application can connect to the Availability Group replica databases after the failover. I noticed that your connection string includes a MultiSubnetFailover parameter. Can the application server connect to the secondary replica after failing over to the other network subnet?

Monday, June 2, 2014 - 12:01:32 PM - abeku66 Back To Top (32039)

Edwin, regarding the Conection String , does the Login Account(Intergrated Security= SSPI) always have to be a domain account ? I have an issue where my Database servers are in a different domain from the app server . The connection string works perfectly and connects successfully to the Primary Replica using the listener(Virtual Name) but when failover occurs, we can not read all the Data from the secondry altough it successfully becomes the new primary. When failover occurs, the 2nd instance succesfully assumes the role of the primary replica and the 3 rd instance also becomes the secondary replica but some of the combo boxes on our website are not able to populate data when you click on the drop down arrow.. Below is a samle of my current connection sring

<connectionStrings>
 <add name="Sample1ConnectionString" connectionString="server=AGListener;database=mySampleDatabase1;User Id=myUsername; pwd=mypassword;MultiSubnetFailover=True;ApplicationIntent=ReadOnly; Connection Reset=false; Pooling=true;" />

<add name="Sample2ConnectionString" connectionString="server=AGListener;database=mySampleDatabase2;User Id=myUsername; pwd=myPassword;MultiSubnetFailover=True;ApplicationIntent=ReadOnly; Connection Reset=false; Pooling=true;" />


<add name="Sample3ConnectionString" connectionString="server=AGListener;database=mySampleDatabase3;User Id=myUsername; pwd=myPassword;MultiSubnetFailover=True;ApplicationIntent=ReadOnly; Connection Reset=false; Pooling=true;" />
 </connectionStrings>


 

 



 


 Any ideas will help.

 

Thanks!

Tuesday, May 6, 2014 - 4:52:34 PM - Sri Back To Top (30636)

Awesome article..enjoyed readin through out...!!

Sunday, March 30, 2014 - 2:00:18 AM - Cwchambe Back To Top (29923)

Hello Edwin,

Thank you for this informative article, as I have a deeper understanding of how to route appication coonections!  Can you provide the T-SQL to revert the AG nodes back to the default configuration,  in case an issue arises?

Thanks in advance!

Friday, August 9, 2013 - 4:49:02 PM - bass_player Back To Top (26198)

Please check back in a few days. The MSSQLTips staff usually have the recording available after the webcast

Friday, August 9, 2013 - 10:20:44 AM - sree Back To Top (26182)

Thanks Edwin! So, when will this webcast be available for watching? As of now, it says

The following Webinar is not available:
 
Fundamentals of SQL Server AlwaysOn Availability Groups

Thursday, August 8, 2013 - 5:07:02 PM - bass_player Back To Top (26161)

Hi sree,

Glad to hear that you were able to make it work. Be sure to watch the recording of the webcast on the fundamentals of Availability Groups because this is the exact same setup that I used - multi-subnet cluster with 2 replicas on the primary data center and 1 replica on the DR data center.

http://www.mssqltips.com/webcastlist.asp

Thursday, August 8, 2013 - 3:05:17 PM - sree Back To Top (26158)

Never Mind...I was able to make it work after thorough understanding of how this works. Thank you...

Thursday, August 1, 2013 - 2:37:18 PM - sree Back To Top (26090)

Hi Edwin,

Let's say you have 3 Replicas(AG1, AG2 and AG3) where AG1 and AG2 being in one Data Center(Synchronous commit/Automatic Failover) and AG3 in different Data Center(Async/Manual Failover) in a multi-subnet cluster. What would be the script look like for routing URL and routing List? This looks simple, but confusing me a lot for some reason :(

Thanks in advance!

Wednesday, April 10, 2013 - 4:53:11 PM - bass_player Back To Top (23291)

Apologies for the late response. You can download the client application from here http://sdrv.ms/ZGMDYF

Note that you need the .NET Framework 4.0 and the SQL Server 11.0 Native Client on your workstation to use the Application Intent=ReadOnly parameter on your connection string

Let me know if there is anything else I can help you with

Wednesday, April 10, 2013 - 11:09:18 AM - sree Back To Top (23278)

Hi Edwin - Just wondering regarding the application..

Thanks-Sree!

Thursday, February 7, 2013 - 11:20:34 AM - Ranga Back To Top (21980)

Thank you very much!

Wednesday, February 6, 2013 - 7:13:15 PM - bass_player Back To Top (21956)

I will package it up and provide a link for download. You need the .NET Framework 4.0 and the SQL Server 2012 Native Client installed on the client machine

Wednesday, January 30, 2013 - 2:15:50 PM - Ranga Back To Top (21803)

Hi Edwin,

Do you have that little application "sql Server Denali alwaysOn client" for download so we can test the always on feature.

 

Thanks,

Ranga

 

Tuesday, January 29, 2013 - 11:58:15 AM - bass_player Back To Top (21774)

Hi Jeaux,

You need Enterprise Edition to implement AlwaysOn Availability Groups. The licensing for your standby servers will always be based on this assumption: if you are using it for something else other than as a standby, you need a license. Once you start running read-only workloads on your secondary replicas, they are bo longer considered as standby and, therefore, need a license. This is the same concept even with database mirroring whe you need to have the mirror licensed if you will create snapshots of the mirror database for reporting purposes

Tuesday, January 29, 2013 - 10:14:33 AM - Jeaux Back To Top (21771)

Thanks Edwin for the article.  Do the secondary read only nodes also have to be licensed with Enterprise Edition versions of SQL Server?

 














get free sql tips
agree to terms