Author: Paul Endras
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:
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:
- Benefits of using AlwaysOn AG with BizTalk Server 2016
- Real World scenarios
- High Availability options with BizTalk Server 2016
- Infrastructure layout
If you want to know more about this, please refer to he first part of this video tutorial:
and the corresponding 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:
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:
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:
Read more about the feature support and restrictions for AO AG:
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:
- The BizTalk Group
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: firstname.lastname@example.org
The information given here is as is, and without any obligation or guarantees. You can use this information at your own risk.