High Availability: BizTalk Server 2016 with AlwaysOn Availability Groups SQL Server 2016, PART 2

Author: Paul Endras

Intro   

This article continues the series about High Availability for BizTalk Server 2016 environment with Always On Availability Groups SQL Server 2016.

Watch the whole video tutorial, including the demo:

BizTalk Server 2016 High Availability with AlwaysOn Availability Groups SQL Server 2016, PART 2    

In the first part of this article I introduced the core concepts about the infrastructure layout for a BizTalk Server 2016 environment. The following topics were presented:

  1. Benefits of using AlwaysOn AG with BizTalk Server 2016
  2. Real World scenarios
  3. High Availability options with BizTalk Server 2016
  4. Demo
  5. Prerequisites
  6. Infrastructure layout

If you want to know more about this, please refer to he first part of this video tutorial:

High Availability: BizTalk Server 2016 with AlwaysOn Availability Groups SQL Server 2016, part 1 / Video tutorial

and the corresponding article:

High Availability: BizTalk Server 2016 with AlwaysOn Availability Groups SQL Server 2016, part 1 / Article

In the second part of this article I will show you the essential steps on How-To-Implement a High Available BizTalk server 2016 environment with the new feature AlwaysOn Availability Groups (in short AO AG) in SQL Server 2016.

The following topics will be covered in this article:

High Availability with BizTalk Server 2016 Part 2 Agenda.001

1.What are Availability Groups in SQL Server 2016 ?

Basically, the concept of AO AG is to provide high availability on the level of the database. This contrasts to the concept of high availability on the level of the service that we know from the classic failover cluster.

As we have seen in part 1 of this article, we use AO AG together with Windows Server failover cluster to achieve high availability. The windows failover cluster provides automatic failover in case of failure. The Availability Groups are in fact used as cluster resources. Therefore, availability groups are a unit for failover.

That is the very basic concept.

The database layer in AO AG SQL Server 2016 with BizTalk Server 2016:

AOAG_BTS16_SQL16

The SQL Server hosts the instances for each database that is part of AO AG in the BizTalk Server environment.

In the given scenario, we have 3 instances for every database.

From the right to the left you can see:

Firstly, the database instances themselves are cluster resources, as we discussed in the first part of the video tutorial.

From the SQL Server perspective, each of them is called replica, where the primary is the active instance and all the others are secondary replicas.

All of the configured SQL Server instances of a certain database together form an availability group.

Secondly, the cluster resources are setup as Listeners to the AO AG.

Thirdly, to the BizTalk Server the Listeners are represented as virtual node names (VNN) as a database connection.

2.What is needed for AO AG in SQL Server 2016 ?

To setup the AO AG SQL Server 2016 we need at least:

  • Windows failover cluster service
  • SQL instances configured

To get acquainted with basics of AO AG, please refer to the getting-started document:

https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/getting-started-with-always-on-availability-groups-sql-server

Read more about the feature support and restrictions for AO AG:

https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/always-on-availability-groups-interoperability-sql-server

But that’s not the whole game.

There are a few options that must be considered when using AO AG for a BizTalk Server 2016 environment.

I will highlight these options here.

3.Setup of the AO AG, Additional steps

a. Check the network configuration settings for all SQL instances

First of all, we should check the proper configuration of the network settings for SQL Server instances. Therefore we open the SQL configuration tool.

Make sure to have the proper network protocols set.

b. Activate the AlwaysOn Availability Groups in SQL Server Configuration

Next, we go and enable the AO AG feature per instance.

c. Make a full backup of your primary replica

Copy the backup files to all the planed secondary host instance of SQL Server.

d. Restore the backup on all participating secondary hosts instance

Use the option “RESTORE WITH RECOVERY” for this purpose.

e. Setup the Availability Groups

Due to the lack of cross-database transactions, the Availability Groups have to be setup with the T-SQL command, including the dtc_support parameter.

You can setup the SQL Listeners later. Hence it is recommended to prepare the IP-numbers and DNS names before the setup

