Last updated: 2019-06-13

Recommendations for BizTalk Logging

Both your existing BizTalk Server environment and Nodinite Log agent for BizTalk will benefit from additional tuning of the involved BizTalk databases. The changes described here gives better performance due to less disk IO needed/wasted.

We at Nodinite strongly recommends all customers running BizTalk Server to perform the steps listed on this page (with or without our product).

SQL Server

Make sure to keep the tracking database small as large DTA databases may have an impact on the overall performance of BizTalk Server.

For example, the default configuration of the DTA Purge and Archive (BizTalkDTADb) sql job, includes an Archive operation that writes old data (Archive) to disk. If you do not keep and use your archived tracking databases, there is no need to create them in the first place.

Read more here

In order to purge the BizTalk tracking database, without the Archive operation, replace the DTA Purge and Archive (BizTalkDTADb) sql job with the following sql command: replace the numbers with whatever values meets your design/policy criterions

DECLARE @dtLastBackup DATETIME
SET  @dtLastBackup = GetUTCDate()
-- Note: You should add logic here to get the datetime of the last successful backup of the BizTalkDTADb database! 
EXEC dtasp_PurgeTrackingDatabase 1, 4, 7, @dtLastBackup

This means, relative to the last backup, delete messages older than: 1 = 1 hour 4 = 4 days for all completed messages - means data is still around on monday for changes done in the environment on friday 7 = 7 days for incomplete messages

Microsoft external web link

Always on

if you are using SQL Server Always on, make sure to have the same configuration on ALL SQL Nodes and you must also make sure the SQL job can run without any errors when being executed on the passive nodes.

IF (sys.fn_hadr_is_primary_replica(‘BizTalkDTADb’) = 1)  
BEGIN
    DECLARE @dtLastBackup DATETIME
    SET @dtLastBackup = GetUTCDate()
    -- Note: You should add logic here to get the datetime of the last successful backup of the BizTalkDTADb database! 
    EXEC dtasp_PurgeTrackingDatabase 1, 4, 7, @dtLastBackup
END

Read more about the sys.fn_hadr_is_primary_replica here

Optimize TempDB

  1. Add one extra file per core (up to 8)
    • Static / No autogrowth
    • 128 MB
  2. Set the -T1118 flag on ALL BizTalk and Nodinite related SQL Server Instances

BizTalk Tracking Database (BizTalkDTADB)

Tune you MessageBox and Tracking Database using the scripts below. The 'Set text in row option' for specific tables (make sure small payloads (<=6000 bytes) forces SQL Server to store the data in less pages)

  • Allow the message payload to be stored on the same SQL Page as the event. This will nearly bisect the number of SQL IOs (!)

Read more here about SQL Pages

exec sp_tableoption N'[Tracking_Parts1]', 'text in row', '6000'
go
exec sp_tableoption N'[Tracking_Parts2]', 'text in row', '6000'
go
exec sp_tableoption N'[Tracking_Spool1]', 'text in row', '6000'
go
exec sp_tableoption N'[Tracking_Spool2]', 'text in row', '6000'
go
exec sp_tableoption N'[Tracking_Fragments1]', 'text in row', '6000'
go
exec sp_tableoption N'[Tracking_Fragments2]', 'text in row', '6000'
go

BizTalk Messagebox (BizTalkMsgBoxDB)

exec sp_tableoption N'Spool', 'text in row', '6000'
go
exec sp_tableoption N'Parts', 'text in row', '6000'
go

Registry tweaks

Consider disabling the process where the Kerberos client verifies the Privilege Attribute Certificate (PAC) signature in the Kerberos ticket by using the domain controller.

Consider allowing any amount of traffic between Nodinite Servers and related SQL Servers (also BizTalk <-> SQL Server instances) without having Windows believe it is under an DDOS attack.

Consider the following TCP Settings that may impact Nodinite and BizTalk Server

Enable BizTalk Tracking using Powershell

Enable tracking to get data logged from BizTalk into Nodinite. Enable tracking is all that is required by Nodinite to start the archiving process of copying events and payload from the Tracking database into the IMLog* databases.

The BizTalk Monitoring Agent also has this Action, see

Enable tracking with ease:
ISE

Using an elevated ISE (x86) powershell window, execute the following PowerShell script to enable BizTalk tracking on all ports, orchestrations and pipelines.

<#
    Script to enable tracking in BizTalk.

    Param: bizTalkServer - The BizTalk application server
    Param: filter        - Application name filter
    Param: orchestration - Ignore or Disable orchestration tracking

    Usage: .\Enable-Tracking -bizTalkServer
           .\Enable-Tracking -bizTalkServer <Server name>
           .\Enable-Tracking -bizTalkServer <Server name> -filter Order
           .\Eanble-Tracking -bizTalkServer <Server name> -orchestration Disable
#>

# User input
param(
    [string]$bizTalkServer = ".", 
    [string]$filter = "", 
    [ValidateSet("Ignore", "Disable")][string] $orchestration = "Ignore"
)

# Get BizTalk SQL Instance and database name
try {
    $bizTalkSQLInstance  = Get-WmiObject MSBTS_GroupSetting -Namespace root\MicrosoftBizTalkServer -ComputerName $bizTalkServer -ErrorAction Stop | Select-Object -Expand MgmtDbServerName
    $bizTalkManagementDb = Get-WmiObject MSBTS_GroupSetting -Namespace root\MicrosoftBizTalkServer -ComputerName $bizTalkServer -ErrorAction Stop | Select-Object -Expand MgmtDbName
} Catch {
    $reg = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey('LocalMachine', $bizTalkServer)
    $key = $reg.OpenSubKey('SOFTWARE\Microsoft\BizTalk Server\3.0\Administration')
    $bizTalkSQLInstance  = $key.GetValue('MgmtDBServer')
    $bizTalkManagementDb = $key.GetValue('MgmtDBName')
}

# Connect to the BizTalk Management database
[void] [System.reflection.Assembly]::LoadWithPartialName("Microsoft.BizTalk.ExplorerOM")
$bizTalkCatalogExplorer = New-Object Microsoft.BizTalk.ExplorerOM.BtsCatalogExplorer
$bizTalkCatalogExplorer.ConnectionString = "SERVER=$bizTalkSQLInstance;DATABASE=$BizTalkManagementDb;Integrated Security=SSPI"

function getTracking($port)
{
    $tracking = 0

    if (($port.GetType() -like 'Microsoft.BizTalk.ExplorerOM.SendPort') -or ($port.IsTwoWay))
    {
        $tracking = $tracking -bor
                    [Microsoft.BizTalk.ExplorerOM.TrackingTypes]::BeforeSendPipeline -bor
                    [Microsoft.BizTalk.ExplorerOM.TrackingTypes]::AfterSendPipeline -bor
                    [Microsoft.BizTalk.ExplorerOM.TrackingTypes]::TrackPropertiesBeforeSendPipeline -bor
                    [Microsoft.BizTalk.ExplorerOM.TrackingTypes]::TrackPropertiesAfterSendPipeline
    }

    if (($port.GetType() -like 'Microsoft.BizTalk.ExplorerOM.ReceivePort') -or ($port.IsTwoWay))
    {
        $tracking = $tracking -bor
                    [Microsoft.BizTalk.ExplorerOM.TrackingTypes]::BeforeReceivePipeline -bor
                    [Microsoft.BizTalk.ExplorerOM.TrackingTypes]::AfterReceivePipeline -bor
                    [Microsoft.BizTalk.ExplorerOM.TrackingTypes]::TrackPropertiesBeforeReceivePipeline -bor
                    [Microsoft.BizTalk.ExplorerOM.TrackingTypes]::TrackPropertiesAfterReceivePipeline
    }

    return $tracking
}
 
$applications = $bizTalkCatalogExplorer.Applications | Where-Object { $_.name -like "*$filter*"}

foreach ($application in $applications)
{
    $appName = $application.name

    Write-host "Enabling tracking for application: $appName" -ForegroundColor Cyan

    if ($orchestration -eq 'Disable')
    {
        # Disable tracking settings in orchestrations
        $application.Orchestrations | %{ $_.Tracking = [Microsoft.BizTalk.ExplorerOM.OrchestrationTrackingTypes]::None }
    }

    # Enable full tracking for send ports
    $application.SendPorts | %{ $_.Tracking = getTracking $_ }

    # Enable full tracking for receive ports
    $application.ReceivePorts | %{ $_.Tracking = getTracking($_) }

    $application.Pipelines | %{ $_.Tracking = [Microsoft.BizTalk.ExplorerOM.PipelineTrackingTypes]::InboundMessageBody -bor
                                              [Microsoft.BizTalk.ExplorerOM.PipelineTrackingTypes]::OutboundMessageBody -bor
                                              [Microsoft.BizTalk.ExplorerOM.PipelineTrackingTypes]::MessageSendReceive -bor
                                              [Microsoft.BizTalk.ExplorerOM.PipelineTrackingTypes]::ServiceStartEnd }
}

#
# Save tracking
#
$bizTalkCatalogExplorer.SaveChanges()

Write-host "Tracking is enabled" -ForegroundColor Green

Next Step

Install

Update
BizTalk Logging Agent PreReqs
BizTalk Logging Overview
Remote Actions