February 22, 2017July 14, 2018 by Samuel Kastberg

Setting up my BizTalk Server 2016 using Availability groups LAB

This document summarizes my experience setting up my first BizTalk Server 2016 using SQL Server availability groups. The document is based on the description in https://msdn.microsoft.com/en-us/library/mt743081.aspx

Note: Some security settings that I used in this lab would never make to a real environment and were used just to move forward during the limited time I had.

Note:
Read about important improvements here Availability Group improvements with SQL Server 2016 SP2 and BizTalk 2016 CU5
Here you have a tool to do basic validation of your configuration: Validate your BizTalk Availability Groups.

1. Installing the servers

I installed Windows 2016 servers. I added the servers to my lab domain that I have on my local Hyper-V environment.

2. Creating the Cluster

I created a two-node cluster. The process is quite straight forward and had no issues here.

Added names and the addresses to use to my DNS. Cluag for the cluster and the others for my listerners.
Add Failover Clustering Feature to the servers.
Create the cluster
Configure File Share Witness pointing to a share on the DC.

3. Installing SQL Server

From the documentation

Make sure to have at least four different SQL instances which will host the various BizTalk databases. The secondary replicas should also be set up on different SQL instances. This results in a minimum of 8 SQL instances (1 primary and 1 secondary replica for each of the 4 instances), and a minimum of 4 Availability Groups. See the above illustration for this Availability Group configuration. Make sure Availability Groups are created with the Per Database DTC Support option as this cannot be changed later.

3.1. Installing and configuring SQL Server

1. Install 4 SQL instances. In my case, MGMT, MBOX, DTA and SSO

a. Ensure to have the protocols enabled as usual.
Note: Had an issue with SQL Server Agent needing Shared memory enabled to work. This should not be a requirement and is not recommended in a BizTalk environment, I prioritized to continue since this is a lab environment. Later I found out that I needed to set HostServer setting for SQL Server agent to work without shared memory.

b. I configured the same Service accounts on all the database server instances. This saves a lot of security configuration job giving the service accounts from the different servers access to each other as needed.

2. Enable Availability group feature in SQL Server Configuration Manager

3.2. Prepare the AG creation scripts

The required Per Database DTC Support option cannot be applied from the Microsoft SQL Server Management Studio (13.0.16100.1) wizards so the creation must be done in script.

The easiest way to create the Ags is to have a database to synchronize. The BizTalk Databases are created while configuring which is a bit late for SQL. Create an empty database on each database server instance, in my case AGTest. You need to take a backup of each of them before running the scripts. The upside of this is that you will be able to test the AGs before starting to configure BizTalk.

1. Create a share that can be accessed from all the servers, you will need it to startup synchronization. I created one share with one folder for each database server instance.

2. Run the wizard to generate the Create the Availability groups script

3. Specify a name for the AG

4. Select your test database

5. On the Replicas Tab: The local Server instance is automatically added.Add the Secondary Server, specify automatic failover and Synchronous commit. Also, make the replicas readable, if you don’t the jobs will fail on the secondary servers which pollutes the logs.

6. On the Endpoints Tab: Change the default port (5022) I change them to match my IP numbers.
When you have several instances using the default endpoint port 5022 will only work on the first instance. Note: on my version of SQL Management Studio the port number in the generated script was the default even if I changed it.

7. On the Backup Preferences tab: I Selected Primary.
Note: I don’t know if it is a requirement that Backups should be performed on the primary replica. My AG knowledge is not good enough here but primary sounded safe.

8. On the Listener tab:Add a listener name and IP address from the ones you prepared previously. I selected the default SQL port 1433.

9. Move to the next page. Decide how to handle the start of synchronization. Now we use the share we created previously. Note: I don’t see that something else than full is reasonable for us.

10. Now a validation is performed and if it is ok we move on and on the summary page select to get the script.

11. Select cancel to move further to use the script.

Created one script for each AG changing DTC_SUPPORT from none to PER_DB instance names and ports used.
— YOU MUST EXECUTE THE FOLLOWING SCRIPT IN SQLCMD MODE.

:Connect SERVER01\MGMT

USE [master]

CREATE ENDPOINT [Hadr_endpoint]

AS TCP (LISTENER_PORT = 5050)

FOR DATA_MIRRORING (ROLE = ALL, ENCRYPTION = REQUIRED ALGORITHM AES)

IF (SELECT state FROM sys.endpoints WHERE name = N’Hadr_endpoint’) 0

BEGIN

ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED

END

use [master]

GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [MYDOMAIN\SA-BTS-SVC]

:Connect SERVER01\MGMT

IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name=‘AlwaysOn_health’)

BEGIN

ALTER EVENT SESSION [AlwaysOn_health] ON SERVER WITH (STARTUP_STATE=ON);

END

IF NOT EXISTS(SELECT * FROM sys.dm_xe_sessions WHERE name=‘AlwaysOn_health’)

BEGIN

ALTER EVENT SESSION [AlwaysOn_health] ON SERVER STATE=START;

END

:Connect SERVER02\MGMT

USE [master]

CREATE ENDPOINT [Hadr_endpoint]

AS TCP (LISTENER_PORT = 5050)

FOR DATA_MIRRORING (ROLE = ALL, ENCRYPTION = REQUIRED ALGORITHM AES)

IF (SELECT state FROM sys.endpoints WHERE name = N’Hadr_endpoint’) 0

BEGIN

ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED

END

use [master]

GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [MYDOMAIN\SA-BTS-SVC]

:Connect SERVER02\MGMT

IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name=‘AlwaysOn_health’)

BEGIN

ALTER EVENT SESSION [AlwaysOn_health] ON SERVER WITH (STARTUP_STATE=ON);

END

IF NOT EXISTS(SELECT * FROM sys.dm_xe_sessions WHERE name=‘AlwaysOn_health’)

BEGIN

ALTER EVENT SESSION [AlwaysOn_health] ON SERVER STATE=START;

END

:Connect SERVER01\MGMT

USE [master]

CREATE AVAILABILITY GROUP [AGMGMT]

WITH (AUTOMATED_BACKUP_PREFERENCE = PRIMARY,

DB_FAILOVER = ON,

DTC_SUPPORT = PER_DB) — Changed from none to PER_DB

FOR DATABASE [AGTest]