f. Configure the tcp-endpoints

Configure a new TCP-endpoint for the every replica within the AG. Be aware of the TCP-port numbers. AO AG needs the tcp-port numbers for communication from the cluster to the server instances and vice versa.

For the purpose of clarity I have used a numerical scheme for the tcp ports on every database replica in order to recognize their membership to a certain availability group, as you can see here:

Double check the communication of the tcp ports and take also into consideration firewall exceptions.

For this purpose i was using the tcp viewer, from sysinternals.microsoft.com .

The state <ESTABLISHED> indicates a valid communication for the participants.

g. Check the proper permissions for the SQL accounts and GRANT access to the service accounts as needed

Use sqlcmd to verify accounts login ability and the database grants.

h. Connect to the AG and add the database to the AG

i. Finally, Configure BizTalk Server 2016 to use the SQL Listeners for:

  • SingleSignOn
  • The BizTalk Group

4.Conclusion

In this article i explained the database layer of the AO AG SQL Server 2016 when used for BizTalk Server 2016 environment. I mentioned the prerequisites and I also showed you the most significant topics when setting up the Always On Availability Groups SQL Server 2016 for the BizTalk Server 2016 environment.

In the next part of this video tutorial i will demonstrate the high availability feature within the BizTalk Server in action running a business process.

If you l like this video click the button below, if you want to see more of my videos in the future click also the button.

Need more information on BizTalk Server solutions ? Please contact: info@endras.de

5.Disclaimer

The information given here is as is, and without any obligation or guarantees. You can use this information at your own risk.

6.Links:

Overview of Always On Availability Groups (SQL Server)
Prerequisites, Restrictions, and Recommendations for Always On Availability Groups (SQL Server):
SQL Server 2016 DTC Support In Availability Groups

High Availability: BizTalk Server 2016 with AlwaysOn Availability Groups SQL Server 2016

Part 1: Infrastructure layout

Author: Paul Endras

Intro

In this article, I will demonstrate you the infrastructure layout of a BizTalk server 2016 environment with the new high availability feature AlwaysOn Availability Groups (in short AOAG) in SQL Server 2016.

Watch the whole video tutorial, including the demo:

BizTalk Server 2016 High Availability with AlwaysOn Availability Groups SQL Server 2016, part 1    

BizTalk Server 2016 provides a new feature for high availability and disaster recovery in conjunction with SQL Server 2016.

Basically, the BizTalk Server relies heavily on database transactions for processing and storing business data. One or more instances of the SQL Server is needed to handle all that data persistency. Since SQL Server itself is based on distributed transactional processing the data consistency is guaranteed even in the case of failure.

Especially in enterprise-scale, real-time scenarios like B2B or B2C there is a need for high availability. Hence, the requirement is to avoid the cost of losing business transactions and valuable business data. This becomes a crucial point for any business.

The following topics will be covered:

High Availability with BizTalk Server 2016.001

1.Benefits of using AOAG with BizTalk Server 2016

So what are the benefits of AOAG in SQL Server 2016 when used together with BizTalk Server 2016 ?

Here are some of the most relevant aspects about AOAG and why you should use them with BizTalk Server 2016:

  1. The business data as well as processing data is kept redundant. This behavior takes place in real time, with nearly no costs at the level of the database.
  2. The windows failover cluster service can host the SQL database instances and provide automatic failover in case of failures.
  3. Since data is kept redundant, multiple instances of the database are hosted and can be used also for read-only purposes like database backup and reporting.

2.Real world scenarios

The real-world scenarios provide plenty of reasons why failures could happen.

Therefore, we can distinguish:

  1. Planned scenarios (e.g. maintenance work), or
  2. Unplanned scenarios (e.g. power failure)

The failure might affect different levels within the operational stack:

  1. Service layer
  2. Infrastructure layer (e.g. network, storage)
  3. Data layer

3.High availability options within the BizTalk Server 2016 environment

There are several approaches.

