March 6, 2013 at 4:53 am
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
March 6, 2013 at 6:19 am
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
March 6, 2013 at 6:38 am
I think I explained myself incorrectly. I want to do this in a SQL Query.
I have a 3rd party application that allows me to run SQL against the database that pulls back results for anlaytical purposes. I have created SPs within the database and simply use the EXEC command from the query windows within this application.
Unfortunately, the application does not allow me to change the Application Intent = Read Only. So the quetion remains, am I able to achieve this in T-SQL?
Thanks for you reply.
March 6, 2013 at 7:59 am
shaycullen (3/6/2013)
I think I explained myself incorrectly. I want to do this in a SQL Query.I have a 3rd party application that allows me to run SQL against the database that pulls back results for anlaytical purposes. I have created SPs within the database and simply use the EXEC command from the query windows within this application.
Unfortunately, the application does not allow me to change the Application Intent = Read Only. So the quetion remains, am I able to achieve this in T-SQL?
Thanks for you reply.
You cannot do this with TSQL. Like I said, once you are connected to SQL Server you are on that box, and it doesn't use/know about a .NET connection string to hop over to another server. Maybe someone else has a trick I am unaware of...
I haven' tried it, but I suppose you can try a linked server over to the secondary. I have no confidence that it will be allowed or functional, but you never know.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 6, 2013 at 8:19 am
"Like I said, once you are connected to SQL Server you are on that box, and it doesn't use/know about a .NET connection string to hop over to another server"
Sorry, but I fail to see how this makes sense? When I connect to the listener in my Always On cluster (which is essentially a SQL Instance in a virtual machine) and run the statement "SELECT @@SERVERNAME" against that listener it automatically knows to direct this query to the Primary Replica and returns the server name of said replica... What I want to do is force this to the Secondary Replica. Which has the "Read-Intent Only" property set.
Thanks again for your reply, it is hugely appreciated.
March 6, 2013 at 8:58 am
shaycullen (3/6/2013)
"Like I said, once you are connected to SQL Server you are on that box, and it doesn't use/know about a .NET connection string to hop over to another server"Sorry, but I fail to see how this makes sense? When I connect to the listener in my Always On cluster (which is essentially a SQL Instance in a virtual machine) and run the statement "SELECT @@SERVERNAME" against that listener it automatically knows to direct this query to the Primary Replica and returns the server name of said replica... What I want to do is force this to the Secondary Replica. Which has the "Read-Intent Only" property set.
Thanks again for your reply, it is hugely appreciated.
Sorry, but I don't know how to further assist you.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 6, 2013 at 9:29 am
You can't do this. The listener reacts to the read intent setting in the connection string. The architectural guidelines say that you should have multiple connection strings for your application and use those appropriately for the connections. If you cannot alter the connection string for the third party, you can't force it to the other server.
Perhaps you can alias the connection string somehow, but I'm not sure how.
March 6, 2013 at 9:35 am
I was afraid of this. Over to the 3rd party app developers to make the connection string within their client customizable, which slows the delivery of this greatly.
Thanks for the help folks.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply