BizTalk Environment Maintenance From A DBA Perspective

|  Posted: October 28, 2011  |  Categories: BizTalk Server

BizTalk Databases

This is one of the common challenges we see in many enterprises. SQL servers will always be owned by a dedicated database team. The bigger the organisation, the partition between the BizTalk support team and database team will increase. One of the biggest challenges with this setup is, from DBA’s perspective they wanted to follow all the best practices, they normally follow as a standard for all the SQL Servers/Databases in the organisation. These may include things like standard backup procedures, indexing procedures, standard recovery model, their own SQL bookkeeping jobs etc.

This situation is a real red alert, BizTalk databases are special databases. A typical BizTalk installation at the minimum will have 4 databases BizTalkMsgBoxDb, BizTalkMgmtDb, BizTalkTrackingDb, SSODB. They are designed to work as a single group. There are certain dependencies between them. Example: Some of the SQL jobs are designed to move data from BizTalk message box to tracking DB, the Tracking subservice will also do similar thing to move data from message box. There are lot of chances for distributed transactions spanning across these databases.  So, the general rule of thumb is

Do not treat your BizTalk databases as your standard database. It’s just a black box and you are not allowed to make any changes (there are few exceptions)

BizTalk server by default comes with all the weapons required to maintain/protect its databases. The product is designed in a way to self maintain. The main challenge here is SQL DBAs are not fully aware of the BizTalk capabilities.  In this article we will see some of the routine jobs SQL DBA’s must be aware of when maintaining a BizTalk environment related databases.

1.    Know how to use Message Box Viewer/Terminator
2.    One time settings
3.    Make sure SQL Agent is running
4.    Procedures to rebuild indexes for BizTalk databases
5.    Monitor the growth of certain tables
6.    Monitor the size of databases
7.    Monitor transaction log sizes
8.    BizTalk 2010 Monitor BizTalk Server Job

You can download the entire article as a PDF document.
BizTalk Environment Maintenance From A DBA Perspective.

Know how to use Message Box Viewer/Terminator

Message box viewer and Terminator tool are the two tools the DBA’s maintaining the BizTalk environment should be completely aware of. MBV is designed to identify all potential problems in the BizTalk environment that could be critical or need attention and present them in a nice report. Most of the BizTalk environment issues will come down to poorly maintained database(s); MBV is extremely good at picking them up. Full Q&A can be found here
http://blogs.technet.com/b/jpierauc/archive/2008/07/22/msgboxviewer-mbv-q-a.aspx

In earlier days people executed MBV, identified the issues and manually corrected the problems. But with latest version of MBV (10.3 and above), MBV produce a cleanup script, which can then be used with another tool called Terminator to fix the database issues either automatically or manually.

http://blogs.msdn.com/b/biztalkcpr/archive/2011/02/10/using-biztalk-terminator-to-resolve-issues-identified-by-biztalk-msgboxviewer.aspx

One time settings

Here are some of the steps you need to perform once the environment is setup.
1. Install the Latest BizTalk Server Service Pack and Cumulative Update
2. Disable the Auto Update Statistics and Auto Create Statistics options

You must disable the Auto Create Statistics and Auto Update Statistics options on the BizTalkMsgBoxDb database. To determine whether these settings are disabled, execute the following stored procedures in SQL Server:
exec sp_dboption ‘BizTalkMsgBoxDB’, ‘auto create statistics’
exec sp_dboption ‘BizTalkMsgBoxDB’, ‘auto update statistics’

You should set the CurrentSetting setting to off. If this setting is set to on, turn it off by executing the following stored procedures in SQL Server:

exec sp_dboption ‘BizTalkMsgBoxDB’, ‘auto create statistics’, ‘off’
exec sp_dboption ‘BizTalkMsgBoxDB’, ‘auto update statistics’, ‘off’

3. Set the Max Degree of Parallelism property correctly

On the computer that is running SQL Server and hosting the BizTalkMsgBoxDb database, set the Max Degree of Parallelism run_value and config_value properties to a value of 1. To determine the Max Degree of Parallelism setting, execute the following stored procedure against the Master database in SQL Server:
exec sp_configure ‘max degree of parallelism’

