TransactionScope automatically escalating to MSDTC on some machines?
SQL Server 2008 can use multiple SQLConnection
s in one TransactionScope
without escalating, provided the connections are not open at the same time, which would result in multiple "physical" TCP connections and thus require escalation.
I see some of your developers have SQL Server 2005 and others have SQL Server 2008. Are you sure you have correctly identified which ones are escalating and which not?
The most obvious explanation would be that developers with SQL Server 2008 are the ones that aren't escalating.
The result of my research on the topic:
See Avoid unwanted Escalation to Distributed Transactions
I am still investigating Oracle’s escalation behavior: Do transactions spanning multiple connections to same DB escalate to DTC?
That code will cause an escalation when connecting to 2005.
Check the documentation on MSDN - http://msdn.microsoft.com/en-us/library/ms172070.aspx
Promotable Transactions in SQL Server 2008
In version 2.0 of the .NET Framework and SQL Server 2005, opening a second connection inside a TransactionScope would automatically promote the transaction to a full distributed transaction, even if both connections were using identical connection strings. In this case, a distributed transaction adds unnecessary overhead that decreases performance.
Starting with SQL Server 2008 and version 3.5 of the .NET Framework, local transactions are no longer promoted to distributed transactions if another connection is opened in the transaction after the previous transaction is closed. This requires no changes to your code if you are already using connection pooling and enlisting in transactions.
I can't explain why Dev 3: Windows 7 x64, SQL2005 succeeds and Dev 4: Windows 7 x64 fails. Are you sure that is not the other way round?