REPLICA ON N’SERVER01\MGMT’ WITH (ENDPOINT_URL = N’TCP://SERVER01.mydomain.local:5050′, FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL)),

N’SERVER02\MGMT’ WITH (ENDPOINT_URL = N’TCP://SERVER02.mydomain.local:5050′, FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL));

:Connect SERVER01\MGMT

USE [master]

ALTER AVAILABILITY GROUP [AGMGMT]

ADD LISTENER N’AGMGMT’ (

WITH IP

((N’10.100.100.50′, N’255.255.255.0′)

)

, PORT=1433);

:Connect SERVER02\MGMT

ALTER AVAILABILITY GROUP [AGMGMT] JOIN;

:Connect SERVER01\MGMT

BACKUP DATABASE [AGTest] TO DISK = N’\\SERVER01\AG-Backup\MGMT\AGTest.bak’ WITH COPY_ONLY, FORMAT, INIT, SKIP, REWIND, NOUNLOAD, COMPRESSION, STATS = 5

:Connect SERVER02\MGMT

RESTORE DATABASE [AGTest] FROM DISK = N’\\SERVER01\AG-Backup\MGMT\AGTest.bak’ WITH NORECOVERY, NOUNLOAD, STATS = 5

:Connect SERVER01\MGMT

BACKUP LOG [AGTest] TO DISK = N’\\SERVER01\AG-Backup\MGMT\AGTest_Startup.trn’ WITH NOFORMAT, NOINIT, NOSKIP, REWIND, NOUNLOAD, COMPRESSION, STATS = 5

:Connect SERVER02\MGMT

RESTORE LOG [AGTest] FROM DISK = N’\\SERVER01\AG-Backup\MGMT\AGTest_Startup.trn’ WITH NORECOVERY, NOUNLOAD, STATS = 5

:Connect SERVER02\MGMT

— Wait for the replica to start communicating

begin try

declare @conn bit

declare @count int

declare @replica_id uniqueidentifier

declare @group_id uniqueidentifier

set @conn = 0

set @count = 30 — wait for 5 minutes

if (serverproperty(‘IsHadrEnabled’) = 1)

and (isnull((select member_state from master.sys.dm_hadr_cluster_members where upper(member_name COLLATE Latin1_General_CI_AS) = upper(cast(serverproperty(‘ComputerNamePhysicalNetBIOS’) as nvarchar(256)) COLLATE Latin1_General_CI_AS)), 0) 0)

and (isnull((select state from master.sys.database_mirroring_endpoints), 1) = 0)

begin

select @group_id = ags.group_id from master.sys.availability_groups as ags where name = N’AGMGMT’

select @replica_id = replicas.replica_id from master.sys.availability_replicas as replicas where upper(replicas.replica_server_name COLLATE Latin1_General_CI_AS) = upper(@@SERVERNAME COLLATE Latin1_General_CI_AS) and group_id = @group_id

while @conn 1 and @count > 0

begin

set @conn = isnull((select connected_state from master.sys.dm_hadr_availability_replica_states as states where states.replica_id = @replica_id), 1)

if @conn = 1

begin

— exit loop when the replica is connected, or if the query cannot find the replica status

break

end

waitfor delay ’00:00:10′

set @count = @count – 1

end

end try

begin catch

— If the wait loop fails, do not stop execution of the alter database statement

end catch

ALTER DATABASE [AGTest] SET HADR AVAILABILITY GROUP = [AGMGMT];

3.3. Create the Availability Groups

1. Open the scripts

2. Set the Query to SQLCMD mode
Note: The generated scripts must run in SQLCMD mode

3. Run the scripts one by one

4. Review the created roles and resources in the cluster.

5. Test to fail over and back all the roles.

4. Install and configure BizTalk Server

4.1. Install and prepare

The installation procedures are the same as any normal BizTalk Server Installation. Review Set up and install prerequisites for BizTalk Server 2016 and Install BizTalk Server 2016.

5. Configuring BizTalk Server

When configuring BizTalk Server and specifying the SQL server name, use the Availability Group’s listener name instead of the actual machine name. This creates the BizTalk databases, logins, linked servers and SQL Agent jobs on the current primary replica as local resources.

The resulting linked servers:

Server instance	Linked to
MGMT	DTA, SSO and MBOX
MBOX	DTA and MBOX
DTA	None
SSO	None

6. Join the BizTalk Databases to the availability groups

1. Configure the backup job and run the job once so you have a full backup which is a requirement to join the AG.
Note: If you don’t take a full backup the configuration will fail.

2. Stop BizTalk processing (Host Instances, SSO Service, IIS, Rules Engine Update Service, BAMAlerts Service, and so on), and stop the SQL Agent Jobs.

3. Now add BIzTalk databases to the respective Availability Groups.

4. Select databases to join

5. Select initial data synchronization FULL and the location to a share that is accessible to both servers.

6. Connect to the secondary replica in the AG.

7. Step forward until joined.

8. Enclose body of all SQL Agent job steps within IF block to make sure they run only if the target is the primary replica.

Note: I used a new version of RestoreScriptForBizTalkJob.ps1 (4.0.5) to generate restore scripts that internally implements this functionality.

9. Script Linked servers, Script Logins and SQL Agent Jobs to replicate them on corresponding replica. I used the Export-LoginsAndJobs.ps1 from the DR fast start to export logins and jobs, linked servers exported manually.

10. Start all services and review that the jobs work as expected.

38 Comments

Yahya SÖNMEZ March 3, 2017 at 12:25

Hi,
how to typing Availability Group’s listener name without linked server on BTS configuring?
Why did you choose linked server when you had AG listener name?

Reply
1. skastberg March 3, 2017 at 13:30
  
  Hi Yahya,
  The linked servers are needed since the different instances interact with each other. I.e. every time you take a backup the management SQL Server instance (agmgmt in the post) will connect to the other listeners to perform a backup. I did not write that in the post but in the linked server configuration you set security and RPC configuration. This is fully normal in a BizTalk Server environment with several SQL Server instances involved.
  
  Reply
Mani Vasisht March 21, 2017 at 14:46

Normally HA installations require that the ENTSSO Windows service is clustered how would this be achieved in this configuration?

Reply
1. skastberg March 21, 2017 at 15:36
  
  Hello Mani,
  I have done it a couple of times with customers. Basically, we followed the documentation for clustering SSO. We created a specific role for the SSO service and added it as a generic service. The only “problem” we experienced was that while configuring the SSO service on a machine, it could not be the primary server for the SSO AG. I don’t remember the exact error massage at the moment and away from my notes. One thing I remember was important was to take the service offline in the cluster and bring it online before restoring the secret on the second server.
  
  Reply
  1. BizTalkers (@BizTalkers) January 17, 2018 at 17:40
    
    The error I was getting for this (on the primary node) was: =”
    When I changed to a secondary db node then the SSO configuration wizard worked ok.
  2. BizTalkers (@BizTalkers) January 18, 2018 at 09:18
    
    The error I received each time I attempted to configure the master secret on the primary AG server was ”The dependency service does not exist or has been marked for deletion”
Mani Vasisht March 22, 2017 at 09:37

Thank you Mr. Kastberg, I think it is here I am doing something wrong , will try again. Kind Regards

(“One thing I remember was important was to take the service offline in the cluster and bring it online before restoring the secret on the second server.”)

Reply
Asad Khan April 24, 2017 at 03:59

Hi skastberg,
Found your article very useful. Thanks for putting it together.

One thing I noticed that in Bam configuration, you specified BAM Archive database to be on ‘agmgmt’ availability group. Since it is an on-prem setup, so BAM Archive does not need to be on a separate sql instance, as specified in the diagram. Wondering if there is any specific reason for putting Bam Archive in agmgmt? Also, does it need some special settings at the availability group level, since SQL Analysis services and SQL Integration Services will be accessing this database?

Reply
1. skastberg April 30, 2017 at 16:14
  
  Hi Asad,
  
  Thanks. glad you liked it! Sorry for the delay in responding.
  I had no special reason to put it on agmgmt, more than I needed to put it somewhere. If you add them to the ag instances it should not be in the same instance as BAMPrimaryImport since there will be distributed transactions when archiving data. No special setting as I understand it.
  
  Samuel
  
  Reply
TechBook May 1, 2017 at 14:00

Why did you choose linked server when you had AG listener name? This is fully normal in a BizTalk Server environment with several SQL Server instances involved.

Reply
1. skastberg May 1, 2017 at 15:38
  
  The linked servers on the primary instances are created during BizTalk Server configuration as needed, as you say completely normal in an environment with several SQL Server environment. The linked servers I create are on the secondary SQL Server instances and those are not create while configuring the group.
  
  Samuel
  
  Reply
Amit K June 1, 2017 at 19:55

Hi Sam

Appreciate & accolades for your efforts and knowledge sharing. We have BizTalk 2016 environment setup from guidelines on your blog, I have requirement for ESB configuration in BizTalk 2016 environment and I couldn’t find any MSDN doc which speaks on ESB, Which DB ESB should be configured with ?

Reply
1. skastberg June 24, 2017 at 13:10
  
  Hi Amit,
  I have to pass on that one at the moment. If I have some news I get back to you.
  
  Reply
DevBlog June 5, 2017 at 15:24

Why did you choose linked server when you had AG listener name? The only “problem” we experienced was that while configuring the SSO service on a machine, it could not be the primary server for the SSO AG.

Reply
1. skastberg June 24, 2017 at 12:23
  
  BizTalk use the linked servers to communicate between the SQL Server instances, in the linked server configuration you have settings like which user to use. Correct the SSO service cannot be on the primary while configuring, that will be another post sometime 😉
  
  Reply
Jo June 6, 2017 at 00:45

The issue with SQL Server Agent needing the Shared Memory protocol can be fixed by installing an updated Microsoft ODBC Driver 13 (13.1+) for SQL. It is due to SQL 2016 SP1 over writing the version 13.1 driver.

Reply
1. skastberg June 24, 2017 at 12:18
  
  Thanks Jo
  Samuel
  
  Reply
kenneth June 25, 2017 at 10:09

One thing I noticed that in Bam configuration, you specified BAM Archive database to be on ‘agmgmt’ availability group. The linked servers on the primary instances are created during BizTalk Server configuration as needed, as you say completely normal in an environment with several SQL Server environment.

Reply
1. skastberg June 25, 2017 at 17:18
  
  Note that if you include BAM archive in the Biztalk backup job it cannot be in that instance. I don’t have it in my setup, thus ok to have it there.
  
  Reply
How to cluster the Enterprise Single Sign-On (SSO) Service on SQL Server 2016 Always On Availability Groups cluster in Azure - BizTalkGurus November 23, 2017 at 21:51

[…] this problem. We assume the Always On cluster is already installed. If not, you can find directions here and […]

Reply
Gert Lievens April 18, 2018 at 17:25

We are setting up SQL Always On for BizTalk Server 2016. I have created 4 named instances on each node. Everything works fine, but I wonder why you are not providing the Instance name when configuring the Data stores in the BizTalk Server Configuration. How can this work? As far as I know, this is not possible because you always have to provide the Instance name. Did you use SQL Aliases?

Reply
1. skastberg April 19, 2018 at 13:00
  
  The instances are configured with dynamic ports. I have set each availability groups listener to respond on port 1433, that way each instance will respond on port 1433 for its designated ip address. I have not had any issues with this setup.
  Samuel
  
  Reply
San November 19, 2018 at 18:10

We are setting up SQL Always On for BizTalk Server 2016. Can I use two availability groups instead of 4 AG recommended ? trying to understand Active -Active scenario with 2 BizTalk servers and 2 sql servers. Provide some info on above scenarios.

Reply
1. skastberg November 19, 2018 at 18:25
  
  Hello San,
  
  Yes you can do so, as long as you install SP2 for SQL Server 2016 and BizTalk Server 2016 CU5. I have written a post about those changes https://skastberg.wordpress.com/2018/06/28/availability-group-improvements-with-sql-server-2016-sp2-and-biztalk-2016-cu5/
  BizTalk itself can be run Active -Active. With SQL you decide which server is primary replica for a certain AG then having some of the databases as primary on one of the servers and the others on the others. I would separate in two instances with one AG on each and dedicate one of them to the msgbox due to the MDOP=1 setting.
  
  Reply
  1. San November 27, 2018 at 18:11
    
    Thank you skastberg.
    1) If I consider msgbox only DB in an Availability Group (AG), then what is the mechanism to sync both the instances (Primary and Secondary DBs)
    2) What about the licenses in above scenario? do we need two SQL licenses for Primary and Secondary DBs for msgbox in an AG?
    
    Thanks
    Santosh.
  2. skastberg November 29, 2018 at 20:47
    
    1) Not sure if I understand exactly what you mean, I see two alternatives and try to respond both variants.
    Each AG is individual and not aware of the other one, there is no process to synchronize them.
    In short the primary to secondary synchronization: A process on the primary SQL detects that changes are written to the transaction log. The changes are replicated to the secondary replica that sends back an “ack” when it has written to its log. Then the primary continues.
    
    2) Licensing is a bit outside of my scope but generally you would pay full license for each SQL Server that is active/readable. I would contact the vendor that help you with licenses to get the right information.
    
    Thanks
    Samuel