If the run_value and config_value properties are not set to a value of 1, execute the following stored procedure in SQL Server to set them to 1:

exec sp_configure ‘max degree of parallelism’, ‘1’
reconfigure with override

4. Make sure BizTalkMsgBoxDb and BizTalkTrackingDb data and log files are in separate drives, also if possible make sure you don’t put BizTalkMsgBox and BizTalkTrackingDb data files or log files in the same drive.

Rebuilding indexes

The only supported method to rebuild an index in the BizTalkMsgBoxDb database is to run the bts_RebuildIndexes stored procedure. On BizTalk Server 2006 and later versions, you can run the dtasp_RebuildIndexes stored procedure to rebuild indexes in the BizTalkDTADb database.

If lots of data is expected to build up in the BizTalkMsgBox database, you can periodically rebuild indexes during scheduled downtime. The same guidelines apply to the tracking database.

Updated: 28/08/2014. The following lines were bit old, right way to do reindex is using the ALTER INDEX command

Ex: ALTER INDEX ALL ON [dbo].[TrackingData_0_0] REBUILD; as seen on the stored procedure bts_RebuildIndexes

You can use the DBCC DBREINDEX SQL command to rebuild an index in the other BizTalk Server databases. For an example of how to use the DBCC DBREINDEX SQL command, right-click the bts_RebuildIndexes stored procedure, and then click Properties.
http://support.microsoft.com/default.aspx?scid=kb;EN-US;917845

You can download the entire article as a PDF document.
BizTalk Environment Maintenance From A DBA Perspective.

Monitor the growth of certain tables

Message Box Database (BizTalkMsgBoxDb)

Spool:

If the Spool tables have many records, many messages are currently active, dehydrated, or suspended. Depending on the size, the number of parts, and the fragmentation settings in these tables, a single message may spawn all these tables. Each message has exactly one row in the Spool table and at least one row in the Parts table. Create a benchmark for your BAU activities and make sure the spool table is not growing indefinitely. During you quite period when there are no BAU activities if you notice a large number of rows in this table, make sure unwanted suspended instance are cleared periodically. You can also use Message Box Viewer regularly to check if there are any unusual numbers of rows in the table and can use Terminator tool to safely remove them.

TrackingData_0_x and TrackingData_1_x

TrackingData_0_x: These four tables store the Business Activity Monitoring (BAM) tracked events in the BizTalkMsgBoxDb database for TDDS to move the events to the BAMPrimaryImport database.
TrackingData_1_x: These four tables store the tracked events in the BizTalkMsgBoxDb database for TDDS to move the events to the BizTalkDTADb database.

You make sure there is at least one host configured with tracking enabled. This will run TDDS subservice responsible for moving the data from message box db to BizTalkDTADb and BAMPrimaryImport

BizTalk Tracking Database (BizTalkDTADb)

dta_DebugTrace

This table typically becomes large in environments where orchestration shapes start/end is enabled. If there is no business value and if you got lots of orchestration in your solution, it’s better to clear the check box for? Shape start and end? option in the orchestration(s) properties.

dta_MessageInOutEvents

This table typically becomes large in environments where ?Message send and receive? is enabled for orchestrations and/or pipelines. If these tracking events are not required, clear the check box for this option in the orchestration and/or pipeline properties.

If the dta_DebugTrace table and/or the dta_messageInOutEvents table in the BizTalkDTADb database are too large, you can truncate the tables manually after you stop the tracking host. The BizTalk Terminator tool also provides this functionality.

dta_ServiceInstanceExceptions

This table typically becomes large in an environment that regularly has suspended instances. dta_serviceinstances table grows every time the user terminates an instance. DTA Purge and Archive SQL Server jobs should take care of clearing this table, so make sure the jobs are running correctly.

BAMPrimaryImport

TDDS_FailedTrackingData

