How to open multiple sql connections with ADO when handling an nservicebus message

Question

I have a message handler using NServiceBus that needs to execute SQL code on two different databases. The connection strings have different initial catalogs but are otherwise identical.

When the message is picked up, the first sql connection opens successfully but the second sql connection causes the following exception to be thrown when .Open is called.

Network access for Distributed Transaction Manager (MSDTC) has been disabled. Please enable DTC for network access in the security configuration for MSDTC using the Component Services Administrative tool.

We don't use MSDTC.

Here's the code that fails. It will fail on connB.Open()

public void Handle(MyMsgCmd message)
        {
            using (SqlConnection connA = new SqlConnection(myConnectionStringA))
            {
                connA.Open();
            }

            using (SqlConnection connB = new SqlConnection(myConnectionStringB))
            {
                connB.Open();
            }
        }

This same code works perfectly fine when run from a command line application or web application. The exception is only thrown when it's called from NServiceBus.

Each of these connections will successfully open when opened first or when opened by itself but whenever there's a second connection present the second connection will always fail to open with the same exception even when it's known good.

Is there additional configuration needed to open more than one connection in sequence with NServiceBus?

score 0 · Answer 1 · answered Aug 16 '18 at 22:08

It looks like by default NServiceBus wraps each message handler in a transaction and that causes queries to different database connections inside the same message handler to fail unless MSDTC is enabled.

I can disable that with BusConfiguration.Transactions().DoNotWrapHandlersExecutionInATransactionScope()

score 0 · Answer 2 · answered Aug 17 '18 at 10:38

You can find more information on transaction in the NServiceBus documentation.

This isn't related exclusively to NServiceBus, we just provide different ways of connecting to a transport (like MSMQ, Azure Service Bus, etc), a persister and your own database.

But even without NServiceBus, when connecting to two databases, you need either a distributed transaction, or make sure the transaction is not escalated to a distributed transaction. The thing is, without distributed transactions, when one transaction successfully commits, the other transaction might fail. With the result that your two databases are not in-sync or consistent anymore.

If orders in DatabaseA are stored and inventory is tracked in DatabaseB, you might deduct 1 from inventory, but the order might never be stored because the transaction failed. You need to compensate for this yourself without distributed transactions.

THat's not to say distributed transactions are always the way to go. You're probably not using them because your DBA doesn't like them. MSDTC always puts serializable transactions on your data, which have the heaviest locks. The longer you keep them open, the more concurrently running transactions will need to wait. With possibly huge performance issues in your software.

On the other hand, it can be very, very difficult to create compensating transactions. And just think about the fact that DatabaseA might fail, DatabaseB might succeed. But what happens to the message? Is it gone from the queue? Or will it remain in the queue and be processed again? Will DatabaseB succeed again with the possible result of duplicate data?

Luckily you're already using NServiceBus. You might want to check out the Outbox feature that can help solve some of these issues.

How to open multiple sql connections with ADO when handling an nservicebus message

2 Answers2