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.

1. Added names and the addresses to use to my DNS. Cluag for the cluster and the others for my listerners.
   
2. Add Failover Clustering Feature to the servers.
   
3. Create the cluster
   
   
4. 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] GO   CREATE ENDPOINT [Hadr_endpoint]             AS TCP (LISTENER_PORT = 5050)             FOR DATA_MIRRORING (ROLE = ALL, ENCRYPTION = REQUIRED ALGORITHM AES) GO   IF (SELECT state FROM sys.endpoints WHERE name = N’Hadr_endpoint’) 0 BEGIN             ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED END GO   use [master] GO   GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [MYDOMAIN\SA-BTS-SVC] GO   :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 GO   :Connect SERVER02\MGMT USE [master] GO CREATE ENDPOINT [Hadr_endpoint]             AS TCP (LISTENER_PORT = 5050)             FOR DATA_MIRRORING (ROLE = ALL, ENCRYPTION = REQUIRED ALGORITHM AES) GO   IF (SELECT state FROM sys.endpoints WHERE name = N’Hadr_endpoint’) 0 BEGIN             ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED END GO use [master] GO GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [MYDOMAIN\SA-BTS-SVC] GO   :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   GO   :Connect SERVER01\MGMT   USE [master] GO   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));   GO   :Connect SERVER01\MGMT   USE [master]   GO   ALTER AVAILABILITY GROUP [AGMGMT] ADD LISTENER N’AGMGMT’ ( WITH IP ((N’10.100.100.50′, N’255.255.255.0′) ) , PORT=1433);   GO   :Connect SERVER02\MGMT   ALTER AVAILABILITY GROUP [AGMGMT] JOIN;   GO   :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   GO   :Connect SERVER02\MGMT   RESTORE DATABASE [AGTest] FROM  DISK = N’\\SERVER01\AG-Backup\MGMT\AGTest.bak’ WITH  NORECOVERY,  NOUNLOAD,  STATS = 5   GO   :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   GO   :Connect SERVER02\MGMT   RESTORE LOG [AGTest] FROM  DISK = N’\\SERVER01\AG-Backup\MGMT\AGTest_Startup.trn’ WITH  NORECOVERY,  NOUNLOAD,  STATS = 5   GO   :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 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];   GO

 

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.