Lesson Learned #173: MSDTC on server 'xxxxx' is unavailable

Published May 21 2021 11:20 AM 1,452 Views

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

 

 

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!

%3CLINGO-SUB%20id%3D%22lingo-sub-2376529%22%20slang%3D%22en-US%22%3ELesson%20Learned%20%23173%3A%20MSDTC%20on%20server%20'xxxxx'%20is%20unavailable%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2376529%22%20slang%3D%22en-US%22%3E%3CP%3EToday%2C%20I%20worked%20on%20a%20very%20interesting%20service%20request%20that%20our%20customer%20is%20facing%20the%20following%20error%20message%20%22MSDTC%20on%20server%20'xxxxx'%20is%20unavailable%22%20using%20a%20distributed%20transaction%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fazure-sql%2Fdatabase%2Felastic-transactions-overview%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EDistributed%20transactions%20across%20cloud%20databases%20(preview)%20-%20Azure%20SQL%20Database%20%7C%20Microsoft%20Docs%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFollowing%20I%20would%20like%20to%20explain%20what%20is%20happening.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3EScenario%20based%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3EBased%20on%20the%20article%20that%20we%20have%3A%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fazure-sql%2Fdatabase%2Felastic-transactions-overview%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EDistributed%20transactions%20across%20cloud%20databases%20(preview)%20-%20Azure%20SQL%20Database%20%7C%20Microsoft%20Docs%3C%2FA%3E%26nbsp%3Bwe%20have%20a%20transaction%20scope%20and%20two%20connections%20against%20two%20different%2Fsame%20databases.%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3EIn%20the%20first%20scenario%2C%3C%2FSTRONG%3E%20if%20the%20first%20connection%20is%20a%20readonly%20(Using%20ApplicationIntent%3DReadonly%20connecting%20to%20ReadScale%20out%2C%20the%20error%20message%20that%20your%20are%20going%20to%20receive%20is%3A%26nbsp%3B%26nbsp%3B%22Exception%20has%20been%20thrown%20by%20the%20target%20of%20an%20invocation.%22%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3EIn%20the%20second%20scenario%2C%3C%2FSTRONG%3E%20if%20the%20first%20connection%20is%20a%20readwrite%20and%20the%20second%20is%20readonly%20(Using%20ApplicationIntent%3DReadonly%20connecting%20to%20ReadScale%20out%2C%20the%20error%20message%20that%20your%20are%20going%20to%20receive%20is%3A%26nbsp%3B%20MSDTC%20on%20server%20'xxxxxx'%20is%20unavailable%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20both%20situations%2C%20the%20issue%20is%20related%20that%20%3CSTRONG%3Eit%20is%20needed%20to%20have%20a%20readwrite%20mode%3C%2FSTRONG%3E%20in%20both%20connections%20that%20the%20distribution%20transaction%20will%20take%20effect.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20a%20workaround%2C%20if%20you%20are%20not%20able%20to%20modify%20the%20code%2C%20you%20could%20disable%20the%20ReadScale%20out%20of%20your%20Azure%20SQL%20DB.%20You%20could%20see%20this%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fazure-database-support-blog%2Flesson-learned-172-applicationintent-readonly-in-general-purpose%2Fba-p%2F2375941%22%20target%3D%22_self%22%3Elink%3C%2FA%3E%20to%20obtain%20more%20details.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EEnjoy!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-2376529%22%20slang%3D%22en-US%22%3E%3CP%3EToday%2C%20I%20worked%20on%20a%20very%20interesting%20service%20request%20that%20our%20customer%20is%20facing%20the%20following%20error%20message%20%22MSDTC%20on%20server%20'xxxxx'%20is%20unavailable%22%20using%20a%20distributed%20transaction%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fazure-sql%2Fdatabase%2Felastic-transactions-overview%22%20rel%3D%22noopener%20noreferrer%22%20target%3D%22_blank%22%3EDistributed%20transactions%20across%20cloud%20databases%20(preview)%20-%20Azure%20SQL%20Database%20%7C%20Microsoft%20Docs%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFollowing%20I%20would%20like%20to%20explain%20what%20is%20happening.%3C%2FP%3E%3C%2FLINGO-TEASER%3E
Version history
Last update:
‎May 21 2021 11:20 AM
Updated by: