Monday, June 29, 2015

How To access multiple SQL Server databases without MS DTC

In one of my .NET projects, we had the following situation.
  • We wanted to access multiple SQL Server databases hosted on the same SQL Server instance 
  • These databases had to be accessed via EntityFramework (EF)
  • And from a single transaction.
Sounds simple enough?  

Only problem is, as soon as we use two or more connection objects in the same scope, the transaction is elevated to a distributed transaction (DT).  Such transactions are handled by Microsoft Distributed Transaction Coordinator (MS DTC).

All is well, if you really need DT.  But in our setup, we were sure that all these databases will be hosted on a single SQL Server instance.  I really wanted to avoid elevating the transaction to DT, obviously wanted to avoid the performance penalty.

I went looking for answers, on how to achieve this, after some searching and hacking, I was able to achieve this (here is the SO post that explains the solution).  So here's how its done!

How Do They Do It!

It turned out to be pretty simple in the end.  All that is needed is to make sure that we do not instantiate more than one DbConnection objects in one scope!  

Well, then the key question is, How do we use the same DbConnection object, to talk to two different EF contexts - which access two different databases?  

Let me explain this with an example:
  • Say that you need two EF context instances
  • EF1 connects to DB1 and EF2 connects to DB2.
  • Pseudo code that will elevate the transaction to DT looks like this
  • To avoid DT all we need to do is, avoid EF2 from creating its own connection, instead use the Connection instance created by EF1 in someway.
  • We can get hold of the DBConnection instance from EF1
  • Change the database using the ChangeDatabase method
  • And use this DBConnection instance to build the instance of EF2
  • Updated pseudo code looks like this

That's about it!  A simple cheap trick to avoid paying the performance penalty of MS DTC!
Have some Fun!