SQLTeam.com | Weblogs | Forums

AlwaysOn Read secondary replica

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

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.