May 18, 2011 at 12:05 pm
ningaraju.n (5/17/2011)
Hi Craig Farrell,We have done all the indexing and query optimization at the best,So thought of asynchronous processing.Could you please help on this?
Fire up a new thread on the forums and post the link to it here so we don't spam Gary's conversation and you should get a few of us involved to help you out with it.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
May 18, 2011 at 12:11 pm
BobA 66314 (5/18/2011)
B) Also, you recommend Service Broker for using heavy user traffic. What do you recommend for 5 users initiating database actions that take 10 to 90 seconds to process? That's an important scenario for us; our code is efficient, but the volume of data cannot move faster.
I can't speak for Gary, but I would say that in this case Service Broker isn't going to help you. SB single DB queue'ing is good for when you're maxing off resources for bulk routines or a batch series and you don't want to interrupt standard, everyday work because of it. If you've got five users expecting results back simultaneously, I probably wouldn't take this approach because the idea is to process them one or two at a time, and not 5 at once (or 50 at once, you get my drift).
What SB in a heavy user environment can give you is less concurrency and deadlocking problems if they're all hungry for the same data at once. It's an oddball (and not really recommended) workaround to dealing with heavy locking/deadlocking by blocking it before it becomes a problem. It also gives the rest of the system room to breathe if you're bringing in some heavy loads during peak usage times, so you don't interfere with that heavy traffic.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
May 18, 2011 at 1:20 pm
BobA 66314 (5/18/2011)
Thanks, Gary.A) You ACTIVATE the CustomerRequestList queue (file: 080 EmployStaffManager.sql)
Why is there no need to activate the CustomersResponseToAskList queue? Is it because it is not carrying the message type that is SENT BY INITIATOR?
I am guessing Gary didn't setup activation on "CustomersResponseToAskList" queue on purpose because he wanted readers to see the messages returned from "ShopKeeper" service to the "Customer" service. So instead in his script we see he manually queries the queue:
SELECT *
FROM CustomersResponseToAskList
and then manually runs the procedure getting messages from that queue:
EXEC CustomerLogging
Had he setup activation, the messages would be retrived very fast, and we wouldn't see antyhing.
I would think that normally "CustomerLogging" stored procedure would be configured as an activation procedure on "CustomersResponseToAskList" queue, but since this is just a lab, the the main purpose here is to learn.
I hope I am right, but Gary can confirm or deny.
May 18, 2011 at 2:19 pm
Thanks mishaluba. That makes perfect sense!
Thanks also Gary and Craig. This has been quite helpful.
May 19, 2011 at 2:00 am
Thank you Bob for bringing up a few questions about the article. I was worried at first that no comments would be left at all and people didn't get my paradigm.
Like some of the others have suggested there is no activation on the initiator (customer) queue because I wanted to keep the article as simple as possible whilst explaining what is a very in depth topic and like mishaluba mentioned you get a chance to query a queue before all it's entries are popped.
Regarding your technical challenge you have to ascertain your functional requirements and constraints. Essentially what you are asking is can I use SB to replace a synchronous process with a asynchronous process and the answer is always yes, regardless of the tech you use. But I think want Craig and other members are suggesting to you, is that changing to asynchronous may not bring you closer to the tech solution that satisfies your functional goals.
An example of how SB may be used in a SOA is when you have a highly scaled distributed database architecture. Imagine you have a database in London and one in New York. Your application wants to run a fairly complex query that may take up to 4 seconds, the query needs to run against the London and New York servers. If you implemented a synchronous process your total query time would be 8 seconds because you'll have to wait for the london query to finish first before you can run the NY query. 8 seconds to a user is an eternity. So if you use asynchronous processes you can satisfy the same queries in 4 seconds as they will run in parallel. However if you run the same queries in parallel on the same machine it could end up taking longer than 8 seconds as they fight it out for resources.
I would suggest defining your functional goals and then posting that information online so the sqlservercentral community can give you the best advice.
May 19, 2011 at 2:00 am
Thank you Bob for bringing up a few questions about the article. I was worried at first that no comments would be left at all and people didn't get my paradigm.
Like some of the others have suggested there is no activation on the initiator (customer) queue because I wanted to keep the article as simple as possible whilst explaining what is a very in depth topic and like mishaluba mentioned you get a chance to query a queue before all it's entries are popped.
Regarding your technical challenge you have to ascertain your functional requirements and constraints. Essentially what you are asking is can I use SB to replace a synchronous process with a asynchronous process and the answer is always yes, regardless of the tech you use. But I think want Craig and other members are suggesting to you, is that changing to asynchronous may not bring you closer to the tech solution that satisfies your functional goals.
An example of how SB may be used in a SOA is when you have a highly scaled distributed database architecture. Imagine you have a database in London and one in New York. Your application wants to run a fairly complex query that may take up to 4 seconds, the query needs to run against the London and New York servers. If you implemented a synchronous process your total query time would be 8 seconds because you'll have to wait for the london query to finish first before you can run the NY query. 8 seconds to a user is an eternity. So if you use asynchronous processes you can satisfy the same queries in 4 seconds as they will run in parallel. However if you run the same queries in parallel on the same machine it could end up taking longer than 8 seconds as they fight it out for resources.
I would suggest defining your functional goals and then posting that information online so the sqlservercentral community can give you the best advice.
December 5, 2011 at 4:42 am
Just a quick stupid one!
Ive set this up in its own MyAppServiceBrokerDB and it works fine for local stored procs.
How do i execute sp's in a different database? (both dbs are on the same box)
(Ive been asked to customise a COTS product and dont want to turn on service broker on that db but i do have some bespoke sps to load some batch data.)
I keep getting
The Execute permission was denied on the object 'sp_name', database....
Ive granted execute on the destination sp to a shared user but to no avail.
I just cant see the wood for the trees here as its not a remote call....
Any help is greatly appreciated.
December 5, 2011 at 9:36 am
wesley.boyd (12/5/2011)
Just a quick stupid one!Ive set this up in its own MyAppServiceBrokerDB and it works fine for local stored procs.
How do i execute sp's in a different database? (both dbs are on the same box)
Create a local proc that is nothing more than a redirector to the one in the other db.
The Execute permission was denied on the object 'sp_name', database....
This has to do with the login associated with the connection for the service broker. You may have similar problems using the local redirector depending on your cross-database ownership chaining settings.
Ive granted execute on the destination sp to a shared user but to no avail.
This will only matter if you do it to the service broker account.
I just cant see the wood for the trees here as its not a remote call....
Actually, it is, just on the same instance. Ownership chaining and the like only work if you turn them on. You don't need the MSDTC though which is what most folks think of when dealing with remote calls. Just a matter of perspective I guess.
How are you currently doing security? Certificates? If so check out this article:
If not, you need to reference this: http://msdn.microsoft.com/en-us/library/ms166036.aspx
For a conversation that uses full security, the connection on each side of the conversation acts with the permissions of the user that is specified in the remote service binding. For example, if a remote service binding associates the service name InventoryService with the user InventoryServiceRemoteUser, SQL Server uses the security context for InventoryServiceRemoteUser to put messages for the InventoryService application on the queue for the destination service.
Under most circumstances though you'll want to work with certificates when dealing with service broker security to keep yourself sane. However, look into CREATE ROUTE (if memory serves) for determining who the owner is.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply