Compartilhar via

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

  • Artigo

 

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

Comments

  • Anonymous
    October 06, 2015
    Thanks for this information - very helpful. Just wondering if while the configuration is not "Supported" - will it work? Can i connect a biztalk 2006 adapter to a DB that is connected to an AlwaysOn Group?

  • Anonymous
    October 06, 2015
    Hi Simon, BizTalk 2006 is out of Main support already so my guess is that you cannot use the SQL Adapter to access a Always ON group. The SQL Adapter uses DTC transactions to commit changes to destination databases and in case something goes wrong, DTC could will fail to recover the transaction. This is just an assumption based on the information we have for recent BizTalk versions. Hope it helps you! My advice: While sometimes non supported things works, I would never deploy something to production that is not supported by Microsoft. You could experiment unexpected issues that in a long run are very though to detect. Agustín

  • Anonymous
    October 06, 2015
    Adding more information to this: The SQL Server adapter (BizTalk 2006) was build while SQL Server Always On feature did not exits. So the BizTalk product group probably never tested this configuration.