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?

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.

  • 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 '19 at 12:52
  • @JamesJenkins, yes that is correct – Denis Rubashkin Jul 15 '19 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 '19 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 '19 at 13:23
  • Thank You Denis – Dash Jul 16 '19 at 3:55
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!

  • 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 '19 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 '19 at 13:17
  • Thank You Rajesh – Dash Jul 16 '19 at 3:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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