Difference between Readable Secondary setting Yes and Read Intent only

-> Readable Secondary setting in AlwaysON is discussed in article “https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-read-only-access-on-an-availability-replica-sql-server” is as below,

No
No user connections are allowed to secondary databases of this replica. They are not available for read access. This is the default setting.

Read-intent only
Only read-only connections are allowed to secondary databases of this replica. The secondary database(s) are all available for read access.

Yes
All connections are allowed to secondary databases of this replica, but only for read access. The secondary database(s) are all available for read access.

Environment

Blog29_1.PNG

-> Creating the Read-Only routing list using below query,

ALTER AVAILABILITY GROUP AG1
MODIFY REPLICA ON
N'JBSERVER1' WITH
(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));
ALTER AVAILABILITY GROUP AG1
MODIFY REPLICA ON
N'JBSERVER1' WITH
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://JBSERVER1.JBS.COM:1433'));
ALTER AVAILABILITY GROUP AG1
MODIFY REPLICA ON
N'JBSERVER2' WITH
(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));
ALTER AVAILABILITY GROUP AG1
MODIFY REPLICA ON
N'JBSERVER2' WITH
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://JBSERVER2.JBS.COM:1433'));
ALTER AVAILABILITY GROUP AG1
MODIFY REPLICA ON
N'JBSERVER3' WITH
(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));
ALTER AVAILABILITY GROUP AG1
MODIFY REPLICA ON
N'JBSERVER3' WITH
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://JBSERVER3.JBS.COM:1433'));
ALTER AVAILABILITY GROUP AG1
MODIFY REPLICA ON
N'JBSERVER1' WITH
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('JBSERVER3','JBSERVER2')));
ALTER AVAILABILITY GROUP AG1
MODIFY REPLICA ON
N'JBSERVER2' WITH
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('JBSERVER3','JBSERVER1')));
ALTER AVAILABILITY GROUP AG1
MODIFY REPLICA ON
N'JBSERVER3' WITH
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('JBSERVER2','JBSERVER1')));

-> The Readable Secondary is set to “Read-intent only”,

Blog29_2.PNG

-> Connecting to the secondary Replica JBSERVER3 on database JB1 directly and trying a select query,

Blog29_3

-> It is pretty clear that we are not able to make a connection to database JB1 on Server JBSERVER3 directly. Let us try a connection using ApplicationIntent=ReadOnly and it connects fine,

Blog29_4
Blog29_5
Blog29_6

-> This advises us that we cannot read the readable secondaries without a ApplicationIntent = ReadOnly.

-> The Readable Secondary is set to “Yes”,

Blog29_7.PNG

-> Connecting to the secondary Replica JBSERVER3 on database JB1 directly and trying a select query. It works fine without any issues,

Blog29_8.PNG

-> In short, Readable Secondary = “Read Intent only” requires ApplicationIntent=ReadOnly to execute a select query on the secondary. Readable Secondary = “Yes” allows us to execute a select query on the secondary directly.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

2 thoughts on “Difference between Readable Secondary setting Yes and Read Intent only

  1. Hi Vivek

    I have one question for you. If all Secondary are configured “Yes”. When connecting to the AG listener, will the command always execute on the Primary? I can read only from the Secondary, if I connect directly?

    • Hello Sven,

      When you connect via listener the command will execute on primary only.
      You can read only from secondary if you connect directly to it.
      Alternatively, you can configure a read routing list. When you connect via the listener for a read-only workload and use application intent Readonly, then it goes to secondary as configured.

      Regards;
      Vivek Janakiraman

Leave a Reply