- 7 minutes to read

Recommendations for BizTalk Logging

For a successful installation of the Nodinite Logging from BizTalk Server; Make sure to also review the Recommended tracking settings for BizTalk default pipelines user guide.

Your existing (or planned) Microsoft BizTalk Server environment and Nodinite Log agent for BizTalk will benefit a lot from the additional tuning of the involved Windows Servers, SQL Server instances and BizTalk databases.

The changes described here give better performance due to less disk IO needed/wasted, better utilization of the network, and overall a more robust environment

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

The most important configuration is that ONLY the instance with BizTalkMsgboxDB has the MAX DEGREE OF PARALLELISM=1. This should also be the only database in this instance(!) Review KB899000. With SQL Server 2016 SP2 and later you can tune this setting on the database level.

SQL Server Settings That Should Not Be Changed

SQL Server

Make sure to keep the tracking database small as large DTA databases may have a negative 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. This option wastes tons of resources and if you are not using the backups for any reason (like a 3rd party tool or custom portal solution then you should skip the archiving part as described in this user guide)

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

To "only" purge the BizTalk tracking database, without the Archive operation, simply replace the DTA Purge and Archive (BizTalkDTADb) sql job with the following sql command: replace the numbers with whatever values meet your design/policy criteria

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

With our recommendation, you can manage a whole weekend with problems without loosing any data due to the purge job.

Always on

if you are using SQL Server Always on, then 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

We strongly recommend you set these options on all SQL Server instances (regardless of version).

  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 (<2016)

BizTalk Tracking Database (BizTalkDTADB)

Tune your MessageBox and Tracking Database using the scripts below. The 'Set text in row option' for specific tables (make sure small payloads (<=6000 bytes) force SQL Server to store the data in fewer 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.

Ports in use for BizTalk Server

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

Enable BizTalk Tracking

Enabling Tracking for BizTalk can be performed in many different ways, for example:

  1. Manual using the BizTalk Administration console (not recommended)

    • Not Log Audited
    • Requires BizTalk Administrative user rights

  2. Using Nodinite (Recommended)

    • Log Audited
    • Granular access control (Specific BizTalk application, single artifact, ...)
    • Does NOT require BizTalk Administrative user rights for the end-user
    • Overview available (e.g. get the whole picture)

Manage BizTalk Tracking Using Nodinite Remote Actions

Review the Manage BizTalk Server Tracking user guide.

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 Nodinite Log Databases.

The BizTalk Monitoring Agent also has Remote Actions to manage the tracking settings; review the Tracking user guide for additional information.

Enable tracking using Powershell:
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
           .\Enable-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