This Q&A was previously posted on the Stack Exchange Network of websites.
This content is licensed under the Creative Commmons Attribution-Sharealike (CC BY-SA) license. As the author of these answers, this archive serves as a canonical location for permalinks, which will outlive Stack Overflow. See all my answers by looking at the Stack Exchange post category.
Connecting to ReadOnly Secondary Replica of SQL Server via the Listener by Default
Question
We have the ApplicationIntent=ReadOnly properly routing our users to the Secondary ReadOnly replica. Is there a way to make this the default behavior?
Apparently the default behavior seems to be ReadWrite, and if you don’t specify an ApplicationIntent it will redirect to the Primary server.
asked 2017-03-31 by Edward
Answer
The Availability Group Listener is (per the documentation), simply “a Domain Name System (DNS) listener name, listener port designation, and one or more IP addresses.” It becomes a resource in the Windows Server Failover Cluster (WSFC), and is used for routing traffic to the appropriate Availability Group (AG) node. However, it is ultimately just a DNS Name and IP.
When you connect to the Listener, it will always route to the primary (read/write) replica. The only way to have traffic routed to a secondary (read-only) replica is to explicitly specify ApplicationIntent=ReadOnly. There is no way to change the default behavior on the Listener to route traffic to a read-only secondary without specifying ApplicationIntent.
If you want users to connect to a read-only replica without explicitly specifying the ApplicationIntent, then those users would need to connect directly to the secondary server.
Note that if the user connects directly to the secondary server, a failover could result in that server becoming primary, and thus, no longer read-only.
In your scenario, if you have automatic failover between DB01 & DB02, and DB03 will “never” be primary, and you also want users to always connect to DB03, then you should simply direct users to connect directly to DB03.
However, if you want to take advantage of Read-Only routing so that the server names are obscured, and the specific secondary node to which a user is connected is transparent to them, then your only choice is to connect to the AG Listener, and specify ApplicationIntent=ReadOnly.
answered 2017-03-31 by Andy Mallon