ravindarjobs December 27, 2018 at 01:50

Hi skastberg, where can i find the script Export-LoginsAndJobs.ps1 ? Any download link?

Reply
1. skastberg December 27, 2018 at 07:29
  
  Hi Ravindarjobs,
  I haven’t published the script, no special reason not to. I will share it later today in a short post.
  Samuel
  
  Reply
2. skastberg December 27, 2018 at 12:53
  
  Now I have created a repository in GitHub and ExportLoginsAndJobs is the first script I added. https://skastberg.wordpress.com/2018/12/27/script-repository-in-github/
  
  Reply
Santosh January 28, 2019 at 08:16

Hi skastberg.
We are configuring BizTalk Server 2016. We have two server, one is for BizTalk and another is for SQL server.
In BizTalk Configuration, while configuring BAM, we are getting below exception.
Error configuring BAM Tools (BAMTools)
Additional Information:
Error executing bm.exe with tracing enabled. (Microsoft.BizTalk.Bam.CfgExtHelper.Utility)
Error: Failed to Set up BAM database(s)
Database’BAMAnalysis had compatibility level larger than 1102 with StorageEngineUsed set to InMemory. Tabular databases with compatibility level at 1200 or above must use StorageEngineUsed set to TabularMetadata.
(Microsoft.BizTalk.Bam.CfgExtHelper.Utility)
Did you seen this type of exception anytime? if you have any solution, please let me know.
Thanks & Regards
Santosh.

