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:
- Upgrading SQL Server to 2016 & AGs and/or
- 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.