In this article we are not going to discuss about how to utilize BAM for your BizTalk applications. I’ve made the assumption; you created few BAM activities and utilized them inside your BizTalk applications and the system is now running on your productions servers. In my experience, majority of the clients don’t take advantage of BAM, when they do they just stick to the basics of creating few BAM activities and utilizing them in their applications. So this article is focused only on this basis and we are going to ignore all the fancy BAM stuff like BAM Views, Alerts, Notifications, real-time data etc.
At the very high level your production BAM configuration should like the one shown below
This one might look silly 🙂 but it’s possible you might have developed your solution taking advantage of BAM and not deployed BAM activities in your production environment. BAM failures are not treated very seriously by the BizTalk runtime. For example: Your orchestration might have BAM code embedded inside, but you haven’t deployed the corresponding BAM activities. In this case the orchestrations will complete successfully without getting suspended but there will be lot of events in the event viewer notifying the BAM failures and also records will start accumulate in the TDDS_FailedTrackingData table in the database. Over a period of time this table will grow substantially, resulting in an unhealthy database and its not nice to see lot of failure events in your event viewer, which will interfere with analysing the real issues.
When you configure a BizTalk host with the option “Allow Host Tracking” to true and created a host instance for that host, then you have created a TDDS sub-service inside that host instance automatically.
The important step is to make sure there is at least one BizTalk Host/Host Instance configured to run “Tracking Data Decode Service (TDDS)” in your environment. When you utilize BAM inside your BizTalk applications (inside your Orchestrations, Pipelines etc) BizTalk runtime will utilize one of the asynchronous event stream to write BAM data for performance reasons. When an asynchronous event stream is used, the BAM data will be first written to the BizTalk message box database and its the job of the TDDS service to move the BAM data from BizTalk message box database to BAMPrimaryImport database. If you don’t run the TDDS service the data will start accumulating in the message box database and over a period of time will result in a bloated unhealthy message box database. For more info about Tracking host read this article http://msdn.microsoft.com/en-us/library/ee308950(BTS.10).aspx
Based on your business needs, it will be critical to keep the historical BAM data for certain period. The default setting by standard BizTalk installation is to keep the data for 6 months. If you are capturing lot of BAM data, you might need to reduce this value to days, weeks or months. You can change the value by using the bm.exe tool.
You can see the existing value by executing the following command
bm get-activitywindow ?Activity:your_activity_name
You can set the active window to desired value by executing the following command
bm set-activitywindow ? Activity:your_activity_name ?TimeLength:1 ?TimeUnit:Day
One of the biggest advantages of BAM is it creates the database infrastructure for you to capture business data. You don’t need to manually provision the database tables and views; it’s automatically created when you deploy the BAM activities using the management tool bm.exe. For every activity deployed there will be 5 corresponding database tables created in the BAMPrimaryImport database with the postfix _Active, _ActiveRelationships, _Completed, _CompletedRelationships and _Continuations (few SQL views will created to work with these tables and corresponding BAM_DM_<<activity name>> SSIS package will be created).
The main reason for this provisioning is to improve the read/write speed while accessing the BAM data. The number of records in the _Active table will be less; it will contain only in-flight instance data, once the instance complete processing the data will be moved to _Completed tables.
This active/completed partition is good to certain extend, until the number of records in your completed tables started to grow, that’s where BAM_DM_<<activity names>> SSIS packages comes into picture. This SSIS packages are mainly responsible for 2 tasks:
Whenever you run this SSIS package additional tables are created in BAMPrimaryImport database post 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 NOT configured automatically in your environment. In your production environment it’s critical to create a SQL job to run the SSIS packages in a periodic basis (every day or week based on your BAM data volume).
NOTE: If you have installed your BAM databases on a named SQL instance, then you won’t see the BAM_DM_ packages automatically when you connect to Integration services. See the topic “Where are my BAM_DM_ <<BAM Activity Name>> packages?” topic in the end of the article for the solution.
Historical Data in BAM falls under 2 categories.
It will be a business decision to keep the historical data for governance purpose. Until recently, before Microsoft issued the hot fix 971984 (http://support.microsoft.com/kb/971984 ) it was not possible to purge the data directly from BAMPrimaryImport database in BizTalk Server 2006. You always need to move the old historical data to BAMArchive database and then purge it manually (see the script in the bottom of the article for doing it).
With the hot fix (its available by default on BizTalk 2009) you can configure whether or not the data goes into BAMArchive database by using the bm.exe tool.
bm set-archive ?Activity:your_activity_name ?shouldArchive:true|false
If you have installed BAM databases on a named SQL instance and try to connect to Integration Services and expand the MSDB node, you’ll see the following error message.
The SQL server specified in SSIS service configuration is not present or is not available. This might occur when there is no default instance of SQL Server on the computer. For more information, see the topic “Configuring the Integration Services Service” in Server 2005 Books Online.
As noted in the error message the solution is available under Configuring the Integration Services article. You need to open the MsDtsSrvr.ini.xml file located under the folder %ProgramFiles%Microsoft SQL Server100DTSBinn (90 instead of 100 if you are using SQL 2005) and put the correct server nameinstance name inside the <ServerName> element.
You need to restart the SQL Integration Service after making this change.
There is no clear procedures documented in dealing with the data present in the BAMArchive database. Over a period of time BAMArchive database will also grow to substantial size and it will be required to truncate the data. Here is the script I use to truncate the BAMArchive database, please note this will wipe out all the data present in the BAMArchive database (use it at your own risk), but you will still have the historical data based on your active window configuration in the BAMPrimaryImport database.
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
<<repeate the truncate table steps for all of 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
Begin
Set @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%-%-%’)
Execute(@SqlQuery)
End
