locked
BizTalk Server 2016 and SQL Server 2016 AlwaysOn RRS feed

  • Question

  • I tried really hard today to wrap my head around BizTalk 2016 and the support for SQL Server 2016 AlwaysOn: Does BizTalk 2016 need SQL Server 2016 Standard or Enterprise?

    There is support for "AlwaysOn", that's a fact, now that SQL Server 2016 AlwaysOn supports MSDTC.

    However, are BAG (Basic Availability Groups) sufficient, or do we need the Enterprise feature DAG (Distributed Availability Groups)?

    BAG is available with SQL Server Standard Edition, however DAG is only available at Enterprise level, which is a big thing in terms of licensing cost.

    BizTalk uses cross-database transactions, typically on the same SQL instance, which are not supported apparently.
    See: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/eb24bc3f-ef39-4570-acf5-74ad2c63a9fc/sql-alwayson-support-in-biztalk-2016-version?forum=biztalkgeneral

    We are trying to work our offerings around the upcoming release of BizTalk 2016, but this confuses me.


    Pieter Vandenheede - Codit

    Wednesday, June 15, 2016 12:06 AM

Answers

  • Hi Pieter

    Very pertinent question - hope MS clarifies BizTalk supportability/standard topology with SQL Server 2016 Always-On soon.

    As per official documentation at the moment (link)-

    Cross-database transactions within the same SQL Server instance are not supported for Always On Availability Groups. This means that no two databases in a cross-database transaction may be hosted by the same SQL Server instance. This is true even if those databases are part of the same Availability Group.

    Cross database transactions are common esp. between the MsgBoxDb and DTA/BAMPI databases. So, at the very least we need two SQL instances to separate out these databases it seems.

    Another key point regarding BAG (Basic Availability Groups) - you can only add a single database to a Basic Availability Group. So, to have High Availability for all the BizTalk databases, you would need to create a separate BAG for each BizTalk database. Creating multiple BAG within the same SQL Server instance is supported, as noted here.


    Thanks Arindam



    Wednesday, June 15, 2016 5:13 AM
    Moderator

All replies

  • I tried really hard today to wrap my head around BizTalk 2016 and the support for SQL Server 2016 AlwaysOn: Does BizTalk 2016 need SQL Server 2016 Standard or Enterprise?

    Rachit: Yes, Standard Edition of Windows Server and SQL Server are fully supported with any Edition of BizTalk Server till yet, hopefully this will stay true for future releases of BizTalk Servers as well.

    There is support for "AlwaysOn", that's a fact, now that SQL Server 2016 AlwaysOn supports MSDTC.

    However, are BAG (Basic Availability Groups) sufficient, or do we need the Enterprise feature DAG (Distributed Availability Groups)?

    Rachit: Yes, Always On Basic Availability Groups provide a high availability solution for SQL Server 2016 Standard Edition. A basic availability group supports a failover environment for a single database. There are some limitations though, Basic availability groups use a subset of features compared to advanced availability groups on SQL Server 2016 Enterprise Edition.

    Quoting MSDN:

    Basic availability groups include the following limitations:

    • Limit of two replicas (primary and secondary).

    • No read access on secondary replica.

    • No backups on secondary replica.

    • No support for replicas hosted on servers running a version of SQL Server prior to SQL Server 2016 Community Technology Preview 3 (CTP3).

    • No support for adding or removing a replica to an existing basic availability group.

    • Support for one availability database.

    • Basic availability groups cannot be upgraded to advanced availability groups. The group must be dropped and re-added to a group that contains servers running only SQL Server 2016 Enterprise Edition.

    • Basic availability groups are only supported for Standard Edition servers.

    BAG is available with SQL Server Standard Edition, however DAG is only available at Enterprise level, which is a big thing in terms of licensing cost.

    BizTalk uses cross-database transactions, typically on the same SQL instance, which are not supported apparently.
    See: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/eb24bc3f-ef39-4570-acf5-74ad2c63a9fc/sql-alwayson-support-in-biztalk-2016-version?forum=biztalkgeneral

    Rachit: This still holds good. Currently Cross-database transactions within the same SQL Server 2016 instance are not supported for AlwaysOn Availability Groups and therefore this limitation will apply to BizTalk database as well.


    For further clarification you can also consider opening a case with Microsoft BizTalk Support

    Rachit Sikroria (Microsoft Azure MVP)

    Wednesday, June 15, 2016 3:22 AM
    Moderator
  • Hi Pieter

    Very pertinent question - hope MS clarifies BizTalk supportability/standard topology with SQL Server 2016 Always-On soon.

    As per official documentation at the moment (link)-

    Cross-database transactions within the same SQL Server instance are not supported for Always On Availability Groups. This means that no two databases in a cross-database transaction may be hosted by the same SQL Server instance. This is true even if those databases are part of the same Availability Group.

    Cross database transactions are common esp. between the MsgBoxDb and DTA/BAMPI databases. So, at the very least we need two SQL instances to separate out these databases it seems.

    Another key point regarding BAG (Basic Availability Groups) - you can only add a single database to a Basic Availability Group. So, to have High Availability for all the BizTalk databases, you would need to create a separate BAG for each BizTalk database. Creating multiple BAG within the same SQL Server instance is supported, as noted here.


    Thanks Arindam



    Wednesday, June 15, 2016 5:13 AM
    Moderator