How to integrate the ESBExceptionDB database into the BizTalk Server backup job

The BizTalk ESB Toolkit contains a great pattern of error management, based on a database for the storage of the errors in the environment. It’s the SQL Server database named EsbExceptionDb.

This database includes:

• Errors occurred: detail of the error message (s) in error,…
• Actions on these errors: error was handled or not by an operator,message was resubmitted in the system.

This database becomes just as critical as the other BizTalk databases and there should be a suitable backup strategy.

You can certainly create a SQL Server standard maintenance plan to do the backup but I advise you to opt for the BizTalk backup: the backup BizTalk Server job performs the backup of the BizTalk databases (ensuring consistency in backups of all databases) and you can configure it so that it includes also some third-party databases. Here’s how to proceed for the EsbExceptionDb database.

 

1. CREATE THE MARKLOG TABLE IN THE ESBEXCEPTIONDB DATABASE

 

When the Backup BizTalk job runs on a database, and before it does the backup, it updates the contents of the MarkLog table. This table must be present in the EsbExceptionDb database (as in all the other databases included in the Backup job).

To create this table, simply run the script called Backup_Setup_All_Tables.sql in the Directory Schema for the BizTalk installation directory.

 

2. CREATE STORED PROCEDURES IN THE ESBEXCEPTIONDB BASE

 

The actual backup of the database and the update of the table MarkLog are performed by a set of stored procedures to be created in the EsbExceptionDb database.

To do this, run the script Backup_Setup_All_Procs.sql located in the Directory Schema for the BizTalk installation directory.

 

3. CONFIGURE THE BACKUP JOB

 

So that the BizTalk integrated backup job includes EsbExceptionDb in the backup process, you simply update the table adm_OtherBackupDatabases located in the BizTalkMgmtDb database:

Add an entry in this table for your database as shown below:

Of course, you must customize the record added to the table so that it matches your environment:

• The ServerName field must contain the name of the SQL Server instance,
• The BTSServerName field must contain the name of the BizTalk Server.

 

4. TEST THE JOB

 

Run this job to test the backup. You will get a backup file of the database EsbExceptionDb. In a traditional configuration, you will have a full backup once a day and a log backup every 15 minutes.

 

BEST PRACTICE

Of course this applies to all third-party databases (external configuration, other databases of the ESB Toolkit…). It is a good practice to use the job to backup BizTalk because it ensures consistency in the backups. If you use the BizTalk Log Shipping for a recovery site, all the databases that are backed up by the BizTalk job will automatically be restored on site without effort.