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.
| Version | Rules | |||||||||||||||
| 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
BizTalk Server 2016 CU5 https://support.microsoft.com/en-us/help/4132957/cumulative-update-5-for-microsoft-biztalk-server-2016
Crumbs on the integration table 
[…] This post from Samuel Katsberg, Microsoft PFE from Sweden, gives a good overview of the databases placement pre and post CU5. […]
Wagner Silveira also writes about this topic in a good way. https://notetoself.tech/2018/06/30/biztalk-server-2016-and-sql-server-2016-sp2/
[…] Availability Group improvements with SQL Server 2016 SP2 and BizTalk 2016 CU5 by Samuel Kastberg […]
Hi skastberg,
and thanks for this great and usefull blog. I just moved my biztalk 4 instances to only one but I get a critical warning about that on BHM :
AlwaysOn AG – dbs in same SQL instance
BizTalkDtaDb BizTalkDTADb shares SQL Server instance with other BT dbs
AlwaysOn AG – dbs in same SQL instance
BizTalkMgmtDb BizTalkMgmtDb shares SQL Server instance with other BT dbs
AlwaysOn AG – dbs in same SQL instance
SSO SSODB shares SQL Server instance with other BT dbs
do you have an idea? thanks
Hi,
Thanks, glad you like my posts. BHM is not updated yet to take the latest changes into account. I have not updated the validation script I have written myself yet either. Previously if you had your databases in the same SQL Server instance the backups did not work, so that can be a way to validate.
My Previous databases were splited on 4 instances and i created a new one to host all the databases. it gives also this error:
Cumulatives Updates : No CUs is installed
but I already installed CU5 before backup/restore databases, maybe I have to apply it on the new instance?
As long as you have installed SQL Server 2016 SP2 for the new instance you should be up and running. CU5 will update the binaries on BizTalk Server and the databases if needed and that is something you did before moving. The BHM repository is not updated with CU5 yet (to my knowledge, but I generally get the information upon release).
Thanks a lot skastberg for these great informations
I have updated the script to validate the AG setup to honor the improvements.
https://skastberg.wordpress.com/2017/06/30/validate-your-biztalk-availability-groups/
[…] Read about important improvements here Availability Group improvements with SQL Server 2016 SP2 and BizTalk 2016 CU5 Here you have a tool to do basic validation of your configuration: Validate your BizTalk […]
Great article. Thanks for sharing. Any recommendations on articles for Biztalk2020 databases on Azure VMs configured for AlwaysOn?
Thanks George!
Sorry, no good pointers to posts. Be aware of the latency added for synchronous commit with AG. Ensure to have the machines in a proximity group, to minimize latency. Some reading here: https://learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/availability-group-overview?view=azuresql
You have the alternative of FCI in Azure also https://learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/failover-cluster-instance-overview?view=azuresql, which would be my preferred setup today. It was a while ago since I did setup a cluster in Azure so I’m not sure how much the networking parts have changed/evolved.