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.

BizTalk Custom Pipeline for Excel 2007 (xlsx)

Hi there,

I have just released a custom pipeline to process and debatch xlsx (Excel 2007) files in BizTalk Server 2009 and up. The purpose of this custom pipeline is to decode the xlsx files and convert it to xml. This component also adds a namespace and debatches the xml files into individual records.

The following properties are available to the developer at design time:
NamespaceBase: – The new namespace to be inserted.
NamespacePrefix: – The namespace prefix to use.
RootNodeName: – Name of the root node after debatching the records.
IsFirstRowHeader: – Flag to indicate if the first row represents column names.

You can download it from codeplex:

http://excel2007pipeline.codeplex.com/

Cheers!