BAM – Production Environment Management

Scenario:

In this article we will not discuss how to use BAM for your BizTalk applications. I made the assumption; You have created some BAM activities and used them in your BizTalk applications and the system is now running on your production servers. In my experience the majority of customers don't use BAM, if they do they just stick with the basics of creating few BAM activities and using them in their applications. So this article will just focus on that foundation and we'll ignore all the fancy BAM stuff like BAM views, alerts, notifications, real-time data, etc.

You can download this article as a PDF documentdownload now.

Production configuration:

At the very high level, your production BAM configuration should look like the one shown below

Step 1: Make sure to deploy the BAM activities:

This might look silly 🙂, but it's possible that you developed your solution taking advantage of BAM and didn't deploy any BAM activities in your production environment. BAM errors are not taken very seriously by the BizTalk runtime. Example: Your orchestration might contain BAM code, but you haven't deployed the corresponding BAM activities. In this case, the orchestrations will complete successfully without stopping, but there will be many events in the Event Viewer notifying the BAM errors, and records will also accumulate inTDDS_FailedTrackingDatatable in the database. Over time, this table will grow significantly, resulting in a bad database, and it's not nice to see a lot of error events in your event viewer, interfering with the analysis of the actual problems.

Step 2: Make sure the TDDS subservice is running.

If you have a BizTalk host with the option "Allow host tracking” to true and created a host instance for that host, then you automatically created a TDDS subservice inside that host instance.

The important step is to ensure that at least one BizTalk host/host instance is configured to run.Tracking Data Decoding Service (TDDS)" in your area. When you use BAM in your BizTalk applications (in your orchestrations, pipelines, etc.), the BizTalk runtime uses one of the asynchronous event streams to write BAM data for performance reasons. When an asynchronous event stream is used, the BAM data is first written to the BizTalk message box database and it is the responsibility of the TDDS service to move the BAM data out of the BizTalk message box databaseBAMPrimaryImportDatabase. If you do not run the TDDS service, the data in the message box database will accumulate and over time result in a bloated, unhealthy message box database. For more information about the tracking host, see this articlehttp://msdn.microsoft.com/en-us/library/ee308950(BTS.10).aspx

Step 3: Set up your active window for the BAM data:

Based on your business needs, it is important to retain historical BAM data for a specific period of time. The default setting in the standard installation of BizTalk is to keep the data for 6 months. If you collect a lot of BAM data, you may need to reduce this value to days, weeks, or months. You can change the value by usingbm.exeTool.

You can view the existing value by running the following command

Step 4: Make sure BAM_DM_<<activity name>> SSIS packages are configured to run

One of BAM's greatest advantages is that it creates the database infrastructure to collect business data. You don't need to manually deploy the database tables and views; It is created automatically when you deploy the BAM activities using the bm.exe administration tool. For each activity provided, 5 corresponding database tables are createdBAMPrimaryImportDatabase with the postfix_Active, _ActiveRelationships, _Completed, _CompletedRelationships und _Continuations(Few SQL views are created to work with these tables and corresponding tablesBAM_DM_<<activity name>>SSIS package is created).

The main reason for this deployment is to improve the read/write speed when accessing the BAM data. The number of records in the _Activetable will be less; It only contains in-flight instance data. Once the instance has finished processing, the data is moved to _.accomplishedtables.

This active/locked partition is good to some degree, until the number of records in your locked tables started to grow, that's the pointBAM_DM_<<Activity Names>>SSIS packages come into the picture. These SSIS packages are mainly responsible for 2 tasks:

Taking database table serving to the next level to improve read/write access speed, and
Remove the old data from theBAMPrimaryImportDatabase. It can either be movedBAMArchivDatabase deleted or erased as shown in the picture (beginning of the article).

Additional tables are created whenever you run this SSIS packageBAMPrimaryImportDatabase post is fixed by a GUID and old data is removed based on the active window configuration.

The SSIS packages for your BAM activities are created when you deploy your activities, but they are createdNOT automatically configuredin your environment. In your production environment, it is important to create a SQL job to run the SSIS packages on a regular basis (every day or every week based on your BAM data volume).

NOTE:If you installed your BAM databases on a named SQL instance, theBAM_DM_Packages automatically when you connect to integration services. See topic "Where are my BAM_DM_ <<BAM activity name>> packages?" topic at the end of the article for the solution.

Step 5: Should it be archived or deleted?

Historical data:

Historical data in BAM falls into 2 categories.

Based on the active window settings, the data is preservedBAMPrimaryImportdatabase for this period.Example:If your active window is set to 5 days, 5 days of BAM data will be stored in different activity tables (partitioned with multiple GUIDs) in itBAMPrimaryImportDatabase.
All data older than the active window is saved in theBAMArchivdatabase until manually deleted.

It will be a business decision to keep the historical data for governance purposes. Until recently, before Microsoft released Hotfix 971984 (http://support.microsoft.com/kb/971984) it was not possible to delete the data directlyBAMPrimaryImportDatabase in BizTalk Server 2006. You always need to move the old history dataBAMArchivDatabase and then delete it manually (see the script at the end of the article to do this).

With the hotfix (it is available by default on BizTalk 2009) you can configure whether the data comes in or notBAMArchivDatabase with the tool bm.exe.

bm set-archive ?activity:your_activity_name ?shouldArchive:true|false

Where are my BAM_DM_ <<BAM activity name>> packages?

If you have BAM databases installed on a named SQL instance and try to connect to Integration Services and expand the MSDB node, the following error message is displayed.

The SQL server specified in the SSIS service configuration does not exist or is not available. This can occur if there is no default instance of SQL Server on the computer. For more information, see the "Configuring the Integration Services Service" topic in Server 2005 Books Online.

As noted in the error message, the solution is available atConfigure integration servicesArticle. You have to open itMsDtsSrvr. ini. xmlFile located in the %ProgramFiles%Microsoft SQL Server100DTSBinn folder (90 instead of 100 if you are using SQL 2005) and enter the correct server name in the instance names<Servername>Element.

After this change, you must restart the SQL Integration Service.

BAMArchive delete script:

There are no clear documented procedures for handling the existing dataBAMArchivDatabase. Over a period of timeBAMArchivDatabase will also grow to a significant size and it will be necessary to truncate the data. Here is the script I use for truncationBAMArchivdatabase, please noteThis will erase all datapresent in theBAMArchivdatabase (use at your own risk), but you still have the historical data based on your active window configuration in theBAMPrimaryImportDatabase.

Truncate table bam_<<your_bam_activityname_1>>_instances
Truncate table bam_<<your_bam_activityname_1>>_relationships

Truncate table bam_<<your_bam_activityname_2>>_instances
Truncate table bam_<<your_bam_activityname_2>>_relationships

<<Repeat the steps to truncate the table for all your Bam activities>>

Declare @SqlQuery nvarchar(4000)
Declare @num int

Set @num = (Select Count([Name]) From sys.tables
Where [name] like "%bam_%Instances_200%-%-%" or
[name] like ‘%bam_%Relationships_200%-%-%’)

— Print @Num
Declare @i int
Set @i = 0
While @Num > @i
Start
Setze @i = @i + 1
Set @SqlQuery = (Select Top 1 ‘Drop Table [‘ + [Name] + ‘]’ From sys.tables
Where [name] like "%bam_%Instances_200%-%-%" or
[name] like ‘%bam_%Relationships_200%-%-%’)
Run(@SqlQuery)
End