October 21, 2020 at 9:12 pm
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 22, 2020 at 8:31 am
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]
October 22, 2020 at 8:35 am
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
October 26, 2020 at 4:53 pm
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?
October 27, 2020 at 6:06 pm
Any thoughts?
October 28, 2020 at 9:00 am
This was removed by the editor as SPAM
October 29, 2020 at 1:37 pm
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.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply