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.
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.
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 override4. 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.
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
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_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
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.
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.
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.
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
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
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).
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.
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.
