Including Custom Databases in the Backup BizTalk Server Job

I’m sure its common knowledge that other databases can be backed-up using the Backup BizTalk Server Job, meaning that the transaction consistency that is that is written the the BizTalk databases can be included in your own custom databases.

I thought it was pretty easy to include custom databases, simply by adding an entry into the adm_OtherBackupDatabases table in the BizTalkMgmtDb database. In the screenshot below, I’ve added the BizTalk RosettaNet Accelerator (BTARN) databases:

I thought it was that simple, until I tried it for the first time in real life and was greeted with the following error from SQL Agent (notice the error in bold):

Executed as user: NT AUTHORITYSYSTEM. Processed 17520 pages for database ‘BizTalkDTADb’, file ‘BizTalkDTADb’ on file 1. [SQLSTATE 01000] (Message 4035)  Processed 2 pages for database ‘BizTalkDTADb’, file ‘BizTalkDTADb_log’ on file 1. [SQLSTATE 01000] (Message 4035)  BACKUP DATABASE successfully processed 17522 pages in 10.454 seconds (13.093 MB/sec). [SQLSTATE 01000] (Message 3014)  Processed 2264 pages for database ‘BizTalkMgmtDb’, file ‘BizTalkMgmtDb’ on file 1. [SQLSTATE 01000] (Message 4035)  Processed 3 pages for database ‘BizTalkMgmtDb’, file ‘BizTalkMgmtDb_log’ on file 1. [SQLSTATE 01000] (Message 4035)  BACKUP DATABASE successfully processed 2267 pages in 2.284 seconds (7.751 MB/sec). [SQLSTATE 01000] (Message 3014)  Processed 3280 pages for database ‘BizTalkMsgBoxDb’, file ‘BizTalkMsgBoxDb’ on file 1. [SQLSTATE 01000] (Message 4035)  Processed 2 pages for database ‘BizTalkMsgBoxDb’, file ‘BizTalkMsgBoxDb_log’ on file 1. [SQLSTATE 01000] (Message 4035)  BACKUP DATABASE successfully processed 3282 pages in 2.156 seconds (11.890 MB/sec). [SQLSTATE 01000] (Message 3014)  Processed 256 pages for database ‘BizTalkRuleEngineDb’, file ‘BizTalkRuleEngineDb’ on file 1. [SQLSTATE 01000] (Message 4035)  Processed 1 pages for database ‘BizTalkRuleEngineDb’, file ‘BizTalkRuleEngineDb_log’ on file 1. [SQLSTATE 01000] (Message 4035) Could not find stored procedure ‘BTARNARCHIVE.dbo.sp_BackupBizTalkFull’. [SQLSTATE 42000] (Error 2812) BACKUP DATABASE successfully processed 257 pages in 0.326 seconds (6.158 MB/sec). [SQLSTATE 01000] (Error 3014).  The step failed.

‘Could not find stored procedure ‘BTARNARCHIVE.dbo.sp_BackupBizTalkFull’. Oh really?

It turns out that it isn’t as simple as adding a custom database to the adm_OtherBackupDatabases table – a number of stored procedures and tables also need to be added to your custom database to get the backup job to successfully work, however these can be easily added by executing the two SQL scripts Backup_Setup_All_Procs.sql and Backup_Setup_All_Tables.sql against your custom databases (the scripts can be found in the ‘[BizTalk Installation Directory]Schema’ directory). You may want to force a full backup to ensure you have full backups across all of your databases:

BEGIN TRAN
UPDATE [BizTalkMgmtDb].[dbo].[adm_ForceFullBackup] SET ForceFull = 1
COMMIT TRAN

Re-run your backup job and you will see your custom databases included in the backup.

The requirement to include additional stored procedures and tables in custom databases is well worth remembering, especially if you are deploying (or re-deploying) custom databases that don’t include these artifacts by default.

The procedure is detailed in more depth on MSDN.

Advertisements

BTARN ‘Service Content’ Error in the RNDisAssembler Component

We came across the following error late last night which was a bit of a show-stopper. We were trying to load a custom PIP (specifically a PIDX OrderChange), but kept hitting this issue time and time again:

Event Type: Error
Event Source: BizTalk Accelerator for RosettaNet
Event Category: None
Event ID: 4096
Description:
Source module:
RNDisAssembler
Correlation information:
Description:
Receive pipeline rejected incoming message
due to the following RNIF exception:
UNP.SCON.VALERR : A failure occurred while validating the service content.
Details:
Event Type: ErrorEvent Source: BizTalk Accelerator for RosettaNetEvent Category: NoneEvent ID: 4096Date: 18/08/2010Time: 10:33:47User: N/AComputer: [COMPUTER NAME]Description:Source module:RNDisAssembler
Correlation information:

Description:

Receive pipeline rejected incoming message due to the following RNIF exception: UNP.SCON.VALERR : A failure occurred while validating the service content.
Details:

At first I didn’t quite understand the cause of the error – the PIDX OrderChange message contained within the Service Content was valid (as far as I was aware), all of the other messages within the payload looked correct and it was 3am….

It turns out that the RNDisassembler does in-fact attempt to validate the message contained within the Service Content against a deployed schema just like the standard XmlDisassembler. The message that our trading partner was sending did not validate and hence the RosettaNet Accelerator threw this error message; once we had corrected the schema and redeployed, the error went away.

This is certainly one to be aware of if you are developing custom PIP’s to use with the RosettaNet Accelerator: ensure that the message in the Service Content validates against your custom PIP schema!