Business Activity Monitoring (BAM) is designed to handle high volumes of data without slowing down your core BizTalk processes. It achieves this by splitting the workload across several specialized SQL Server databases.
If you are managing a BizTalk environment, you must understand the “Who’s Who” of the BAM database world.
📝 One-Minute Brief
BizTalk BAM isn’t just one database; it’s a sophisticated architecture designed for high-performance tracking and historical analysis. This post breaks down the five core BAM databases: BAMPrimaryImport (the landing zone for real-time data), BAMArchive (for long-term storage), BAMStarSchema (for OLAP staging), and the BAMAnalysis and BAMAlerts databases. Understanding these roles is crucial for database administrators and developers to ensure system scalability and data integrity.
BAMAlertsApplication and BAMAlertsNSMain
BAM isn’t just for looking at charts; it can also tell you when things go wrong.
- Role: Stores the configuration and state for BAM Alerts. It works with SQL Server Notification Services (or Database Mail in newer versions) to send emails or notifications when business thresholds are hit.
Databases:
- BAM Notification Services Application database (BAMAlertsApplication): Contains alert information for BAM notifications.
- Role: Stores the configuration and state for BAM Alerts. It works with SQL Server Notification Services (or Database Mail in newer versions) to send emails or notifications when business thresholds are hit.
- BAM Notification Services Instance database (BAMAlertsNSMain): Contains instance information specifying how the notification services connect to the system that BAM is monitoring.
BAMPrimaryImport
The BAM Primary Import database (BAMPrimaryImport) table is primarily designed to store data for current and recently completed – The Heart of BAM. You will see several tables inside:
- The table bam_<activity>_Active and bam_<activity>_Completed contains the activities;
- The other tables are used by relationships and continuations
This is the most critical database. Every piece of data tracked via the TPE or the BAM API first lands here.
- Role: Real-time data collection and storage of active process instances.
- Note: It is optimized for writes, not long-term storage.
BAMStarSchema
The BAM Star Schema database (BAMStarSchema): Contains the staging table, the measure, and the dimension tables.
- Role: Acts as the staging area for multi-dimensional analysis (OLAP). It organizes data into “Facts” and “Dimensions.”
BAMAnalysis
BAM Analysis database (BAMAnalysis) contains BAM OLAP cubes for both online and offline analysis.
- Role: Houses the OLAP cubes used by the BAM Portal and Excel to show complex aggregations and KPIs.
BAMArchive
BAM Archive database (BAMArchive) archives old business activity data. You can create a BAM Archive database to minimize the accumulation of business activity data in the BAM Primary Import database.
To prevent the Primary Import database from becoming a bottleneck, older data is moved here.
- Role: Stores completed business activities that are no longer needed for real-time monitoring but are required for historical reporting.
- Pro Tip: Regular maintenance jobs move data from Primary Import to Archive to keep the system fast.
Understanding this architecture helps you troubleshoot performance issues. If your BAM Portal is slow, you likely have a bottleneck in BAMPrimaryImport or a stalled maintenance job. By spreading the data across these five zones, BizTalk ensures that business visibility never comes at the cost of system performance.
