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

Fire and Forget Solution. Expand / Collapse
Author
Message
Posted Monday, March 25, 2013 9:30 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 9, 2013 8:19 AM
Points: 179, Visits: 751
Fire and Forget Solution

The solution is let the target end the conversation first. The initiator can simply send the message to the target and then continue. The target will receive the message, end the conversation from the target side and this will send an EndDialog message back to the initiator. All we have to do is attach an activated stored procedure to the initiator’s queue, and in this stored procedure we can end the initiator side of the conversation:

How does this work if the initiator is part of a trigger. If it is changed to a SYNCHRONOUS setup. Does the trigger close after the send message is fired or does it have to wait for the full conversation cycle to complete. If so how can this be avoided in a synchronous setup.
Post #1434980
Posted Monday, March 25, 2013 9:50 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 6:59 AM
Points: 1,880, Visits: 3,465
Bobby Glover (3/25/2013)
Fire and Forget Solution

The solution is let the target end the conversation first. The initiator can simply send the message to the target and then continue. The target will receive the message, end the conversation from the target side and this will send an EndDialog message back to the initiator. All we have to do is attach an activated stored procedure to the initiator’s queue, and in this stored procedure we can end the initiator side of the conversation:

How does this work if the initiator is part of a trigger. If it is changed to a SYNCHRONOUS setup. Does the trigger close after the send message is fired or does it have to wait for the full conversation cycle to complete. If so how can this be avoided in a synchronous setup.

1. What you describe is not a fire and forget solution. You describe is a conversation where both sides end the conversation correctly.

2. There is nothing called a synchronous service broker setup. Service broker as asynchronous, unless something changed in SQL Server 2012 that is not documentet in BOL.
Sending a message from a trigger is no different from sending from a sproc or ad-hoc T-SQL. The execution will continue once the message has been placed in the transmission queue. It does not wait for the message to be sent to the target, nor does it wait for the target to respond. You have to handle message processing manually or by using activation procedures.
Post #1434994
Posted Monday, March 25, 2013 10:02 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 9, 2013 8:19 AM
Points: 179, Visits: 751
The first paragraph was a comment online to resolving a fire and forget setup. What I’m trying to do is setup a SYNCHRONOUS process and change it from an ASYNCHRONOUS process F and F.
OK so let me get this straight as this will save me countless hours. If the initiator is part of the trigger. Once it fires the trigger is released and allows the user to continue, ending the transaction service broker then takes over and completes the remainder in the back ground.
This is based on the assumption that the initiator doesn’t end the conversation first.
Post #1434999
Posted Monday, March 25, 2013 11:16 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 6:59 AM
Points: 1,880, Visits: 3,465
Bobby Glover (3/25/2013)
The first paragraph was a comment online to resolving a fire and forget setup. What I’m trying to do is setup a SYNCHRONOUS process and change it from an ASYNCHRONOUS process F and F.
OK so let me get this straight as this will save me countless hours. If the initiator is part of the trigger. Once it fires the trigger is released and allows the user to continue, ending the transaction service broker then takes over and completes the remainder in the back ground.
This is based on the assumption that the initiator doesn’t end the conversation first.

You got it :)
The only thing the trigger will have to wait for is placing the message on the transmission queue.
The message will not be sent and received until your transaction commits, so it is a very lightweigh operation.

We actually use this trigger pattern for two relatively heavy processes in one of our production environments. One of them is to maintain a de-normalized table of tables related to documents in our in-house developed document management system. This table has a full text index that we use for full text queries.
The other thing we use it for is to maintain row-level ACL inheritance in the same document management system.

Both of the service broker "installations" have been running without problems for years now.
Post #1435068
Posted Monday, March 25, 2013 1:16 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 9, 2013 8:19 AM
Points: 179, Visits: 751
Thanks, that will save me hours sitting at my desk looking totallyt p*ssed off.
At last there is light at the end of the tunnel.

Thanks again.
Post #1435113
Posted Tuesday, March 26, 2013 7:40 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 9, 2013 8:19 AM
Points: 179, Visits: 751
Of course Service Broker is always ASYNC. It wouldn't be a queueing system if it wasn't. That little fact did slip me. Thanks
Post #1435483
Posted Wednesday, March 27, 2013 4:25 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 9, 2013 8:19 AM
Points: 179, Visits: 751
Is it best to have 2 queues one for the initiator and one for the target ?
Post #1435816
Posted Wednesday, March 27, 2013 5:17 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 6:59 AM
Points: 1,880, Visits: 3,465
I prefer two queued, even though there is nothing that stops you from using the same queje for multiple services

The reason I prefer having separate queues for each service is the activation procedures. I like to have target handling and initiator handling in separate procedures, and that of cpurse requires separate queues.
Post #1435831
Posted Wednesday, March 27, 2013 8:28 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 9, 2013 8:19 AM
Points: 179, Visits: 751
Thanks, do you mind if a bug you a little more on this one. There is very little on the NET that is useful in this area. Not specific enough.

I'm getting closer to my my goal with every one of your replies.
Post #1435939
Posted Wednesday, March 27, 2013 11:14 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 6:59 AM
Points: 1,880, Visits: 3,465
Feel free to ask

It's easter holiday here in Norway now, and I'm up in the mpintain where the internet connection is very limited, so I can't guarantee an answer before I'm back in civilization.
Post #1436052
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse