shaycullen (3/6/2013)
Hi,I have setup a clustered environment for my SQL Server database and configured Always On replication with no issues, everything seems to be working perfectly, however I have one problem that I'm hoping someone has the answer to?!
I have configured Read Only Intent on my secondary replica and setup a dummy report in SSRS that simply gets the @@SERVERNAME property. I have configured the applicaton intent = read only within my connection string and when I run the report it works perfectly, returning the secondary server name as my result set.
What I want to know is there a way that I can do this within a SP so that when it is executed it automatically offloads the query to the secondary replica?? I've been scouring the internet for awhile trying to find an answer to this but so far I'm coming up blank!!!!!!
Any help is appreciated.
Thanks
A stored procedure lives on and executes on a single server, right? To get to it you must first connect to said server, and getting connected to a server/database is the job of the connection string. Thus what you want to do doesn't really make sense, namely connecting to a server to run a sproc that then hops over to a secondary for it's execution.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service