The TDDS_FailedTrackingData table gets populated whenever there is a tracking failure or even in cases where you haven’t deployed your BAM activities, but your solution is trying to insert BAM data. In earlier version of BizTalk (2006 and 2006 R2) the DTA Purge and Archive SQL Server jobs didn’t clear this table periodically which resulted in unlimited growth of this table. Make sure you got the hot fix explained here http://support.microsoft.com/kb/977289

Monitor the size of databases

The size of the databases will vary from organisation to organisation depending on the volume of data processing. In any organisation having a bloated/large database will adversely effects the performance of your BizTalk environment. As a general rule of thumb, you can use the following numbers for guidance

BizTalkMsgboxDb

Not more than 5GB. However big your deployment is, your message box database should NOT go beyond this limit. In theory Message Box database only should hold transit data (aka inflight messages).  On a healthy environment all the processed message will either be moved to BizTalk tracking database or BAM database. Having lot of suspended instances will also bloat the size of the message box database, so periodically clear them out or better design your solution in a way you are not going to have too many suspended instances (unless otherwise there is a genuine unhanded exception scenario).

BizTalkDTADb, BAMPrimaryImportDB

Around 10 GB max.  This is again a finger in the air estimate. It’s better to keep the tracking data and BAM data within this limit. You can control this by setting the appropriate values for your DTA purging/archiving job and making sure you are moving your BAM data from BAMPrimaryImport table to BAMArchieve database. Also make sure your BAMArchive database is not bloated over a period of time. Do a hard purge after a set period like 3 or 6 months based on your business requirement.

The other databases like BizTalkMgmtDb, SSO, RulesEngine etc all store configuration data and they should be any bigger than 2GB.

Monitor transaction log sizes

The size of the BizTalk databases transaction log is controlled by the BizTalk backup job. By default the recovery model for the BizTalk databases is set to Full. If the transaction log is not backed up or truncated on a regular basis, the log files or files can fill up. So, please make sure BizTalk backup job is configured and running successfully. This job automatically backup the BizTalk databases including transaction log, and thus ensures the transaction log does not grow to an unmanageable size. The backup job should also be performed multiple times during the day as it’s the job that lets you recover your messages. The bigger the log file the more messages that could be lost.

It is not recommended to change the recovery model settings of the BizTalk databases. Changing this setting will put the BizTalk environment into a state where it may not be fully recoverable in the event of failure.

Not but the not the least the Backup BizTalk Server job is the only supported method to back up the BizTalk Server databases.

BizTalk 2010 Monitor BizTalk Server Job

If you are using BizTalk Server 2010, you can run the Monitor BizTalk Server SQL Agent job to identify any known issues in Management, Message Box, or DTA databases. The job is created when you configure a BizTalk group in BizTalk Server Administration console or upgrade BizTalk from the previous version.

The Monitor BizTalk Server job scans for the following issues in Management, Message Box, and DTA databases:

  • Messages without any references
  • Messages without reference counts
  • Messages with reference count less than 0
  • Message references without spool rows
  • Message references without instances
  • Instance state without instances
  • Instance subscriptions without corresponding instances
  • Orphaned DTA service instances
  • Orphaned DTA service instance exceptions
  • TDDS is not running on any host instance when global tracking option is enabled.

The Monitor BizTalk Server job is configured and automated to run once in a week. Since the job is computationally intensive, it’s recommended you to schedule it during downtime/low traffic.

The job fails if it encounters any issues; error string contains the number of issues found. Else, it runs successfully. You can see the details in the job history. If you run the job with Administrator privileges, error string will be logged to Event Viewer also (along with the job history).

Ad-hoc Full backup

It may be required once in a while to force a full database backup. The BizTalkMgmtDb.dbo.sp_ForceFullBackup stored procedure can be used to force a full backup of the data and log files. Execute this stored procedure, and then execute the Backup BizTalk Server SQL Agent job.

Cleanup all the data in test environment

DO NOT do this in PRODUCTION
When you are managing test environment (especially performance testing) it may be required to clean up BizTalkMsgBoxDb and BizTalkDTADb. When I say clean up, completely wipe out all the data, hence the bold warning.

BizTalkMsgboxDb

