Always On Readable Secondar Option

Question

Always On Readable Secondar Option

PJ_SQL

SSCertifiable

Points: 6705
More actions
September 4, 2019 at 9:14 pm

#3677383

Always On scenario:
Connections in Primary Role: Allow all connections
Readable Secondary : What is the difference between "Yes" and "Read-Only Intent".
According to my understanding "Read-Only Intent" re-routes the read requests once it is configured.
What is the purpose of readable secondary "Yes"? In the application does the Node has to be hard-coded for this to work or can we use Listener?

Thanks in advance.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Sue_H SSC Guru Points: 90860 More actions · Answer 1

Read-Only Intent requires you to connect with a read only connection by specifying the connection with ApplicationIntent = ReadOnly. When you set the Readable Secondary to Yes, you still connect to read only databases but don't need to specify anything in the connection. And you don't connect to the listener but rather to the instance with the secondary.

Sue

PJ_SQL SSCertifiable Points: 6705 More actions · Answer 2

Thanks Sue for the information.

So, basically SQL Instance would need to hardcoded in the application for read-only purpose.

In this scenario how would failover work?

Node 1 : all connections

Node1 : readable "yes"

If we were to failover Node1 to Node2, then the read only purpose would not work right as we have the instance name in the connection string rather than Listener.

Sue_H SSC Guru Points: 90860 More actions · Answer 3

Correct. And it doesn't always work as expected so that is something to consider when using readable yes. You can end up connected to the primary depending on where in the connection process things were when the failover occurred.

Sue

Jeffrey Williams SSC Guru Points: 90335 More actions · Answer 4

It is not recommended that you setup a read-only secondary as an automatic failover. In fact - I would not recommend using a read-only secondary as part of an HA or DR strategy.

If you need the ability to run reports against production databases - and those reports would impact the production workload - then a read-only secondary is a good choice for that purpose. In this scenario you should have 3 nodes in your cluster...

Node 1 - Primary
Node 2 - HA Secondary - automatic fail-over, not read-only or read-intent, listener defined for nodes 1 & 2
Node 3 - Read-Only secondary - no listener, direct connections for reports/extracts/etc...

You could then add a 4th node for DR - having that node in another DC and setup with manual failover, if needed.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs