BizTalk Databases: Survival Guide

BizTalk Databases: Survival Guide


Introduction

Microsoft BizTalk Server databases and the health of the databases are very important for a sustainable BizTalk Server messaging environment. The BizTalk database jobs play an important role in keeping the BizTalk databases healthy. Besides jobs there are many other factors you have to consider like disaster recovery, tracking, and availability (e.g. clustering). There are some best practices when it comes to maintenance (see table below).

Action

Description

Monitor the size of databases and tables

Performance degrades on High Size of BizTalk databases. BizTalk Server takes a longer time than normal to process even a simple message flow scenario.

Enable tracking on BizTalk Server Host

By default, tracking is enabled on the default Host. BizTalk requires the Allow Host Tracking option be checked on a single Host. When tracking is enabled, the Tracking Data Decode Service (TDDS) moves the tracking event data from the BizTalk Server MessageBox database to the BizTalk Server tracking database. If no BizTalk Server Hosts are configured with the option to Allow Host Tracking or if the Tracking Host is stopped, then TDDS will not run and the TrackingData_x_x tables in the BizTalk Server Messagebox database will grow unchecked. Therefore, a dedicated BizTalk Server Host should be configured with the option to Allow Host Tracking.

Use the correct BizTalk SQL Server Agent jobs

Execution of the BizTalk Server SQL Agent jobs are crucial for managing the BizTalk Server databases and for maintaining optimal performance.

Monitor and terminate suspended instances

Service instances can be suspended (resumable) or suspended (not resumable). These service instances may be Messaging, Orchestration, or Port. BizTalk Server 2009 accommodates termination and removal of these instances by using the Group Hub page in the BizTalk Server Administration Console or through the use of the Terminate.vbs script.

Monitor the performance counters of the PhysicalDisk performance object

BizTalk Server makes a large number of short, very quick transactions to SQL Server within one minute. If the SQL Server cannot sustain this activity, you may experience BizTalk Server performance issues. Monitor the Avg. Disk sec/Read, Avg. Disk sec/Transfer, and Avg. Disk sec/Write performance monitor counters in the PhysicalDisk performance object. The optimal value is less than 10 ms (milliseconds). A value of 20 ms or larger is considered poor performance.

Ensure all required BizTalk SQL Server Agent jobs are enabled and running

All the BizTalk SQL Server Agent jobs except the MessageBox_Message_Cleanup_BizTalkMsgBoxDb job should be enabled and running successfully. Do not disable any other job. The MessageBox_Message_Cleanup_BizTalkMsgBoxDb is called by the MessageBox_Message_ManageRefCountLog_BizTalkMsgBoxDb job.

Delete all unwanted data

If the databases have grown to become too large and if the data contained in the databases will not be required any longer, the preferred method is to delete the data

Resources

The following list of resources (most targeted for BizTalk 2010) can be beneficial for you in having a healthy and sustainable BizTalk environment.

General

Upgrading

Database Jobs

Disaster Recovery

Tracking

High Availability

Other languages

This article is also available in other languages

See Also

Another important place to find a huge amount of BizTalk related articles is the TechNet Wiki itself. The best entry point is BizTalk Server Resources on the TechNet Wiki

Read suggested related topics:

Leave a Comment
  • Please add 4 and 7 and type the answer here:
  • Post
Wiki - Revision Comment List(Revision Comment)
Sort by: Published Date | Most Recent | Most Useful
Comments
  • Steef-Jan Wiggers edited Revision 10. Comment: Added link

  • Naushad.Alam edited Revision 9. Comment: Added wiki article for deleting backup files, Its a major issues for some database admins

  • Steef-Jan Wiggers edited Revision 8. Comment: Formatting, added link

  • Steef-Jan Wiggers edited Revision 6. Comment: Added topic upgrading

  • Steef-Jan Wiggers edited Revision 5. Comment: Added resource link

  • Steef-Jan Wiggers edited Revision 4. Comment: More resource links

  • Steef-Jan Wiggers edited Revision 3. Comment: Adjusted link, formatting

  • Steef-Jan Wiggers edited Revision 2. Comment: Formatting

  • Steef-Jan Wiggers edited Revision 1. Comment: Added link for configuring BizTalk Server Database Jobs

  • Steef-Jan Wiggers edited Original. Comment: Added resource links.

Page 2 of 2 (20 items) 12
Wikis - Comment List
Sort by: Published Date | Most Recent | Most Useful
Posting comments is temporarily disabled until 10:00am PST on Saturday, December 14th. Thank you for your patience.
Comments
  • Added fix for deleting biztalk backup files.

  • Steef-Jan Wiggers edited Revision 10. Comment: Added link

  • Steef-Jan Wiggers edited Revision 11. Comment: Correction

  • Great one! Added an article about cleaning up the MarkLog table with Terminator

  • Tord G.Nordahl edited Revision 13. Comment: Added reference to norwegian copy of the article

  • There is a mistake in which jobs should not be enabled:-

    MessageBox_Message_ManageRefCountLog_BizTalkMsgBoxDb  - this should be enabled !!!

    It's actually MessageBox_Message_Cleanup_BizTalkMsgBoxDb which should not be enabled as it is called from MessageBox_Message_ManageRefCountLog_BizTalkMsgBoxDb .

    Thanks

    Balbir Singh

  • Steef-Jan Wiggers edited Revision 17. Comment: Changed text.

  • Steef-Jan Wiggers edited Revision 18. Comment: Edit text

  • Thanks Balbir I have changed it!

  • Great article! One item to point out - the first link in the disaster recovery section of the resource list appears to be missing its href.

  • Great post

  • Lex Hegt edited Revision 22. Comment: Minor edits

  • Lex Hegt edited Revision 23. Comment: Minor edits

  • Lex Hegt edited Revision 24. Comment: Removed a double reference

  • Lex Hegt edited Revision 25. Comment: Minor edits

Page 2 of 3 (32 items) 123