April 1, 2019 at 1:11 pm
Hi there,
I've built a SQL 2017 AlwaysOn AG and am confused about the "Readable Secondary" settings and would like some clarity please. I understand "read intent only" requires the ApplicationIntent=ReadOnly connection string setting where as "yes" accepts all read traffic.
But I don't understand how non "read-intent" traffic would ever reach the secondary node as the listener would pass it to the primary. I guess you could bypass the listener and connect directly to a single server (breaking HA), but that's not desirable so I don't understand the practical difference between "yes" and "read-intent only".
I hope that's clear.. What am I not understanding please?
Thanks,
Rob
April 1, 2019 at 3:09 pm
I think "Yes" just means that if you connect directly to the secondary database, it's accessible, whereas "Read-intent only" means that you have a read-only routing URL configured, and connections can be assigned dynamically by the listener.
April 1, 2019 at 6:59 pm
If you have configured read-only routing properly between the primary and readable secondaries, adding "ApplicationIntent=ReadOnly" to your connect strings will automatically route these connections to the secondaries.
If your connection is only preforming a select (read), then use the connection string in this manner.
if your connection will be performing an update, insert, or delete, then you would need to leave this out of your connect string.
If you attempt to perform an insert/update/delete on a read only connection, you will get an error.
This is a simple article on the subject:
https://www.rdx.com/blog/configuring-read-only-routing-always-availability-group
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/
April 1, 2019 at 7:45 pm
If you have configured read-only routing properly between the primary and readable secondaries, adding "ApplicationIntent=ReadOnly" to your connect strings will automatically route these connections to the secondaries.
If your connection is only performing a select (read), then use the connection string in this manner.
if your connection will be performing an update, insert, or delete, then you would need to leave this out of your connect string.
If you attempt to perform an insert/update/delete on a read only connection, you will get an error.
This is a simple article on the subject:
https://www.rdx.com/blog/configuring-read-only-routing-always-availability-group
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/
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply