How to configure Read-Only routing on SQL Server 2016 Always On Availability Group (AG)?

The great advantage of Always On Availability Group over other availability solutions is their ability to scale-out read operations (SELECT queries). Read-only routing is a feature of Always On Availability Group, which redirects connection requests from applications into readable secondary. In SQL Server 2016 Always On Availability Group, you can configure upto 8 readable secondary replicas.

The connections are redirected based on the routing rules. Always On Availability Group provides the following options to define the rules:
  • Read-Only Routing URL
  • Read-Only Routing List
Before defining the routing rules, we must understand the following conditions:

The application must connect to the Virtual Network Name (VNN) and not to the secondary replica directly. VNN is defined at the time of configuring listener.

The application connection string must contain the read-only connection parameter, ApplicationIntent=ReadOnly;

There must be at least on readable secondary exist on the AG. Let’s configure the routing rules:

Read-only Routing URL:

The URL is used when an application explicitly trying to connect to readable secondary with read-only intent. This URL contains the hostname and port number.

Format : TCP://server.domain.com:1433
Example : TCP://node1.ms.com:1433 (note: this on node1.ms.com)
This rule is applicable only when the node is acting as a secondary replica (if it is primary, obviously it will accept, read and write connections).

Configure read-only routing URL using T-SQL:


ALTER AVAILABILITY GROUP [TestAG]
MODIFY REPLICA ON
N’node1’
WITH
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N’TCP://node1.ms.com:1433’));
GO


Read-Only Routing List:

The read-only routing list contains the list of readable secondary with their priority. For example, node1.ms.com is the primary replica, and when an application is trying to connect to AG with explicit read-only intent, the primary replica will redirect the read-only connection to available secondary replicas as defined on the routing-list.

Format : ‘replica1’,’replica2’
Example  : ‘node2’,’node3’ (note; on node1.ms.com)

Configure read-only routing list using T-SQL:

ALTER AVAILABILITY GROUP [TestAG] 
MODIFY REPLICA ON 
N’node1’ 
WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST= (N’node2’, N’node3’))); 
GO

Note the role, it is when node1.ms.com server is in primary role, it will take effect. Now you have configured the routing URL and routing list, follow the below steps to verify it is working as expected.

Steps to verify Read-Only routing:

In SSMS login, enter server name as Always On AG listener and go to options and find the additional connector parameters.

Enter the below connection parameters and click on the connect button.

ApplicationIntent=ReadOnly; InitialCatalog=databasename;

Open new query window and see the server name to identify, currently which server it is connecting.

SELECT @@SERVERNAME;

The output should be name of Read-only Secondary Replica. 

At the Read-Only Secondary Replica, if you try to update tables, you will get the error message stating, you cannot perform DML operation on secondary replica.

If you are not getting the Read-Only Secondary replica, mention it on comment section, I will be glad to help you and get back to as soon as possible.

Load-balancing across Read-Only Secondary Replicas:

Starting from SQL Server 2016, you can configure the load balancing across the read-only replicas.  Load balancing can be configured as below:

ALTER AVAILABILITY GROUP [TestAG] 
MODIFY REPLICA ON 
N’node1’ 
WITH 
(
PRIMARY_ROLE 
(
READ_ONLY_ROUTING_LIST= ((’node1’, ’node2’,’node3’), ‘node4’, ‘node5’)); 
));
GO

Load-balancing performed using Round-robin algorithm, and connection requests are load-balanced between node1, node2 and node3. Next priority will be node4 and the last priority will be node5.

I hope this article helps you in configuring the read-only routing, if you have questions or doubts, please mention it on comment section. 

10 comments:

  1. my read request going to primary after read intent also

    ReplyDelete
  2. Let me know the connection string. By default primary accepts both read and write.

    ReplyDelete
  3. parameter works with space: Application Intent=ReadOnly; With my server it fails without space: ApplicationIntent=ReadOnly;

    ReplyDelete
    Replies
    1. I will check on that, Which version of SQL server you are using and what is the connection string.

      Delete
  4. Thanks,
    Here it is some useful tips about SQL Server replication https://blog.devart.com/how-to-create-custom-sql-server-replication-for-read-only-databases.html

    ReplyDelete
  5. A bank routing,routing number on a check in any case alluded to as an ABA or a routing travel number, is a nine digit code.

    ReplyDelete
  6. Hi
    I have configure two server AG and secondary serve as a Read_only
    I am using listener through connect application and connection string using ApplicationIntent=ReadOnly;
    My Question below
    (1) Create one Stored procedure and SP write Update and Select statement , If i execute SP through application then select run in primary or secondary server ?

    Thanks

    ReplyDelete
  7. Initial You got a awesome blog .I determination be involved in plus uniform minutes. i view you got truly very functional matters , i determination be always checking your blog blesss. read more

    ReplyDelete
  8. Omg I Finally Got Helped  !! I'm so excited right now, I just have to share my testimony on this Forum.. The feeling of being loved takes away so much burden from our shoulders. I had all this but I made a big mistake when I cheated on my wife with another woman  and my wife left me for over 4 months after she found out..  I was lonely, sad and devastated. Luckily I was directed to a very powerful spell caster Dr Emu who helped me cast a spell of reconciliation on our Relationship and he brought back my wife and now she loves me far more than ever.. I'm so happy with life now. Thank you so much Dr Emu, kindly Contact  Dr Emu Today and get any kind of help you want.. Via Email emutemple@gmail.com or Call/WhatsApp +2347012841542

    ReplyDelete
  9. Read Up My Story Today as my story is a very special and unique one as is not like the numerous advert you always see online and also I want to be 100% sincere and truthful to you that there can never be any other online spell caster that can help you bring back your EX BACK to you other than DR HUNTER ALVIN as he alone is 100% effective,dynamic and very reliable as 99.9% of other spell casters are not for real and can never help you solve your problems at all instead they will complicate your present condition OK SO BE WISE AND ALSO BE WARNED AND CONTACT DR HUNTER ALVIN TODAY... MY OWN LIFE STORY / EXPERIENCES IS BELOW:


    My Name is Mark Kelvin, From USA. I wish to share my testimonies with the general public about what this man called Dr HUNTER ALVIN has just done for me , this man has just brought back my lost Ex WIFE to me with his great spell, I was married to this woman called Sharon we were together for a long time and we loved our self’s but when I was unable to make her pregnant for me and also give her al she needs she left me and told me she can’t continue anymore then I was now looking for ways to get her back until a friend of mine told me about this man and gave his contact email: huntersalvinsolution@yahoo.com OR huntersalvinsolution@gmail.com .... YOU CAN USE ANY OF THE EMAILS TO CONTACT HIM as you won't believe this when I contacted this man on my problems he prepared this spell cast and bring my lost WIFE back, and after a month she became pregnant for me because he gave me some herbs to take also and when she went for a test and the result stated that she was pregnant am happy today am a FATHER of a baby girl, thank you once again the great DR HUNTER ALVIN for what you have done for me, if you are out there passing through any of this problems listed below:

    (1) If you want your ex back.
    (2) if you always have bad dreams.
    (3) You want to be promoted in your office.
    (4) You want women/men to run after you.
    (5) If you want a child/PREGNANCY SPELL.
    (6) You want to be rich.
    (7) You want to tie your husband/wife to be
    yours forever.
    (8) If you need financial assistance.
    (9) How you been scammed and you want to recover you lost money.
    (10)Stop Divorce
    (11) CURE TO ALL KIND OF SICKNESS/DISEASES HERE
    (12) Winning of lottery
    (13) Cure To Hiv/Aids
    (14) LOTTERY/LOTTO SPELL WINNING
    (15) CURE TO HERPES AND ANY OTHER SEXUALLY TRANSMITTED DISEASES AT ALL...
    (16) HAVE YOU BEEN SCAMMED BEFORE AND NEEDS TO RECOVER ALL THE DOLLARS/MONEY YOU LOST TO THESE FRAUDSTER ???.. CONTACT HUNTER ALVIN AS HE HELPED MY FRIEND CALLED LEONARD CAPTAIN FROM USA TO RECOVER BACK THE SUM OF $300,000.00 DOLLARS HE LOST TO SCAMMERS ONLINE

    Email: huntersalvinsolution@yahoo.com OR huntersalvinsolution@gmail.com .... YOU CAN USE ANY OF THE EMAILS TO CONTACT HIM ...... YOU CAN ALSO CALL / ADD HIM UP ON WHATSAPP USING THIS MOBILE NUMBER:+2347059232579

    ReplyDelete