What server property makes TransactionScope escalating to MSDTC?

Question

In our application we're using TransactionScope's. We're aiming to not use the MSDTC service because it's a lot slower than lightweight transactions.

using (var transactionScope = new TransactionScope())
{
  ...
  transactionScope.Complete();
}

While connected to the test database we need no MSDTC but the exact same code, executed on the same machine, escalates to use MSDTC when connected to the production or development database.

The development database is not clustered and isn't a named instance.

All are SQL Server 2008 (10.50.2500) and the connection strings are exactly the same except for the hostnames.

It seems there has to be some difference in the setup of the databases. Any ideas on what that could be?

EDIT

The transaction was there to protect inserts in two different tables. I noticed that there was also two calls that resulted in two selects in there. There was no need for these to be in the transaction so I moved them outside of it. The need for MSDTC then disappeared in all three databases. Problem solved but without really knowing what caused it.

Answer 1

Here are some of the things you can try to get it to work:

• Compare Enlist=false in your connection strings
• Compare MultipleActiveResultSets=true in your connection strings
• User instance databases appear to work differently for some reason - we couldn't get it to work consistently with them - use full databases all of the time
• You can reuse the same connection (close/reopen), or use more than one connection in a TransactionScope, but you cannot use more than one connection at the same time. You CAN however keep a ThreadStatic connection and pass that around (or, as we did, use ThreadLocal<> in a db wrapper class) - this solved a concurrency problem for us in ASP.NET which was causing unnecessary escalation
• Don't bother if your db < 2008 or if compatibility level < 100
• When using connection strings, don't use the ConnectionString property of another connection to create your new connection - it may not be the same as the original, which will cause escalation
• Keep your connections open only for as long as you need to, close them at your earliest opportunity.

Stuff I found useful:

• A ConnectionScope class gave me some ideas: Link
• There's more to TransactionScope than the default ctor! Link
• Note that there was nothing new I could find that would be different between 2008 and 2008 R2 - here is a list of stuff that was new in that release: http://msdn.microsoft.com/en-us/library/bb500435%28SQL.105%29.aspx

Answer 2

In this case there are multiple connections using the same connection string

Irrelevant. Second connection = propagation to MSDTC. All you can do is not open multiple connections. Stick to ne connection and it will not propagate to MSDTC. Open a second one - same string, even if the first is closed - and it must propagate as it can not / does not reasign the conenction to the new one. That makes it 2 conenctions which is per definition a distributed transaction.