Scripting Hosts, Instances and Handlers

Having a common set of Hosts and Handlers over different environments is a quite common practice. Being able to script the setup is a great way to ensure you have the same set hosts and settings. A good approach when you write scripts to handle Hosts, instances and handlers is to be able to re-run them over and over and just creating new if not present.

Sample scripts can be found here:

Create a host and instances

I use the PowerShell Provider that comes with BizTalk Server which I find handy. If you prefer that you could use the WMI classes MSBTS_Host, MSBTS_HostInstance and MSBTS_ServerHost. The sample below uses the PowerShell Provider. Note that for hosts I do a Push-Location to “BizTalk:\Platform Settings\Hosts\“. To create objects with the Provider, navigating to the right location is a must before calling New-Item.

To create a host


To create an instance


The full samples can be found in the BizTalk-Common.psm1.

Making a host handler for an adapter

To configure handlers with WMI use the classes MSBTS_ReceiveHandler and MSBTS_SendHandler2. Doing it with the PowerShell Provider is straight forward using New-Item.


The full sample can be found in the BizTalk-Common.psm1.

Exporting and importing Hosts, instances and handlers

A lot of customers have asked for a way to “copy” their setup in let’s say QA to Production. One tool you could use is the BizTalk Server Migration Tool in which you can select what parts you want to move, that said you might want to change some settings along the way. I have written a script (Export-BizTalkHostsRemote.ps1) to export Hosts, Host Instances and Handler to a CSV file and another (Configure-Hosts.ps1) to import using the edited output from the export. I could have used the existing settings export but wanted to easily be able to edit the file in Excel. I have intentionally left out throttling settings. Hope you find them handy, if you find any issues let me know.

Format of the file

Column Comment
HostName Name of the host
HostType Expected values InProcess or Isolated
GroupName Windows Group that controls access to the host
AuthTrusted Authentication Trusted setting in the properties dialogue.
IsTrackingHost Allow host tracking setting in the properties dialogue.
Is32BitOnly 32-Bit only setting in the properties dialogue.
MessagingMaxReceiveInterval Messaging Polling Interval for the host
XlangMaxReceiveInterval Orchestration Polling Interval for the host
InstanceServer A pipe | separated list of instances


InstanceUser Service Account used for the host instances
InstanceUserPwd In the export same value as InstanceUser for the import it expects path to a password in a KeePass database
ReceiveHandler A pipe | separated list of Receive adapters for this host.


SendHandler A pipe | separated list of Send adapters for this host. A * will mark this as the default host.



Configure BizTalk Server with Script

To configure BizTalk Server with script you need a configuration file that describes your choices. You can export it from an existing environment or run Configuration.exe do your selections and export. To reuse the file, you will need to edit it to match the new environment. Generally, I create template files with tokens that I replace at configuration time. You can read about the configuration framework here.


The configuration files differ slightly between the first server in which you create the group and the secondaries that joins the group. In the image below you see that the Selected Attribute differs. The same kind of change for SSO if you create the group on the first server.

Compare Configs

Handling secrets

In the configuration files you will enter passwords for service accounts and SSO Backup. One alternative could be to use KeePass files that are encrypted or Azure KeyVault. That said at some point you will have secrets in clear text so ensure you delete the configuration file when you’re done. Below a sample function to extract the secrets from a KeePass database using the PoShKeePass module. You can find information extracting KeyVault secrets here.


The execution itself is quite straight forward starting Configuration.exe with /S parameter


Other configurations

Something I see quite often is that customers forget to configure Backups and DTA Purge jobs in a timely manner which leads to unnecessary database growth. You don’t need many lines of code to configure them.


Other potential targets are to register the BizTalk PowerShell provider, WinSCP setup for the SFTP adapter and third-party software.

Sample scripts can be found here:


Installing BizTalk Server binaries with script

Feature installation

When installing BizTalk Server with script you will need to tell the setup program what features to install. You can use the /ADDLOCAL parameter using a list of features as described here or use a configuration file with the /S parameter. I prefer to use the later by exporting the features from a reference installation or a previous version installation (Compare an export from previous versions with the list below since some features are no longer valid).

This table shows an export for features from the msi-files, I have matched them with a configuration export from a fully installed BizTalk Server. The feature column is the name that must be used, and it is case sensitive.

Feature Parent Feature Description
Documentation Selecting the Documentation component installs the core documentation, tutorials, UI reference (F1 help), programmer’s reference, and usage instructions for the SDK samples and utilities.
AdditionalApps Additional Software is a set of optional components that extend the functionality of Microsoft BizTalk Server.
BAMEVENTAPI AdditionalApps Select the BAM-Eventing Support component to install necessary software for the BAM-Eventing Interceptors for Windows Workflow Foundation and Windows Communication Foundation. Selecting this component also installs the BAM Event API that is used to send events to the BAM database from custom applications.  BAM-Eventing Support is part of the Business Activity Monitoring feature in Microsoft BizTalk Server.
FBAMCLIENT AdditionalApps Selecting the BAM Client component installs the necessary client side software that allows business users to work with the Business Activity Monitoring feature of Microsoft BizTalk Server.
MQSeriesAgent AdditionalApps Selecting the MQSeries Agent component installs the necessary software that enables Microsoft BizTalk Server to send and receive messages to an MQSeries message bus.
OLAPNS AdditionalApps Selecting the BAM Alert Provider component installs the necessary software that enables Microsoft BizTalk Server to provide Business Activity Monitoring (BAM) alerts.
ProjectBuildComponent AdditionalApps Project Build Component enables building BizTalk solutions without Visual Studio.
RulesEngine AdditionalApps Selecting the Business Rules Composer and Engine component installs the necessary software to compose policies that are consumed by the Business Rules Engine.Engine component provides a mechanism for capturing dynamically changing business policies and the ability to implement those changes quickly within and across business applications.
SSOAdmin AdditionalApps Selecting the Enterprise Single Sign-On (SSO) Administration component installs the necessary software for administering, managing, and connecting to SSO Servers.
SSOServer AdditionalApps Selecting the Enterprise Single Sign-On (SSO) Master Secret Server component installs the necessary software that enables this server to become the master secret server, store the master secret (encryption key), and generate the key when an SSO administrator requests it.To use this feature, you must also install the following: Enterprise Single Sign-On (SSO) Server.
AdminAndMonitoring Selecting the Administration Tools component installs the necessary software to administer Microsoft BizTalk Server. To use this feature, you must also install the following: Enterprise Single Sign-On (SSO) Administration.
AdminTools AdminAndMonitoring This feature contains tools to monitor, administer, and deploy onto a Microsoft BizTalk Server solution. These tools include MMC snap-ins, Health and Activity Tracking, Deployment Wizards and other tools for monitoring, administration and deployment.
BAMTools AdminTools Business Activity Monitoring administration
BizTalkAdminSnapIn AdminTools Configure and manage Microsoft BizTalk Server.
HealthActivityClient AdminTools Health and Activity Tracking Client
MonitoringAndTracking AdminTools Health Monitoring, Reporting and Tracking tools
PAM AdminTools PAM
WcfAdapterAdminTools AdminAndMonitoring Windows Communication Foundation Administration Tools
Development Selecting the Developer Tools and SDK component installs samples and utilities that enable the rapid creation of Microsoft BizTalk Server solutions. This includes: SDK samples and supporting documentation, BizTalk Explorer, schema and map designers, and Visual Studio 2015 project templates. This component requires Visual Studio 2015.To use this feature, you must also install the following: Enterprise Single Sign-On (SSO) Administration.
DeploymentWizard Development Deploy, import, export and remove BizTalk assembly
Migration Development Migration
SDK Development Provides programmatic access to Microsoft BizTalk Server
SDKScenarios SDK SDK Scenarios
Note: I’m not sure of the usage, it is not included in a configuration export but it is accepted and noted in the logs when used.
TrackingProfileEditor Development Business Activity Monitoring Tools
VSTools Development Visual Studio Tools
WCFDevTools VSTools Windows Communication Foundation Tools
BizTalkExtensions VSTools Biz Talk Extensions
AdapterImportWizard BizTalkExtensions Adapter Import Wizard
BizTalkExplorer BizTalkExtensions Manage BizTalk Configuration databases
MsEDISchemaExtension BizTalkExtensions Microsoft EDI Schema Design Tools
XMLTools BizTalkExtensions XML Tools
Designer BizTalkExtensions Orchestration and Pipeline designers
OrchestrationDesigner Designer BizTalk Orchestration Designer
PipelineDesigner Designer BizTalk Pipeline Designer
MsEDISDK Development Microsoft EDI SDK
MsEDIMigration MsEDISDK Selecting the Microsoft EDI Migration Wizard installs the necessary software that enables migration of existing EDI documents to
BizTalk BizTalk
InfoWorkerApps  The Portal Components are a set of services used by business people to communicate, collaborate, and reach decisions enabling them to interact, configure, and monitor business processes and workflows. To use this feature, you must also install Internet Information Services (IIS).
BAMPortal InfoWorkerApps  Selecting the Business Activity Monitoring component installs the necessary software that gives business users a real-time view of their heterogeneous business processes, enabling them to make important business decisions. To use this feature, you must also install Internet Information Services (IIS).
Runtime  Selecting the Server Runtime component installs the runtime services for Microsoft BizTalk Server. These runtime services are an essential part of the BizTalk Server platform. To use this feature, you must also install the following: Enterprise Single Sign-On (SSO) Administration, Enterprise Single Sign-On (SSO) Server.
Engine Runtime  The Engine feature contains components for performing messaging, orchestration, and tracking functions. This is the core runtime component of Microsoft BizTalk Server. This option also includes Enterprise Single Sign-On components to allow encryption of configuration data.
MOT Engine  Messaging, Orchestration, and Tracking runtime components.
MSMQ Engine  BizTalk Adapater for Microsoft Message Queue Service.
MsEDIAS2 Runtime  Selecting the BizTalk EDI/AS2 Runtime components installs the necessary software that enables Microsoft BizTalk Server to process documents in the Electronic Data Interchange (EDI) format.
MsEDIAS2StatusReporting MsEDIAS2 Microsoft EDI/AS2 Status Reporting
WCFAdapter Runtime  Selecting the Windows Communication Foundation Adapter component installs the necessary software that enables Microsoft BizTalk Server to integrate with Windows Communication Foundation.


