August 6, 2014 at 1:43 pm
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
August 6, 2014 at 1:52 pm
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 6, 2014 at 3:24 pm
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.
August 6, 2014 at 10:07 pm
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
August 7, 2014 at 7:02 am
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
August 7, 2014 at 9:25 am
Thanks Steve.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply