Here are some samples you can use to list, start/stop, enable/disable, enlist/unenlist and so on your Send Ports, Receive Ports, and Recieve Locations.

Note the path hierarchy. You must specify you application name. I’m using “BizTalk EDI Application” since anyone with EDI installed will have the same results.

Sample Script

cls
Add-PSSnapIn -Name BiztalkFactory.PowerShell.Extensions  #NOTE: Must be in 32-bit version of Powershellto use this SnapIn

cd "Biztalk:\Applications\BizTalk EDI Application\Send Ports"
Get-ChildItem | ft -auto      # or just type in "DIR", the "| ft -auto" avoids the Name being cut off with the elipse ... 
Write-Host "`nSend Ports - List Completed"
Get-ChildItem |Where-Object { $_.Name -match 'ResendPort' } | Unenlist-SendPort
Get-ChildItem |Where-Object { $_.Name -match 'ResendPort' } | Start-SendPort


cd "Biztalk:\Applications\BizTalk EDI Application\Receive Ports"
Get-ChildItem | ft -auto      # or just type in "DIR", the "| ft -auto" avoids the Name being cut off with the elipse ... 
Write-Host "`nReceive Ports - List Completed"


cd "Biztalk:\Applications\BizTalk EDI Application\Receive Locations"
Get-ChildItem | ft -auto      # or just type in "DIR", the "| ft -auto" avoids the Name being cut off with the elipse ... 
Write-Host "`nReceive Ports - List Completed"

cd "Biztalk:\Applications\BizTalk EDI Application\Receive Locations"
Get-ChildItem |Where-Object { $_.Name -match 'ResendReceiveLocation' } | Disable-ReceiveLocation
Get-ChildItem |Where-Object { $_.Name -match 'ResendReceiveLocation' } | Enable-ReceiveLocation

Note: I didn't put any Write-Host to show when the ports were succesfully stopped or started, you could add that i you want it.

Output of running the above script



    Path: BiztalkFactory.PowerShell.Extensions\BizTalk::Biztalk:\Applications\BizTalk EDI Application\Send Ports


Name       Status  Application            
----       ------  -----------            
ResendPort Started BizTalk EDI Application



Send Ports - List Completed


    Path: BiztalkFactory.PowerShell.Extensions\BizTalk::Biztalk:\Applications\BizTalk EDI Application\Receive Ports


Name                        Application            
----                        -----------            
BatchControlMessageRecvPort BizTalk EDI Application
ResendReceivePort           BizTalk EDI Application



Receive Ports - List Completed


    Path: BiztalkFactory.PowerShell.Extensions\BizTalk::Biztalk:\Applications\BizTalk EDI Application\Receive Locations


Name                       Enabled Application            
----                       ------- -----------            
BatchControlMessageRecvLoc True    BizTalk EDI Application
ResendReceiveLocation      True    BizTalk EDI Application



Receive Ports - List Completed

PS BizTalk:\Applications\BizTalk EDI Application\Receive Locations> 

When you install the Powershell Extensions for BizTalk, there is a readme.txt file created ("d:\Program Files (x86)\Microsoft BizTalk Server 2016\SDK\Utilities\PowerShell\readme.txt") that will show you all the cmdlets that are available (such as Start-SendPort, Unenlist-SendPort, etc...)

When dealing with Host Instances, you must specify the fully-qualified name, or use a couple of tricks I show in this blog.

I’ve been setting up some pre-canned scripts that I can just open when needed, and that have already been tested. Today I was looking at listing, starting, and stopping BizTalk Host Instances.

Powershell Script

cls
Add-PSSnapIn -Name BiztalkFactory.PowerShell.Extensions  #NOTE: Must be in 32-bit version of Powershellto use this SnapIn

cd "Biztalk:\Platform Settings\Host Instances"
Get-ChildItem | ft -auto      # or just type in "DIR", the "| ft -auto" avoids the Name being cut off with the elipse ... 
Write-Host "`nList Completed"


#Demonstrating 3 ways: 
#  1) Use fully qualified host instance name (a big pain to type it correctly) 
#  2) Use the -match operator to match on a string 
#  3) Use the Host name instead
# With last two, it will start/stop all host-instances on all machines

Stop-HostInstance "Microsoft BizTalk Server OrchHost SERVER01"
Write-Host "Stopped"

#Get-ChildItem |Where-Object { $_.Name -match 'OrchHost' } |Start-HostInstance
Get-ChildItem |Where-Object { $_.Host -eq 'OrchHost' } |Start-HostInstance
Write-Host "Started"

Results of running the above Powershell Script


    Path: BiztalkFactory.PowerShell.Extensions\BizTalk::Biztalk:\Platform Settings\Host Instances


Name                                                           Host Name                 Windows Group                         Running Server
----                                                           ---------                 -------------                         --------------
Microsoft BizTalk Server BizTalkServerApplication SERVER01  BizTalkServerApplication  BizTalk Application Users             SERVER01   
Microsoft BizTalk Server BizTalkServerIsolatedHost SERVER01 BizTalkServerIsolatedHost BizTalk Isolated Host Users           SERVER01   
Microsoft BizTalk Server TrackingHost SERVER01              TrackingHost              SERVER01\BizTalk Application Users SERVER01   
Microsoft BizTalk Server AS2HostReceive SERVER01            AS2HostReceive            SERVER01\BizTalk Application Users SERVER01   
Microsoft BizTalk Server AS2HostSend SERVER01               AS2HostSend               SERVER01\BizTalk Application Users SERVER01   
Microsoft BizTalk Server SFTPHostReceive SERVER01           SFTPHostReceive           SERVER01\BizTalk Application Users SERVER01   
Microsoft BizTalk Server SFTPHostSend SERVER01              SFTPHostSend              SERVER01\BizTalk Application Users SERVER01   
Microsoft BizTalk Server FileHostReceive SERVER01           FileHostReceive           SERVER01\BizTalk Application Users SERVER01   
Microsoft BizTalk Server FileHostSend SERVER01              FileHostSend              SERVER01\BizTalk Application Users SERVER01   
Microsoft BizTalk Server OrchHost SERVER01                  OrchHost                  SERVER01\BizTalk Application Users SERVER01   



List Completed
Stopped
Started

PS BizTalk:\Platform Settings\Host Instances> 

The use of “-ft auto” is used to avoid the ellipsis and show the full name (rather than truncating it).

After you add Host and Host-Instances to BizTalk, they are not useful and cannot be associated with Send/Receive ports until you relate the Host to the Adapter. This can be done in BizTalk Admin Console, but below is a program that allows you to do it with the BizTalk Powershell Extensions.

See related script to use Powershell to add new BizTalk Hosts and Host Instances.

cls
Add-PSSnapIn -Name BiztalkFactory.PowerShell.Extensions  #NOTE: Must be in 32-bit version of Powershellto use this SnapIn
#get-PsSnapIn -registered   ### list registered Snap-In's

function UpdateAdapter($AdapterName, $HostName, $Direction)
{
        # modeled after code from: 
        # https://social.technet.microsoft.com/wiki/contents/articles/32456.powershell-script-for-creating-biztalk-hostshost-instancesadapter-handlers-and-custom-event-log.aspx}

        Write-Host "Starting of `$Adapter=$AdapterName and `$Host=$HostName `$Direction=$Direction"
        #Example: Set-Location –Path '..\SFTP'
        $Pathname = "BizTalk:\Platform Settings\Adapters\$AdapterName"
        Write-Host "`$PathName=$PathName"
        Set-Location –Path $PathName 

        if ($Direction -eq "Send" -or $Direction -eq "Both") 
        {
            Write-Host "Setup Sending" 
            ### Sending 
                                                           #Example:  -eq "SFTP Send Handler (Sending_64)"
            $VarAdapterHandler = Get-ChildItem | Where-Object{$_.Name -eq "$AdapterName Send Handler ($HostName)"}
            if($VarAdapterHandler.Name -eq $null)
            {
                   Write-Host "Adding $AdapterName handler for Host=$HostName"
                   #New-Item  -Path .\Sending_64 -HostName Sending_64 -Direction Send 
                   New-Item  -Path .\$HostName -HostName $HostName -Direction Send
            }
        }

        if ($Direction -eq "Receive" -or $Direction -eq "Both") 
        {
            Write-Host "Setup Receiving" 
            ### Receiving
            $VarAdapterHandler = Get-ChildItem | Where-Object{$_.Name -eq "$AdapterName Receive Handler ($HostName)"}
            if($VarAdapterHandler.Name -eq $null)
            {
                   Write-Host "Adding SFTP Receive handler for Receiving_64 Host"
                   New-Item  -Path .\$Hostname -HostName $HostName -Direction Receive
            }
        }
        Write-Host "End of $AdapterName and $HostName"
        Write-Host "----"

}


### MAIN CODE HERE - Calls Function Above once per Host/Host-Instance ### 


#Pass Three Parms: 1) AdapterName, 2) HostName, 3) Direction [Send/Recive/Both]
#UpdateAdapter  "TrackingHost"   "Both" 

UpdateAdapter "HTTP" "AS2HostReceive" "Receive"
UpdateAdapter "HTTP" "AS2HostSend"    "Send"

UpdateAdapter "SFTP" "SFTPHostReceive" "Receive"
UpdateAdapter "SFTP" "SFTPHostSend"    "Send"

UpdateAdapter "FILE" "FileHostReceive" "Receive"
UpdateAdapter "FILE" "FileHostSend"    "Send"

Write-Host "Script Completed"

The following shows how to set the $HostType for the BizTalk Powershell Extensions.

 if ($HostType -eq "Isolated") 
   {
     $HostTypeCode = 2  #Isolated 
   }
   else 
   {
     $HostTypeCode = 1  #In-Process
   }
 $temp = New-Item -path $hostName -HostType:$HostTypeCode -NtGroupName:$myNTHostGroupName -AuthTrusted:$AuthTrusted 

See full function in this blog about Adding BizTalk Hosts and Hostinstances with Powershell.

In a previous blog, I gave a full Powershell Script to Add BizTalk Hosts, but it didn’t show how to set the 32-bit vs 64-bit flag (it has now been updated to include that).

64 bit vs 32 bit Host

In BIzTalk, the flag is called the 32BitOnly flag.  To create a 64-bit Host, set this flag to false (same as unchecking it in the BizTalk Admin Console).

Instead of setting it on the New-Item command, you have to set it as a property on the following line of code, as shown below.

Code Sample

$temp = New-Item -path $hostName -HostType:$HostType -NtGroupName:$myNTHostGroupName -AuthTrusted:$AuthTrusted 
Set-ItemProperty -Path $hostName -Name 'Is32BitOnly' -Value 'False'
#similarly, to set whether or not the Host is a "Tracking Host", use the following line 
Set-ItemProperty -Path $hostName -Name 'HostTracking' -Value 'True' 


 
To get a list of all the miscellaneous properties, I set a break point and use the Get-Members cmdlet:

[DBG]: PS BizTalk:\Platform Settings\Hosts>> $temp | Get-Member


   TypeName: BizTalkFactory.Management.Automation.BtsHost

Name                                  MemberType   Definition                                                                                
----                                  ----------   ----------                                                                                
Equals                                Method       bool Equals(BizTalkFactory.Management.Automation.BtsArtifact other), bool Equals(System...
ExportHostSettings                    Method       void ExportHostSettings(string path)                                                      
GetHashCode                           Method       int GetHashCode()                                                                         
GetType                               Method       type GetType()                                                                            
ImportHostSettings                    Method       void ImportHostSettings(string path)                                                      
ToString                              Method       string ToString()                                                                         
PSChildName                           NoteProperty System.String PSChildName=TrackingHost                                                    
PSDrive                               NoteProperty BizTalkFactory.PowerShell.Extensions.BizTalkDriveInfo PSDrive=BizTalk                     
PSIsContainer                         NoteProperty System.Boolean PSIsContainer=False                                                        
PSParentPath                          NoteProperty System.String PSParentPath=BiztalkFactory.PowerShell.Extensions\BizTalk::Biztalk:\Platf...
PSPath                                NoteProperty System.String PSPath=BiztalkFactory.PowerShell.Extensions\BizTalk::Biztalk:\Platform Se...
PSProvider                            NoteProperty System.Management.Automation.ProviderInfo PSProvider=BiztalkFactory.PowerShell.Extensio...
AllowMultipleResponses                Property     bool AllowMultipleResponses {get;set;}                                                    
AuthTrusted                           Property     bool AuthTrusted {get;set;}                                                               
Catalog                               Property     BizTalkFactory.Management.Automation.BtsCatalog Catalog {get;}                            
Clustered                             Property     bool Clustered {get;}                                                                     
DBQueueSizeThreshold                  Property     uint32 DBQueueSizeThreshold {get;set;}                                                    
DBSessionThreshold                    Property     uint32 DBSessionThreshold {get;set;}                                                      
DehydrationBehavior                   Property     BizTalkFactory.Management.Automation.BtsDehydrationBehavior DehydrationBehavior {get;set;}
DeliveryQueueSize                     Property     uint32 DeliveryQueueSize {get;set;}                                                       
GlobalMemoryThreshold                 Property     uint32 GlobalMemoryThreshold {get;set;}                                                   
HostTracking                          Property     bool HostTracking {get;set;}  
HostType                              Property     BizTalkFactory.Management.Automation.BtsHostType HostType {get;}                          
InflightMessageThreshold              Property     uint32 InflightMessageThreshold {get;set;}                                                
Is32BitOnly                           Property     bool Is32BitOnly {get;set;}  
IsDefault                             Property     bool IsDefault {get;set;}                                                                 
IsHost32BitOnly                       Property     bool IsHost32BitOnly {get;set;}                                                           
LegacyWhitespace                      Property     bool LegacyWhitespace {get;set;}                                                          
MessageDeliveryMaximumDelay           Property     uint32 MessageDeliveryMaximumDelay {get;set;}                                             
MessageDeliveryOverdriveFactor        Property     uint32 MessageDeliveryOverdriveFactor {get;set;}                                          
MessageDeliverySampleSpaceSize        Property     uint32 MessageDeliverySampleSpaceSize {get;set;}                                          
MessageDeliverySampleSpaceWindow      Property     uint32 MessageDeliverySampleSpaceWindow {get;set;}                                        
MessagePublishMaximumDelay            Property     uint32 MessagePublishMaximumDelay {get;set;}                                              
MessagePublishOverdriveFactor         Property     uint32 MessagePublishOverdriveFactor {get;set;}                                           
MessagePublishSampleSpaceSize         Property     uint32 MessagePublishSampleSpaceSize {get;set;}                                           
MessagePublishSampleSpaceWindow       Property     uint32 MessagePublishSampleSpaceWindow {get;set;}                                         
MessagingMaxReceiveInterval           Property     uint32 MessagingMaxReceiveInterval {get;set;}                                             
MessagingReqRespTTL                   Property     uint32 MessagingReqRespTTL {get;set;}                                                     
MsgAgentPerfCounterServiceClassID     Property     guid MsgAgentPerfCounterServiceClassID {get;set;}                                         
Name                                  Property     string Name {get;}                                                                        
NtGroupName                           Property     string NtGroupName {get;}                                                                 
ProcessMemoryThreshold                Property     uint32 ProcessMemoryThreshold {get;set;}                                                  
SubscriptionPauseAt                   Property     uint32 SubscriptionPauseAt {get;set;}                                                     
SubscriptionResumeAt                  Property     uint32 SubscriptionResumeAt {get;set;}                                                    
ThreadPoolSize                        Property     uint32 ThreadPoolSize {get;set;}                                                          
ThreadThreshold                       Property     uint32 ThreadThreshold {get;set;}                                                         
ThrottlingBatchMemoryThresholdPercent Property     uint32 ThrottlingBatchMemoryThresholdPercent {get;set;}                                   
ThrottlingDeliveryOverride            Property     uint32 ThrottlingDeliveryOverride {get;set;}                                              
ThrottlingDeliveryOverrideSeverity    Property     uint32 ThrottlingDeliveryOverrideSeverity {get;set;}                                      
ThrottlingLimitToTriggerGC            Property     uint32 ThrottlingLimitToTriggerGC {get;set;}                                              
ThrottlingPublishOverride             Property     uint32 ThrottlingPublishOverride {get;set;}                                               
ThrottlingPublishOverrideSeverity     Property     uint32 ThrottlingPublishOverrideSeverity {get;set;}                                       
ThrottlingSeverityDatabaseSize        Property     uint32 ThrottlingSeverityDatabaseSize {get;set;}                                          
ThrottlingSeverityInflightMessage     Property     uint32 ThrottlingSeverityInflightMessage {get;set;}                                       
ThrottlingSeverityProcessMemory       Property     uint32 ThrottlingSeverityProcessMemory {get;set;}                                         
ThrottlingSpoolMultiplier             Property     uint32 ThrottlingSpoolMultiplier {get;set;}                                               
ThrottlingTrackingDataMultiplier      Property     uint32 ThrottlingTrackingDataMultiplier {get;set;}                                        
TimeBasedMaxThreshold                 Property     uint32 TimeBasedMaxThreshold {get;set;}                                                   
TimeBasedMinThreshold                 Property     uint32 TimeBasedMinThreshold {get;set;}                                                   
UseDefaultAppDomainForIsolatedAdapter Property     bool UseDefaultAppDomainForIsolatedAdapter {get;set;}                                     
XlangMaxReceiveInterval               Property     uint32 XlangMaxReceiveInterval {get;set;}                                                 

[DBG]: PS BizTalk:\Platform Settings\Hosts>> 

Error: No snaps-ins have been registered for Windows PowerShell

You run the following:

cls
Add-PSSnapIn -Name BiztalkFactory.PowerShell.Extensions  #NOTE: Must be in 32-bit version of Powershellto use this SnapIn


 

And you get this error:

Add-PSSnapIn : No snap-ins have been registered for Windows PowerShell version 3.


 

Solution:Go the SDK/Utilities/Powershell and install them

Basically follow the steps laid out by here MSDN BizTalk Forum, except you don’t need to download from CodePlex, you can use the one in the install.

  1. Navigate to: C:\Program Files (x86)\Microsoft BizTalk Server 2013 R2\SDK\Utilities\PowerShell
  2. Optionally open and follow the readme.txt
  3. Open a command prompt as an administrator
  4. Change directory to the folder containing the binaries – \SDK\Utilities\PowerShell under the base install location
  5. Run the following command to install/register the dlls
    %windir%\Microsoft.NET\Framework\v4.0.30319\InstallUtil.exe .\BizTalkFactory.PowerShell.Extensions.dll

I needed this today to run a powershell to create biztalk hosts.

I’m creating my own checklist of things to watch out for on a BizTalk install, beyond the basic BizTalk hardware/software prerequisites.

Sites below are for BizTalk 2010 – but same applies for most any other release:

https://msdn.microsoft.com/en-us/library/aa577661.aspx – List of Active Domain (AD) groups and service accounts.
I took this, put it in MS/Word and added a column to the left with a suggested name for the client.  Then I asked the client to verify my suggest name to fit their existing AD group/account naming conventions.  I included an “X” for each environment, e.g. (D=DEV, T=Test, Q=QA, P=Prod), or a 3-character (DEV, TST, QUA, PRD).   Example, BTSPRDADMINS (or if you like mixed case, BtsPrdAdmins).


https://msdn.microsoft.com/en-us/library/gg634639(v=bts.10).aspx  Windows Configuration Checklist –
– turn off hyperthreading,
– Ensure Windows Server processor scheduling is set to “Background services”
– Place the Windows paging file on a separate local physical drive.
– Stop real-time virus scanning of database files and .xml, .csv, etc…


https://msdn.microsoft.com/en-us/library/gg634450(v=bts.10).aspx – IIS Configuration Checklist –


https://msdn.microsoft.com/en-us/library/gg634642(v=bts.10).aspx – SQL Configuration Checklist
  – enable Trace Flag 1118 (TF1118) – to reduce contention
  – sp_configure ‘Max Server memory (MB)’,(max size in MB)sp_configure ‘Min Server memory (MB)’,(min size in MB)
 – Autogrow to fixed MB not % for BizTalk DBs


https://msdn.microsoft.com/en-us/library/gg634496(v=bts.10).aspx  – SQL Server Settings that Should Not Be Changd


1) Max Degree of Parallelism (MDOP) is set to “1” during the configuration of BizTalk Server for the SQL Server instance(s) that host the BizTalk Server MessageBox database(s). This is a SQL Server instance-level setting. This setting should not be changed from the value of “1”. Changing this to anything other than “1” can have a significant negative impact on the BizTalk Server stored procedures and performance. If changing the parallelism setting for an instance of SQL Server will have an adverse effect on other database applications that are being executed on the SQL Server instance, you should create a separate instance of SQL Server dedicated to hosting the BizTalk Server databases.

2) Auto create statistics and auto update statistics are turned off for the BizTalk MessageBox DB. 
zTalk Server stored procedures have exact joins and lock hints specified on the queries. This is done to ensure that the optimal query plan is used by the BizTalk Server queries in SQL Server. The distributions and expected results for the queries are known; the approximate number of rows returned is known. Statistics are generally not needed.

3)  BizTalk Server does not support defragmenting indexes. “DBCC INDEXDEFRAG” and “ALTER INDEX … REORGANIZE …” are not supported since they use page locking, which can cause blocking and deadlocks with BizTalk Server. BizTalk Server does support database index rebuilds (“DBCC DBREINDEX” and “ALTER INDEX … REBUILD …”), but they should only be done during maintenance windows when BizTalk Server is not processing data. Index rebuilds while BizTalk Server is processing data are not supported.

Index fragmentation is not as much of a performance issue for BizTalk Server as it would be for a DSS system or an OLTP system that performs index scans. BizTalk Server does very selective queries and updates and BizTalk Server stored procedures should not cause table or index scans.

https://blogs.msdn.microsoft.com/biztalk_core_engine/2007/01/04/what-you-can-and-cant-do-with-the-messagebox-database-server/  – What you can and cannot do with the MessageBox Database

The next very, very important thing to understand is that while the database is installed on your server, it is not “your” database. It is our (or if you catch me on the wrong day, not thinking, “my”) database

A quick list of things which can effect the query plans:

– Statistics (don’t enable these)

– Parallelism (don’t turn this on )

– Table structure (don’t add indexes, columns, triggers, … If you do you will hear silence when you call for help)

– Stored procedures (don’t change them. You can look all you want, but no touching)


USE BizTalkDTADb
--select top 10 * from dbo.dta_DebugTrace
select COUNT(*) from dbo.dta_DebugTrace as TotalRowCount

SELECT YEAR(dtBeginTimeStamp) AS Yr,
Month(dtBeginTimeStamp) AS Mo,
COUNT(*) AS [RowCount]
FROM dbo.dta_DebugTrace nolock
GROUP BY
YEAR(dtBeginTimeStamp),
DatePart(mm,dtBeginTimeStamp)
Order BY
YEAR(dtBeginTimeStamp),
Month(dtBeginTimeStamp)

The results show you by year/month how many rows. In most cases, you will find you need to purge data prior the current month. At least on a test system, I can think of any possible use of month old orchestration trace data (or even on Production for that matter).  But if you have long-running dehydrated orhestrations you might need it).

dta_DebugTrace_Counts

Orchestrations write data to these tables each time they run (when Trace is enabled).

Go to SQL Agent on the SQL Server that supports your BizTalk server, check the job entitled: DTA Purge and Archive (BizTalkDTADb)
SQLAgent_Jobs_BizTalk_2010
I take the original code that is there, and copy it to the line below, then comment out the code that is there with the T-SQL comment (two dashes).
Then change the second line to the parameters you want. Follow-that by running the job manually, or set it up to run on a scheduled basis.


