Always On Read-only routing

Question

Always On Read-only routing

Admingod

SSCertifiable

Points: 5997
More actions
October 21, 2020 at 9:12 pm

#3799871

I was working on with requirement to load balance the traffic coming from application to secondary SQL Server replicas in an Always on Availability group. Read-only routing refers to the ability of SQL Server to route qualifying read-only connection requests to an availability always on readable secondary replica. The advantage of setting the readable secondary as Read-Intent Only is to allow the secondary server to serve the read-only workload, though only if the connection string of the application contains Application Intent=Readonly parameter. I could able to configure read only routing configuration successfully.
However, my question is I have only one application which is pointing to this server which would do reads/writes? Would it be possible to configure the same application for read only routing? Please advise? Thanks in Advance!
October 28, 2020 at 9:00 am

This was removed by the editor as SPAM

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

VastSQL SSC-Dedicated Points: 36097 More actions · Answer 1

Admingod wrote:

I was working on with requirement to load balance the traffic coming from application to secondary SQL Server replicas in an Always on Availability group. Read-only routing refers to the ability of SQL Server to route qualifying read-only connection requests to an availability always on readable secondary replica. The advantage of setting the readable secondary as Read-Intent Only is to allow the secondary server to serve the read-only workload, though only if the connection string of the application contains Application Intent=Readonly parameter. I could able to configure read only routing configuration successfully.
However, my question is I have only one application which is pointing to this server which would do reads/writes? Would it be possible to configure the same application for read only routing? Please advise? Thanks in Advance!

you have to configure listener and use listener name\ip in the application connection string , use the settings ApplicationIntent=ReadOnly for read only routing.

OLEDB CONNECT TO [Provider=SQLNCLI11.1;Data Source=LISTENER_NAME;Initial Catalog=DB_Name;Application Intent=readonly]

ODBC CONNECT TO [Provider=SQLNCLI11.1;Data Source=LISTENER_NAME;Initial Catalog=DB_Name;ApplicationIntent=readonly]

This reply was modified 4 years, 1 month ago by VastSQL.

Cebisa SSC Enthusiast Points: 151 More actions · Answer 2

Sounds like your application will need 2 connection strings. One for general application and another for read only. I would create 2 application logins just to keep the separation more clear

Admingod SSCertifiable Points: 5997 More actions · Answer 3

Thanks. Actually it's a vendor application. Do you think it would be possible to control one application with 2 connection strings like you said one for general application use and other for read only traffic? Does applications have that flexibility or it needs to be redesign? Would that be a vendor question?

Admingod SSCertifiable Points: 5997 More actions · Answer 4

Admingod

SSCertifiable

Points: 5997

October 27, 2020 at 6:06 pm

#3801796

Any thoughts?

jcarranza 23978 Ten Centuries Points: 1380 More actions · Answer 5

Well, unless you have access to modify the application itself in order to modify the connection string then yest the application vendor (developer) has to make those changes. Unless there is documentation where they allow the user to make those changes.