0

if i'm change connection string to applicationintent:readonly then i setup read only routing list in my SQL server 2016 , does that mean my application will route all write request to primary and then read only request to secondary server which i setup as read intent only?

Improve this question

2 Answers 2

Reset to default
2

No, it means connections with applicationintent:readonly will be routed to the readable replica. If one of them tries to modify data it will get an error message. Something like below:

Msg 3906, Level 16, State 2, Line 2

Failed to update database "XXX" because the database is read-only.

Note: if the secondary readable replica is not available the connection will be able to be established to the primary one depending on Connections in primary Role option.

Improve this answer
4
  • I believe routing only works if using the listener connect. If you attempt to connect to the primary, you will not be routed to the secondary/readonly.
    – James Jenkins
    Jul 15, 2019 at 12:52
  • @JamesJenkins, yes that is correct
    – Denis Rubashkin
    Jul 15, 2019 at 12:58
  • Thanks Dennis. Is it possible to route all read only request to secondary database which is read only DB and write request only in primary by using listener to reduce load in primary.Does SQL server have that kind of features to automatically route read only request to secondary and write request to primary.
    – Dash
    Jul 15, 2019 at 13:06
  • I doubt there is such a feature. There are different approaches to achieve a similar behaviour but it's up to the client deciding which requests have to be sent to primary replica and which to secondary one.
    – Denis Rubashkin
    Jul 15, 2019 at 13:23
0

There three options in Readable Secondary column while.

  1. No - If you want to use secondary replica for stand by only.
  2. Yes - All connections are allowed to secondary replica, but only read requests will be executed. Executing DML statements result to error.
  3. Read-Intent Only Connecting to database using listener andApplicationIntent=ReadOnly, session will be redirected to secondary replica.

You can use same parameter in application connection string to route read requests to secondary replica.

Thanks!

Improve this answer
2
  • Thank you Rajesh. Is it possible to route all read only request to secondary database which is read only DB and write request only in primary by using listener to reduce load in primary.Does SQL server have that kind of features to automatically route read only request to secondary and write request to primary.
    – Dash
    Jul 15, 2019 at 12:57
  • @Dash No. How would SQL Server identify that connecting session will execute read or write request only until we don't send a hint ApplicationIntent=ReadOnly to it. Connecting to database without the parameter will redirect connection to primary replica, where we can execute read & write both. Primary replica accepts both type of requests because if Read-Intent Only secondary replica goes down primary will be available to serve read requests.
    – Rajesh Ranjan
    Jul 15, 2019 at 13:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.