2

My production environment uses MS DTC and SQL Server 2014. I must have an HA solution such as Mirroring or AG in place. (Not FCI).

I am trying to explain to my team that MS DTC is not supported by SQL Server with Mirroring / AG and what could go wrong. But all the examples I am seeing online are for cross database scenarios, which doesn't apply to us.

I am looking for a worst case scenario & likelihood of its occurrence. Ideally something that I could actually demonstrate to them. ie: creating a transaction that uses MS DTC, triggering a failover, showing what happens.

As a DBA, I would like to be able to explain to the business what are the real world implications of this lack of support for MS DTC from Microsoft, so that they can decide if the risk is worth the cost of:

  1. Upgrading SQL Server to 2016 & AGs and/or
  2. Changing how their application relies on MS DTC (I am told this is impossible).

I don't want to just parrot "Not supported" to them because that's unacceptable.

Any help appreciated.

  • 1
    Have you checked Starting Distributed Transactions or use DTCTester Tool – Kin Shah Jan 11 '18 at 16:02
  • I have used DTCTester for troubleshooting but I don't think I can use it to simulate data loss. Not sure if this could cause an outright database corruption (worst case scenario) which is what I would love to demo if at all possible (if that's a possibility ofcourse). Thanks for the links. I will try the first one. – Dina Jan 11 '18 at 16:12
  • I think the only issues were with cross-database transactions. "In SQL Server 2016 and before, cross-database transactions within the same SQL Server instance are not supported for availability groups. This means that no two databases in a cross-database transaction may be hosted by the same SQL Server instance. This is true even if those databases are part of the same availability group." docs.microsoft.com/en-us/sql/database-engine/… – Tony Hinkle Jan 11 '18 at 16:47
  • The documentation is absolutely not clear. Initially I thought Mirroring supports DTC as well for SQL 2014. – Dina Jan 11 '18 at 16:49
0

Here is the most complete official-ish statement available about the risks and consequences of using DTC in an unsupported configuration with AGs:

This means you could test a cross database transaction, force failover and everything work fine. Test again it fails. It could be as simple as the controlling DBID changes on the secondary and the outcome of the cross-database transaction can’t be properly determined.

For Availability Groups you should avoid the use of DTC and cross-database transactions. Relying or using them could manifest as a problem with the database recovery activities and that is certainly not what you want in a HADR environment.

https://blogs.msdn.microsoft.com/alwaysonpro/2014/01/06/not-supported-ags-with-dtccross-database-transactions/

Note that all use of DTC with AGs was unsupported in SQL 2014 and before. In SQL 2016 support was added for DTC transactions not involving multiple databases on the same instance. In SQL 2017 that restriction was removed.

  • It's still not really enough for me in terms of explaining risks to business in a way that means something to them, especially regarding mirroring. "Node 2 can’t determine the proper outcome of the pending DTC transaction." then what? Could this cause a deadlock? Will I lose data for that transaction? Could it cause db corruption? – Dina Jan 11 '18 at 18:52
  • It's not enough that your HA solution wouldn't actually be highly available? – David Browne - Microsoft Jan 11 '18 at 18:54
  • HA isn't highly available to begin with :P ie: a mirroring failover without a maintenance outage, still has rolled back transactions & therefore some data loss. So its all about understand & mitigating risks here. – Dina Jan 11 '18 at 18:57
  • Well that's all the information there is. It's known to cause problems, and beyond that was not exhaustively tested. – David Browne - Microsoft Jan 11 '18 at 19:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.