BizTalk Server 2013 R2 and earlier versions are not supported with SQL Always on

  • Article

 

At the time of writing this post  (October 2014), BizTalk Server 2013 R2 and earlier product versions are not supported with the feature SQL Server Always ON Triste enabled.

The only version supported as of today (10/11/2017)  is BizTalk Server 2016 (even on Windows Azure running as IAAS)

 

SQL Server Always ON

The AlwaysOn Availability Groups feature is a high-availability and disaster-recovery solution that provides an enterprise-level alternative to database mirroring. Introduced in SQL Server 2012, AlwaysOn Availability Groups maximizes the availability of a set of user databases for an enterprise. An availability group supports a failover environment for a discrete set of user databases, known as availability databases, that fail over together. An availability group supports a set of read-write primary databases and one to eight sets of corresponding secondary databases. Optionally, secondary databases can be made available for read-only access and/or some backup operations.

An availability group fails over at the level of an availability replica. Failovers are not caused by database issues such as a database becoming suspect due to a loss of a data file, deletion of a database, or corruption of a transaction log.

BizTalk, DTC and Always ON

When BizTalk Server and SQL Server are installed on separate computers, Distributed Transaction Coordinator (MS DTC) handles the transactions between the computers. As a result, the SQL Server AlwaysOn feature is not supported. The SQL Server AlwaysOn feature does not support MSDTC transactions.

This is because transaction atomicity/integrity cannot be guaranteed 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. In other words, after failover, the new principal, contacts MS DTC but MS DTC has no knowledge of the new principal server, and it terminates any transactions that are "preparing to commit," which are considered committed in other databases.

This might cause many database inconsistences across all BizTalk Databases.

To understand how BizTalk and MSDTC work together see a post I wrote time ago here –> https://blogs.technet.com/b/biztalkpfe/archive/2011/01/12/understanding-msdtc-amp-biztalk.aspx#comments

References:

AlwaysOn Availability Groups (SQL Server) https://msdn.microsoft.com/en-us/library/hh510230.aspx

Understanding MSDTC & BizTalk https://blogs.technet.com/b/biztalkpfe/archive/2011/01/12/understanding-msdtc-amp-biztalk.aspx#comments

What's New in BizTalk Server 2013 and 2013 R2  https://msdn.microsoft.com/en-us/library/jj248703(v=bts.80).aspx