Accessing archived (& purged) tracking data in BizTalk 2009/2010

It is known that the BizTalkDTADb database can grow over a period of time if not purged regularly. For this there is a sql job in BizTalk which can be configured to archive and purge tracked data at regular intervals. The job is called DTA Purge and Archive. Once this job is configured and enabled, it archives and purges tracking data as per the schedule in the job.

Everytime the job runs, it archives this database which can be restored with a different name in SSMS.

Database restored in SQL Server Management Studio with a diff name:

Once the the archived copy is restored, it can be accessed from BTS Admin Console.

You now get to select the tracking database as shown below:

On connecting to the archived database, the database now appears in BTS Admin Console which can be queried to display tracked events and messages.

Restoring BizTalk Databases (Disaster Recovery)

I found an interesting article on msdn which explains the backup and restore strategies for BizTalk databases. Setting this up is fairly straight forward but requires a fair knownledge of BizTalk Server and SQL Server 2008.

BizTalk Server makes available a SQL Server job (Backup BizTalk Server) that you can use to back up all of the databases in your BizTalk Server source system, except for some databases used by Business Activity Monitoring (BAM). The source system is the server or group of servers that contain live data. Because BAM databases have different backup and restore requirements, these databases are backed up and restored using other methods.

The following BizTalk databases are backed up by the sql job:

  • BAMPrimaryImport
  • BAMAlertsApplication
  • BAMAlertsNSMain
  • BizTalkDTADb
  • BizTalkMgmtDb
  • BizTalkMsgBoxDb
  • BizTalkRuleEngineDb
  • SSODB

Backing up the BizTalk Server databases and restoring them involves the following steps:

1. Configuring the Backup BizTalk Server job

This is the first step required to be performed before you can back up the BizTalk Server databases. You must first configure the Backup BizTalk Server job on the source system, which directs backups to be automatically written to a folder where they can then be used to restore the databases on the destination system. The destination system is the server or group of servers that will be used to restore the database backups produced by the source system. For more information about this step, see link.

2. Configuring the destination system for log shipping

You must also configure the destination system for log shipping, which provides standby server capabilities and reduces downtime in the event of a system failure. For more information about this step, see link.

The result of this step is three new SQL Server agent jobs as described under:

  • BTS Log Shipping Get Backup History
    The BizTalk Server Log Shipping Get Backup History job moves backup history records from the source to the destination. It is scheduled by default to run every minute. This job runs as frequently as possible in order to move history records from the source to the destination. In the event of a system failure to the source system, the server that you identified as the destination system will continue to process the history records that have already been imported.
  • BTS Server Log Shipping Restore Databases
    The BizTalk Server Log Shipping Restore Databases job restores backup files for the given databases for the source to the destination server. It is scheduled by default to run every minute. This job runs continuously without completing as long as there are backup files to restore. As an extra precaution, you can run this job an additional time to ensure that it is complete.
  • BTS Log Shipping Restore To Mark
    The BizTalk Server Log Shipping Restore To Mark job restores all of the databases to a mark in the last log backup. This ensures that all of the databases are in a transactionally consistent state. In addition, this job re-creates all of the SQL Server Agent jobs on the destination system that had been on the source system.

3. Restoring the databases

When a hardware failure occurs, you can restore your databases by using the backups and logs sent to your destination system. For more information about this step, see link.

After completing this step, your BizTalk Server should be restored and running. If you are using the Rules Engine, after restoring the databases, you must restart the Rule Engine Update Service on every server in the BizTalk Server group.