Thursday, 16 February 2017

Issue while connecting On Premise BizTalk Server to Azure SQL DB

While trying to connect On-Promise BizTalk Server to Azure SQL database you may encounter an error message like below:

The adapter failed to transmit message going to send port "xxxxxxxxx" with URL "mssql://xxxxxxxxxxx". It will be retransmitted after the retry interval specified for this Send Port. Details:"System.ArgumentException: Value does not fall within the expected range.


Resolution:


Please make sure that on your WCF-Custom send port Transport Properties under Binding Tab you have the properties; EnableBizTalkCompatibilityMode set to true and AmbientTransaction setting set to false. The latter needs to be false as with true the local transaction will be escalated to Microsoft Distributed Coordinator (MSDTC).  According Handling Transactions in Windows Azure SQL Database written by Wayne Walter Berry:

Windows Azure SQL Database SQL Database does not support distributed transactions, which are transactions that multiple transaction managers (multiple resources). This means that Windows Azure SQL Database SQL doesn’t allow Microsoft Distributed Transaction Coordinator (MSDTC) to delegate distributed transaction handling. Because of this you can’t use ADO.NET or MSDTC to commit or rollback a single transaction that spans across multiple Windows Azure SQL Databases or a combination of Windows Azure SQL Database and an on-premise SQL Server.

Hope this helps!

1 comment: