October 25, 2011 at 5:16 pm
Hi,
I have a setup where I'm using triggers to capture data changes in a few tables in one database, and send those changes over to another database via service broker. The basic setup works fine, except that occasionally changes that I know had to have happened in a certain order in the source database are arriving in a different order at the target database. For example, let's say I was capturing changes to people, orgs, and the relationships between people and orgs. A user is adding a new person in the source database, then linking that new person to an org. On the target database I'm sometimes getting the message to link the person to an org before I get the message adding the person, even though that's not possible. Any idea how something like this could happen?
FWIW, both databases are SQL Server 2008.
October 25, 2011 at 5:55 pm
Different queues will not operate in tandem. The broker overall doesn't operate by timestamps, but by queues. My guess is each of these structures have a different queue on your source system.
The only way to avoid hierarchal interruptions is to use conversations to move data in the correct hierarchal order.
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
October 25, 2011 at 7:35 pm
No, no different queues. All messages are going into the same service in the source database, to be delivered to the same service on the target database.
Let me be more specific:
1) The app calls a proc to add a new person.
2) The proc inserts a new record in the person table.
3) A trigger on the person table sees the insert and sends a message via service X to service Y to add the new person.
4) The app calls a proc to link the new person to an org.
5) The proc inserts a new record in an xref table linking the person to the org.
6) A trigger on the xref table sees the insert and sends a message via service X to service Y to add the new link.
7) When the messages arrive in the queue for service Y in the target database, sometimes the message to add the new person arrives after the message to add the link between the new person and an org.
Clearly the message to add the link couldn't be sent before the message to add the user, so given that service broker guarantees that messages are delivered in the order they are sent, I'm a bit nonplussed as to how they are arriving in a different order than it seems they logically should.
October 26, 2011 at 10:23 am
Are you sending the messages within the same conversation? I'm pretty sure that only messages within the same conversation are guaranteed to be received in the order they were sent.
October 26, 2011 at 11:41 am
notoriousdba (10/25/2011)
No, no different queues. All messages are going into the same service in the source database, to be delivered to the same service on the target database.Let me be more specific:
1) The app calls a proc to add a new person.
2) The proc inserts a new record in the person table.
3) A trigger on the person table sees the insert and sends a message via service X to service Y to add the new person.
4) The app calls a proc to link the new person to an org.
5) The proc inserts a new record in an xref table linking the person to the org.
6) A trigger on the xref table sees the insert and sends a message via service X to service Y to add the new link.
7) When the messages arrive in the queue for service Y in the target database, sometimes the message to add the new person arrives after the message to add the link between the new person and an org.
Clearly the message to add the link couldn't be sent before the message to add the user, so given that service broker guarantees that messages are delivered in the order they are sent, I'm a bit nonplussed as to how they are arriving in a different order than it seems they logically should.
1. Queue processing is guaranteed to be in order by conversation on the receiving side. If you have 100 messages, each with their own conversation, you have not guarantee as to the order processed on the receiving side.
2. If this is using 1 conversation we need much more information to determine cause. i.e. Queue definitions, sender code, receiver code, etc.
October 27, 2011 at 1:54 pm
venoym (10/26/2011)
1. Queue processing is guaranteed to be in order by conversation on the receiving side. If you have 100 messages, each with their own conversation, you have not guarantee as to the order processed on the receiving side.
To confirm this:
http://msdn.microsoft.com/en-us/library/ms166053.aspx
Applications receive messages from the queue for the service. For each conversation, queues return messages in the order in which the sender sent the message. All the messages returned from a single receive operation are part of conversations that belong to one conversation group. In effect, a queue holds sets of related messages, one set for each conversation group.
notoriousdba (10/25/2011)
1) The app calls a proc to add a new person.2) The proc inserts a new record in the person table.
3) A trigger on the person table sees the insert and sends a message via service X to service Y to add the new person.
4) The app calls a proc to link the new person to an org.
5) The proc inserts a new record in an xref table linking the person to the org.
6) A trigger on the xref table sees the insert and sends a message via service X to service Y to add the new link.
7) When the messages arrive in the queue for service Y in the target database, sometimes the message to add the new person arrives after the message to add the link between the new person and an org.
Add in a handling message or a wait. What's a handling message...
In the contract, include two new message types. Have one be a non-exist Person Request. That request on the conversation will be sent back to the originating queue. On pickup of that in the conversation (which will include the original request for the association), it will resend both the necessary Person data AND the association data, allowing the Person to be built...
... and if you do that, you'll have to decide on the option of removing the Person send at all or not. If there's no association, do you need the Person?
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
November 3, 2011 at 10:20 am
Craig,
Ah, that's an excellent idea. I should have thought of that. Thanks!
BTW, no, the messages being sent aren't in the same conversation, but what I found is that this is happening because the app calling the procs has them wrapped in its own transaction. Without the outer transaction surrounding the two procedure calls, things work the way I expect them to. I'm trying to make this whole setup less brittle, so that things like this wouldn't have any effect. I tried forcing all the messages to be part of the same conversation, and that worked for a while, but has broken down for reasons I haven't figured out yet.
Clearly this process isn't as simple as I had hoped, and I'm probably going to have to do some major re-writing. One option I'm looking at is based on the "Real-time Data Integration" sample code in the Microsoft SQL Server Community Samples: Service Broker[/url] project on codeplex. That code has the triggers on the tables simply send messages that some data has changed, not send the actual data itself, then the receiving service uses Change Tracking to find any changed data and send it along to the target system. I'd hoped to not have to build something that elaborate, but it looks like I don't have much choice.
-Chris
November 15, 2011 at 4:20 pm
notoriousdba (11/3/2011)
Craig,Ah, that's an excellent idea. I should have thought of that. Thanks!
My pleasure. Sorry it's taken me a while to get back to this, was down ill for a bit.
BTW, no, the messages being sent aren't in the same conversation, but what I found is that this is happening because the app calling the procs has them wrapped in its own transaction. Without the outer transaction surrounding the two procedure calls, things work the way I expect them to.
Has... what... wrapped in the transaction? Sorry, you lost me there.
I'm trying to make this whole setup less brittle, so that things like this wouldn't have any effect. I tried forcing all the messages to be part of the same conversation, and that worked for a while, but has broken down for reasons I haven't figured out yet.
That can be tough to do, and you usually want broker to handle the conversation associations, but you can pull it off if you're careful. We'd have to go through your process with a fine toothed comb though to find the break.
Clearly this process isn't as simple as I had hoped, and I'm probably going to have to do some major re-writing. ... I'd hoped to not have to build something that elaborate, but it looks like I don't have much choice.
-Chris
There's one other choice... turn off the foreign key association and let the database be eventually consistent.
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 - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply