BizTalk Server: Download suspended messages in bulk from SQL Server

아티클
1/17/2024

This article describes how to rapidly download suspended messages in bulk from the BizTalk MessageBox database.

Problem

To resolve an incident in a medium-to-large sized BizTalk production site, there was a need to download some 50,000+ messages in order to resubmit them, as they could not be resumed for technical reasons (the orchestration design and underlying techniques in called web service made resume impossible). Many other scripts use the BizTalk WMI API, which is a slow method with this large number of suspended messages, but they work perfectly for a few hundred suspended messages.

Refresher on BizTalk Messages

BizTalk Message Parts

A BizTalk message has at least one part, the body part. The other parts are just referred to as Message Parts. Each message part can be of any arbitrary message type.

BizTalk Message Fragments

If a message part is larger than a certain threshold value, the part is split into fragments when stored in the Message Box. The database table that holds message parts is Parts. If the part's size is smaller than a certain threshold, the Parts row contains the complete message part. If the part's size is larger than the threshold, the Parts row contains the first fragment, and the following fragments are stored in the Fragments table.

BizTalk Message Compression

A message part in the message box is compressed; the part/first fragment in Parts as well as each fragment in Fragments is individually compressed. Note that each message part is first compressed before fragmentation, but if the compressed size requires more than 1 fragment, the message part is fragmented first and then each fragment is compressed individually.

Solution

The solution consists of four major parts: A SQL query, two Powershell scripts, and a small C# program. The first Powershell script runs the SQL query, retrieves the fragments, and stores them on disk in the BizTalk compressed format. The fragments are then decompressed using the small C# program, and re-assembled to their original form with the second Powershell script.

This article shows how to retrieve the suspended messages from the database to disk, decompress them, and concatenate them into their original form.
At the end, under Improvements, some modifications are discussed that shows how to retrieve messages pertaining to a specific service instance, and how to retrieve an XLang Multi-Part Message's parts.

Usage Overview

Refer to the Usage Details section below before actually following these instructions.

Save these files in a new location.
Compile the C# program.
Adjust the SQL and Powershell scripts.
Create a subfolder for data.
Run the Powershell script to retrieve the data.
Decompress the files using the compiled program.
Move away the compressed files.
Concatenate the fragments
Done!

Usage Details

Copy the files to a new folder:

This example uses E:\tmp\get-suspended-messages\
Start powershell from that folder (otherwise the process' current directory will be the Windows' System32 directory).

cd /d E:\tmp\get-suspended-messages\
powershell

Compile the C# program:

Choose the same framework version as your BizTalk version requires, preferably compile it on the BizTalk server:

c:\Windows\Microsoft.NET\Framework64\v4.0.30319\csc.exe -out:biztalk-decompress-message.exe biztalk-decompress-message.cs

Edit the SQL and Powershell files:

Add where-conditions in the SQL query file, for example if you only want to select a certain message type. NULL for messages with no message type.
Set server name, instance name, port number, and database name in the Powershell script.

4. Create a subfolder 'data' for data files:

New-Item -Path . -Name data -Type Directory -Force

Run the Powershell script:

.\biztalk-get-suspended-messages.ps1 .\biztalk-get-suspended-messages.sql .\data

Decompress the files:

The syntax for the biztalk-decompress-message.exe program is:
biztalk-decompress-message.exe filename.compressed [BizTalk program files folder]
The compressed file's name must end with .compressed.
The second argument is the folder name of your BizTalk installation (where Microsoft.BizTalk.Pipeline.dll resides), the program defaults to "D:\Program Files (x86)\Microsoft BizTalk Server 2013".

Get-ChildItem -Path .\data -Filter "*.compressed" | % { .\biztalk-decompress-message.exe $_.FullName }

Move the original compressed files to a subfolder, to get them out of the way for the concatenation:

New-Item -Path .\data\ -Name compressed -Type Directory -Force
Move-Item .\data\.compressed -Destination .\data\compressed\

8. Concatenate the fragments to rebuild the files:

.\concatenate-fragments.ps1 .\data\

Done!

The concatenated, complete files have the extension .out.

Key parts in the scripts and the program

SQL Query

