Configuring BizTalk 2016 in High Availability Config with SQL Server 2016 Always On

This article tells about our recent experience with an installation of BizTalk Server 2016 in high availability configuration with SQL Server 2016 Always On. It’s just a small experience, but I hope it can be useful for anyone who wants to install a BizTalk infrastructure in high availability on Microsoft Azure or in a virtual environment where cluster failover is not well accepted by system engineers.

There are several articles dealing with this topic and we highly recommend reading them carefully.

In addition, we suggest you to read the article related to Master Secret Server configuration in cluster (https://docs.microsoft.com/en-us/biztalk/core/how-to-cluster-the-master-secret-server1).

To design our server infrastructure, we referred to the scenario suggested by the Microsoft article.

Anyway, our implementation is smaller than the examples you can see in the article. This because it often happens to have the need for configurations in high availability but not to have a very high load.

As recommend, we have grouped BizTalk Server databases into the following four SQL Server instances:

The subdivision of the instances is due to the fact that MSDTC on SQL Server 2016 Always-On Availability Group is not able to handle distributed transactions between multiple databases within the same instance. We are all waiting for BizTalk to support the 2017 version of SQL that is able to do that.

In this specific case we don’t need ESB toolkit databases and BAM Analysis and Alert, so our final cluster implementation is illustrated in the figure below.

As you can see, we have configured one Listener for each SQL Server instance.

We do not report the installation and configuration of SQL Server as it is extensively described in the articles we referred to.

SQL Server Installed Features

On each server node and node instance we have installed the following services and features:

  • Database Engine Services
    • SQL Server Replication
    • Full-Text and Semantic Extractions for Search
  • Shared Features
    • Client Tools Connectivity
    • Integration Services

DTC Configuration

In this configuration it is not necessary to configure a DTC instance as a cluster resource because a local DTC instance will be used.

To enable Network DTC Access on the BizTalk Server and the SQL Server follow the step below:

  • In the Start menu, open “dcomcnfg”.
    • Or, open Administrative Tools, and then select Component Services.
    • Or, open Server Manager, select Tools, and then select Component Services.
  • Expand Component Services, expand Computers, expand My Computer, and expand Distributed Transaction Coordinator.
  • Right-click Local DTC, and select Properties.
  • Go to the Security tab, and check the following:
    • Network DTC Access
    • Allow Inbound
    • Allow Outbound
    • No Authentication Required
  • Select OK. If prompted to restart MS DTC, select Yes.

Enable remote access to COM

Follow the instructions in the following article to enable remote access to COM.

https://support.microsoft.com/en-us/help/3182294/0x80004027-error-when-you-try-to-remotely-access-com-object-after-you

Cluster resource check

From the failover cluster management console, enable the prevent back option in each resource group created.

This may be your preferred setting because it allows you to control when the failback occurs. For example, you may want to select Prevent failback if you want to take time to troubleshoot or run diagnostics on the failed node before allowing the node to take ownership of the SQL Server again.

Availability Group Options

Make sure Availability Groups are configured with the Per Database DTC Support option.

Also configure “Readable Secondary” equal to “Yes”. Otherwise the SSO service could generate the following error:

Failed to contact the SSO database: The target database, ‘SSODB’, is participating in an availability group and is currently not accessible for queries. Either data movement is suspended or the availability replica is not enabled for read access. To allow read-only access to this and other databases in the availability group, enable read access to one or more secondary availability replicas in the group. For more information, see the ALTER AVAILABILITY GROUP statement in SQL Server Books Online.
Data Source=***;Integrated Security=SSPI;Initial Catalog=SSODB

Error code: 0x800710D9, Unable to read from or write to the database.

Finally, you must check that “Primary” has been selected in the Backup Preferences tab, in the “Where backup should occur” configuration setting.

SSO Setup

After completing the installation of SQL Server, before proceeding with the installation of BizTalk, you need to install the Enterprise Single Sign-On master secret server.

We will follow this article regarding the installation of the clustered SSO service: https://docs.microsoft.com/en-us/biztalk/core/how-to-cluster-the-master-secret-server1.

We will set up a cluster group that will contain IP address, Network Name and a generic service resource for the SSO service.

Execute the following step on both SQL Server nodes.

Enable only the following components

  • Enterprise Single Sign-On Administration Module
  • Enterprise Single Sign-On Master Secret Server

Execute the following step on first SQL Server node

Execute the following step on second node

To configure SSO in failover cluster follow the below steps:

  1. Restart the Enterprise SSO service on the first node
  2. From the command prompt type the following command:

    “<drive>:\Program Files\Common Files\Enterprise Single Sign-On\ssomanage” -updatedb XMLFile

    Where XMLFile is a file with the following content “<sso><globalInfo><secretServer>[SSO NET NAME CLUSTER RES]</secretServer></globalInfo></sso>”.

  3. Configure manual start type the “Enterprise Single Sign-On Service” service on both servers.
  4. From the failover cluster management console create a Generic Services resource for the SSO service
  5. Restore the master secret on the second cluster node

Configure SSO Database replica

Before to start to configure SSODB database replica you have to execute e full backup of database.

Install BizTalk Server Application Server

Before starting with the BizTalk Server installation, the prerequisites must be installed. Follow the step described in the Microsoft article https://docs.microsoft.com/en-us/biztalk/install-and-config-guides/set-up-and-install-prerequisites-for-biztalk-server-2016.

The article shows a series of mandatory and optional steps. Following what I have done:

  • Enable Network DTC Access
  • Disable Windows Firewall
  • Disable UAC
  • Enable IIS
  • Run 64-bit BAM portal
  • Install Visual C++ redistributable package
  • Install SQL XML 4
  • SQL Server Integration Services

BizTalk Setup

Execute the following step on both BizTalk Servers.

Enable all available components

BizTalk Configuration (First Node)

In general, the configuration of the first BizTalk node creates configuration databases while the configuration of the second node is limited to joining the existing farm.

BizTalk Configuration (Second Node)

Final Database Configuration

Database Replica

Now that all the databases have been created it is necessary to proceed with the configuration of the replica within the different SQL Server availability groups. Below are the steps to configure the BizTalkMgmtDb database.

Repeat these steps for each database created by the configuration procedure.

Linked Server & Job

Linked servers and jobs are automatically created by the configuration procedure, anyway it is better to verify that they have been created on both nodes.

It is also necessary to modify all the steps of each job to include the control that the server running is the primary one. So, include the original step inside the following code:

if (sys.fn_hadr_is_primary_replica(‘<DBNAME>’)
= 1)
begin

    [original step commands]

end;

I suggest to use the validation script for BizTalk Availability Group (https://skastberg.wordpress.com/2017/06/30/validate-your-biztalk-availability-groups/) in order to check the configuration, it is a very useful tool.

I want to thank my colleague Paolo Barbieri who helped me to reason during this little adventure and write this article.

Advertisements

BizTalk Server 2013 on Windows 2012 can’t access third-party NAS via UNC path

If you have a BizTalk Server 2013 installed on Windows Server 2012 and configure receive or send ports that access to third-party NAS via UNC paths, you may receive the error code 0x80004005.

In this scenario, only one Windows process at a time is able to access to the remote share, the other processes will fail.

Therefore, the problem exists if multiple receive or send ports are configured on different BizTalk hosts.

In addition, if you enable the ports, trying to access the remote share from Windows Explorer you will get the following error:

 

clip_image001

 

The problem is not related to BizTalk layer, but to Windows Server 2012-based or Windows 8-based computer that fail to connect to a third-party file server that supports the SMBv2 file protocol. In fact, you can reproduce the problem following the step below:

 

·         Log on with a first user to Windows Server 2012;

·         Log on with a second user to the same machine;

·         Both users should try to map a network drive specifying the same remote folder;

·         The second user should get the error.

 

At this moment, to allow the ports configured on multiple BizTalk hosts to works fine, you have to disable SMBv2 and SMBv3 protocol forcing the communications on SMBv1.

To do that execute the following commands:

 

 

sc.exe config lanmanworkstation depend= bowser/mrxsmb10/nsi

sc.exe config mrxsmb20 start= disabled

 

 

Additional information on how to enable and disable Server Message Block (SMB) version 2 (SMBv2) and SMB version 3 (SMBv3) on the SMB client and server components, can be found at this address http://support.microsoft.com/kb/2696547.

 

Remember that SMBv1 must be configured in order to works correctly with BizTalk server environments.

By default with SMBv1 are allowed just 50 client connections and 265 server connections. Typically these value are not compatible with integration scenarios.

So you have to modify the following registry settings in order to grow the number of concurrent sessions.

 

Registry Key:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\LanmanWorkstation\Parameters

 

Parameter:

MaxCmds (DWORD)

Possible values: 1-65535

Default Value: 50

Example Value: 16384

 

Registry Key:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\LanmanServer\Parameters

 

Parameter:

MaxMpxCt (DWORD)

Possible values: 1-65535

Example Value: 4096

 

Parameter:

MaxWorkItems (DWORD)

Possible values: 1-65535

Example Value: 16384 (Should be at least MaxMpxCt * 4)