SQL Transport with multiple databases would benefit from store and forward/local transmission queue

[Erik Steinebach]

We are developing a new application using nServicebus in an enterprise environment. The application will use multiple databases for storing different commands and events, using the newly supported multi database functionality in nServicebus SQL transport. Now I’m looking into the use of distributed transactions and where this could cause performance problems. This led me to the conclusion that a store and forward mechanism with a local transmission queue could (in complex scenario’s) be more optimal with SQL transport. SQL Server Service Broker Queues could be used to accomplish this. I understand that the use of service broker functionality would increase complexity, but for complex enterprise situation it could greatly improve performance and availability.

I included an example scenario to show this: An external web service is called to initiate the flow, a command is send to a nServicebus queue. A worker processes the command and sends an event to 2 subscribers.

Current SQL Transport (with multi database support):

The distributed transaction could under high load cause problems. Depending on the functionality during processing this transaction could take some time, during which MSDTC is involved.

SQL Transport with store and forward / a local delivery queue (based on SQL Server Service Broker):

Here we would only need native transactions, because the transfer to another database would be done by SQL Server Service Broker. The transaction would be as long, but would only be a native database transaction.

Don't get me wrong, the first scenario will work, but we would like to avoid DTC if possible because of the risks. I'm wondering if other people have run in these kind of scenario's and have found other solutions or have implemented this and found/didn't find any problems? Is there any interest in supporting Service Broker queues as transport alongside SQL transport? 

Thanks,
Erik 

 

[Jimmy Bogard]

Service Broker - yes, we've had a couple of clients inquire about it. Especially in cases where they have existing service broker implementation. In fact, it might be simpler since you could rely on SQL service broker itself as a broker. If you watch any of the Pat Helland videos, he talks about designing Service Broker for this exact scenario - avoiding DTC.

--
You received this message because you are subscribed to the Google Groups "Particular Software" group.
To unsubscribe from this group and stop receiving emails from it, send an email to particularsoftw...@googlegroups.com.
To post to this group, send email to particula...@googlegroups.com.
Visit this group at http://groups.google.com/group/particularsoftware.
To view this discussion on the web visit https://groups.google.com/d/msgid/particularsoftware/a6ade526-fde9-4ac2-8674-61c5f904c7c3%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Marc Wils]

One upvote for this feature request!

Beside the optimization of not requiring DTC transactions, there's the wish to build autonomous services or business components. Without a local delivery queue, the NServiceBus component has a runtime dependency on all the other SQL Server queues. If I publish an event with 5 subscribers, each with its own database, all those databases should be up and running before the message can be handled. So much for loosely coupled...

With SQL Server Broker Queues the transmission of a message to a remote queue is handled by SQL Server and runs on the background. It has no impact on the message handlers.

Kind regards
Marc

(colleague of Erik working on the same project)

Op dinsdag 25 maart 2014 14:03:33 UTC+1 schreef Erik Steinebach:

[andreas.ohlund]

This is indeed a very interesting approach. Since the "forward" part using SSSB is quite disconnected from NServiceBus we can probably start with guidance/sample scripts to setup the forwarding between the different database. Is there any changes in the NSB Core/SqlTransport that would make this easier? 

Cheers,

Andreas

[Adam Tybor]

I will just add from an operations point of view SSSB sucks.  It can't handle poison messages and errors, the whole transport stops on the first error so you will need some creative ops strategies to deal with any hiccups.

Also I would be careful of your investment here as well.  Its a great solution for on-premise but if you ever move to something more PaaS like either in Azure or AWS you have no choice but to manage your own SQL Infrastructure with IaaS and VNets.  Neither Azure nor AWS support SSSB and my understanding is neither have any plans to in their regular offerings.

Adam

[Marc Wils]

I agree that SSSB introduces some extra complexity. You'll also need extra monitoring on the SSSB queues to verify that no messages get stuck on transport. I agree that poison message handling can be a handful. Starting from SQL Server 2008R2 it 's possible to disable poison handling so the queue doesn't get disabled. But there's no built-in mechanism for delayed retries or something like that. You have to build something custom for that. But I don't see why that couldn't be part of NSerivceBus.

If not SSSB, then what would you choose?

In my opinion, the choice of transport is always related to the platform. On Azure you'll go for the Windows Azure Transport. In our case, we run in an on-premise platform with SQL Server. So the SQL Transport is a logical choice.

Regardless of the platform or choice of transport, you always want the same things:
- Loosely coupled services
- Scalability
- ...

Those are the selling points of NServiceBus and I believe every transport should give you those features. But that's my personal opinion...

- Marc