1

Version: SQL 2012 SP3 Two Node. 1 Primary 1 Secondary

This is how nodes are set up.

enter image description here

Using SSMS connection options when I set connection property ApplicationIntent=ReadOnly I am not getting routed to secondary node. Yes I am using listener name as Server name.

enter image description here

I did not create any read-only routing list because I only have 2 nodes.

Question 1

Am I wrong that even with only 2 nodes I need read-only routing list?

Question 2

What is the difference between these 2 settings about Readable Secondary? Secondaries can only serve read-only traffic, so why route other connections with selecting Yes?

As per books online:

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.

Improve this question

1 Answer 1

Reset to default
2

You have to have a read-routing list, even with only two instances, if you want to offload reads to a secondary replica.

The following script is an example of setting this up (you would change the AG name to whatever your AG is, and the replicas to your servernames (using the fully qualified domain name, and adjusting the port if required).

ALTER AVAILABILITY GROUP YOURAGNAME MODIFY REPLICA ON 'REPLICA1' with (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://REPLICA1.FQDN:1433'));
ALTER AVAILABILITY GROUP YOURAGNAME MODIFY REPLICA ON 'REPLICA2' with (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://REPLICA2.FQDN:1433'));

ALTER AVAILABILITY GROUP YOURAGNAME MODIFY REPLICA ON 'REPLICA1' with (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST = ('REPLICA2', 'REPLICA1')));
ALTER AVAILABILITY GROUP YOURAGNAME MODIFY REPLICA ON 'REPLICA2' with (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST = ('REPLICA1', 'REPLICA2')));

As regards the flags:

  • ReadIntent allows for connections only passed along with the ApplicationIntent=ReadOnly flag

  • Yes allows for any connection to the database (meaning you could connect directly with SSMS and run queries)

Improve this answer
4
  • on Yes allows me to connect with SSMS using secondary SQL server name? I can do the same even when it set to read-intent.
    – SqlWorldWide
    Commented Jul 11, 2017 at 1:43
  • Yes would allow you to connect directly to the secondary replica, into the database, and give you the chance to query things as you normally would. The readintent would require you to pass along the applicationintent information in order to connect. learn.microsoft.com/en-us/sql/database-engine/… does a fairly good job of explaining it.
    – Nic
    Commented Jul 11, 2017 at 13:23
  • Thanks again. I am still confused because with readintent I can connect to a secondary node using server name in SSMS which I expect to. Then I can browse the database in AG and query the content.
    – SqlWorldWide
    Commented Jul 11, 2017 at 17:23
  • @SqlWorldWide I suspect this is the case because you're connecting with a privileged account. Try this using a SQL Login with limited permissions to the database and see what happens.
    – John Eisbrener
    Commented Jul 12, 2017 at 17:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.