01.SELECT
02.       s.[uidMessageID ] as  [uidMessageID]
03.       ,p.uidPartID
04.       ,s.dtTimeStamp
05.       ,s.nvcBodyPartName
06.       ,mp.nvcPartName
07.       ,s.nvcMessageType
08.       ,mp.nBodyPart as  [IsBodyPart]
09.       ,p.nPartSize           as  [UncompressedLengthPart]
10.       ,DATALENGTH(p.imgPart) as  [DatalengthPart]
11.       ,p.nNumFragments
12.       ,f.nFragmentNumber
13.       ,f.nFragmentSize       as  [UncompressedLengthFragment]
14.       ,DATALENGTH(f.imgFrag) as  [DatalengthFragment]
15.       ,p.imgPart
16.       ,f.imgFrag
17.FROM [Spool] s WITH(NOLOCK)
18.LEFT JOIN MessageParts mp WITH(NOLOCK) ON  s.[uidMessageID ] = mp.[uidMessageID ]
19.LEFT JOIN Parts p  WITH(NOLOCK) ON  mp.uidPartID = p.uidPartID
20.LEFT JOIN Fragments f  WITH(NOLOCK) ON  p.uidPartID = f.uidPartID
21.WHERE
22.s.uidBodyPartID is  not null -- orphans have null here
23.--and s.nvcMessageType = 'my-specific-message-type'

The SQL Query file is read by the Powershell script. It was developed for a BizTalk Server 2013 database (not R2). Noteworty is that the Spool table's uidMessageID column name actually has a terminating space, hence the need for bracketing its name as s.[uidMessageID ] on line 2. Note that there is no need for a sort clause, as BizTalk serves us with the fragment number.
The last line, line 23, is where you can edit if you want to download only a specific message type. Don't edit the existing column names without also edit the Powershell script.

Powershell Script: biztalk-get-suspended-messages.ps1

01.param
02.(
03.  [parameter (Mandatory = $true)] [string]$QueryFileName
04. ,[parameter (Mandatory = $true)] [string]$folderName
05.)
06.Push-Location
07.# 0x0100000 = 1 Mbyte
08.$RSmessages = Invoke-Sqlcmd -ServerInstance "servername\instancename,portnumber"  -Database "BizTalkMsgBoxDb"  -InputFile $QueryFileName -MaxBinaryLength 0x0100000
09.$RSmessages | % {
10.  if ($_.nvcMessageType -ne [System.DBNull]::Value)
11.  {
12.    $messageTypePart = $_.nvcMessageType.Replace(":", "_").Replace("/", "_")
13.  }
14.  else
15.  {
16.    $messageTypePart = "no_message_type"
17.  }
18.  $baseFileName = ($_.nvcBodyPartName.ToString() + "_"  + $messageTypePart + "_"  + $_.uidMessageID.ToString())
19.  $isCompressed = ""
20.  if ( (($_.nNumFragments -eq 1) -and ($_.UncompressedLengthPart -ne $_.DatalengthPart)) -or (($_.nNumFragments -gt 1) -and ($_.UncompressedLengthFragment -ne $_.DatalengthFragment)) )
21.  {
22.    $isCompressed = ".compressed"
23.  }
24. 
25.  $fragmentNumber = 0
26.  $fragmentNamePart = "_fragment"  + $fragmentNumber.ToString("000")
27.  if (($_.nNumFragments -eq 1) -or (($_.nNumFragments -gt 1) -and ($_.nFragmentNumber -eq 1)))
28.  {
30.    $fileName = "$baseFileName$fragmentNamePart$isCompressed"
31.    $bytesToWrite = $_.DatalengthPart
32.    Write-Output "Writing imgPart $bytesToWrite bytes to $fileName"
33.    [System.IO.File]::WriteAllBytes("$folderName\$fileName", $_.imgPart[0..($bytesToWrite-1)])
34.  }
35.  if ($_.nNumFragments -gt 1)
36.  {
37.    $fragmentNumber = $_.nFragmentNumber
38.    $fragmentNamePart = "_fragment"  + $fragmentNumber.ToString("000")
39.    $fileName = "$baseFileName$fragmentNamePart$isCompressed"
40.    $bytesToWrite = $_.DatalengthFragment
41.    Write-Output "Writing imgFrag $bytesToWrite bytes to $fileName"
42.    [System.IO.File]::WriteAllBytes("$folderName\$fileName", $_.imgFrag[0..($bytesToWrite-1)])
43.  }
44.}
45.Pop-Location

The first Powershell script loads and runs the query, and reads the data and writes them to disk. The file name is built with message part name, message type, message ID, and fragment number. The first fragment, number 000, is always the data from the [Parts] table.
Lines 10 – 17: If there is no message type, for example when the message is sent through the PassThru pipeline, the Spool table column contains NULL, and the script then sets a hard-coded message type "no_message_type".
Lines 19 – 23: If the data is compressed, the file extension is hard-coded to ".compressed". This is expected from the decompress program.

C# Program: biztalk-decompress-message.cs