--exec dtasp_BackupAndPurgeTrackingDatabase
0, --@nLiveHours tinyint, --Any completed instance older than the live hours +live days
1, --@nLiveDays tinyint = 0, --will be deleted along with all associated data
30, --@nHardDeleteDays tinyint = 0, --all data older than this will be deleted.
null, --@nvcFolder nvarchar(1024) = null, --folder for backup files
null, --@nvcValidatingServer sysname = null,
0 --@fForceBackup int = 0 --


exec dtasp_BackupAndPurgeTrackingDatabase 24, 30, 32, 'e:\Backup\BizTalkDatabases\', null, 0

MSDN Reference: https://msdn.microsoft.com/library/aa558715.aspx

NOTE: Running the job creates a backup, so if your DTA database is large, and you are short on disk space, you will have to make sure you find disk space for the backup to be written.

If you get odd errors, try running the command directly in a SQL query window, then the a more useful error may be displayed than what you would see in the “View History” of the SQL Agent Job.

dtasp_BackupAndPurgeTrackingDatabase_Errors


The hard purge window cannot be less than the live data window [SQLSTATE 42000] (Error 50000).
The step failed.

After changing the second 30 to 32, I re-ran it, and saw this result:

Successful_Run_of_dtasp_BackupAndPurgeTrackingDatabase

 

The Parms are as follows:
dtasp_BackupAndPurgeTrackingDatabase_storedProc_Parms

 

However, after running it, I still saw many of the same rows there (this is on BT2010 by the way). I did some more research, found Sandro Pereira’s blog and ran the following:


declare @dtLastBackup datetime
set @dtLastBackup = GetUTCDate() exec dtasp_PurgeTrackingDatabase 1, 0, 7, @dtLastBackup

Suppose you walk into a new company, they don’t have documentation, and they use a lot of content-based routing. The first thing I do is usually draw a Visio diagram. There never really been one widely adopted standard for how to draw such diagrams. The secret is to pack the most useful information that you can in the smallest space.

When drawing BizTalk diagrams, I like to keep everything one page if possible. Of course, sometimes you have to break on multiples pages, and when I do, I don’t like cross-page lines or pointers, if they can be avoided.

In BizTalk, a Send Port or a SendPort Group can subscribe to the data coming in from a Receive Port (and of course one Receive Port can have multiple Receive Locations). The subscription however, is by the ReceivePortName, not the ReceiveLocationName. You can have multiple send ports subscribe to the same message (thus the name Pub-Sub, short for Publisher/Subscriber model, upon which BizTalk is based).

BizTalk_Filter_Subscriptions

To get to the above screen, I found a SendPort in BizTalk Admin Console, right clicked on it, then selected “Filters” on the left side. The above has one simple filter. All messages received from a ReceivePortName of MSMQDemo_RcfFromMSMQ_biztalktrans will be routed to this SendPort. You can add and change the filter/subscriptions on the top part of the screen. For convenience, the filters are shown at the bottom (and you can copy/paste them from there). It’s hard to see the entire names on the top, because of the limited width of the columns.

So often you can use business logic, naming convention, and and guessing to determine which SendPorts subscript to which ReceivePorts. Or you can open each SendPort and write them down, or copy/paste the above filters into a Word Doc, Spreadsheet, or Visio diagram.

But all the data in BizTalk Admin Console is stored in one BizTalk’s SQL Databases (BizTalkMgmtDB). The query below allows you find all SendPorts subscribing to a ReceivePort.

The code below was original found here: gallery.technet.microsoft.com/scriptcenter.

use BizTalkMgmtDb
go  -- If you have the "use" above, without the "GO" here you will get an error on the "With" statement
WITH
TmpXMLNode
  ( SendPortName, ApplicationName, tempXMLcolumn )
AS
  (SELECT
    SP.nvcName AS SendPortName
    , APP.nvcName AS ApplicationName
    , CAST(REPLACE(REPLACE(REPLACE(CONVERT(NVARCHAR(MAX), SP.nvcFilter),'>','>'),'<','<'),
      'xmlns="http://www.w3.org/2001/XMLSchema-instance"','') AS XML) AS tempXMLcolumn
  FROM
    bts_sendport AS SP
    INNER JOIN bts_application AS APP ON SP.nApplicationID = APP.nID
  WHERE
    CONVERT(VARCHAR(MAX), nvcFilter) <> ''
  )

SELECT 
  SendPortName,
  ApplicationName,
  CONVERT(VARCHAR(255), nref.query('data(@Value)')) AS FilterValue,
  CONVERT(VARCHAR(255), nref.query('data(@Property)')) AS FilterProperty,
  CONVERT(VARCHAR(255), nref.query('data(@Operator)')) AS FilterOperator
FROM
  TmpXMLNode
  CROSS APPLY
    TmpXMLNode.tempXMLcolumn.nodes('/Filter/Group/Statement') AS R(nref)
WHERE
  CONVERT(VARCHAR(255), nref.query('data(@Property)')) = 
     'BTS.ReceivePortName' -- filter type  (do not change the value in quotes) 
  AND CONVERT(VARCHAR(255), nref.query('data(@Value)')) = 
     'YourRcvPortHere'  -- change this value in quotes  

You can of course put a LIKE clause on the final where clause:

  AND CONVERT(VARCHAR(255), nref.query('data(@Value)')) like 'MSMQ%'  

Results of query in SSMS:
Result_of_BizTalkMgmtDB_Sql_Query_Against_SendPorts_Filters2
You may recall that you can also filter on your own schema/promoted fields. So you could have one SendPort subscribe to PurchaseOrderAmount > 5000 and a different one subscribe to PurchaseOrderAmount <= 5000.

Note in the above query, I added the “Operator” column, which was not in the original SQL sample.

The filters above are stored in a text column called nvcFilter. Part of the trick of the SQL is convert this to an XML column, so XQuery statement can be performed on it.

So to demonstrate what is going on internally, I created a dummy SendPort with several filters just to see something beyond the most simple example.

BizTalk_SendPort_Complex_Filters

I then ran this query

SELECT *
  FROM bts_sendport AS SP
    INNER JOIN bts_application AS APP ON SP.nApplicationID = APP.nID
  WHERE  nvcFilter like '%msmq%'

I then copied the contents of the nvcFilter column to NotePad++ and formatted it:

<Filter xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
	<Group>
		<Statement Property="BTS.ReceivePortName" Operator="0" Value="ABC" />
		<Statement Property="MSMQ.Priority" Operator="3" Value="5" />
		<Statement Property="MSMQ.SourceMachine" Operator="0" Value="Machine1" />
	</Group>
	<Group>
		<Statement Property="BTS.ReceivePortName" Operator="0" Value="DEF" />
		<Statement Property="BTF2.commitmentRct_decidedAt" Operator="0" Value="test" />
	</Group>
	<Group>
		<Statement Property="BTF2.commitmentRct_commitmentCode" Operator="0" Value="test" />
	</Group>
</Filter>

 

So from this, we can discern that the > Operator is stored as a 3. One could continue this exercise and map each of the operators to the numbers, and then decode the numeric values back to the humanly readable values to show in the query results.

Notice also that the “and”s and “or”s are saved in separate “Group” elements.

So now, you know how to find and cross-reference all the SendPorts that are used by your ReceivePorts, and hopefully that will help you figure out content-based routing and better understand and document your system.

You can specify more than one email in the to: of the SendPort.  Just be sure and use a semicolon as the separator, and not a comma.

SMTP Send Port Configuration