Test your Liquid transformations without deployment

I work on a project doing integration with Azure Integration Services and Azure Functions. As always in integration projects doing mapping is a key area. I’m used from BizTalk development to be able to test my maps without doing deployment which makes it easy to develop in an iterative manner. While working with Logic Apps I started using Liquid transformations and did not find any tool to help with that. Logic Apps transforms with the DotLiquid library (C# Naming conventions). With that information in hand I created a program to test. I separated it in two a library, LiquidTransformationLib.dll, and a program, LiquidTransform.exe. The library makes it easy to use in automated tests.

Parameters for LiquidTransform.exe:

Parameter Description Required
-t | –template Full path to the Liquid template to use. Yes
-c | –content Full path to Content file. Yes
-d | –destination Full path to destination file. Will overwrite an existing file Yes
-r | –rootelement Root element to add before render. For Logic Apps you will need to use content No
-u | –rubynaming Use RubyNamingConvention, Logic Apps use C# naming conventions and will be the default. No
-? | -h | –help Show help information No

liquidtransformYou can download source code from the GiHub repo https://github.com/skastberg/LiquidTransformation

If just want the binaries https://github.com/skastberg/LiquidTransformation/tree/master/Binaries

More information about Liquid Transformation in Logic Apps:

https://docs.microsoft.com/en-us/azure/logic-apps/logic-apps-enterprise-integration-liquid-transform

https://blogs.msdn.microsoft.com/logicapps/2017/12/06/introducing-the-liquid-connector-transform-json-to-json-json-to-text-xml-to-json-and-xml-to-text/

 

Availability Group improvements with SQL Server 2016 SP2 and BizTalk 2016 CU5

SQL Server 2016 Availability Groups has a limitation, two databases involved in a distributed transaction cannot reside in the same SQL Server instance. For a BizTalk Server installation that will be using Availability Groups this means you need to separate the databases in several SQL Server instances, making the installation more complicated than desired.

SQL Server 2016 SP2 adds the functionality that solves this problem, thus two databases involved in a distributed transaction can be in the same SQL Server instance. BizTalk Server 2016 CU5 and FP3 adds support for this functionality. As a result, BizTalk Server 2016 installations using Availability Groups can be configured using less SQL Server instances if desired.

Database location rules when using Availability Groups

Rules for database location when using Availability Groups with BizTalk Server installations.

Version Rules
Prior to SQL Server 2016 – BizTalk Server 2016 Distributed transactions not supported, thus not supported for BizTalk Server.
SQL Server 2016 (RTM-SP1)

BizTalk Server 2016 (RTM-CU4)

Instance Role BizTalk Databases in that group
1 Authentication SSODB
2 Management BizTalkMgmtDb
3 Runtime BizTalkMsgBoxDb
BizTalkRulesEngineDb
BAMPrimaryImport
BAMStarSchema
BAMAlertsApplication
4 Tracking BizTalkDTADb
EsbItineraryDb
EsbExceptionDb
SQL Server 2016 SP2 and later

BizTalk Server 2016 CU5 and later

Free database placement

 

Note: Other limitations as described in the documentation are still valid.

SQL Server 2016 SP2 https://go.microsoft.com/fwlink/?linkid=869608

BizTalk Server 2016 CU5 https://support.microsoft.com/en-us/help/4132957/cumulative-update-5-for-microsoft-biztalk-server-2016

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
https://blogs.msdn.microsoft.com/biztalkhealthmonitor/
https://blogs.msdn.microsoft.com/biztalkhealthmonitor/2014/06/26/overview-of-biztalk-health-monitor-bhm/

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
Example:
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.
Example:
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.
Example:
Set g_objShell = createobject(“Wscript.shell”)
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.
Example:
$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.
Example:
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.
Example:
Tasklist.exe

ExtBHM-01

  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

Targets

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.
    ExtBHM-06
  3. Select Commit changes
  4. Select and edit the default condition
    ExtBHM-07
  5. Select Commit changes
  6. Add more conditions if needed
    ExtBHM-10
  7. Edit the default action to match your requirements
    ExtBHM-08
  8. Select Commit changes
  9. Test and Save
    ExtBHM-09

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.

ExtBHM-11

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.

AG-Report

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.

Replicas

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.

Databases

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

Download the script with some fixes for failed backups in the chain and secondary replica not readable RestoreScriptForBizTalkJob.4.1.0  Note: Only tested on a single instance 2016 machine.

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.

Mandatory

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.

Reminders

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

FirstLookAtRestAPI.Module

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

Conclusion

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!