locked
Biztalk BAM archiving issues RRS feed

  • Question

  • HI experts,

    using the below link i am trying to move  BAmPrimaryImport data older than 5 months to BAMArchive database.

    http://blogs.biztalk360.com/bam-production-environment-management/

    So i set time unit to my activity, which in turn will move all the data older  to BAMArchive db.

    bm set-activitywindow ? Activity:your_activity_name ?TimeLength:5 ?TimeUnit:Month

    Till here every thing went smooth.I know i have to call the SSIS package: BAM_DM_<<activity names>> 

    from the job, with curiosity i executed the ssis package directly.I believe no difference between direct executing and calling from job.

    Now i see all the data rows(even the new records) are moved to newly created additional tables in BAMPrimaryImport database post fixed by a GUID .

    Actually the  ssis package is not suppose to delete any data less than or equal to 5 months.

    Now my question here is :

    1.)Why can't i move the old data directly to BAMArchive DB?

    2.)Why is that all my new data is moving?

    Can any one help me in this?

    Tuesday, March 8, 2016 5:29 PM

Answers

  • For clarity, The DTS package, BAM_DM_<ActivityName>, performs both the partitioning and archiving/purging.

    The BAM_DM_<ActivityName> package performs two operations:

    1) building partition tables and

    2) archiving those partitions to the BAMArchive database when a partition’s creation date is past the online window.

    If you closely look into dbo.BAM_Metadata_Activites table, corresponding to each activity you see OnlineWindowTimeUnit and OnlineWindowTimeLength which by default is 6 Month.

    So, when you execute the job, partitioned tables are created as part of the first step of the SSIS job. what you see is all data from the active table is however moved to the new partitioned table but not moved to the Archive database.

    Its on the second step that SSIS job look at “Creation Time” of the partitioned table in the “BAM MetaData Partitions” table and only when the creation time is greater than the online window that partitioned view is actually moved to the archive database.

    So if you wish to move the partitioned views to archive database all you then have to do is of course to change that “Creation Time” value in the BAM Metadata Partitions table so it is outside of the online window value for the activity. 


    Rachit Sikroria (Microsoft Azure MVP)

    Tuesday, March 8, 2016 6:53 PM
    Moderator
  • Hi Sandy,

    Thank you for posting on MSDN forum.

    The archive process will not start until the CreationTime of the row in the bam_Metadata_Partitions is older than the OnlineWindowTimeLength.

    Reference: http://www.biztalkbill.com/Home/tabid/40/EntryId/103/BizTalk-BAM-Archiving.aspx

    Please have a look into some another articles that might be clear your doubt,

    http://richardhallgren.com/bam-tracking-data-not-moved-to-bam-archive-database/

    MSDN Article: Archiving and Purging the BizTalk Tracking Database

    Reference: BAM Archive

    I hope above articles will give you answer for both questions.


    Thanks,

    If my reply is helpful please mark as Answer or vote as Helpful.

    My blog | Twitter | LinkedIn

    This post is my own opinion and does not necessarily reflect the opinion or view of Microsoft, its employees, or other MVPs.


    Tuesday, March 8, 2016 5:36 PM
    Moderator
  • So when the job runs, all the records are deleted from BAMPrimaryImport].[dbo].[bam_ACTIVITY_Completed].

    And my c# service will not get that deleted data.

    Yes, that is as expected!

    Whenever you run the SSIS job, the Step 1 will move all the data from your BAMPrimaryImport].[dbo].[bam_ACTIVITY_Completed] to the partition table. and depending upon the OnlineWindowTimeUnit and OnlineWindowTimeLength in dbo.BAM_Metadata_Activites table, the Step 2 will move the partition tables to archive database.

    Its your [dbo].[bam_Activity_AllInstances] view which will display all the consolidated data from your active and partitioned tables until they are moved to archive database.

    So, your c# application should be looking for new record in [dbo].[bam_Activity_AllInstances] view.


    Rachit Sikroria (Microsoft Azure MVP)

    • Marked as answer by sandydv85 Wednesday, March 9, 2016 9:08 AM
    Wednesday, March 9, 2016 7:38 AM
    Moderator

All replies

  • Hi Sandy,

    Thank you for posting on MSDN forum.

    The archive process will not start until the CreationTime of the row in the bam_Metadata_Partitions is older than the OnlineWindowTimeLength.

    Reference: http://www.biztalkbill.com/Home/tabid/40/EntryId/103/BizTalk-BAM-Archiving.aspx

    Please have a look into some another articles that might be clear your doubt,

    http://richardhallgren.com/bam-tracking-data-not-moved-to-bam-archive-database/

    MSDN Article: Archiving and Purging the BizTalk Tracking Database

    Reference: BAM Archive

    I hope above articles will give you answer for both questions.


    Thanks,

    If my reply is helpful please mark as Answer or vote as Helpful.

    My blog | Twitter | LinkedIn

    This post is my own opinion and does not necessarily reflect the opinion or view of Microsoft, its employees, or other MVPs.


    Tuesday, March 8, 2016 5:36 PM
    Moderator
  • For clarity, The DTS package, BAM_DM_<ActivityName>, performs both the partitioning and archiving/purging.

    The BAM_DM_<ActivityName> package performs two operations:

    1) building partition tables and

    2) archiving those partitions to the BAMArchive database when a partition’s creation date is past the online window.

    If you closely look into dbo.BAM_Metadata_Activites table, corresponding to each activity you see OnlineWindowTimeUnit and OnlineWindowTimeLength which by default is 6 Month.

    So, when you execute the job, partitioned tables are created as part of the first step of the SSIS job. what you see is all data from the active table is however moved to the new partitioned table but not moved to the Archive database.

    Its on the second step that SSIS job look at “Creation Time” of the partitioned table in the “BAM MetaData Partitions” table and only when the creation time is greater than the online window that partitioned view is actually moved to the archive database.

    So if you wish to move the partitioned views to archive database all you then have to do is of course to change that “Creation Time” value in the BAM Metadata Partitions table so it is outside of the online window value for the activity. 


    Rachit Sikroria (Microsoft Azure MVP)

    Tuesday, March 8, 2016 6:53 PM
    Moderator
  • HI, thank you Rachit and kamlesh for your responses.

    Here what i understood is when ever you run the ssis job all the current data is also moved to partition tables,

    and from there based on creationtime in bam metadata activities, data is archived from partion tables to BAm archive database.

    but my problem is, when ever i run ssis job, today's data is also moved to partition tables.

    I really don't need that, it should only move data older than 'ONLINEWINDOWTIMEUNIT' to the partition tables.

    why i am very particular is we have a c# window service which polls bamprimaryImport db for every 2 mins to find if any new record arrived.

    So when the job runs, all the records are deleted from BAMPrimaryImport].[dbo].[bam_ACTIVITY_Completed].

    And my c# service will not get that deleted data.

    So i require data older than 'ONLINEWINDOWTIMEUNIT' only should move to partition tables.

    Please guide me if i am wrong and let me know if any solution.

    Thanks in Advance.

    Wednesday, March 9, 2016 6:29 AM
  • So when the job runs, all the records are deleted from BAMPrimaryImport].[dbo].[bam_ACTIVITY_Completed].

    And my c# service will not get that deleted data.

    Yes, that is as expected!

    Whenever you run the SSIS job, the Step 1 will move all the data from your BAMPrimaryImport].[dbo].[bam_ACTIVITY_Completed] to the partition table. and depending upon the OnlineWindowTimeUnit and OnlineWindowTimeLength in dbo.BAM_Metadata_Activites table, the Step 2 will move the partition tables to archive database.

    Its your [dbo].[bam_Activity_AllInstances] view which will display all the consolidated data from your active and partitioned tables until they are moved to archive database.

    So, your c# application should be looking for new record in [dbo].[bam_Activity_AllInstances] view.


    Rachit Sikroria (Microsoft Azure MVP)

    • Marked as answer by sandydv85 Wednesday, March 9, 2016 9:08 AM
    Wednesday, March 9, 2016 7:38 AM
    Moderator
  • Thanks a lot Rachit, your above point answered my question.


    Wednesday, March 9, 2016 9:09 AM