Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Server Always On Expand / Collapse
Author
Message
Posted Wednesday, March 06, 2013 4:53 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 3:09 AM
Points: 118, Visits: 170
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
Post #1427316
Posted Wednesday, March 06, 2013 6:19 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 8:07 AM
Points: 4,128, Visits: 5,835
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 at GMail
Post #1427342
Posted Wednesday, March 06, 2013 6:38 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 3:09 AM
Points: 118, Visits: 170
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.
Post #1427352
Posted Wednesday, March 06, 2013 7:59 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 8:07 AM
Points: 4,128, Visits: 5,835
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 at GMail
Post #1427428
Posted Wednesday, March 06, 2013 8:19 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 3:09 AM
Points: 118, Visits: 170
"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.
Post #1427449
Posted Wednesday, March 06, 2013 8:58 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 8:07 AM
Points: 4,128, Visits: 5,835
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 at GMail
Post #1427473
Posted Wednesday, March 06, 2013 9:29 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 1:49 PM
Points: 32,768, Visits: 14,929
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.

http://social.technet.microsoft.com/wiki/contents/articles/13503.read-only-routing-with-sql-server-2012-always-on-database-availability-groups.aspx







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1427490
Posted Wednesday, March 06, 2013 9:35 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 3:09 AM
Points: 118, Visits: 170
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.
Post #1427499
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse