2

There is a stored procedure which has select statement as well as insert and other statements. What will be the behaviour of the stored procedure in a Read Only Routing configured in AlwaysOn Availability Group?

Will the select statement go to the secondary server for reading and rest will be done on the primary server or since it is an inline statement in a stored procedure, that's why everything will take place on the primary itself.

I could not find any documentation regarding this, how will the select statement behave?

1 Answer 1

4

Read-only routing is per connection, not per batch or statement. Read-only intent is specified via the application connection string and the connection will be directed to a read-only replica (if possible) when the connection is established.

6
  • Thanks Dan, so when this particular stored procedure will be called by the application (read-intent connection) the particular inline SELECT query would be done on the secondary server, right? Just want to confirm.
    – SSS
    Mar 16, 2021 at 13:36
  • 2
    @ShauryaS, all of the statements will be run on the secondary. The insert queries in the proc will fail because the database is read-only. Applications should not specify read-only intent when modifications are performed.
    – Dan Guzman
    Mar 16, 2021 at 14:08
  • I'm sorry, but what i have read is read only routing does what is whenever application is using read intent and connecting through listener, it will push the select statements to secondary and all other query to primary. If otherwise, what is the use of read only routing?
    – SSS
    Mar 16, 2021 at 14:20
  • Quoting from Microsoft doc, To configure an Always On availability group to support read-only routing in SQL Server, you can use either Transact-SQL or PowerShell. Read-only routing refers to the ability of SQL Server to route qualifying read-only connection requests to an available Always On readable secondary replica (that is, a replica that is configured to allow read-only workloads when running under the secondary role). To support read-only routing, the availability group must possess an availability group listener. Read-only clients must direct their connection requests to this listener.
    – SSS
    Mar 16, 2021 at 14:22
  • @ShauryaS That MS Docs quote is saying exactly what Dan answered. Mar 16, 2021 at 15:10

Your Answer

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

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