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.

  1. Added names and the addresses to use to my DNS. Cluag for the cluster and the others for my listerners.
    clip_image002[4]
  2. Add Failover Clustering Feature to the servers.
    clip_image004[4]
  3. Create the cluster
    clip_image006[4]
    clip_image008[4]
  4. Configure File Share Witness pointing to a share on the DC.
    clip_image010[4]
    clip_image012[4]

 

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

clip_image014[4]clip_image016[4]

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
clip_image018[4]

3.       Specify a name for the AG clip_image020[4] 

4.       Select your test databaseclip_image022[4]

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.clip_image024[4]

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.
clip_image026[4]

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.
clip_image028[4]

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. clip_image030[4] 

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.
clip_image032[4]

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

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
clip_image036[4]

3.       Run the scripts one by one

4.       Review the created roles and resources in the cluster.
clip_image038[4]

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.

clip_image040[4]

clip_image042[4]

 

 

clip_image044[4]

clip_image046[4]

The resulting linked servers:

Server instance

Linked to

MGMT

DTA, SSO and MBOX

MBOX

DTA and MBOX

DTA

None

SSO

None

clip_image048[4]

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.
clip_image050[4]

4.       Select databases to join

clip_image052[4]

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

6.       Connect to the secondary replica in the AG.
clip_image056[4]

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.
clip_image058[4]

clip_image060[4]

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.

 

Author: skastberg

Integration consultant working as Premier Field Engineer at Microsoft Sweden. My opinions only, most certainly not necessarily those of my employer.

26 thoughts on “Setting up my BizTalk Server 2016 using Availability groups LAB”

    1. 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.

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

    1. 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.

  2. 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.”)

  3. 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?

    1. 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

  4. 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.

    1. 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

  5. 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 ?

  6. 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.

    1. 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 😉

  7. 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.

  8. 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.

  9. 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?

    1. 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

  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.

    1. 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.

      1. 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. 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

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s