Availability Group improvements with SQL Server 2016 SP2 and BizTalk 2016 CU5

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)

Instance Role BizTalk Databases in that group
1 Authentication SSODB
2 Management BizTalkMgmtDb
3 Runtime BizTalkMsgBoxDb
BizTalkRulesEngineDb
BAMPrimaryImport
BAMStarSchema
BAMAlertsApplication
4 Tracking BizTalkDTADb
EsbItineraryDb
EsbExceptionDb
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

Author: skastberg

Integration consultant working as Premier Field Engineer at Microsoft Sweden. My opinions only, most certainly not necessarily those of my employer.

10 thoughts on “Availability Group improvements with SQL Server 2016 SP2 and BizTalk 2016 CU5”

  1. 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

    1. 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.

      1. 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?

      2. 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).

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s