I have created a repository in GitHub to share some scripts I have written. I will add scripts over time. All scripts will be shared As Is under MIT license.
SQL Server 2016 Availability Groups has a limitation, two databases involved in a distributed transaction cannot reside in the same SQL Server instance. For a BizTalk Server installation that will be using Availability Groups this means you need to separate the databases in several SQL Server instances, making the installation more complicated than desired.
SQL Server 2016 SP2 adds the functionality that solves this problem, thus two databases involved in a distributed transaction can be in the same SQL Server instance. BizTalk Server 2016 CU5 and FP3 adds support for this functionality. As a result, BizTalk Server 2016 installations using Availability Groups can be configured using less SQL Server instances if desired.
Database location rules when using Availability Groups
Rules for database location when using Availability Groups with BizTalk Server installations.
|Prior to SQL Server 2016 – BizTalk Server 2016||Distributed transactions not supported, thus not supported for BizTalk Server.|
|SQL Server 2016 (RTM-SP1)
BizTalk Server 2016 (RTM-CU4)
|SQL Server 2016 SP2 and later
BizTalk Server 2016 CU5 and later
|Free database placement|
Note: Other limitations as described in the documentation are still valid.
SQL Server 2016 SP2 https://go.microsoft.com/fwlink/?linkid=869608