01.using System;
02.using System.IO;
03.using System.Reflection;
04. 
05.public class  MyClass
06.{
07.    public static  void DecompressFile(string inputFilename, string outputFilename, Type compressionStreamsType)
08.    {
09.        var compressedInputStream = File.OpenRead(inputFilename);
10.        var decompressedStream = (Stream)compressionStreamsType.InvokeMember("Decompress", BindingFlags.Public | BindingFlags.InvokeMethod | BindingFlags.Static, null, null, new  object[] { (object)compressedInputStream });
11.         
12.        int decompressedLength = (int)decompressedStream.Length;
13.        BinaryReader reader = new  BinaryReader(decompressedStream);
14.        var messageBody = reader.ReadBytes(decompressedLength);
15.        FileStream outputFS = File.OpenWrite(outputFilename);
16.        outputFS.Write(messageBody, 0, decompressedLength);
17.        outputFS.Flush();
18.        outputFS.Close();
19.        WL(String.Format("Wrote {0} bytes to {1}", decompressedLength, outputFilename));
20.        compressedInputStream.Close();
21.    }
22.     
23.    #region Helper methods
24.     
25.    public static  int Main(string[] args)
26.    {
27.        try
28.        {
29.            WL("Decompressing BizTalk message parts/fragments");
30.            if (args.Length < 1 || args.Length > 2)
31.            {
32.                WL("Syntax: biztalk-decompress-message.exe filename.compressed [BizTalk program files folder]\r\nThe compressed-filename must end with .compressed");
33.                return 1;
34.            }
35.            string inputFilename = args[0];
36.            string extension = Path.GetExtension(inputFilename);
37.            if (extension != ".compressed")
38.            {
39.                WL("Supplied file does not end with .compressed");
40.                return 2;
41.            }
42.            if (!File.Exists(inputFilename))
43.            {
44.                WL("Supplied file does not exist: " + inputFilename);
45.                return 3;
46.            }
47.            string outputFilename = Path.GetFileNameWithoutExtension(inputFilename);
48.            if (String.IsNullOrEmpty(outputFilename))
49.            {
50.                WL("Supplied file was only extension");
51.                return 4;
52.            }
53.            outputFilename = Path.Combine(Path.GetDirectoryName(inputFilename), outputFilename);
54.             
55.            string BizTalkFolder = @"D:\Program Files (x86)\Microsoft BizTalk Server 2013";
56.            if (args.Length == 2)
57.            {
58.                BizTalkFolder = args[1];
59.            }
60.             
61.            Assembly pipelineAssembly = Assembly.LoadFrom(Path.Combine(BizTalkFolder, "Microsoft.BizTalk.Pipeline.dll"));
62.            Type compressionStreamsType = pipelineAssembly.GetType("Microsoft.BizTalk.Message.Interop.CompressionStreams", true);
63.             
64.            DecompressFile(inputFilename, outputFilename, compressionStreamsType);
65.            return 0;
66.        }
67.        catch (Exception e)
68.        {
69.            string error = string.Format("---\nThe following error occurred while executing the program:\n{0}\n---", e.ToString());
70.            Console.WriteLine(error);
71.            return 9;
72.        }
73.        finally
74.        {
75.        }
76.    }
77. 
78. 
79.    private static  void WL(object text, params object[] args)
80.    {
81.        Console.WriteLine(text.ToString(), args);   
82.    }
83. 
84.     
85.    private static  void RL()
86.    {
87.        Console.ReadLine(); 
88.    }
89. 
90.     
91.    private static  void Break() 
92.    {
93.        System.Diagnostics.Debugger.Break();
94.    }
95. 
96.    #endregion
97.}

The decompression uses the BizTalk classes in Microsoft.BizTalk.Message.Interop.CompressionStreams, found in assembly Microsoft.BizTalk.Pipeline.dll. The program needs to know the folder from where it can load this assembly; there is a hard-coded default value if it isn't supplied on the command line. The most part of the Main() function is command line parsing and validation.
To compile this program, you should use the C# compiler supplied with the .Net Framework used by your BizTalk version. The program is simple and has no static dependencies other than the mscorlib.dll.
Lines 7 – 21: The core decompression function. It calls the Decompress() method in the CompressionStreams library.
Lines 61 – 62: Loads the assembly and retrieves the CompressionStreams type from the assembly.

Powershell Script: concatenate-fragments.ps1

01.param
02.(
03.  [parameter (Mandatory = $true)] [string]$folderName
04.)
05. 
06.# First, create the main file with the first (and possibly only) fragment:
07.Get-ChildItem .\data\*_fragment000 | ForEach-Object {
08.  $dataDir = $_.Directory
09.  $isMatch = $_.Name -match '(.*)_fragment000'
10.  $mainName = $Matches[1].ToString() + ".out"
11.  Write-Host "First fragment of $dataDir\$mainName"
12.  Copy-Item $_.FullName -Destination $dataDir\$mainName
13.}
14.# Then, concatenate the other possible fragments in order:
15.Get-ChildItem .\data\*_fragment??? -Exclude *_fragment000 | Sort-Object | ForEach-Object {
16.  $dataDir = $_.Directory
17.  $isMatch = $_.Name -match '(.*)_fragment(\d{3})'
18.  $mainName = $Matches[1].ToString() + ".out"
19.  $fragmentNo = $Matches[2].ToString()
20.  Write-Host "Adding fragment $fragmentNo to $dataDir\$mainName"
21.  Get-Content $_.FullName -Raw -Encoding Byte | Add-Content $dataDir\$mainName -Encoding Byte
22.}

