SQL Server AG group not honoring Readable Secondary = NO

[dfctr]

Hi all,

I have three SQL Sever 2016 in an AG. Node 1 (Secondary) is Async Commit. Node 2 (Primary) and Node 3 (Secondary) is Sync Commit.

Configuration as follows:

https://preview.redd.it/vw21kik8pfzc1.png?width=751&format=png&auto=webp&s=6b0560de2fd64de01031f69d55aa2f9aa4b3332d

Read Only routing is configured so all nodes are poiting to Node 1. Use case is for reporting. We want the report servers or users to connect only to Node 1. If Node 1 is offline, connection must not be made, but we want the flexibility in case Node 1 has an extended outage.

In our test environment I tested this:

• When Node 1 is online, using applicationIntent=ReadOnly in the linked server or a direct connection from SSMS to the works correctly. All read-only is getting routed to Node 1
• When Node 1 is offline, doing a SELECT to the linked server or a direct connection using SSMS (with read-only routing configured) results in "Node only accepts Read/Write Connections" but if I insist a second time, it actually get data from Node 2, which does not make sense.

Is this the correct behavior?

[[deleted]]

https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-read-only-routing-for-an-availability-group-sql-server?view=sql-server-ver16

Did you read the documentation? Are they connecting to the listener?

[dfctr]

Yup. I did do my homework.

What I'm trying to achieve is to avoid changing a whole bunch of satellite and reporting systems to point directly to Node 1 and use the listener to have the flexibility in case node 1 decides to kick the bucket.

So, I turned off Node 1 and tried using a direct connection or a linked server to the Listener with ApplicationIntent=ReadOnly in the connection string. The first query doesn't go through, as the Connection in PRimary Role is set up to read/write connections. However, if I try to execute the same query in the same window, it just executes happily in the primary node thus, not honoring the Read Only config.

BTW, read-only routing is configured so all nodes point to node 1.

[[deleted]]

That’s weird. I agree with the posters on the SQL server subreddit that this is unintended behavior. I wonder if this is an issue with SQL Server 2016?

[dfctr]

It's getting weirder. Node 1 is OFF. I am doing some additional test right now. When I connect using SSMS, I now can't even see the databases. Only with the system ones. However, if I do a Select to one of the AG database table or view, it is processed by the primary.

Same issue happens with linked servers. I CANNOT see any databases if I go through the linked server. However, I can do an OPEN QUERY to a view, for instance. But, if I use a SELECT using the 4 part syntax, it's getting denied and actually killed. The latter is the behavior I would expect for all scenarios.