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.

  • 2
    The listener is just an IP and DNS name. You can't specify application intent – AMtwo Mar 31 '17 at 1:15
  • @AMtwo I might be getting the terminology wrong. We have a DB01 (active) & DB02 (passive) that are synced together. Then we have DB03 which currently gets replication from the active database (in this case DB01). When we connect to DB_Listener without specifying an ApplicationIntent we will be sent to DB01. When we specify ApplicationIntent=ReadOnly we get sent to DB03. That is currently working now. I want to default to getting only readonly to DB03. – Edward Mar 31 '17 at 15:20
  • I added a full answer with some more detail--hopefully that answers your question. I'd also steer away from referring to servers as "active" or "passive".... They're trigger words for some people. Primary/Secondary seem to be more universally used. =D – AMtwo Mar 31 '17 at 15:44

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.

Your Answer

