July 26, 2016 at 11:07 am
I could try this , was wondering if anyone has tried this before. I have AAG read-only intent enabled, if i have a sproc that does bunch of reads from one database and writes it to another database, would the read-only type queries automatically get transferred to secondary instance?
July 27, 2016 at 7:38 am
Based on this: https://connect.microsoft.com/SQLServer/feedback/details/697217/applicationintent-readonly-allows-updates-to-a-database
The connection does not know what your sproc does.
July 27, 2016 at 8:28 am
curious_sqldba (7/26/2016)
if i have a sproc that does bunch of reads from one database and writes it to another database, would the read-only type queries automatically get transferred to secondary instance?
No. The listener doesn't know what you intend to do. If you want to connect to the read only secondary, the connection string has to specify the application intent read only option in the connection string, and if you're writing to another database, then that DB has to be on the secondary server and be read-write, or you'll get errors (if it works at all).
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply