BizTalk : How To : Call .Net Component inside Biztalk mapper using XSLT call template PART 2

Wednesday, March 27, 2013
post by  Richard Seroter

A problem I mentioned was that the member variable in the class that the map was calling seemed to be getting shared amongst execution instances. Each map creates a sequential page number in the XSLT and puts it into the destination XML. However, I’d see output where the first message had pages “1..3..5..7..8″ and the second message had pages “2..4..6..9.” Very strange. I thought I fixed the problem, but it surfaced today in our Test environment.
So, I set out to keep everything local to the map and get rid of external assembly calls. After banging my head for a few minutes, I came up the perfect solution. I decided to mix inline script with inline XSLT. “Madness” you say? I built a small test scenario. The map I constructed looks like this:
In the first Scripting functoid, I have “inline C#” selected, and I created a global variable. I then have a function to increment that variable and return the next number in sequence.
Did you know that you could have “global variables” in a map? Neat stuff. If I check out the XSLT that BizTalk generates for my map, I can see my function exposed as such:
Now I know how to call this within my XSLT! The second Scripting functoid’s inline XSLT looks like this:

Notice that I can call the C# method written in the previous functoid with this code:
<xsl:value-of select=”userCSharp:GetPageNumber()”/>
The “prefix” is the auto-generated one from the XSLT. Now, all the calculations are happening locally within the map, and not relying on outside components. The result of this map is a document that looks like this:
There you go. Using global variables within a BizTalk map and calling a C# function from within the XSLT itself.
Read more ...

BizTalk : How To : Call .Net Component inside Biztalk mapper using XSLT call template PART 1

Tuesday, March 26, 2013

post by  Richard Seroter
I encountered a particularly tricky multi-part mapping scenario. I had to build a destination message that contained groupings from the two source messages. Each record in the first source message created a destination node, and each record in the second source message created a destination node directly beneath the related first source record. To make matters tougher, every destination record has an attribute containing a sequential number. So out of this …
<source1>
  <Node1><Node1>
  <Node2></Node2>
</source1>
<source2>
  <NodeRelatedToNode1></NodeRelatedToNode1>
  <NodeRelatedToNode1></NodeRelatedToNode1>
  <NodeRelatedToNode2></NodeRelatedToNode2>
</source2>
The destination was supposed to look like this …
<destination>
  <Node1 page=”1″><Node1>
  <NodeRelatedToNode1 page=”2″></NodeRelatedToNode1>
  <NodeRelatedToNode1 page=”3″></NodeRelatedToNode1>
  <Node2 page=”4″></Node2>
  <NodeRelatedToNode2 page=”5″></NodeRelatedToNode2>
</destination>
The grouping part wasn’t too tough, just used a Scripting functoid with the XSLT Call Templateand a little hand written XSL. The hard part was creating the sequential “page” numbers. Those familiar with XSLT know that the “variables” in XSLT are basically constants, so you can’t create a variable and increment it. I considered building some sort of recursion to get my incremented number, but in the end, decided to call a custom .NET component from my map’s XSLT. I built a C# component that had a member variable, and a method called “GetNext()” which incremented and then returned the next sequential number. I then set my map’s Custom Extension XML to an XML document referencing my custom component. Now in my XSLT Call Template I could get the next “page” number each time I built a destination node. Neat!
See here for an example of doing this.
Here’s where a “quirk” was introduced. When I deployed this map, and ran multiple documents through it, the first document had it’s paging correct (e.g. pages 1-5), but the next messages had the wrong values (e.g. 6-10, 11-16, etc). What was happening was that somehow this custom C# component was being shared! The “increment” kept counting on each orchestration call! My C# component wasn’t built as a “static” object, and I assumed that the scope of each custom object was the individual map (or orchestration) instance.
I still have no idea why this happened, but to ensure it wouldn’t keep happening, I added a method to the custom component called “Reset()” which set the counter to 0. Then at the top of the map I call out to that method to ensure that each map starts its counter at 0.
Read more ...

BizTalk : How To : Save suspended messages in BizTalk Vb Script

Monday, March 25, 2013

Last week, I needed to save 700+ suspended messages to file. I didn’t want to go in to each instance and click ‘save to file’ 700 times. A quick search in the web did not find what I want, however there were a number of articles about extracting messages from the BizTalk tracking database. One of which is this excellent article by Thiago Almeida.
My scenario was for an existing BizTalk 2004 implementation in the company and is only a once-off thing I need to do. For the newer BizTalk 2006 applications, there is the ‘Failed Message Routing’ feature that can be enabled on send ports and receive ports. The failed messages could then be easily subscribed to a send port to output to file.
Then it occured to me that the WMI script Terminate.vbs has the option to save the messages before terminating the instances (http://go.microsoft.com/fwlink/?LinkID=107591 and slightly updated for 2009). Thus changing this script to do what I want required the least effort. I could just use the script as it is to save all the messages and terminate the instances. However it didn’t take much to modify it to take a parameter for filtering on an instance name and to only save messages (and not terminate them). Below is the usage description of the save_messages.vbs script and the actual script. It works for BizTalk 2004, 2006 and 2009.
There is also a replacement of the Terminate.vbs script: Biztalk Terminator.
Usage:
cscript save_messages.vbs < -Z | -A | -DIS | -SR | -SNR > [Port/Orchestration name]
-Z saves all “Zombie” instances (e.g. completed with discarded messages)
-A saves all suspended and zombie instances as well as all routing failure reports
-SR saves suspended resumable instances only
-SNR saves suspended non-resumable instances only
-DIS saves all dehydrated ‘isolated adapter’ instances
optionally supply the name of the orchestration or port name to filter on specific instances
Ensure that the C:\Temp folder exists before running as that is where it saves the instances
Example: cscript save_messages.vbs -z “E-Reporting Data Transform Port”
Visual Basic Script:
‘ save_messages.vbs
‘ Enter save_messages.vbs with no arguments from a command prompt for usage
‘ This script needs to be run under a user account that is a member of the BizTalk Administrators
‘ group. This script needs to be run on a machine that is configured with BizTalk administration
‘ tools.
dim objBtsWmiNS, objMsg, svcinsts, inst, msg, ndx, size
Dim aryHostNames()
Dim aryObjQueues()
Dim aryHostBatchSize()
Dim strKey2Instance
Dim strQuery2Msg
Dim strServiceName
On Error Resume Next
Dim objArgs: Set objArgs = WScript.Arguments
If ( objArgs.Count = 0 OR objArgs.Count > 2) Then
PrintUsage()
wscript.quit 0
End If
wmiQuery = “”
‘ServiceStatus = 16 – ‘Completed With Discarded Messages’ in BizTalk Server 2004
‘ServiceStatus = 32 – ‘Suspended (not resumable)’
‘ServiceStatus = 4 – ‘Suspended (resumable)’
‘ServiceClass = 64 – ‘Routing Failure Report’
‘ErrorId = “0xC0C01B4C” – is how ‘Completed With Discarded Messages’ are exposed in BizTalk Server 2006
If (objArgs(0) = “-Z” OR objArgs(0) = “-z”) Then
wmiQuery = “select * from MSBTS_serviceinstance where ServiceStatus=16 OR ErrorId=’0xC0C01B4C’”
End If
If (objArgs(0) = “-A” or objArgs(0) = “-a”) Then
wmiQuery = “select * from MSBTS_serviceinstance where ServiceStatus=4 OR ServiceStatus=32 OR ServiceStatus=16 OR ErrorId=’0xC0C01B4C’ OR ServiceClass=64″
End If
If (objArgs(0) = “-SR” or objArgs(0) = “-sr”) Then
wmiQuery = “select * from MSBTS_serviceinstance where ServiceStatus=4″
End If
If (objArgs(0) = “-SNR” or objArgs(0) = “-snr”) Then
wmiQuery = “select * from MSBTS_serviceinstance where ServiceStatus=32″
End If
If (objArgs(0) = “-DIS” or objArgs(0) = “-dis”) Then
wmiQuery = “select * from MSBTS_serviceinstance where ServiceClass=32 AND ServiceStatus=8″
‘ServiceClass = 32 ‘Isolated Adapter
‘ServiceStatus = 8 ‘Dehydrated
End If
saveMessagesBeforeTermination = True
If ( objArgs.Count > 1) Then
strServiceName = objArgs(1)
End If
If(wmiQuery = “”) Then
PrintUsage()
wscript.quit 0
End If
wscript.echo “–>Connecting to BizTalk WMI namespace”
Set objBtsWmiNS = GetObject(“WinMgmts:{impersonationLevel=impersonate, (security)}\\.\root\MicrosoftBizTalkServer”)
If Err <> 0 Then
CheckWMIError
wscript.quit 0
End If
wscript.echo “–>Getting BizTalk host collection”
Set hosts = objBtsWmiNS.ExecQuery(“select * from MSBTS_HostSetting”)
If Err <> 0 Then
CheckWMIError
wscript.quit 0
End If
hostCount = hosts.count
ReDim aryHostNames(hostCount – 1)
ReDim aryObjQueues(hostCount – 1)
ReDim aryHostBatchSize(hostCount – 1)
wscript.echo “–>Retrieve BizTalk host names and loading host queues”
ndx = 0
For Each host in hosts
wscript.echo “Found host ” & host.Properties_(“Name”)
aryHostNames(ndx) = host.Properties_(“Name”)
Set aryObjQueues(ndx) = objBtsWmiNS.Get(“MSBTS_HostQueue.HostName=”"” & aryHostNames(ndx) & “”"”)
If Err <> 0 Then
CheckWMIError
wscript.quit 0
End If
ndx = ndx + 1
Next
wscript.echo “–>Getting collection of service instances”
Set svcinsts = objBtsWmiNS.ExecQuery(wmiQuery)
‘Iterate through instances and save them in host-specific arrays.
wscript.echo “–>Start iterating service instances”
totalCount = 0
For Each inst in svcinsts
If (objArgs.Count = 1 Or (objArgs.Count > 1 And strServiceName = inst.Properties_(“ServiceName”) ) ) Then
wscript.echo “Found suspended instance “”" & inst.Properties_(“ServiceName”) & “”" on host ” & inst.Properties_(“HostName”)
‘Resolve host index
For hostIdx = 0 To hostCount-1
If aryHostNames(hostIdx) = inst.Properties_(“HostName”) Then
Exit For
End If
Next
’16 is an internal service class that cannot be terminated
If 16 = inst.Properties_(“ServiceClass”) Then
wscript.echo “Skipping BizTalk internal service instances (they cannot be terminated anyway)”
Else
’64 is a routing failure report and doesn’t have messages that can be saved
If 64 = inst.Properties_(“ServiceClass”) Or 16 = inst.Properties_(“ServiceClass”) Then
saveMessagesBeforeTermination = False
End If
errorCountSavingMessages = 0
If saveMessagesBeforeTermination Then
strQuery2Msg = “select * from MSBTS_MessageInstance where ServiceInstanceID=”"” & inst.Properties_(“InstanceId”) & “”"”
Set msgInsts = objBtsWmiNS.ExecQuery(strQuery2Msg)
For Each msg in msgInsts
msg.SaveToFile “C:\Temp”
If Err <> 0 Then
CheckWMIError
wscript.echo “Failed to save MSBTS_MessageInstance”
wscript.echo Err.Description & Err.Number
errorCountSavingMessages = errorCountSavingMessages + 1
Else
wscript.echo “Saved message ” & msg.Properties_(“MessageInstanceID”)
End If
Next
End If
totalCount = totalCount + 1
End If
End If
Next
‘ Delete whatever is left
For hostIdx = 0 To hostCount-1
If aryHostBatchSize(hostIdx) > 0 Then
TerminateAccumulatedInstacesForHost hostIdx
End If
Next
wscript.echo “SUCCESS> ” & totalCount & ” instances were found and attempted to be saved”
‘This subroutine deals with all errors using the WbemScripting object.
‘Error descriptions are returned to the user by printing to the console.
Sub CheckWMIError()
If Err <> 0 Then
On Error Resume Next
Dim strErrDesc: strErrDesc = Err.Description
Dim ErrNum: ErrNum = Err.Number
Dim WMIError : Set WMIError = CreateObject(“WbemScripting.SwbemLastError”)
If (TypeName(WMIError) = “Empty” ) Then
wscript.echo strErrDesc & ” (HRESULT: ” & Hex(ErrNum) & “).”
Else
wscript.echo WMIError.Description & “(HRESULT: ” & Hex(ErrNum) & “).”
Set WMIError = nothing
End If
‘wscript.quit 0
End If
End Sub
Sub PrintUsage()
wscript.echo “Usage:”
wscript.echo “cscript save_messages.vbs < -Z | -A | -DIS | -SR | -SNR > [Port/Orchestration name]“
wscript.echo
wscript.echo “  -Z saves all “”Zombie”" instances (e.g. completed with discarded messages)”
wscript.echo “  -A saves all suspended and zombie instances as well as all routing failure reports”
wscript.echo “  -SR saves suspended resumable instances only”
wscript.echo “  -SNR saves suspended non-resumable instances only”
wscript.echo “  -DIS saves all dehydrated ‘isolated adapter’ instances”
wscript.echo “  optionally supply the name of the orchestration or port name to filter on specific instances”
wscript.echo
wscript.echo “  Ensure that the C:\Temp folder exists before running as that is where it saves the instances”
wscript.echo
wscript.echo “  Example: cscript save_messages.vbs -z “”E-Reporting Data Transform Port”"”
wscript.echo
End Sub
Read more ...

BizTalk : How To : Map Repeating Sequence Elements

Friday, March 22, 2013
source: paul petrov


Repeating sequence groups can often be seen in real life XML documents. It happens when certain sequence of elements repeats in the instance document. Here's fairly abstract example of schema definition that contains sequence group:
<xs:schema xmlns:b="http://schemas.microsoft.com/BizTalk/2003"
           xmlns:xs="http://www.w3.org/2001/XMLSchema"
           xmlns="NS-Schema1"
           targetNamespace="NS-Schema1" >
  <xs:element name="RepeatingSequenceGroups">
    <xs:complexType>
      <xs:sequence maxOccurs="1" minOccurs="0">
        <xs:sequence maxOccurs="unbounded">
          <xs:element name="A" type="xs:string" />
          <xs:element name="B" type="xs:string" />
          <xs:element name="C" type="xs:string" minOccurs="0" />
        </xs:sequence>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>
And here's corresponding XML instance document:
<ns0:RepeatingSequenceGroups xmlns:ns0="NS-Schema1">
  <A>A1</A>
  <B>B1</B>
  <C>C1</C>
  <A>A2</A>
  <B>B2</B>
  <A>A3</A>
  <B>B3</B>
  <C>C3</C>
</ns0:RepeatingSequenceGroups>
As you can see elements A, B, and C are children of anonymous xs:sequence element which in turn can be repeated N times. Let's say we need do simple mapping to the schema with similar structure but with different element names:
<ns0:Destination xmlns:ns0="NS-Schema2">
  <Alpha>A1</Alpha>
  <Beta>B1</Beta>
  <Gamma>C1</Gamma>
  <Alpha>A2</Alpha>
  <Beta>B2</Beta>
  <Gamma>C2</Gamma>
</ns0:Destination>
The basic map for such typical task would look pretty straightforward:
If we test this map without any modification it will produce following result:
<ns0:Destination xmlns:ns0="NS-Schema2">
  <Alpha>A1</Alpha>
  <Alpha>A2</Alpha>
  <Alpha>A3</Alpha>
  <Beta>B1</Beta>
  <Beta>B2</Beta>
  <Beta>B3</Beta>
  <Gamma>C1</Gamma>
  <Gamma>C3</Gamma>
</ns0:Destination>
The original order of the elements inside sequence is lost and that's not what we want. Default behavior of the BizTalk 2009 and 2010 Map Editor is to generate compatible map with older versions that did not have ability to preserve sequence order. To enable this feature simply open map file (*.btm) in text/xml editor and find attribute PreserveSequenceOrder of the root <mapsource> element. Set its value to Yes and re-test the map:
<ns0:Destination xmlns:ns0="NS-Schema2">
  <Alpha>A1</Alpha>
  <Beta>B1</Beta>
  <Gamma>C1</Gamma>
  <Alpha>A2</Alpha>
  <Beta>B2</Beta>
  <Alpha>A3</Alpha>
  <Beta>B3</Beta>
  <Gamma>C3</Gamma>
</ns0:Destination>
The result is as expected – all corresponding elements are in the same order as in the source document. Under the hood it is achieved by using one common xsl:for-each statement that pulls all elements in original order (rather than using individual for-each statement per element name in default mode) and xsl:if statements to test current element in the loop:
  <xsl:template match="/s0:RepeatingSequenceGroups">
    <ns0:Destination>
      <xsl:for-each select="A|B|C">
        <xsl:if test="local-name()='A'">
          <Alpha>
            <xsl:value-of select="./text()" />
          </Alpha>
        </xsl:if>
        <xsl:if test="local-name()='B'">
          <Beta>
            <xsl:value-of select="./text()" />
          </Beta>
        </xsl:if>
        <xsl:if test="local-name()='C'">
          <Gamma>
            <xsl:value-of select="./text()" />
          </Gamma>
        </xsl:if>
      </xsl:for-each>
    </ns0:Destination>
  </xsl:template>
BizTalk Map editor became smarter so learn and use this lesser known feature of XSLT 2.0 in your maps and XSL stylesheets.
Read more ...

BizTalk : Q&A : Read Repeating Namepair values using XSLT for-each

Friday, March 22, 2013
Q: Hi i have a strange issue with matching specific attribute of xml node. Example code that doesnt work:
<xsl:for-each select="../../unit/service/price/season[@name=$period_name]">
     <xsl:attribute name="std_bed_price">
          <xsl:value-of select="../@amount"/>
     </xsl:attribute>
</xsl:for-each>
Example code that DOES work but i don't like this way too much:
 <xsl:for-each select="../../unit/service/price/season">
     <xsl:if test="@name = $period_name">
          <xsl:attribute name="std_bed_price">
               <xsl:value-of select="../@amount"/>
          </xsl:attribute>
     </xsl:if>
 </xsl:for-each>
If in first example i replace the variable name with some of the values like 'A' it works, i also tested what variable name is selected and it has the correct data inside (so, 'A','B','C' ...)
Anyone had this problem before?

A: You might try changing it to an apply-templates instead of a foreach. Something like the following should work.
<xsl:template match="price">
    <xsl:attribute name="std_bed_price">
        <xsl:value-of select="@amount" />
    </xsl:attribute>
</xsl:template>
And then call it like:
<xsl:apply-template select="../../unit/service/price/[season/@name=$period_name]" />
or

select="../../unit/service/price/season[./@name=$period_name]
Read more ...

BizTalk Highly Available Installation with Scale Out Configuration

Thursday, March 21, 2013

scale out configuration for highly available server farm

Source: MSDN
To provide high availability for the BizTalk Server databases, configure two computers that are running SQL Server 2008 or SQL Server 2005 in a Windows cluster. These computers can run in an active/active, active/passive, or active/active/passive (requires three computers) configuration for redundancy and can store data on a shared drive (such as a RAID 1+0 SCSI disk array) or storage area network (SAN).
If the SQL Server service becomes unavailable for any reason, the database cluster transfers resources from the active computer to the passive computer. During this failover process, the BizTalk Server service instances experience database connection failures and automatically restart to reconnect to the databases. The functioning database computer (previously the passive computer) begins processing the database connections after assuming the resources during failover.
Clustering the BizTalk Server databases is discussed in Clustering the BizTalk Server Databases. This section focuses on scaling out the BizTalk Server databases to provide high availability.
This section provides information about to configure BizTalk MessageBox database for high availability.

Running Multiple MessageBox Databases

To enhance the scalability of the BizTalk Server databases and to address high CPU utilization on the MessageBox database SQL Server computer, you can configure BizTalk Server to store data across multiple MessageBox databases. You create the first MessageBox database when you run the Configuration Wizard. This MessageBox database is the master MessageBox database. There is a single master MessageBox database in your BizTalk Server deployment. The master MessageBox database contains the master subscription information and routes messages to the appropriate MessageBox database. Typically, you want to dedicate the master MessageBox database to do routing only and let the other MessageBox databases do the processing. To make a MessageBox database to do routing only, select Disable new message publication from the MessageBox properties in BizTalk Administration console.
An example of MessageBox database processing flow is as follows:
  1. When the master MessageBox database receives a new activation message—a brand new instance of a business process or a subscription message—the master MessageBox database distributes the activation message to the next available MessageBox database. For example, if you have one master MessageBox database and two MessageBox databases, the master MessageBox database routes the first activation message to MessageBox database 1, the second activation message to MessageBox database 2, the third activation message to MessageBox database 1, and so on in a round-robin pattern. The master MessageBox database uses built-in logic to load balance, and does not need additional load-balancing mechanisms.

  2. After the master MessageBox database routes the activation message to a particular MessageBox database (for example, MessageBox database 1), the business process goes into memory and runs. 

  3. If the business process has to wait for a message, and the wait time is longer than several seconds, the business process is persisted back into MessageBox database 1. The business process is waiting for a correlation message. 

  4. When the correlation message arrives at the master MessageBox database, the Message Engine does a lookup operation in the database for the MessageBox database that contains the state for the correlation message (in this example, MessageBox 1). The master MessageBox database delivers the message to the MessageBox database that contains the business process. 

  5. The business process is brought back into memory to continue processing until it finishes or until it has to wait for another correlation message.

BizTalk Server stores all the states in the MessageBox databases, and each MessageBox database contains state information for different business processes. For reliability, you must cluster all the MessageBox databases, including the master and secondary MessageBox databases.
To configure multiple MessageBox databases, you use the BizTalk Server Administration console to add the computers that are running SQL Server 2008 or SQL Server 2005. From an administration perspective, you only have to add new MessageBox databases. BizTalk Server automatically handles the round-robin distribution of activation messages and sends correlation messages to the correct MessageBox databases.
If you configure multiple MessageBox databases in your environment you should create a minimum of three MessageBox databases for your BizTalk Server group and you should disable message publication on the master MessageBox database. This recommendation is made because adding additional MessageBox databases incurs overhead by the master MessageBox database for routing messages between the MessageBox databases. If you only configure two MessageBox databases then most of the benefit gained by the additional MessageBox database is offset by the overhead consumed by the master MessageBox database for message routing.
Ee308961.Important(en-US,BTS.10).gifImportant
BizTalk Server stores all the states in the MessageBox databases, and each MessageBox database contains state information for different business processes. For reliability, you must cluster all the MessageBox databases, including the master and secondary MessageBox databases.

Providing High Availability for Multiple MessageBox Databases

While adding MessageBox databases to your BizTalk Server deployment improves scalability, it does not provide high availability because each MessageBox database is unique and independent and is potentially a single point of failure for your BizTalk Server environment. To add redundancy involves configuring a server cluster for each MessageBox database. BizTalk Server distributes data across the multiple MessageBox databases, so the databases do not share data or otherwise provide redundancy without server clustering.
Depending on the requirements of your particular deployment, you might want to enhance performance for tracking by isolating the BizTalk Tracking database onto a separate SQL Server computer and by creating a separate BizTalk Host dedicated to host tracking. The following figure shows a dedicated tracking host with two host instances and clustered databases.
Scaling out the tracking databases
If your deployment has high throughput and involves tracking lots of data for these messages, the tracking overhead could potentially consume lots of resources on the computer that is running SQL Server. If this situation occurs and a high rate of incoming messages continues, BizTalk Server reaches a point where it cannot process new messages because the resources required to track messages are greater than the resources required to run the other BizTalk Server components (such as receiving messages and persisting them to the MessageBox database).
To improve performance and security, we recommend that you dedicate a host for tracking that does not contain any other items (such as receive locations, orchestrations, or pipelines) and that you disable tracking from the receiving, processing, and sending hosts. To provide high availability for the tracking host, create more than one host instance of the tracking host. For more information about creating a tracking host, see How to Create a New Host (http://go.microsoft.com/fwlink/?LinkId=156825) in BizTalk Server 2009 Help.
For each MessageBox database, BizTalk Server uses only one tracking host instance to move messages from the MessageBox database to the BizTalk Tracking database (BizTalkDTADb). If additional computers run instances of the tracking host, BizTalk Server automatically scales out the handling of each MessageBox database to a separate instance of the tracking host. If the number of MessageBox databases is larger than the number of tracking host instances, one or more tracking host instances will service more than one MessageBox database.
To provide high availability for the BizTalk Tracking database, use Windows Clustering to configure two database computers that are running SQL Server 2008 or SQL Server 2005 in an active/passive configuration.
Business Activity Monitoring (BAM) provides visibility into business processes independent of the IT implementation or across a heterogeneous IT implementation. The BAM SQL Server databases (BAM Star Schema database, BAM Primary Import database, and BAM Archive database) and the BAM Analysis database store the business activity data that is different from the operational monitoring data. The following diagram shows the BAM database infrastructure.
BAM database infrastructure
To make sure your BAM infrastructure is highly available, do the following:
  • Cluster the BAM Primary Import database and the BAM Analysis database. The BAM Primary Import database is the center of the Business Activity Monitoring system. It is therefore important that you make this database highly available by using Windows Clustering and that you follow the next two recommendations to prevent this database from filling up. The BAM Analysis database is an Analysis Services database that stores the data that business analysts use to build activity aggregations and OLAP cubes, and therefore any downtime of this database affects their productivity. While you do not have to cluster the BAM Archive database, we recommend that you monitor the event log for errors when the SQL Server Integration Services (SSIS) packages run to make sure the data has been transferred successfully, and to monitor the size of the database so that you can replace it before it fills up.

  • Define an online window. To allow for better performance and avoid downtime, BAM partitions the data in the BAM Primary Import database into tables based on the timestamp when the activity was completed. BAM achieves this by regularly swapping the completed table with another empty table of identical format. After BAM does this, the additional completed activities go into the new partition (table), while BAM keeps the old partitions for the time defined in the online window. You must define an online window to make sure the number of partitions in the BAM Primary Import database does not grow too large. For more information about scheduling online windows, see Archiving Primary Import Database Data (http://go.microsoft.com/fwlink/?LinkId=156826) in BizTalk Server 2009 Help.

  • Schedule SSIS packages to run periodically. Defining an online window makes sure your BAM Primary Import database does not fill up with old activity partitions. You must also schedule SSIS packages to run periodically to create a new partition for the activity data and to move the data from the old partitions in the BAM Primary Import database into the BAM Archive database. For more information about scheduling SSIS packages, see Scheduling SQL Server Integration Services Packages (http://go.microsoft.com/fwlink/?LinkId=156827) in BizTalk Server 2009 Help.

  • Carefully choose small sets of data items (checkpoints), and avoid including unnecessary data items when defining an activity. 

  • Understand the trade-offs between scheduled and real-time aggregations when you design your aggregations. Real-time aggregations are automatically maintained by SQL Server triggers and have zero latency. They are ideal for some mission-critical low-latency scenarios, but they incur a performance cost whenever the events are being written to the BAM Primary Import database. Scheduled aggregations rely on scheduled cubing SSIS packages to update their aggregation data. Their latency is equal to or greater than the SSIS schedule interval, but overall they have a smaller performance impact on the BAM Primary Import database.

  • If you choose scheduled aggregations, make sure you schedule the cubing SSIS to run more frequently than the archiving SSIS. This is because the archiving SSIS will not move the activity data that has been processed for scheduled aggregation to the BAM Archive database.

  • Enable the BAM Event Bus service in multiple computers to obtain failover functionality. 

To provide high availability for the other BizTalk Server databases, configure two computers that are running SQL Server 2008 or SQL Server 2005 in a Windows cluster. These computers can run in an active/active or active/passive configuration for redundancy and can store data on a shared drive (such as a RAID 1+0 SCSI disk array) or storage area network (SAN).
Read more ...