Today, I worked on a very interesting service request that our customer is facing the following error message "MSDTC on server 'xxxxx' is unavailable" using a distributed transaction Distributed transactions across cloud databases (preview) - Azure SQL Database | Microsoft Docs
Following I would like to explain what is happening.
Scenario based
- Based on the article that we have: Distributed transactions across cloud databases (preview) - Azure SQL Database | Microsoft Docs we have a transaction scope and two connections against two different/same databases.
In the first scenario, if the first connection is a readonly (Using ApplicationIntent=Readonly connecting to ReadScale out, the error message that your are going to receive is: "Exception has been thrown by the target of an invocation."
In the second scenario, if the first connection is a readwrite and the second is readonly (Using ApplicationIntent=Readonly connecting to ReadScale out, the error message that your are going to receive is: MSDTC on server 'xxxxxx' is unavailable
In both situations, the issue is related that it is needed to have a readwrite mode in both connections that the distribution transaction will take effect.
As a workaround, if you are not able to modify the code, you could disable the ReadScale out of your Azure SQL DB. You could see this link to obtain more details.
Enjoy!
Microsoft
