BizTalk 2016 and SQL Server 2016 AlwaysOn RRS feed

  • Question

  • Hello,

    we are  about  to  migrate to biztalk 2016 (not in azure  vm)  and we are thinking about  using Alwayson in SQL 2016  side

    but according  to what i found in the internet, it's not  something recommanded ..because at the end  we will have  multi SQL instance, logging, bkp , Groupe vailability    to manage ....

    "Support for cross-database transactions within the same SQL Server instance Cross-database transactions within the same SQL Server instance are not supported for Always On Availability Groups."

    does anyone  did  this?  what do you think about it?

    Wednesday, March 8, 2017 4:22 PM

All replies

  • Hi,
    i can understand what you mean. Indeed, there are some steps to be taken, but come on, we are handling this in an enterprise grade environment, just to mention this.

    To make your hands dirty, you need at least the basic of the BizTalk databases installed on one instance each. That is:
    - SSODB
    - BizTalkMgtDb
    - BizTalkMsgboxDB
    - BizTalkDTADB

    Other BizTalk Server 2016 databases, like BusinessRulesDB or BAM DBs, might be setup as well as AlwaysOn Availability Groups, as you need them.

    There is no sequence in the installation of these databases on SQL Server 2016, yet the SSODB has to be configured at first in BizTalk Server Configuration.

    These SQL instance installation steps have to be done repetitive on every cluster node that will be used to later provision an AlwaysOn Availability Group Replica. You might run your AlwaysOn Availability Groups with the minimum of 2 nodes only. If you have 3 cluster nodes at your disposal, your are busy with at least 12 instances to provide. Do an unattended (silent) installation, as described here:

    Install SQL Server 2016 Using a Configuration File

    Actually, as you can see the primary load of work to set up the AlwaysOn Availability Groups for BizTalk Server 2016 has to be made on your SQL Server 2016 nodes.

    Using the Windows Server 2016 failover cluster with AlwaysOn Availability Groups gives you high availability and disaster recovery, aka HADR functions for your BizTalk Server Group. With a clustered environment like this you can greatly benefit from other cluster's resources already in place, like DNS, DHCP or DTC, FileShare etc... And with the AlwaysOn Availability Groups you gain windfall profits of using load balancing on the the secondary replica for other usage scenarios, like reporting.
    Give it a try !



    If this answers your question and is a helpful post, please click Vote As Helpful, Propose As Answer, and/orMark As Answer.

    Friday, March 10, 2017 4:59 PM
  • Do you meant using AlwaysOn for failover on the BizTalk databases?

    Or, connecting to an AlwaysOn Availability set using the WCF SQL Adapter?

    Saturday, March 11, 2017 9:04 PM
  • Hi Johns-305,

    i mean  using AlwaysOn for failover on the BizTalk databases!


    Monday, March 13, 2017 12:24 PM
  • Hello Paul,

    Thank you for your feedback, just a question:  you don't think that it's too much complicated  to use  the alwayson with Availability groupe   than  using  juste the classic  cluster failover ?

    Monday, March 13, 2017 12:26 PM
  • Hi,

    the classic failover cluster happens on the service/instance level, whereas the AlwaysOn Availability Groups from SQL Server provide you HADR on the database level. Some work to set it up and running, but once you got it, you will see its worth it.

    Since database mirroring is announced as being depricated by Microsoft, i assume AlwaysOn Availability Groups will be the horse to ride on with SQL Server, regarding HADR, the for next future days, at least.



    If this answers your question and is a helpful post, please click Vote As Helpful, Propose As Answer, and/orMark As Answer.

    • Proposed as answer by Angie Xu Monday, March 20, 2017 3:05 AM
    Monday, March 13, 2017 12:40 PM
  • it's seems great!

    in fact me  i was  wandring about  the  complexity using it:  we  should now  use  1 sql instance for  each DB DTC.....  at least we need 4 instances   for  primary    node  and then 1 instance  dor each replica.....because DTC in not supported  while using Alwayson.... that's whay , i was  shoked by the situation.

    for me may be it gives high avalability  but more complexity  to make it work .....

    for my client, i have to take in consideration the costs also ..

    Monday, March 13, 2017 3:31 PM