Within the Windows Server 2016 platform we can make use of the Failover Clustering feature right out-of-the-box. The concept of the failover cluster in Windows Server dates to the days of Windows NT 3.5. Since than the Windows failover cluster made tremendous improvements and now supports newer concepts like SQL Server AlwaysOn Availability Groups, that we discuss today.

  • Failover on the service layer

The failover cluster feature in Windows Server 2016 covers both levels, the service layer as well as the data layer.

The classic failover cluster for application servers like SQL Server, Sharepoint Server or BizTalk Server uses failover clustering on the service / instance level. A common usage scenario is an active / passive cluster. This offers the service redundancy on all cluster nodes, where one only is serving actively while the other(s) is  / are in spare and one gets active only when the active node is considered to be unavailable. There are further options and nuances, that we will not cover here in detail.

  • Failover on the infrastructure layer

The Windows failover cluster provides additional cluster roles for the infrastructure layer, like network components (DHCP, DNS, DTC) as well as for other devices within your enterprise network, like the typical storage for FileShares or Archives. The concept of clustering the infrastructure layer covers both the unplanned and the planned kind of failures.

  • Failover on the data layer

In following demo, I will focus on handling the risks of failures on the data layer.

For this purpose, the Windows failover cluster is used to provide a cluster role and cluster resources for the SQL Server AlwaysOn Availability Groups.

The concept of clustering the data layer covers both the unplanned and the planned kind of failures.

The aim here is to run the BizTalk Server environment with all of its applications regardless of failures on the data layer. The uptime of the whole middleware layer must be guaranteed.

Hint: The concept of AOAG in SQL Server supports also the SQL Server in virtual machines in the Azure Cloud.

4.Introduction to the demo

So lets have a closer look at the infrastructure layout for the given scenario.

Here is the big picture that summarises the server landscape:

AOAG_BTS16_SQL16_Service_Layers

From the bottom to the top you can see:

  1. The ADS Domain Server with DNS
  2. The Cluster Shared Disks with the Quorom-Disk
  3. The layer with the SQL Server Cluster Nodes, each node has several SQL instances installed for the BizTalk databases
  4. The BizTalk Server

I will go into the details for each and every functional server in a minute. But before, I would like to summarice the prerequisites briefly.

5.Prerequisites

In the given scenario, the Windows Server was setup with all the roles and features needed. I was using the Windows Nano Server together with some Powershell scripts to quickly setup the cluster server. For the sql server instances an unattended setup with a configuration file might do a good job.

This way you have full control over the automatic creation and can reproduce the results as needed. I am not going into the details about setting up the windows failover cluster here, as we will be focusing more on the AOAG in SQL Server together with the BizTalk Server configuration.

Hint: Please follow the links below and at the end of this presentation for the basic concepts on failover cluster with Windows Server 2016.

6.The infrastructure layout

1.Active Directory Domain Server with DNS

Here you can see the infrastructure as whole with all the servers included. The foundation of this infrastructure is the Domain Controller is hosting the Active Directory. Here you can see all the different kind of accounts, e.g. users, groups, computers. An OU includes all the BizTalk Server accounts.

This server provides also the DNS role. As you can see, the DNS lists all the domain and network resources needed for the cluster, the cluster service as well as the cluster automatic update service. Keep in mind the SQL Listeners for the AlwaysOn Availability Groups are also listed here in the DNS.

2.Cluster Shared Disks including Quorum Disk

This the file server which provides the virtual iscsi – Disks. These are needed for the sql instances and for the cluster Quorum disk.

3.SQL Server Nodes (three nodes)

In this scenario, the failover cluster consists of three cluster nodes. In general, the minimum number is 2 nodes for AOAG SQL Server 2016 and the maximum is 9 nodes (=1 primary + 8 secondary) as database replicas.

Each of the BizTalk core databases needs its own instance:

  1. SingleSignOn database
  2. MessageBox database
  3. Management database
  4. Tracking database

In this example: 4 databases x 3 nodes = 12 instances

Here you can see an instance for the SingleSignOn database in the Availability Group together with its replicas from the other instances.

