AlwaysOn: Why there are two options to enable a secondary replica for read workload?

In the previous blog https://blogs.msdn.com/b/sqlserverstorageengine/archive/2011/12/22/AlwaysOn-setting-up-readable-seconary-replica.aspx I mentioned that there are two options to configure secondary replica for running read workload. The first option ‘Read-intent-only’ is used to provide a directive to AlwaysOn secondary replica to accept connections that have the property ApplicationIntent=ReadOnlyset. The word ‘intent’ is important here as SQL Server makes no application check  to guarantee that there are no DDL/DML operations in the application connecting with ‘ReadOnly’ application intent. It assumes that customer will only connect read workloads.  

 Specifically, the benefits of ‘Read_Intent Only’ are

•  Disallowing ReadOnly connections to Primary Replica :  If you set the Primary to accept only Read-Write workload, then any workload with ReadOnly intent  will not be allowed to run on the Primary Replica. This ensures reporting workload will not accidentally be run on primary replica and compromise the performance of your transactional workload.
• Read-Only Routing  https://msdn.microsoft.com/en-us/library/gg471494(v=SQL.110).aspx :  uses ‘ReadOnly’ application intent to route the read workload to the readable secondary transparently without requiring user to know which secondary replica(s) allows read workload. This guarantees that the reporting workloads will always run on secondary replicas.
•  Eliminate surprises: SQL Server allows you to connect an application directly to a physical node. In this case, it is possible for a R/W application to connect to say node N1 when it is in the primary role. Now, if there was a failover and the node N1 transitioned into a secondary node, the R/W application connecting to N1 will error out when any DDL/DML operation is executed leading to a surprise application failure. To eliminate such surprises, you can set secondary replicas to only accept connections with ReadOnly intent. With this setting, the scenario just described will be prevented because the application connecting to node N1 will fail at the connection time itself.

 Note, you can only specify option (i.e. ApplicationIntent=ReadOnly) with new clients shipped as part of SQL12. If you have an application that uses older client, then you will need to use ‘Yes’ option for the secondary replica to allow read workloads. With this setting, secondary replica will allow newer clients as well.

Thanks

Sunil Agarwal

Comments

• Anonymous
  October 07, 2015
  Hi Sunil Amazing Post. I want to ask a Question. With this option = YES  , all readable connections from clients will be passed to secondary replica  or i need explicitally indicate this in connection string?

• Anonymous
  January 13, 2017
  Aw, this was an incredibly good post. Taking a few minutes and actual effort to make a top notch article… but what can I say… I procrastinate a lot and never manage to get anything done.

  • Anonymous
    January 13, 2017
    Thanks for your encouragement. I updated it. Hope it reads better

• Anonymous
  January 13, 2017
  Marcio: 'YES' option is tricky. you will need to connect to the RS physically, We recommend ReadOnly Intent. Now with SQL 2016, your connections will round-robin across multiple secondaries. Please read the blog again as I just reorganized it

• Anonymous
  April 25, 2017
  Hi Sunil, Thanks for sharing this great informative article. I still need to understand that is that possible to restrict a user to a secondary database, with readonly permission? I am looking for the possibilities to use alwayson instead of snapshot to provide reporting team, more updated copy of data. I don't want to give access to listener and I don't want to give them access to edit as well.