Guidance In Configuring Default BizTalk SQL Agent Jobs

Having done some work recently setting up a number of BizTalk Hyper-V machines, I have put some guidance together on configuring the SQL Server Agent jobs that are created as part of the BizTalk install process. It’s pretty simple, but important to get these right as they can create performance issues, and run your server out of disk space!

1. Backup BizTalk Server (BizTalkMgmtDb)
There are three steps to this job: BackupFull, MarkAndBackupLog and ClearBackupHistory.

The BackupFull step contains this sql statement to configure:

exec [dbo].[sp_BackupAllFull_Schedule] ‘d’ /* Frequency */,‘BTS’ /* Name */,‘<destination path>’ /* location of backup files */

Generally most installations will keep the frequency daily and the name the same but you need to provide a full destination patch for the backup files to be stored in. Also, there are two important extra parameters that should be applied to this procedure. The first is a flag that controls if the job forces a full backup if a partial backup fails. The second extra parameter controls the time of day to run the full backup. Adding this will stop the backups occurring at midnight UTC time, which is the default. After configuration, to run at 2300 hours this step should look like:

exec [dbo].[sp_BackupAllFull_Schedule] 'd'/* Frequency */,'BTS'/* Name */,'C:\BizTalkSqlBackups\'/* location of backup files */, 0, 23

The MarkAndBackUpLog  step has the following statement to configure:

exec [dbo].[sp_MarkAll] ‘BTS’ /* Log mark name */,’<destination path>’  /*location of backup files */

Simply add in your destination path for the log backups, and a extra bit parameter that tells the procedure to use local time:

exec [dbo].[sp_MarkAll] 'BTS'/*  Log mark name */,'C:\BizTalkSqlBackups\'/* location of backup files */,1


The final step, Clear Backup History is configured like this by default:

exec [dbo].[sp_DeleteBackupHistory] @DaysToKeep=14

This procedure clears out the instances in the MarkLog table older than the days specified. Generally it can be left as is, but a enhancement to this procedure has been designed by Bill, to solve the issue of accumulating backups in the file system. You can download the updated stored procedure from his blog (called ‘BizTalk 2004 Stored Procedure to delete backups’), and it will take care of deleting the backup files as well.

After configuring all the steps, make sure the job is enabled in SQL Agent, and do a test run to verify everything is working correctly.

 

2.  DTA Purge and Archive (BizTalkDTADb)
This job only contains one step requiring configuration: Archive and Purge.

The step contains this sql statement to modify:

exec dtasp_BackupAndPurgeTrackingDatabase
0, --@nLiveHours tinyint, --Any completed instance older than the live hours +live days
1, --@nLiveDays tinyint = 0, --will be deleted along with all associated data
30, --@nHardDeleteDays tinyint = 0, --all data older than this will be deleted.
null, --@nvcFolder nvarchar(1024) = null, --folder for backup files
null, --@nvcValidatingServer sysname = null,
0 --@fForceBackup int = 0 --

The window for keeping instances in the Tracking Database is really dependent on your needs. The most important parameter to set is the folder path for backup files, but note there is also an interesting parameter called @nHardDeleteDays. According to the the Professional BizTalk Server 2006 book:


“This deals with incomplete instances that are in the Tracking database. This is useful in many situations, including when you have looping orchestrations that run indefinitely over a long period of time. Left unaddressed, this could result in the DTA database growing and these instances never being purged. Hard purging allows all data older than the specified value of the parameter @nHardDeleteDays to be purged, with the exception of that data that indicates the existence of an instance. The value of this parameter should always be greater than your soft purge window.”  Once this procedure is configured it should look like this:

exec dtasp_BackupAndPurgeTrackingDatabase
0, --@nLiveHours tinyint, --Any completed instance older than the live hours +live days
15, --@nLiveDays tinyint = 0, --will be deleted along with all associated data
16, --@nHardDeleteDays tinyint = 0, --all data older than this will be deleted.
'C:\BizTalkSqlBackups\', --@nvcFolder nvarchar(1024) = null, --folder for backup files
null, --@nvcValidatingServer sysname = null,
0 --@fForceBackup int = 0 --

Again, make sure this job is enabled and run it to ensure everything is working correctly. Configuring these jobs will go a long to ensure your BizTalk installation runs smoothly. Cheers!