BizTalk Server 2016 Cumulative Update 5 was released last week. One of the items in the CU list was this KB – adding support in BizTalk Server 2016 for SQL Server 2016 SP2.
Why is this important? Because this service pack simplifies the deployment of SQL components when setting up BizTalk Server 2016 highly available environments.
Before this service pack, the SQL Server setup would require 4 instances of SQL Server, which would be used to configure 4 different Availability Groups. The BizTalk Server databases would then have to be spread across those instances. The need for different Availability Groups is because of a known issue between Always On and MSDTC – although Always On supported MSDTC, it didn’t support transactions between two databases within the same Availability Group. That’s why so many Availability Groups are required.
With SQL Server Service Pack 2, this issue is now resolved, and two databases within the same Availability Group can now participate in a transaction managed by MSDTC. That means that now only one Availability Group is required to host all the BizTalk Server databases.
So, if you are planning on deploying new BizTalk Server 2016 highly available environments, make sure that you install SQL Server 2016 with Service Pack 2. That will make your setup much simpler, only requiring a single Availability Group for that. Unfortunately there is still no official guidance for that – but hopefully the documentation will be updated soon to reflect this change.
A couple of extra notes on this subject (after I’ve posted this originally):
- This post from Samuel Katsberg, Microsoft PFE from Sweden, gives a good overview of the databases placement pre and post CU5.
- SQL Server SP2 updates the MSDTC issues related to Availability Group (which is supported by SQL Server 2016 Enterprise). SQL Server 2016 Standard only supports Basic Availability Groups – which is a cut down version of the functionality in Enterprise and only supports a single database per availability group. SP2 doesn’t affect this limitation (so, if you are planning to use SQL Server 2016 Standard, you still need to create one availability group per database).