SQL Server AlwaysOn-Readable Secondary Setting January 3rd, 2014

Vinod Kumar

Recently I was questioned by two different customers on the very topic and I thought it was worth writing a mini-post to start the year :). The question was simple, what is the fundamental difference of values of “Yes” and “Read-Intent only” values when we are configuring our AlwaysOn Availability Groups? Aren’t they the same?

If you check the configuration of AlwaysOn we will be presented with these three options and these can be changed even later. This blog is to simply explain what these three mean.

No: This is the easiest of the lot. It just means we will not allow any connections to this server.

Yes: This is used for legacy purposes where *any TDS client* who wants to connect to a secondary replica explicitly for reporting workload can connect.

Updated as per Robert Comments: You can still connect to a instance marked as Yes via the routing list as it is an readable copy.

Read-intent-only: In this option we explicitly need to give the connection string property of “ApplicationIntent=ReadOnly”. Read more about connection strings in my previous blog – SQL Server AlwaysOn–Connection Strings. The only difference here is that you have explicitly shown the intent that the connection is going to be read only and it removes the caveat of previous point because now the routing-list / listener takes care of routing your request to the first available readable secondary as per the configuration. Hence for all practical purposes, for new applications please use this option.

Irrespective to the option selected, if your application fires a write operation to a ReadOnly server, the application will fail on the first DML or DDL operation performed in that connection.

This post is inspired from Book content: SQL Server 2012 AlwaysOn Joes 2 Pros®: A Tutorial for Implementing High Availability and Disaster Recovery using AlwaysOn Availability Groups” (Paperback, Kindle).

Share this article

Tags: , , , , , , , ,

This entry was posted on Friday, January 3rd, 2014 at 08:30 and is filed under Technology. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

2 Responses to “SQL Server AlwaysOn-Readable Secondary Setting”

  1. Robert says:

    I agree with both the “No” and “Read-intent-only” definitions, but I don’t agree with your “Yes” definition. The caveat is not completely true. You can use routing with the “Yes” as long as you conform to the same rules as the “Read-intent-only” setting. That is routing is configured for the availability group, “ApplicationIntent=ReadOnly” is in the connection string, and the database your connecting to is part of the availability group. With those setting it will use the routing tables with either a “Yes” or “Read-intent-only” to send you to the first available read-only secondary. At least that’s how it working with SQL 2012 SP1.

Leave a Reply