none
Pulling XMLs from BizTalk Tracking database (BizTalkDTADb) RRS feed

  • Question

  • Hi, We have requirement where the client needs to get the XML's Received and Sent after a while,

    Which is the better of the two methods?

    1) Pulling the XML's from the BizTalk Tracking Database.
    2) Using a custom table to store and retrieve later.

    Thursday, January 29, 2015 8:52 AM

Answers

  • Hi Praveen,

    DTADb is normally not used for the buisness requirements and is only used for tracking or debugging error scenarios in NON-PROD environments. In PROD its recommeded to turn off the "Global Tracking" to avoid any tracking in PROD.

    1) You can use your custom code to access the message body and store it your custom DB. You can create a custom c# helper and write a method to save the message in db. Then you can call this helper in your BizTalk artifact like pipeline or orchestration.

    2) Use http://biztalkarchiving.codeplex.com/ component, it's a pipeline component that can be used for archiving incoming/outgoing message from any adapters. It provides an option to save the message to either file (local, shared, network) or as in your case in SQL Server.

    3) You can also consider using BAM and using its API’s you can access the message body and store in your custom DB.

    4) You can consider using Enterprise Library’s Logging or Log4Net.

    Refer: tracked message event

    Rachit


    Please mark as answer or vote as helpful if my reply does


    Thursday, January 29, 2015 9:44 AM
    Moderator
  • If you just want to compare these two options, then instead of we choose the best approach, we can give you the pros and cons of the give approaches and you choose the best which suits you most.

    Pulling the XML's from the BizTalk Tracking Database

    Pros:

    Tracking the data is best suited,

    • If you requirement is to retrieve/store the XMLs which are processed in the last short period of time (say 30 days).
    • If you want to retrieve the processed messages (XML) by Receive port/location or send port, then you can use Tracking data.

    Cons:

    • If you want to retrieve/store the XMLs which are processed for longer period of time (say you want to store XML files processed 10 months ago), then tracking d b is not the option. If don’t have a maintenance plan (pruge/archive) Tracking database continues to grow in size. Unchecked growth (if you don’t archive/purge) decreases system performance and may generate errors in the Tracking Data Decode Service (TDDS).
    • You can’t use tracking db where you want to retrieve XML by any of its value. For example, if you want to retrieve a XML file based on OrderNumber which is part of the message, tracking db is not the correct solution.

    Using a custom table to store and retrieve later

    Pros:

    • If you want to retrieve stored XML by any of its value. For example, if you want to retrieve a XML file based on OrderNumber. This will work.
    • You still need to have some maintenance plan for custom db still, either to archive/purge. But any growth in size will not have direct impact on BizTalk’s performance; until custom db is kept in a different SQL server.

    Cons:

    • Development time/process is required.
    • Additional backup and store plan should be considered for custom databases.

     

    You can also consider the following option:

    • Consider using BAM.
    • Consider archiving the files by using the custom archive pipeline components at receive and send level. So archive the files to a shared drive rather than database.

    Regards,

    M.R.Ashwin Prabhu


    If this answers your question please mark it accordingly. If this post is helpful, please vote as helpful by clicking the upward arrow mark next to my reply.

    • Marked as answer by Angie Xu Thursday, February 5, 2015 1:48 PM
    Thursday, January 29, 2015 12:42 PM

