none
High availability for BizTalk databases using always on Availability group

    Question

  • Hi ,

    I am having query on High availability for BizTalk Databases using always on Availability group.

    In the post  https://msdn.microsoft.com/en-us/library/mt743081.aspx it is mentioned that

    MSDTC between databases on same SQL Server instance is not supported with SQL Server AlwaysOn Availability Groups. This means that no two BizTalk databases in a distributed transaction can be hosted on the same SQL server instance. For transactional consistency, BizTalk databases participating in distributed transaction should be hosted on different SQL server instances. Note that it does not matter whether SQL instances are on the same computer, or different computers.

    And the below setup is recommended

    We recommend that the BizTalk Server databases are grouped into the following four SQL Server instances:

    Instance

    Role

    BizTalk Databases in that group

    1

    Authentication

    SSODB

    2

    Management

    BizTalkMgmtDb

    3

    Runtime

    BizTalkMsgBoxDb BizTalkRulesEngineDb BAMPrimaryImport BAMStarSchema  BAMAlertsApplication

    4

    Tracking

    BizTalkDTADb

    The question is :

    In our case we have tracking implementation in pipelines and Orchestration  which makes entries into BAMPrimaryImport . As I understand the whole pipeline execution till the message get published to message box is considered single transaction. If this is the case do we need to  separate Msg box and BAM databases as well?

    Even going with default setting we should needing minimum of 8 database instances considering 4 master and 4 replicas instance to have high availability with Always on  for BizTalk database which can be achieved by 2 SQL instances in normal scenario . So is high availability with always on a cost effective solution?

    Regards,

    Uday Kiran

    Friday, December 16, 2016 9:10 PM

Answers

  • Cost Effectiveness is a relative term. When applying this to BizTalk, the point of comparison has to be the BizTalk Log Shipping. To be able to effectively compare the "Always On" and "Log Shipping: we then introduce the Network Capacity and Availability which gives us latency.

    But all this is dependent on the assessment of how much business is lost in a DR scenario given the RPO/RTO of 10/15 minutes and 2 hours and what is the RPO/RTO that is agreed for the core systems ?? IMHO is does not make any sense for a middleware/ESB to have a lower RPO/RTO as opposed to the core systems.

    So if you're not extending the SQL Server "Always On" as a HA feature to your core systems then it is less likely to be required for BizTalk in which case it may not be cost effective. In a regular deployment with 5/6 instances (I would split the 3rd further to have a separate instance for BAM) on a single 2 node cluster (where both the nodes are licensed) I am saving?? on my DR licenses by choosing between Log Shipping and Always On ??

    Regards.

    Monday, December 19, 2016 5:35 AM

All replies

  • Cost Effectiveness is a relative term. When applying this to BizTalk, the point of comparison has to be the BizTalk Log Shipping. To be able to effectively compare the "Always On" and "Log Shipping: we then introduce the Network Capacity and Availability which gives us latency.

    But all this is dependent on the assessment of how much business is lost in a DR scenario given the RPO/RTO of 10/15 minutes and 2 hours and what is the RPO/RTO that is agreed for the core systems ?? IMHO is does not make any sense for a middleware/ESB to have a lower RPO/RTO as opposed to the core systems.

    So if you're not extending the SQL Server "Always On" as a HA feature to your core systems then it is less likely to be required for BizTalk in which case it may not be cost effective. In a regular deployment with 5/6 instances (I would split the 3rd further to have a separate instance for BAM) on a single 2 node cluster (where both the nodes are licensed) I am saving?? on my DR licenses by choosing between Log Shipping and Always On ??

    Regards.

    Monday, December 19, 2016 5:35 AM
  • Keep in mind, while BizTalk processes, such as Pipeline execution are transactional, they are not always SQL/MSDTC transactions.  So, your concern about Tracking is not necessarily accurate.

    Either way, while AOA is a nice addition to the stack, Windows Cluster Services is still a worthwhile option.

    Monday, December 19, 2016 3:27 PM
    Moderator