In the previous blog http://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=ReadOnly set. The word ‘intent’ is important here as there is no application check made to guarantee that there are no DDL/DML operations in the application connecting with ‘ReadOnly’ but an assumption is made that customer will only connect read workloads.  If you connect an R/W application using false claim ApplicationIntent=ReadOnly, the application will connect successfully but will fail on the first DML/DDL thereby leading to a surprise to the customer.

 

Specifically, the benefits of ‘Read_Intent Only’ are

  •  Ensuring primary workload is not impacted:  The key scenario here is that you have setup a primary replica and one or more secondary replicas. You want to only allow R/W workload to run on the primary replica and prevent any reporting workload to connect to primary to guarantee that the performance of primary workload is not compromised. If primary is set up not to accept connection for read-only workload, then all connections initiated by read-only applications with option (i.e. ApplicationIntent=ReadOnly) to primary node will fail.  Read-Only-Routing http://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 allows read workload.
  •  Allowing older clients to work in AlwaysOn configuration without any changes: One key scenario of AlwaysOn is to allow DBMirror failover partner based clients/applications to work seamlessly in AlwaysOn configuration. The failover partner based protocol works because the connection to SQL Server only succeeds on the primary.  When configuring AlwaysOn, you can set secondary replica to disallow connections or to allow connections with ApplicationIntent=ReadOnly. If you have configured  allows customers to upgrade to AlwaysOn without any changes to existing DBMirror clients while still allowing new clients to take advantage of readable secondary using option (i.e. ApplicationIntent=ReadOnly). Customer can choose to migrate the failover based client applications to take full advantage of AlwaysOn functionality like multiple replicas at a later point.
  •  Eliminate surprises: If an R/W application connects to readable secondary by mistake choosing to connect to the physical node instead of using VNN, the connection will succeed but the application will error out when the DML or DDL operation is executed. This will lead to a surprise to customer if s/he did not know that the application was actually connected to a secondary replica. This can happen if application connected to the primary node using physical node-name, not with VNN, and now there was a failover and the primary/secondary replicas switched roles.  Read-Intent option will eliminate such surprises as presumably the R/W application would not have set ApplicationIntent=ReadOnly on the connection string. In fact, for R/W there is no need to set ApplicationIntent.  Let us take an example. Suppose you have 2 node AlwaysOn configuration with N1 as primary and N2 as secondary. An R/W workload explicitly connects, without using VNN, to N1. Now a fail over happens and N1 now becomes the secondary but the application does not know about it. It connects to N1 thinking it is a primary leading to a surprise later on when it attempts a DDL or Insert/Delete/Update operation.

 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