One of the major news in BizTalk Server 2016 is the full support for SQL AlwaysOn Availability Groups (AGs). When Microsoft announced this, the crowd cheered, and they moved on to the next slide. There are, however, some bits you should be aware of before deciding on your High Availability (HA) architecture for BizTalk.

The Background
SQL Server 2016 is the first (and only) edition to support AlwaysOn for BizTalk Server 2016. The reason is lack of support for MSDTC between BizTalk’s SQL Server databases and other transactional resources. For this reason, High Availability for the database layer in BizTalk scenarios has traditionally been solved by using Windows Server Failover Clusters (WSFC), typically with an active-passive configuration.

The Requirements
First of all, you need to run BizTalk Server 2016 Enterprise, SQL Server 2016 Enterprise, and Windows Server 2016 (or 2012 R2 with KB3090973).

As you may be aware of, an AG consists of one primary replica, and 1-8 secondary replicas. Naturally, you would assume the following would be a valid design for the BizTalk databases.

architecture-2

Unfortunately, this configuration will not ensure transactional consistency. The reason is lack of support for MSDTC between databases on the same SQL instance. This means that no two BizTalk databases can be hosted on the same instance. The following configuration is recommended by Microsoft.

architecture-3

As you may already have noticed, there is an exception to the MSDTC limitation. Some databases can be put on the same instance (BizTalkMsgBoxDb, BizTalkRulesEngineDb, BAMPrimaryImport, BAMStarSchema and BAMAlertsApplication). According to the recommendation, you will need 4 WSFCs, 4 AGs, and 16 SQL Server instances. The latter requirement can be scaled down to 8 instances, if 1 secondary replica will suffice.

The minimum requirements can be solved in two ways:

  • Azure IaaS: 8 servers hosting 1 SQL instance each (due to lack of ILB support for multiple IP addresses), or
  • On-Premise: 2 servers hosting 4 SQL instances each

If you need BAM Analysis and Archiving, you also need:

  • Azure IaaS: One standalone SQL instance (due to lack of HA support), or
  • On-Premise: Same as above, or a WSFC for HA

In a multiple-MessageBox scenario, additional SQL instances will be required.

Other considerations
Several logins are created for your BizTalk applications. These will have to be replicated to all SQL instances. Keep this in mind when creating additional hosts later, which will add more logins.

All SQL Agent jobs for BizTalk must be replicated to all SQL instances. As for SQL logins, added jobs later must be handled manually. All SQL jobs which run against BizTalk databases must also be enclosed with an IF block, to ensure they only run against the primary replica (replace red sections with actual data):

IF (sys.fn_hadr_is_primary_replica(‘dbname‘)=1)
BEGIN
SQL Agent TSQL statement
END

When configuring BizTalk, use the AG Listener as the SQL Server name, instead of the actual machine name. This will create databases, logins and jobs on the current primary replica.

But wait, there is more
There are a few additional limitations you should be aware of:

  • BizTalk Server cannot make use of Read-Only Routing
  • BizTalk Server does not set MultiSubnetFailover connection property
  • BizTalk Backup Jobs using Log Shipping will always target the primary replica irrespective of the backup preference set on the AG

One last tip regarding Log Shipping. Contrary to popular belief, secondary replicas in an AG are not backups. Keep backing up databases and transaction logs using Log Shipping and the built-in Backup BizTalk Server job.

Note! This information is based on the CTP2 edition documentation, published by Microsoft. Some details may change in RTM.

Advertisements