Reply
1. skastberg January 31, 2019 at 22:37
  
  Hi Santosh,
  Sorry for the delay responding. Analysis Services need to be installed in multidimensional mode. This post might help. https://social.msdn.microsoft.com/Forums/sqlserver/en-US/314f7cc6-1af5-44b8-895c-910816d386f1/unable-to-install-bam-in-biztalk-server-2016?forum=biztalkgeneral
  
  Reply
Marshall October 15, 2019 at 15:37

Hello, I saw you mentioning in one of the replies MaxDop 1 is needed for the instance where the msgbox database is landed. Is it also supported to configure this with Database Scoped Configurations? Is there a docs.microsoft.com page that discusses the recommended settings for SQL Server in combination with BizTalk?

Reply
1. skastberg October 16, 2019 at 11:59
  
  Hello Marshall,
  
  From a supportability perspective it’s doubtful to use database scope. The product group haven’t tested the scenario, thus they don’t give any warranties. I generally recomend my customers to go the other way, since it’s just MessageBox database that need the setting you could set 1 at instance level and another value on all the other databases.
  
  This page is probably what you’re looking for https://docs.microsoft.com/en-us/biztalk/technical-guides/checklist-maintaining-and-troubleshooting-biztalk-server-databases
  
  Reply
  1. Marshall October 16, 2019 at 16:06
    
    Thanks for your swift reply. Appreciated. Sounds like a good recommendation when consolidating with other databases indeed.
    
    Also thank you for the link, exactly the page I was looking for. One thing I’m missing there are the settings around MSDTC. Mainly the preferred SQL Server configuration for “in-doubt xact resolution”. I’m struggling with this since I’m hitting customers not wanting to use Clustered DTC and relying on Local DTC might bring the need to configure this pro-actively to not end up with a suspect database.
  2. skastberg October 19, 2019 at 23:57
    
    Well, there is no right answer to the “in-doubt xact resolution”. Having one is right! 😉 Both presuming abort and commit will give some situations when it’s wrong. In some of the discussions I had with peers we landed in presume commit might give less inconsistencies (take that more as a asumption than fully grounded knowledge ). I have talked with one customer that could reproduce a scenario failing transactions using local DTC, so it can definitely happen in some situations. Sorry for not being able to be more exact in my response but this is a difficult area with a lot of if and but …
Adam middleton June 11, 2020 at 09:49

Hi Sam, great post and massively useful.

I’ve got BT2016 setup in an AG as above, but when adding the SSO Service to the Windows Cluster Role for the AG the SSO Service seems to ‘hang’ after the SQL resource moves over.
Primary services all stop fine, and SQL ‘flicks over’ instantly as expected, but SSO startup seems to hang and thinks the DB is not available – it does usually come back online after restarting the service, but can take 2-4 minutes.

Is this something you’ve come across before?

Reply
1. skastberg June 11, 2020 at 15:53
  
  Hi Adam,
  
  Thanks!
  This issue should be fixed with SSO/HIS 2016 CU1 https://support.microsoft.com/en-us/help/4466425/cumulative-update-1-for-enterprise-single-sign-on-in-his-2016
  
  Start with that and reach out if it does not help.
  
  Cheers
  Samuel
  
  Reply
  1. Adam Middleton June 15, 2020 at 10:52
    
    Thanks for that, i had to double check but i have already installed that KB/Patch.
    A bit more investigation has thrown up that it seems to be a result of the second node being ‘unreadable’
    
    I’m not sure why it would care – as such, as the secondary node should be primary by the time the service tries to start following the failover.