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


Read only replicas and stored procedures


Read only replicas and stored procedures

Author
Message
Josh Rotert
Josh Rotert
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 271
I am curious what other people have done to implement read-only routing for a large number of procedures.
Basically figuring out when to call procedures that are read-only with read-only intent.

We have a user application that passes an encrypted string to a web service that directs it to our SQL Servers.

I've been tasked with finding a way to make this happen without changing the application.

The only thing I have been able to come up with is writing something (which I did) that will identify whether something is read-only or not and storing a big list.

Then having the web service look up the given procedure and adding the intent where needed. I really hate the idea of calling SQL to figure out how to call SQL. It just seems backwards to me.

Anyone have any better ideas or approaches?

Thank you,
Josh
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26195 Visits: 17539
talinkio (8/6/2014)
I am curious what other people have done to implement read-only routing for a large number of procedures.
Basically figuring out when to call procedures that are read-only with read-only intent.

We have a user application that passes an encrypted string to a web service that directs it to our SQL Servers.

I've been tasked with finding a way to make this happen without changing the application.

The only thing I have been able to come up with is writing something (which I did) that will identify whether something is read-only or not and storing a big list.

Then having the web service look up the given procedure and adding the intent where needed. I really hate the idea of calling SQL to figure out how to call SQL. It just seems backwards to me.

Anyone have any better ideas or approaches?

Thank you,
Josh


Can you elaborate on the question? Not sure what you mean by intent and read-only etc. I am sure it makes perfect sense since you are close to the situation but from the outside this doesn't provide enough information of what you are trying to do.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Josh Rotert
Josh Rotert
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 271
Thank you.

I am wondering what others have used as a solution for offloading read-only tasks to a read-only replica in the availability group.
We have roughly 5,000 procedures and about half only do reads.

I know which ones only do reads and I have stored it in a table. Currently my solution is to query that table for each procedure call and get back if it is read-only or read-write. Then connect and run that procedure accordingly.

My question is, how do others handle this type of scenario. I don't really like my solution and was hoping to find something better here.

Thank you for your time.
happycat59
happycat59
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4763 Visits: 3221
I am wondering whether you can use read only routing as per http://msdn.microsoft.com/en-au/library/hh710054.aspx

If you can, then all you need to do is to update the connection string - no need for you to explicitly connect to the read only replica. Then all you need to worry about is determining what sprocs can use that (read only) connection NOT which server to send the read only requests to



Steve-3_5_7_9
Steve-3_5_7_9
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1466 Visits: 1599
Hmm

You are using "read only routing" and setting the application intent parameter.

The best solution appears to be to change your application which is something you don't want to do. The parts of the application that are "read only" would have a different connection string with the "application intent" parameter.

I guess the other way is to do what you are doing and lookup each sproc first against the listener and then depending on the sproc being "read only" you would either keep the connection or change it to "applicationintent=readonly". It's kind of messy.

The last way, which is probably the ugliest is let the sproc fail. Meaning always connect with "read only" and sql server will throw you an error if you try to do a DML operation. Then you would connect normally to the primary via the listener.

The solution that we have implemented where I work is to change the sections of the application that perform "read only" operations to connect with "applicationintent=readonly" and direct to the secondary. It's work but probably the best solution.

Steve



Josh Rotert
Josh Rotert
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 271
Thanks Steve.
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