SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Server Always On


SQL Server Always On

Author
Message
shaycullen
shaycullen
SSC-Enthusiastic
SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)

Group: General Forum Members
Points: 143 Visits: 222
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
TheSQLGuru
TheSQLGuru
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12613 Visits: 8560
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
shaycullen
shaycullen
SSC-Enthusiastic
SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)

Group: General Forum Members
Points: 143 Visits: 222
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.
TheSQLGuru
TheSQLGuru
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12613 Visits: 8560
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
shaycullen
shaycullen
SSC-Enthusiastic
SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)

Group: General Forum Members
Points: 143 Visits: 222
"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.
TheSQLGuru
TheSQLGuru
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12613 Visits: 8560
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
Steve Jones
Steve Jones
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: Administrators
Points: 63464 Visits: 19115
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
My Blog: www.voiceofthedba.com
shaycullen
shaycullen
SSC-Enthusiastic
SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)

Group: General Forum Members
Points: 143 Visits: 222
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search