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

  • Article

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