1.    Copy the Msgbox_cleanup_logic.sql script from Drive:Program FilesMicrosoft BizTalk 200xschema to the SQL Server.
2.    Execute this SQL script against the BizTalkMsgBoxDb database to update the bts_CleanupMsgbox stored procedure.
3.    Stop all BizTalk hosts, services, and custom isolated adapters. If you use HTTP or the SOAP adapter, restart the IIS services.
4.    Execute the bts_CleanupMsgbox stored procedure on all the BizTalkMsgBoxDb databases.
5.    Restart all host instances and BizTalk Server services.

BizTalkDTADb

1.    Back up all BizTalk databases.
2.    Execute the dtasp_CleanHMData stored procedure. Only use this option if the BizTalkDTADb database contains many incomplete instances that must be removed.

To do this, follow these steps:
a.    Stop all BizTalk hosts, services, and custom isolated adapters. If you use HTTP or the SOAP adapter, restart the IIS services.
b.    Execute the dtasp_CleanHMData stored procedure on the BizTalkDTADb database.
c.    Restart all hosts and BizTalk Server services.

You can download the entire article as a PDF document.
BizTalk Environment Maintenance From A DBA Perspective.
Author: Saravana Kumar

Saravana Kumar is the Founder and CTO of BizTalk360, an enterprise software that acts as an all-in-one solution for better administration, operation, support and monitoring of Microsoft BizTalk Server environments.

  • Busy Mom

    great article!

  • Guru

    Nice article

  • Ganesh Shanmugam

    Hi Saravana Kumar,

    We have a production server which has BiztalkDTADB of around 200 GB size. We are not using anything related to tracking DB. DTA purge and archive job runs successfully, but the db size doesnt reduce. We have tried shrinking the DB as well. But the size remains same. Can we go for dtasp_CleanHMData ?.

    • Zoran

      Are the 200GB actual data or is that only the allocated disk space. If it is actual data I would run a report to identify the tables with the highest disk size allocation.

  • Dipesh A.

    Hi Sarvana,

    Just on the TDDS_FailedTrackingData table mentioned above. This table exists in both BAMPrimaryImport and BizTalkDTADb. The DTA Purge and Archive jobs only clears this table in the BizTalkDTADb. Maybe that needs to be corrected in the post?

    Or does this job also clear the same table in both databases?

    Regards,
    Dipesh A.

  • Rakesh Agarwal

    Nice Article!

  • Really it was a fantastic blog.

  • We brought BizTalk into our datacenter in 2006. I started out as sole support for several years. Shortly after moving to BTS2010, our DBA’s for the first time became involved, and they now have the “log shipping” backup and recovery plan down pat, and in fact have exercised it in real and disaster recovery test scenarios. So last month I found while routinely running Message Box Viewer, that I had been removed from ‘sa’ from all the databases in all of the environments. I’m still a BizTalk Administrator, but that is not enough permission, in fact the viewer failed to find any databases whatsoever. So I went to the DBA’s and they told me their plan is to put me back in as ‘sa’ when I need it. I think it started when I asked them to replace my account with a service account for DB owner on the databases (the MS health report suggested this change). So how I’m feeling is nervous. I’m nervous they’ll miss something, or have missed something, and I’ll run into problems, when it comes time to run the next cumulative update. We use HIS, and BAM. Any amendment you would add to this article, for DBA perspective related to handling permissions? BTW I have always created the SSO db and clustered it, and configured the BTS SQL jobs, which I can’t now even view, on a regular basis. Thank you

  • Patrick Ge

    Thanks for sharing the info.

    I know here many of you are Biztalk admin. I am a DBA and am supporting Biztalk database environment. I don’t agree the underlying lines that “DBAs don’t really know how to maintain Biztalk database envrionment because Biztalk databases are special.” For sure DBAs in every orgnisation have their standards. DBAs know in depth sql as you know in depth in Biztalk. So please trust DBAs are in better position of supporting the database environment. Its not uncommon application admin or developer come to DBA saying how special their application is. The things personally I would do when being approached are to ask what the specialities are and why. Obviously not all the requests will be accepted. But if there are valid reasons I am happy to take them. For example, the MDOP setting on the sql instance running msgbox. I read the info from Microsoft document. So I accepted it. and even better we redesigned the environment where we want to put biztalk databases. Another example is what you quoted in this post “You must disable the Auto Create Statistics and Auto Update Statistics options on the BizTalkMsgBoxDb database. To determine whether these settings are disabled, execute the following stored procedures in SQL Server:” You didn’t mention where it comes from? Again, if there are valid reason, I can’t see any reason why a DBA would reject it though it is quite unusual to set the stttings off.

  • Patrick Ge

    Following up on my unfinished comment. Regarding maintenance, I’d like to point out “DBCC DBREINDEX” should not be used. Instead you should use Alter Index. And I aso don’t understand why index defragmentation would need a downtime to carry out. The process in our environment is scheduled to run at night while databases are online and only take very short period time to complete. Considering as you said how big the databases should be, many indexes don’t even need to be index fragmented because they are a lot less than 1000 pages. And monitor growth of table, database and log are common sense to DBAs. (In fact, database and log should be properly pre sized. So very rarely you would see auto growth of them). Regarding take an ad hoc full backup, all you need to do is run below script “update dbo.adm_BackupSettings set forcefull=1” rather than playing with the stored proc in your document.

    All in all, I hope you appreciate why DBAs are in better position managing your biztalk database servers. And I believe good communication to enable Biztalk admin and DBA to understand each other is important. Finally I do appreciate a few information you shared in this post. I will take them back and see what we can/need to do.

    • saravanamv

      Hi Patrick,

      Thanks for taking your time to add your valuable comments. I’m not blindly saying all DBA’s are wrong, I agree with you most of the DBA’s will have better understanding of SQL than the BizTalk Admins. As long as the DBA understand the limitations and restrictions of BizTalk Server.

      You are right about DBCC DBREINDEX command, this blog post was originally written about 4 years now. I corrected those lines. Also noticed the BizTalk stored procedures are also using ALTER INDEX statements now (I’m using BizTalk 2013 on my test machine)

  • Great article!

  • Akshay Shaha

    Thanks Sarvana for this wonderful and much helpful article on BizTalk Maintainace from DB perspective 🙂

  • Blinken

    If your SQL version is Enterprise you can rebuild with the Online = On option.
    See if your db has fragmented indexes: SELECT * FROM sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,NULL) ips JOIN sys.indexes idx ON ips.object_id = idx.object_id WHERE ips.avg_fragmentation_in_percent > 30
    Then build your ALTER script with ONLINE=ON:
    ALTER INDEX [dta_MessageInOutEvents_index_MessageId] ON [dta_MessageInOutEvents] REBUILD WITH(ONLINE=ON);

    • Blinken

      Just have to watch out for indexes that include columns of text, ntext, or image datatypes

  • Trada

    I would like to relocate the Message box database to its own disk. I’ve been told I may
    a. shutdown bt processes, then perform a ‘move’ of the MDF/LDF files to the disk of choice, alter the FILES structure in SSMS and restart SQL for the relocation: comments?

  • Srikanth A

    Can Bizztalk databases use merge replication while we setup a DR in Windows Azure VM (SQL) ?
    Merge replication need to ensure the on-premises biztalk db’s sync with SQL on Windows Azure databases

  • Rian

    This article has been useful in helping to highlight the unique qualities of BizTalk support and maintenance that are not self-evident. Thank you for this post!

One Platform Operations, Monitoring and Analytics Software
BizTalk360

microsoft biztalk

Learn more

Over 500 customers across 30+ countries depend on BizTalk360

ServiceBus360

Azure Composite Application

Learn more

Start manage and monitor your Azure Services in minutes

One Platform - Operations, Monitoring and Analytics Software
BizTalk360

microsoft biztalk

Learn more

Over 500 customers across 30+ countries depend on BizTalk360

One Platform - Operations, Monitoring and Analytics Software
ServiceBus360

Azure Composite Application

Learn more

Start manage and monitor your Azure Services in minutes

Back to Top