Example of an installation execution in PowerShell

Start-Process -FilePath $fullPathToBTS -ArgumentList "/S $fullPathToConfig /CABPATH `"$FullPathToCab`" /norestart /l $logFullname /companyname CONTOSO /username CONTOSO" -Wait

Adapter Pack installation

The adapter pack can also be installed silently. The process consists of several steps since you have the SDK and adapters and for each one of them 32 and 64 bit to include. In the setups I do with customers we tweak the installation to match the features that will be used on the specific installation. Detailed description on the parameters can be found here. In the samples you find a function that installs both the SDK and the adapters.

Example of an installation execution in PowerShell that will install WCF-SQL and Oracle DB adapters.

Start-Process -FilePath "$cmd" -ArgumentList "/qn ADDLOCAL=SqlFeature,DbFeature CEIP_OPTIN=false" -Wait

CU and Feature Packs

Keeping your systems updated is a good practice and you can install both CUs and Feature packs silently. In the samples you find a function that installs CU. Scripted installation of CUs and Feature pack is straight forward and you can get the required parameters running the installer with /?.


Example of an installation execution in PowerShell that will install a CU or Feature pack.

Start-Process -FilePath $fullPathToCu -ArgumentList "/quiet /s /w /norestart /log $logFullname" -Wait

Good to remember

Add a check to see if your Virus scanner is enabled. The installation process will try to stop WMI which many scanners use thus protect from an it will lead the installation to fail.

While doing your test installations, you will probably want to do some retries. Most features can be uninstalled so you can write another script to uninstall, then you can start over with your installation.

$installedMsiObject = Get-WmiObject -Class Win32_Product | Where-Object { $_.Name -like "*BizTalk*"

if ($installedMsiObject) {

try {
$installedMsiObject.UnInstall() | Out-Null
catch {
  Write-Error "Error occurred: $_"

Sample scripts can be found here:

Automating BizTalk Server installation and configuration

Installing and configuring BizTalk Server can be complex, time consuming and error prone. The complexity comes not from the process itself but more from all the different components and possible configurations. My objective is to share my experiences from working with several customers and some techniques you can use to create your perfect installation. The objective is not to show “the perfect” process, perfect for me might not be perfect for you. In this post I share an overview, later I will do some more with more details and share some of the functions I use.

Before you start

Decide what your main drivers are and let that be your guide through the creation. Repetition and control are generally the drivers to automate, and the goal standardized developer machines, disaster recovery preparation, test environments or ensuring the environments are equally configured over our Dev to Production pipeline.

Decide what your baseline is and document it, think what could change in 6 month or a year. With one customer we created a brilliant setup that started with bare Windows installations and within 2 hours a highly available solution was in place. Discussing a year later we concluded that creating the LUNs and volumes might have being overdoing it since the underlying storage will be changed. It would have been better to have it as a requirement in our baseline or as a distinct step in the process that could be very easily changed or replaced.

Consider internal organization and politics, i.e. if you will never be allowed to install SQL Server or create groups put it in your baseline document as a pre requisite.

Set a timeframe for your work, if not you can spend weeks searching for perfection instead of reaching your goal. If you’re too ambitious you might end up in a overly flexible process that just is a parallel to the normal one, not a good idea.

Document the execution process. Write the running order of scripts and shortly what each one does.


Generally, Windows installation is already taken care of and I see it as a part of the baseline. That said, you should ensure that the features and configurations you need are in place. You will need the Windows installation source and can use PowerShell Cmdlets like Enable-WindowsOptionalFeature or Install-WindowsFeature (will not work on Windows 10). I find this post good to find features and decide which one to use.

Sample Script to enable Windows Feature

BizTalk Server

Setting up BizTalk Server product consists of two parts, installation and configuration. Installation will add the binaries to the system. Configuration will create or join a BizTalk Group and enable/configure other features as Rules Engine.

When running Setup.exe with the /S command it will use the list of InstalledFeature elements in the configuration file you specify. The silent installation details are documented here.

When running Configuration.exe with the /S command it will use the Feature elements. Each Feature element represents a section in the configuration dialog box. I will look more in depth on this on another post.

Sections of the configuration file

Installation of additional software like Adapter Pack, CU/Feature Pack, WinSCP (needed for SFTP adapter) can also be installed silently. Setting up hosts and handlers can also be done.

SQL Server

SQL Server can be silently installed and use configuration files to use the configurations you will need. I leave this with a pointer to the documentation.

Things I have scripted post installation are

  • Setup of Availability Groups and creating empty BizTalk Databases with the file setup I want to have.
  • Setting in the Primary Check for availability Groups
  • Configuration of Backup and DTA Purge.

Wrapping up

Basically, all parts of setting up a BizTalk Server environment can be done with script. Your needs and environment set the limits. I believe scripting your environment is a good way to get to know the components you’re using. I will follow up with some more posts that will go more into hands on approach to the different parts.

I will do a session on this matter att Integrate 2019 in London See you there!

BizTalk Health Monitor Custom Rules and Queries

BizTalk Health Monitor is a valuable tool to validate the current state of a BizTalk Server Environment. I encourage you to use recurrently review warnings and act on the issues. We use it extensively when doing BizTalk Health Checks and working with support cases. A useful feature is the possibility to add custom queries and rules.

If you want to read more about basic functions of BHM visit the BizTalk Health Monitor Team blog

My sample: ExtBHM-CustomExamples

Adding a Custom Query

BHM Queries collect information to which you can apply rules. If you have some information about your solution that you want to include as part of the health review. Custom queries can be of different types, the possibilities are a bit different, the table below enumerates the types and shows how information is presented. The option I used most is PowerShell, generally I create a script that I can create, and test manually and then just add the full path to the script and send parameters.

Type Comment
WMI WQL Select Statement, all properties included on the select will be accessible for rules and display. About WQL
SELECT AdapterName, HostName, IsDefault FROM MSBTS_SendHandler2 Where ( HostName = ‘TrackingHost’ )ExtBHM-02
SQL SQL Select Statement, all columns included in the Select statement will be accessible for rules and display. Do not query the BizTalk databases. Changes to the BizTalk databases is not advisable and not supported.
SELECT @@SERVERNAME AS ServerName, name, recovery_model_desc FROM sys.databases
Where database_id > 4ExtBHM-03
VBS VB Script, each line printed to the console will be collected but no additional columns. Validate the line text or row count.
Set g_objShell = createobject(“”)
MaxDTCLogSize = g_objShell.RegRead (“HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\MSDTC\MaxLogSize”)Wscript.Echo MaxDTCLogSize
PowerShell PowerShell Script each line printed to the console will be collected but no additional columns. Validate the line text or row count.
$QueryString = Gwmi Win32_OperatingSystem -Comp MyComputer
Write-Host $QueryString.CaptionExtBHM-04
BAT A batch of commands each line printed to the console will be collected but no additional columns. Validate the line text or row count.
ECHO LISTENING && netstat -ano | find /c “LISTENING”
ECHO SYN_SENT && netstat -ano | find /c “SYN_SENT”
ECHO SYN_RCVD && netstat -ano | find /c “SYN_RCVD”
ECHO ESTABLISHED && netstat -ano | find /c “ESTABLISHED”
ECHO FIN_WAIT_1 && netstat -ano | find /c “FIN_WAIT_1”
ECHO CLOSING && netstat -ano | find /c “CLOSING”
ECHO FIN_WAIT_2 && netstat -ano | find /c “FIN_WAIT_2”
ECHO TIME_WAIT && netstat -ano | find /c “TIME_WAIT”ExtBHM-05
CMD Output from one command each line printed to the console will be collected but no additional columns. Validate the line text or row count.


  1. Navigate to the profile you want to customize, tab Queries
  2. Click on New Query after selecting the type you want to use
  3. Add the Query commands
  4. Set properties like Caption and target
  5. Test and select Ok when done


Each query has a target which is the name of the server where to run the query. There are some macros to address the query to multiple servers in an environment. You can use ALLBTSVR to run a query on every BizTalk Server by setting the TargetID element in the configuration file.

Note: I have only made it work with PowerShell queries.

Adding a Custom Rule

A rule acts on the data collected with a query. Rules can be set to give a warning on each failing instance or once for all errors. You can have more than one condition for a rule. In a condition you can validate a column value (%GLOBALPROP_REPORTVALUE:##COLNAME##%) or the number or rows generated by the query(%GLOBALPROP_ROWCOUNT%). Actions give you the possibility to act when conditions are met. Actions can use data from the failing rows and have URLs to documentation, i.e. to a wiki with information on what to do.

  1. Navigate to the profile you want to customize, tab Rules
  2. Write a name and add description comments. Set evaluation rows and trigger actions.
  3. Select Commit changes
  4. Select and edit the default condition
  5. Select Commit changes
  6. Add more conditions if needed
  7. Edit the default action to match your requirements
  8. Select Commit changes
  9. Test and Save

Activating the rules

Now that we have rules we need to activate them by going to the Information Level tab and select the custom queries we want to have in the profile.


Moving the profile to another environment

The profile configuration files are stored under %ProgramData%\Microsoft\BizTalkHealthMonitor as xml files with a name starting with MBVSETTINGS_. Simply edit the file to match the names on the new environment and save it in the new location.

Validate your BizTalk Availability Groups

An installation using SQL Server Always On Availability Groups synchronizes the databases between replicas. It is important that the databases in the different replicas are synchronized. Since transactional consistence is a priority for BizTalk Server Synchronous commit is a requirement. SQL Server 2016 introduced support for distributed transactions but only at the database level. BizTalk Server databases must be separated into at least 4 availability groups with databases divided based on how BizTalk uses distributed transactions internally. Other constraints are DTC support enabled, Synchronous commit used for all replicas and backup preference must be set to primary. When we do health checks we collect information and validate the results. To get an overview of the involved availability groups we have a PowerShell script to collect information and do most of the validations.

The script

Run the script AG-Report.ps1. On a BizTalk Server, the script can get group information from Registry, from another server send parameters.

Download the script:

AG-Report-1.1.0  Version 1.1.0 Add functionality to take into account SQL Server 2016 SP2 and BizTalk 2016 CU5 improvements.

AG-Report-1.0.9  Version 1.0.9 fixes an issue when using ports as part of the litener name.

Parameter Comment Required
outputFolder The folder where the file aginfo_yyyyMMddhhss.html will be saved. Yes
mgmtServer The name of BizTalk Management listener. If you don’t provide this parameter management server and DB will be retrieved from “HKLM:\SOFTWARE\Microsoft\BizTalk Server\3.0\Administration” No
mgmtDatabase The name of BizTalk Management database, default “BizTalkMgmtDb” No

The data collection report is saved to a html file.

As always:

The script is licensed “as-is.” You bear the risk of using it. The contributors give no express warranties, guarantees or conditions. Test it in safe environment and change it to fit your requirements.

The report

Output of the report with some explanations.


Availability group

One row for each availability group.

Column Description
Name Name of the availability group
ListenerName Network name of the availability group
Health Health status of synchronization
IpFromCluster Address information from the cluster
DtcSupport Must be true and tells if the AG is configured to support distributed transactions
BackupPreference Must be primary and tells on which replicas backups are taken.


One row for each replica in the availability group

Column Description
ServerName Local servername of the replica
Role Current role in the availability group
Health Health status of synchronization for this replica
Mode Tells how data is synchronized for this replica. Must be synchronous commit
MDOP MDOP setting for this replica
LinkedServers List of linked servers for this replica. This setting should be the same on all replicas in the group but might be different if the instance is used for other purposes. A diff renders a warning.
JobsPrimaryCheckFail Checks if the SQL type steps have the if statement to ensure that the step will only run on the primary replica. OK or a list of jobs failing.
SecondaryRead Behavior of this replica when being in secondary role. ALL means readable.


One row for each database in this AG and can include non-BizTalk databases.

Column Description
DatabaseName Name of the database
Status Online status of the database
Health Health status of synchronization for this database
IsSuspended Tells if synchronization is suspended for this database. Generally, it should be False
IsJoined Has the database started to synchronize? Should be True

Generating restore scripts for BizTalk Server – Version 4

I have created a new version of my PowerShell script to generate restore scripts for BizTalk Server. The basic idea is the same and you can read more about it in my previous post here

I have rewritten the script and done some changes.

Download the script with some fixes for failed backups in the chain and secondary replica not readable RestoreScriptForBizTalkJob.4.1.0 

Support for Availability groups

With the release of BizTalk Server 2016 we can use SQL Server availability groups. The previous version of the script didn’t work with availability groups. I have added a function that checks if the running server is the primary replica or not in an availability group to return true and false if it is a secondary replica. The script just exits if it is a secondary replica.

Script parameters

The new script is intended to be called from the job step using parameters to the file.

Parameter Default value Comment
ServerName Name of the SQL Server including instance containing BizTalk Management database i.e “Server01\myinstance”.

Note: If you use the listener name in an AG you will need to add the instance name.


MgmtDb BizTalkMgmtDb Name of BizTalk Management database

Not mandatory

SqlScriptDestination NULL By default, the script stores the scripts in the full backup folder, use this parameter to redirect to another folder.

Not mandatory


Removed loading SQL Server PowerShell provider

The script does not load the SQL Server PowerShell provider, the reason is that I just wanted the script to be a bit cleaner.

Check if database exists

This version checks if the database exists before setting it to single user mode to avoid unnecessary error messages while recovering.

Tested scenarios

This version of the script has been tested with:

  • BizTalk 2016 using SQL Server 2016 with Availability groups
  • BizTalk 2016 using SQL Server 2016
  • BizTalk Server 2013 R2 using SQL Server 2014

I have tested both generation of the scripts and recovery.


When you recover BizTalk server databases it is all or nothing. All the databases for the group that are included in the backup job must be restored together and to the same transaction mark.

While recovering, ensure that you have stopped all activity in your BizTalk group.

The script is licensed “as-is.” You bear the risk of using it. The contributors give no express warranties, guarantees or conditions. Test it in safe environment and change it to fit your requirements.

Using BizTalk Management REST APIs from PowerShell


With BizTalk Server 2016 Feature Pack 1 we have a lot of nice functionality to use. The feature I just couldn’t keep my hands from was the Management REST API, it gives a fantastic opportunity to create new tailored tools. Since I love PowerShell my first thought was how to use this new feature in my scripting.

Installation and configuration

The installation and configuration of the Feature Pack was straightforward. It’s well described here, I just followed the steps and had no problems at all. Navigating to http://yourserver/BizTalkManagementService/swagger/ will show you the available APIs.

01-Using BizTalk Management REST APIs from PowerShell

Start scripting

I thought, keep it simple and started with a simple script to get all the applications in my environment. Hey, that was deadly simple!

02-Using BizTalk Management REST APIs from PowerShell

So, next step would be to do something a bit more complex, create a new application since that means that I need to send data. We instantiate a new PsCustomObject setting some properties we need, convert it to Json and then post.

03-Using BizTalk Management REST APIs from PowerShell


The Management REST API is a good addition to our toolbox. With just some simple PowerShell cmdlets, Invoke-RestMethod and ConvertTo-Json, we can do a lot. I created a small PowerShell module that handles applications as a sample. You can download it as a starter. Happy scripting!

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

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.

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

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

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. 

:Connect SERVER01\MGMT


USE [master]



CREATE ENDPOINT [Hadr_endpoint]

            AS TCP (LISTENER_PORT = 5050)




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


            ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED




use [master]






:Connect SERVER01\MGMT


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




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






:Connect SERVER02\MGMT

USE [master]


CREATE ENDPOINT [Hadr_endpoint]

            AS TCP (LISTENER_PORT = 5050)




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


            ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED



use [master]





:Connect SERVER02\MGMT


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




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







:Connect SERVER01\MGMT


USE [master]






DTC_SUPPORT = PER_DB) — Changed from none to PER_DB







:Connect SERVER01\MGMT


USE [master]







((N’′, N’′)


, PORT=1433);




:Connect SERVER02\MGMT






:Connect SERVER01\MGMT






:Connect SERVER02\MGMT






:Connect SERVER01\MGMT






:Connect SERVER02\MGMT






: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)


    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


                         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


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



                         waitfor delay ’00:00:10′

                         set @count = @count 1



end try

begin catch

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

end catch






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










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.


Generate restore scripts for BizTalk Server backups

The BizTalk backup process

A BizTalk group’s data consists of a number of databases with data moving between them. To keep a consistent state when taking a backup, the job uses a marked transaction. A description of that process can be found here. Every time the backup job runs some rows are added to the adm_BackupHistory table in the BizTalkMgmtDb database containing information like the MarkName and files. This information is used by BizTalk Log Shipping to keep track of backups.

BizTalk Log Shipping

BizTalk Log Shipping is the only fully supported way of disaster recovery. When backups are taken SQL Server jobs will restore them to a passive server. You can find a description of BizTalk Log Shipping here and how to configure it here. There might be situations when you need to manually restore the databases. This process is both tedious and error prone, especially during a critical situation.

The task explained

You have a number of backup files of data and logs and need to restore manually. When restoring the last transaction logs, you must use the STOPATMARK argument to keep the databases in a consistent state. What can we do to be prepared in this situation? Having a script prepared to restore is a good way. The best moment to generate the script is directly after we have taken the backups. The idea is to add an extra step to the backup job that generates a restore script based on the information in the adm_BackupHistory table, using the same information as the BizTalk Log Shipping.

Note: You should never change the logic of the BizTalk Server jobs; in this case we’re augmenting the process without changing what the backup job does.

The script

How it works

What do the script need to do to create the restore script?

  • Get last mark name from adm_BackupHistory
  • Get last full backup file information from adm_BackupHistory
  • Loop all databases
    • Generate full database restore script for the current database
    • Get log file information for the current database from adm_BackupHistory
    • Generate log restore script for the current database using with no recovery for all except for the last one which have the current mark. For the file with the current mark generate script using STOPATMARK.
Configure the script

In the top of the script you have three variables that might need to be updated.

Variable Comment
$ServerName Database Server name where BizTalk management database is located. If it is located on a named instance the instance name must be provided.
$MgmtDb The database name for the BizTalk management database.
$SqlScriptDestination Default set to $null, then the generated scripts will be stored in the same location as the full backups. Set a path if you want the scripts saved on a different location.


Getting the script in place

1. Add an extra step to the backup job of PowerShell type. Add your version of the script in the command field or the full path to the PowerShell script.
Note: If you have implemented Log Shipping you must use a file since the script is longer than the 3200 characters that the Log Shipping process will copy.

2. Change the ”Clear Backup History” step to go to next step on success. Also set the new step to quit responding success or failure.

The resulting SQL scripts

For each backup a new set of script files is created, one file for each database instance that is included in the environment. The naming is “Server_Instance” (Green rectangle in the picture), “Markname of starting full backup” (Blue) and then the “last mark” (Red).


Each database will be set to single user mode and close all open connections. All the databases in the backup have a restore database using with move in the expression so you have the possibility to change the location of the database files. Every restore of logs have NORECOVERY except the last one that use STOPATMARK to stop at the transaction mark.

Restore process

When you are in the process of restoring and have an environment with no activity, I.e. all BizTalk Services and SQL Server agent turned off, you can run the scripts on the database instances where you want to restore. The scripts can be edited to have a different file location if needed. If you’re moving databases to another server don’t forget the normal procedure running the UpdateDatabase.vbs script with and updated SampleUpdateinfo.xml file.

The script is licensed “as-is.” You bear the risk of using it. The contributors give no express warranties, guarantees or conditions. Test it in safe environment and change it to fit your requirements.
Download the code and description 

This version only supports writing the full path to the script in the jobstep.
This version don’t work with Availability groups, a new post is coming soon.