I am wondering what others have used as a solution for offloading read-only tasks to a read-only replica in the availability group.
We have roughly 5,000 procedures and about half only do reads.
I know which ones only do reads and I have stored it in a table. Currently my solution is to query that table for each procedure call and get back if it is read-only or read-write. Then connect and run that procedure accordingly.
My question is, how do others handle this type of scenario. I don't really like my solution and was hoping to find something better here.
Thank you for your time.