SQLTeam.com | Weblogs | Forums

AlwaysOn Read secondary replica


#1

Hi all,

I'm trying to understand the read options for secondary replicas in Always On.

When I set the Readable Secondary option to 'Read-intent only' I cannot open the secondary database to read data in management studio

However when I select 'Yes' I am able to read from the secondary database.

Is it only possible to read from a secondary replica in management studio if you set the Readable Secondary option to 'Yes'?

What is the difference between Read Intent Only and Yes?

Thanks


#2

Read Intent Only is used to redirect read queries from the primary to the secondary through the listener. A user would connect to the the listener address - issue a SELECT statement which will be redirected to the secondary instance to be processed. If the user issues an INSERT/UPDATE/DELETE that statement would be redirected to the primary instance.

By setting the option to Yes - you are setting it up so a user can connect directly to the secondary instance and write queries directly against the databases. In this configuration you do not need a listener and would not want any end users accessing the system through the listener.