December 7, 2021 at 6:21 pm
Hi
Doing some testing on a server that has 2 node AG setup. Secondary is set to Read-Intent-only. We are using the AG listener name to connect the application.
If there are absolutely no connections coming in, and 1 single query from the application is using a Read-Intent only connection in it's connection string parameters, will it go to the secondary to read the data OR will it go to the Primary because there is no load?
In other words does the listener load balance the queries, only pushing the read-intent connections over to the Secondary when the Primary is under load?
In my case, the query with Read-Intent using the listener to connect is hitting the Primary. I expected it to use the Secondary.
December 8, 2021 at 2:39 pm
It doesn't load balance, it just directs the connection to the appropriate server.
Is the secondary set to allow connections?
December 9, 2021 at 2:48 pm
Hi
Doing some testing on a server that has 2 node AG setup. Secondary is set to Read-Intent-only. We are using the AG listener name to connect the application.
If there are absolutely no connections coming in, and 1 single query from the application is using a Read-Intent only connection in it's connection string parameters, will it go to the secondary to read the data OR will it go to the Primary because there is no load?
In other words does the listener load balance the queries, only pushing the read-intent connections over to the Secondary when the Primary is under load?
In my case, the query with Read-Intent using the listener to connect is hitting the Primary. I expected it to use the Secondary.
This is part of configuration of the AG. You need to configure the read only routing.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
December 10, 2021 at 1:59 pm
In order for a connection to be redirected to the read-intent secondary, the connection string must include the ApplicationIntent and the database that exists in the AG. If either do not exist, then the connection will not be redirected.
You can verify this in SSMS - when prompted to connect, select Options | Additional Connection Parameters and enter ApplicationIntent=Read-Only - and on the Connection Properties page change the connect to database.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply