I think the answer is no unless you are prepared to accept the risk of duplicates and/or lost messages. Let me explain why below. This is a summary of conversations that I have had with my colleagues and external parties about this topic over the last two months and I have at times copied directly from them without attribution below. I am concerned about this because I think this is a constraint for BizTalk Server solution designs going forward.
The WCF-SQL adapter has been the standard way to connect BizTalk applications to SQL Server since ca 2009.
A new feature was introduced in SQL 2012 called SQL Server Always On. SQL Server AlwaysOn is the new comprehensive high availability and disaster recovery solution in SQL Server 2012. Using AlwaysOn, you can increase application availability and get a better return on your hardware investments through a simplified high availability (HA) deployment and management experience.
When BizTalk Server and SQL Server are installed on separate computers and you are using the WCF-SQL adapter., it is recommended that you use a Distributed Transaction Coordinator (MS DTC) to handle the transactions between the computers.
SQL Server 2012 and 2014 AlwaysOn feature does not support MSDTC transactions. “Cross-database transactions and distributed transactions are not supported by AlwaysOn Availability Groups or by database mirroring. This is because transaction atomicity/integrity cannot be guaranteed for the following reasons:
- For cross-database transactions: Each database commits independently. Therefore, even for databases in a single availability group, a failover could occur after one database commits a transaction but before the other database does. For database mirroring this issue is compounded because after a failover, the mirrored database is typically on a different server instance from the other database, and even if both databases are mirrored between the same two partners, there is no guarantee that both databases will fail over at the same time.
- For distributed transactions: After a failover, the new principal server/primary replica is unable to connect to the distributed transaction coordinator on the previous principal server/primary replica. Therefore, the new principal server/primary replica cannot obtain the transaction status.
Also adding a generic MSDTC resource on cluster won’t help because SQL AlwaysOn Availability Groups does not rely on share disk which is required by MSDTC.”
Wow! That means if I enable MSDTC on a SQL2012 database that uses availability groups it does not work. If MSDTC cannot be turned on then the WCF-SQL adapter cannot use MSDTC ( i.e. you must set the useAmbientTransaction to false.). How safe is that? Most of the experts say don’t do it. If you do do it you, I think you must have logic that is able to handle duplicates so you can always send again. In summary you must ensure that your SQL transaction is idempotent and I don’t know how to test if this is safe.
At the end of the day I think your safest choice is not use the SQL Availability On with the BizTalk WCF-SQL adapter.
In summary I think your choices are (in order of preference):
– Disable AlwaysOn Availability Groups / Mirroring on SQL server if you need to connect to this SQL server which has this enabled
– Disable transactions and implement logic to be able to handle duplicates .
– Disable transactions and handle the duplicates or lost messages with custom logic (e.g. Send twice and compare and implement error handling). You need to write your own DTC handling this which is probably very complicated.
– Disable transactions and live with risk of duplicates or lost messages without handling duplicates.
What do you think?