4.BizTalk Server (Group)

Later I will show you the actual settings for this BizTalk Server Group. The BizTalk Server Group is configured to use the SQL Listener instead of instance names. This BizTalk Server Group consists of multiple hosts and is now configured for high availability with AlwaysOn Availability Groups.

7.Conclusion

In this article, I demonstrated you a full-fledged infrastructure layout for a BizTalk Server 2016 environment using the AOAG feature in SQL Server 2016. I mentioned the options for BizTalk Server high availability, benefits of using AOAG, the prerequisites and the involved servers in the cluster solution. There are further options to scale-out the BizTalk Server, either using multiple Messageboxes or using additional processing hosts with intelligent server distribution.

In the next article, I will show you the AOAG SQL Server 2016 in detail including the most significant configuration settings.

Need more information on BizTalk Server solutions ? Contact me: info@endras.de

8.Disclaimer

The information given here is as is, and without any obligation or guarantees. You can use this information at your own risk.

9.Links:

Overview of Always On Availability Groups (SQL Server)
Prerequisites, Restrictions, and Recommendations for Always On Availability Groups (SQL Server):
SQL Server 2016 DTC Support In Availability Groups
BizTalk Health Monitor

BizTalk Server: How-to setup BizTalk Health Monitor for multiple, remote BizTalk Servers

Author: Paul Endras

Intro

In this screencast i show you how-to setup the BizTalk Server Health Monitor (BHM) v3.1 in order to monitor multiple remote BizTalk Servers.

The version 3.1 of BHM allows not only access to the local BizTalk Server Group, but also to monitor your chain of BizTalk Servers throughout the stages in the development cycle, e.g. from DEV, TEST, … up to PROD environment.

From there you could plan and execute various management tasks for you BizTalk Servers. All your BHM data is in one place and let´s you easily compare metadata, topology, performance and report data for all your managed BizTalk Servers.

Furthermore doing all the relevant maintenance tasks can be done from one centralised spot.

This graphic shows the systems architecture which is used to demonstrate the BHM features. 

In this scenario i assume no usage of firewall and other interfering infrastructure components. If there is a firewall in place, you need at least SQL ports for the relevant instances open (default: 1433) and should check the communication layers as well.

BizTalk Health Monitor

BizTalk Health Monitor

Left side is the Windows Server hosting the BHM. Right sight you see some BizTalk Server Groups. One is BizTalk Server 2013R2, the other ist running BizTalk Server 2010.

What are the preconditions ?

  1. Assign the database logins and appropriate role memberships to access SQL Server and the BizTalk Server databases.
    In my case i am using a domain account for this purpose.
  2. Make sure at least SQL Client is installed and SQL Network Configuration prepared on the machine where you want to have BHM installed.
    Now we open the console and execute the sqlcmd from the command line.  Connecting with an appropriate login schema at the SQL Server will proof we get access to the remote BizTalk Server databases.
  3. All BizTalk Server Groups you want to monitor are in one Windows domain or trusted to your domain.

Keep in mind: There is no need to Install BizTalk Server on the machine where you want to use BizTalk Health Monitor.

How this all works ?

  1. Start the BizTalk Health Monitor app. As you can see i have already defined one Profile for a Remote BizTalk Server Group.
  2. Now, we can create a new Profile and specify the BizTalk Server Group that you want to Monitor.
    From the BHM root right-click and execute the command “New Monitoring Profile”. Complete the information about account information (if not the same as logged in user).
  3. From the BHM menu click  “Analyse Now” to start collecting the monitor data.

Conclusion

In this screencast i showed you How-to setup BHM for multiple Remote BizTalk Servers, including the preconditions.

Interesting Links:

http://blogs.msdn.com/b/biztalkhealthmonitor/archive/2014/06/26/overview-of-biztalk-health-monitor-bhm.aspx

http://blogs.technet.com/b/jpierauc/archive/2014/06/26/quot-biztalk-health-monitor-quot-now-available-in-biztalk-2013-r2.aspx