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
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s