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?
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.
There three options in Readable Secondary column while.
- No - If you want to use secondary replica for stand by only.
- Yes - All connections are allowed to secondary replica, but only read requests will be executed. Executing DML statements result to error.
- Read-Intent Only Connecting to database using listener and
ApplicationIntent=ReadOnly, session will be redirected to secondary replica.
You can use same parameter in application connection string to route read requests to secondary replica.