The second Powershell script concatenates the decompressed fragments into complete files. First, it just copies the first fragment (number 000) to the resulting .out file. Then, any succeeding fragments are appended in fragment number order to the resulting .out file. The main file name is everything up to the _fragmentNNN part, using a regular expression.
Line 7: Selects only the fragment000 files
Line 12: Creates the resulting .out file using Copy-Item instead of Set-Content. If you use Get-Content and pipe to Set-Content, the latter needs -NoNewLine to not add a terminating new-line. That option was added in Powershell 5.0, so the safest and simplest way here is to use Copy-Item instead.
Line 15: Selects all fragment files, excluding the 000 files as they were already copied in the first step, then sorts them in order.
Line 21: Get-Content piped to Add-Content is used here to append to the file, using Byte encoding. Add-Content does not suffer from any new-line insertions.

Caveats

The current file naming scheme will be confusing for us humans with several multi-part messages of the same type, since the first file name part is the message part name, followed by message type name.

Source Code

The code is available on GitHub:
Web link: https://github.com/PeterLindgren-se/biztalk-get-suspended-messages-sql
Repository link: https://github.com/PeterLindgren-se/biztalk-get-suspended-messages-sql.git

Improvements

Download only messages which pertains to a specific service instance (orchestration or messaging)

The SQL script can be modified to only select messages pertaining to a certain service instance, whos GUID can be copied from the BizTalk Administration Console. Right-click on the suspended instance, click on Show Messages. A new tab will open, and among the query fields you have the Service Instance ID. Also note the host instance name, and adjust the FROM statement accordingly. In the SQL script below, the host instance name was "Clustered_LongRunning_Orchestration_64".

The modified SQL script (the SELECT column clause must stay the same):

01.SELECT
02....
03.FROM [Clustered_LongRunning_Orchestration_64Q_Suspended] i WITH  (NOLOCK)
04.INNER JOIN  [Spool] s  WITH(NOLOCK) on  i.[uidMessageID] = s.[uidMessageID ]
05.LEFT JOIN MessageParts mp WITH(NOLOCK) ON  s.[uidMessageID ] = mp.[uidMessageID ]
06.LEFT JOIN Parts p  WITH(NOLOCK) ON  mp.uidPartID = p.uidPartID
07.LEFT JOIN Fragments f  WITH(NOLOCK) ON  p.uidPartID = f.uidPartID
08.WHERE 1=1
09.-- A specific orchestration or messaging instance, depending on which Q_SUSPENDED we select from.
10.-- The value can be found in the BizTalk Admin Console:
11.and i.[uidInstanceID] = 'D89FFAE7-D274-4200-B603-3B0A3AC578E5'
12.and s.uidBodyPartID is not null  -- orphans have null here
13.--and s.nvcMessageType = 'my-specific-message-type'
14.order by  s.[uidMessageID ], [nvcBodyPartName], [nvcPartName]

Line 3 – 4: FROM the suspended queue table INNER JOIN the spool table.
Line 11: Retrieves the specific instance's messages.
Line 14: The Order By clause is for us humans when looking at the result in SQL Server Management Studio.

Retrieve an XLang Multi-Part Message's parts

The SQL script as-is selects both the MessageID and the PartID. The original Powershell script, however, uses only the MessageID in the file name. You can update the Powershell script to let you choose between using the MessageID or the PartID in the file name:

01.param
02.(
03.  [parameter (Mandatory = $true)] [string]$QueryFileName
04. ,[parameter (Mandatory = $true)] [string]$folderName
05. ,[parameter (Mandatory = $false)] [switch]$UsePartID
06.)
07....
08.  if ($UsePartID)
09.  {
10.    $baseFileName = ($_.nvcBodyPartName.ToString() + "_"  + $messageTypePart + "_"  + $_.uidPartID.ToString())
11.  }
12.  else
13.  {
14.    $baseFileName = ($_.nvcBodyPartName.ToString() + "_"  + $messageTypePart + "_"  + $_.uidMessageID.ToString())
15.  }

Line 5: A third, optional, argument to select whether to use PartID. Defaults to false, meaning the default behavior will be as before using MessageID.
Lines 8 – 15: The original file's line 18 is replaced by these lines.

Instead, another possible modification can be to simply append the PartID at the end.

General

A big improvement would be to incorporate the decompression into the Powershell script. Regardless, a wrapper script that performs these steps would also be a welcome improvement.

다음을 통해 공유