BizTalk 2016 and SQL Server AlwaysOn

Below description is a summary of: High Availability using SQL Server Always On Availability Groups.

Traditionally, BizTalk uses SQL Server Failover Clustering and Log Shipping to provide high availability, backup and restore, and disaster recovery for its on-premises databases. In Azure IaaS (Azure virtual machines), previous versions of SQL Server do not support Failover Cluster Instances (no MSDTC support). As a result, BizTalk did not have a HA solution when using Azure VMs.

  • BizTalk Server 2016 has added support for SQL 2016 AlwaysOn Availability Groups.
  • The SQL Server 2016 AlwaysOn feature is supported for BizTalk on-premises and Azure IaaS databases.
  • A SQL Server Always On Availability Group consists of a primary replica and one or more secondary replicas. Each replica must reside on a different SQL server instance. Note that it does not matter whether SQL instances are on the same computer, or different computers.
  • Requirements:
    • BizTalk Server 2016 Enterprise
    • SQL Server 2016 Enterprise
    • Windows Server 2012 R2
    • Windows Server 2016

  • When using SQL Server AlwaysOn, MSDTC – that is: transactional consistency for BizTalk databases participating in distributed transaction – is only supported when the databases are on different SQL Server instances, not when they are on the same SQL Server instance.
  • Deploying AlwaysOn Availability Groups requires a Windows Server Failover Clustering (WSFC) cluster. A WSCF cluster has a WSCF resource group that evaluates the health of the primary SQL Server replica in the SQL Server Avalilability Group.
  • Clients can connect to a given availability group using an availability group listener. The availability group listener will direct client connections to the appropriate replica.
  • BizTalk Server uses Log Shipping for Disaster Recovery. This means a standby server can resume database processing in the event that the production database server fails. Note that secondary databases in availability group are not backups.
  • Continue to backup BizTalk databases and their transaction logs using BizTalk Server Log Shipping jobs. The way BizTalk Log Shipping is implemented ensures that backups are always performed against the current primary replica of every database. The backup preference setting on the availability group is not honored by the BizTalk Server Log Shipping jobs. If you are adding other BizTalk databases to the BizTalk Databases Backup job, be sure to use the Availability Group Listener name as the database server for them when setting up the Backup.

BizTalk and SQL Server Always On

In the basic configuration of BizTalk Server, a minimum of 9 databases are created including Rules and BAM databases.
Due to the MSDTC limitation with Availability Groups mentioned previously, a configuration such as following does not ensure transactional consistency.

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

  1. Authentication, bijv. W12M-VWT-DBBZT1\Authentication
    • SSODB
  2. Management, bijv. W12M-VWT-DBBZT1\Management
    • BizTalkMgmtDb
  3. Runtime, bijv. W12M-VWT-DBBZT1\Runtime
    • BizTalkMsgBoxDb
    • BizTalkRulesEngineDb
    • BAMPrimaryImport
    • BAMStarSchema
    • BAMAlertsApplication
    • Custom databases
    • ESBExceptionDB
  4. Tracking, bijv. W12M-VWT-DBBZT1\Tracking
    • BizTalkDTADb

At my current client we have two database servers each with the above 4 instances:
W12M-VWT-DBBZT1 and W12M-VWT-DBBZT2.

On each SQL Server instance, we see an extra node AlwaysOn High Availability. In this example, we look at the runtime instance. The AlwaysOn High Availability node has a node DBBZTCL1-RUNTIME-AG (Primary) with the following subnodes:

  • Availability Replicas:
    • W12M-VWT-DBBZT1\RUNTIME (primary) and W12M-VWT-DBBZT2\RUNTIME (secondary).
  • Availability Databases:
    • See above
  • Availability Group Listeners:
    • DBBZTCL1RntmL

On DBBZTCL1 we see the same subnodes. I guess this the Availability Group Server. Note. The Availabilty Group Server and the Availablity Group Listeners differ per environment. The Availablity Group Listeners are: DBBZTCL1MngtL, DBBZTCL1AuthL and DBBZTCL1TrkgL.

Remarks:

  • In a scaled-out MessageBox scenario (a configuration with more than one MessageBox), there is more than one MessageBox database, and each MessageBox database must be on its own SQL Server instance.
  • BizTalk Server also depends on SQL Server Analysis Services and SQL Server Integration Services for BAM Analysis and Archiving. SQL Server does not provide a high availability solution for Integration Services or Analysis Services in Azure IaaS. It is therefore recommended to use another standalone SQL Server instance for the BAMArchive and BAMAnalysis Analysis Services databases.
    For on-premises installations, SQL Failover Clustering Instance can be used for setting up a high availability configuration.
    This configuration is illustrated below, and recommended for BizTalk Databases in Availability Groups:

Along with SQL Server databases, BizTalk Server configuration also creates SQL Server security logins and SQL Agent Jobs. AlwaysOn Availability Groups only provide the ability to manage databases inside an Availability Group. Logins and SQL Agent Jobs for BizTalk need to be created and updated/managed manually on all the availability replicas.

The following list of SQL Server security logins are associated with BizTalk Server. You may have additional logins created for your BizTalk Server applications. If so, you need to replicate them on every instance of SQL Server hosting a replica of BizTalk databases.

  • BizTalk Application Users (one or more corresponding to each in-proc Host)
  • BizTalk Isolated Host Users (one or more corresponding to each Isolated Host)
  • BizTalk Server Administrators
  • BizTalk Server B2B Operators
  • BizTalk Server Operators
  • SSO Administrators
  • BAM Alerts User
  • BAM Management Web Service User
  • Rule Engine Update Service Account

If you have created additional hosts or will create additional hosts later, there will be new SQL logins created as part of this process. You must make sure to create these SQL logins manually on the corresponding replicas.

The following SQL Server Agent jobs are associated with BizTalk Server. The jobs installed on each server instance are different depending on the features installed. Most of these jobs are created during BizTalk Server configuration. Several are created when configuring log shipping. These jobs need to be replicated on each instance of SQL Server hosting replicas of their corresponding BizTalk database. This must be performed manually.

Authentication instance:

Management instance:

  • Backup BizTalk Server (BizTalkMgmtDb)
  • CleanupBTFExpiredEntriesJob_BizTalkMgmtDb
  • Monitor BizTalk Server (BizTalkMgmtDb)

RunTime instance:

  • MessageBox_DeadProcesses_Cleanup_BizTalkMsgBoxDb
  • MessageBox_Message_Cleanup_BizTalkMsgBoxDb
  • MessageBox_Message_ManageRefCountLog_BizTalkMsgBoxDb
  • MessageBox_Parts_Cleanup_BizTalkMsgBoxDb
  • MessageBox_UpdateStats_BizTalkMsgBoxDb
  • Operations_OperateOnInstances_OnMaster_BizTalkMsgBoxDb
  • PurgeSubscriptionsJob_BizTalkMsgBoxDb
  • Rules_Database_Cleanup_BizTalkRuleEngineDb
  • TrackedMessages_Copy_BizTalkMsgBoxDb
  • Jobs on additional msgboxes

Tracking instance:

  • DTA Purge and Archive (BizTalkDTADb)

At my current client, the jobs are avalailable on W12M-VWT-DBBZT1 and DBBZTCL1, not on W12M-VWT-DBBZT2.

Unlike SQL Failover Clustering Instances, in Availability Groups all replicas are active, running, and available. When SQL Agent jobs are duplicated on each replica for failover, they run against the corresponding replica, irrespective of whether it is currently in primary role or secondary role. To make sure these jobs are executed only on the current primary replica, every step in every job must be enclosed within an IF block, as shown:
IF (sys.fn_hadr_is_primary_replica(‘dbname’) = 1)
BEGIN

END

Replace ‘dbname’ with the corresponding database name against which the job is configured to run. The following example shows this change for TrackedMessages_Copy_BizTalkMsgBoxDb on BizTalkMsgBoxDb:

 

Leave a Reply

Your email address will not be published. Required fields are marked *