All replies

  • Hi, We have requirement where the client needs to get the XML's Received and Sent after a while,

    Which is the better of the two methods?

    1) Pulling the XML's from the BizTalk Tracking Database.
    2) Using a custom table to store and retrieve later.

    Don't query directly you BizTalk Tracking Db. This will impact your performance...
    You also don't have any key on that database.

    The best you can do is  writing a job or something like that and put the information i a custom db. You can add some key's on that table and it will not impact your production tracking db!

    Thursday, January 29, 2015 8:58 AM
  • Hi,

    It depends on what will be the selection criteria for getting these XML's. If it's just for admin or operations purpose tracking holds good for most cases but if you want to get XML based on the data then a custom store is also an option.


    Bali ... Mark As Answer or Vote As Helpful if this helps.

    Thursday, January 29, 2015 8:59 AM
  • Custom Table to me seems to be a better option as you can control

    1. How you want the XML stored (adding attributes to the table while storing will help in retrieval) - Date Time received, Interchange ID (to help correlate between request/response, message type, etc.
    2. How long such data should be stored - driven through Business/Functional requirements for Data Archiving around business data retention policies.
    3. Not burden DTA database in case message retrieval is outside the DTA time window (you'd need to tweak the DTA Archiving and Purging limits) and or restore from archived DTA instances.

    Regards.

    Thursday, January 29, 2015 9:00 AM
  • Hi Praveen,

    DTADb is normally not used for the buisness requirements and is only used for tracking or debugging error scenarios in NON-PROD environments. In PROD its recommeded to turn off the "Global Tracking" to avoid any tracking in PROD.

    1) You can use your custom code to access the message body and store it your custom DB. You can create a custom c# helper and write a method to save the message in db. Then you can call this helper in your BizTalk artifact like pipeline or orchestration.

    2) Use http://biztalkarchiving.codeplex.com/ component, it's a pipeline component that can be used for archiving incoming/outgoing message from any adapters. It provides an option to save the message to either file (local, shared, network) or as in your case in SQL Server.

    3) You can also consider using BAM and using its API’s you can access the message body and store in your custom DB.

    4) You can consider using Enterprise Library’s Logging or Log4Net.

    Refer: tracked message event

    Rachit


    Please mark as answer or vote as helpful if my reply does


    Thursday, January 29, 2015 9:44 AM
    Moderator
  • If you just want to compare these two options, then instead of we choose the best approach, we can give you the pros and cons of the give approaches and you choose the best which suits you most.

    Pulling the XML's from the BizTalk Tracking Database

    Pros:

    Tracking the data is best suited,

    • If you requirement is to retrieve/store the XMLs which are processed in the last short period of time (say 30 days).
    • If you want to retrieve the processed messages (XML) by Receive port/location or send port, then you can use Tracking data.

    Cons:

    • If you want to retrieve/store the XMLs which are processed for longer period of time (say you want to store XML files processed 10 months ago), then tracking d b is not the option. If don’t have a maintenance plan (pruge/archive) Tracking database continues to grow in size. Unchecked growth (if you don’t archive/purge) decreases system performance and may generate errors in the Tracking Data Decode Service (TDDS).
    • You can’t use tracking db where you want to retrieve XML by any of its value. For example, if you want to retrieve a XML file based on OrderNumber which is part of the message, tracking db is not the correct solution.

    Using a custom table to store and retrieve later

    Pros:

    • If you want to retrieve stored XML by any of its value. For example, if you want to retrieve a XML file based on OrderNumber. This will work.
    • You still need to have some maintenance plan for custom db still, either to archive/purge. But any growth in size will not have direct impact on BizTalk’s performance; until custom db is kept in a different SQL server.

    Cons:

    • Development time/process is required.
    • Additional backup and store plan should be considered for custom databases.

     

    You can also consider the following option:

    • Consider using BAM.
    • Consider archiving the files by using the custom archive pipeline components at receive and send level. So archive the files to a shared drive rather than database.

    Regards,

    M.R.Ashwin Prabhu


    If this answers your question please mark it accordingly. If this post is helpful, please vote as helpful by clicking the upward arrow mark next to my reply.

    • Marked as answer by Angie Xu Thursday, February 5, 2015 1:48 PM
    Thursday, January 29, 2015 12:42 PM
  • ..Received and Sent after a while,

    Which is the better of the two methods?

    1) Pulling the XML's from the BizTalk Tracking Database.
    2) Using a custom table to store and retrieve later.

    Hold on.  What exactly does "after a while" mean?  This will be the primary driver for the solution.

    If it means like 30-90 days then yes, using the Tracking Database is a viable option. 

    If it means store for weeks to years, then a custom table is the better option.

    If you choose to use the Tracking Database, the 'correct' approach would be to retrieve the messages via WMI not direct SQL.

    Thursday, January 29, 2015 2:06 PM
    Moderator