Why is MSDTC not supported when using SQL Server Mirroring & Automatic Failover?

I have an application that I would like to have support SQL Server Mirroring. However, the architecture is currently such that multiple WCF services and DB connections will be enlisted in a single MSDTC transactions, and Microsoft states that MSDTC is not supported when using Mirroring.

Their explanation is not terribly informative:

A similar scenario can occur when you use database mirroring with MS DTC transactions. For example, the new principal server contacts the MS DTC after a failover. However, the MS DTC has no knowledge of the new principal server. Therefore, the MS DTC stops any transactions that are in the "preparing to commit" phase, even though the transactions are considered committed in other databases.

What I am having a problem understanding is the last sentence. How is this any different than if the DB server was not mirrored, and just died at that same point in time? Can someone explain that to me? I need to be able to explain this to others in my organization (as well as customers), but I don't understand why MSDTC can properly rollback/compensate in one scenario, but it cannot if one of the participants is a mirrored SQL server (in Full Safety mode).

MSDTC is no aware of mirroring. So when it enrolls a resource manager in a distributed transaction it will know that RM by its name, say Server A. After a fail over occurs, the log will tell the new principal 'go contact DTC and see what is the status of transaction T'. The new principal, named Server B, goes to DTC and says 'I am server B, what is the result of transaction T?' and the DTC will tell him 'Go away, I don't know you, you are not enrolled in transaction T'. This is what the KB article describes too:

After a failover, the new principal server cannot connect to the MS DTC of the previous principal server that uses the same resource ID. Therefore, the new principal server cannot obtain the transaction status

You are asking "How is this any different than if the DB server was not mirrored, and just died at that same point in time?". The difference is that if this would had occur, then when the database is recovered it will be recovered on the same server and this server can contact the DTC and ask it to rollback the distributed transaction in which it was enrolled.

Would MSDTC have rolled back the other resources? Or does that distributed transaction wait around until the original server comes back? What does the mirrored partner do when MSDTC shuns it (roll back? commit? throw an error?)

Bryan Batchelder 2010-07-12 21:14:39

In two phase commit there is a state when DTC cannot take action: after it notified some RMs that is OK to commit. In that moment, the DTC cannot change its mind about the transaction outcome, and this is the problem moment for a fail over to occur. If the fail over occurs before this moment then is simple: just ask all the other RMs to roll back, no big deal.

Remus Rusanu 2010-07-12 21:20:17

So let's say there are 3 RMs involved. We get to the phase you mentioned, and RM1 is told to commit. RM2 is our SQL Server, and since it has failed-over it cannot be reached. What happens to RM3? Is he told to commit? How long does this distributed transaction hang around if the original server never comes back to tell it to rollback? Did the transaction make it to the mirror partner, or not? If there is somewhere I can go to learn about this in more detail, please direct me. I have not been able to find much on this topic on my own. Thanks for your help, too!

Bryan Batchelder 2010-07-12 21:34:20

Say you have just 2 RMs. RM1 is send 'prepare to commit', it answers 'ready'. RM2 is sent 'prepare to commit' it answers 'ready'. Transaction is prepared, phase 1 is success. RMs cannot change their mind after have answered 'ready'. DTC proceeds and decides the transaction is succeeded, starts notifying participants to commit. IT sends RM1 'commit', RM1 does commit. Now fail-over occurs. DTC attempts to contact RM2, but is offline. Local transaction on RM2 has failed over to RM3, not enlisted in DTC. Now you are in the limbo state, DTC cannot rollback, RM3 cannot enroll.

Remus Rusanu 2010-07-12 21:39:09

ansaurus

tags:

views:

answers:

Why is MSDTC not supported when using SQL Server Mirroring & Automatic Failover?

related questions