How to add a new column to BAM BizTalk tables which has 1 billion records

Question

We have all transactions of our application logged in BizTalk BAM tables. We perform billing activities every month using these tables.We get around a billion transactions every month in this table. We got a new requirement of adding a new column to BAM__Completed table after 6 years.

So our plan is to take backup of this table(which takes time to long time), drop the BAM tables and recreate the activity. But the application should be down until this activity is done and which is not acceptable here.

Is there any other procedure to simply add a new column to BAM tables with out taking BizTalk down?

Answer 1

I suggest you create a new activity to collect the data with this additional field to a new set of BAM tables. Then merge the old bam_completed table to the new bam_completed table.

Answer 2

you can archive the BAM data from bam primary import database to BAM Archive databse. They add a new column using new activity after that just import the data from archive database and delete the archive data. There is already sql job exists to archive the BAM database you just need to configure it –