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

Read only replicas and stored procedures Expand / Collapse
Author
Message
Posted Wednesday, August 6, 2014 1:43 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 9:59 AM
Points: 15, Visits: 211
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
Post #1600371
Posted Wednesday, August 6, 2014 1:52 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:56 PM
Points: 13,078, Visits: 12,529
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 Moden's 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)
Post #1600378
Posted Wednesday, August 6, 2014 3:24 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 9:59 AM
Points: 15, Visits: 211
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.

Post #1600410
Posted Wednesday, August 6, 2014 10:07 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 9:46 PM
Points: 3,026, Visits: 2,630
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



Post #1600457
Posted Thursday, August 7, 2014 7:02 AM This worked for the OP Answer marked as solution
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 5:43 AM
Points: 920, Visits: 1,436
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



Post #1600604
Posted Thursday, August 7, 2014 9:25 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 9:59 AM
Points: 15, Visits: 211
Thanks Steve.

Post #1600707
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse