Following the installation of Biztalk Server 2009 on my development laptop I used the BizTalk Server Best Practice Analyser which highlighted the fact that two of the SQL Server Agent jobs that BizTalk relies on were not running successfully.  Upon investigation it turned out that these jobs needed to be configured before they would run successfully.

To configure these jobs open SQL Server Management Studio, expand SQL Server Agent > Jobs and double click on the appropriate job.  Select Steps and then edit the appropriate entries.

Backup BizTalk Server (BizTalkMgmtDb)

This job is comprised of three steps BackupFull, MarkAndBackupLog and ClearBackupHistory.

BackupFull

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

• The frequency here is set/left as daily
• The name is left as BTS
• You must provide a full destination path for the backup files to be stored.

There are also two optional parameters:

• A flag that controls if the job forces a full backup if a partial backup fails
• A parameter to control the time of day to run the full backup; the default is midnight UTC time

For example:

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

MarkAndBackUpLog

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

You must provide a destination path for the log backups.

Optionally you can also add an extra parameter that tells the procedure to use local time:

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

Clear Backup History

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

This will clear out the instances in the MarkLog table older than 7 days. 
 

DTA Purge and Archive (BizTalkDTADb)

This job is comprised of a single step.

Archive and Purge

exec dtasp_BackupAndPurgeTrackingDatabase 0, --@nLiveHours tinyint, 1, --@nLiveDays tinyint = 0, 30, --@nHardDeleteDays tinyint = 0, null, --@nvcFolder nvarchar(1024) = null, null, --@nvcValidatingServer sysname = null, 0 --@fForceBackup int = 0

Any completed instance that is older than the live days plus live hours will be deleted, as will any associated data.

Any data older than the HardDeleteDays will be deleted - this means that those long running orchestration instances that would otherwise never be purged will at some point have their data cleared down while allowing the instance to continue, thus preventing the DTA databse from growing indefinitely.  This should always be greater than the soft purge window.

The NVC folder is the path for the backup files, if this is null the job will not run failing with the error :

DTA Purge and Archive (BizTalkDTADb)

Job failed

SQL Server Management Studio, job activity monitor, view history

The @nvcFolder parameter cannot be null.

Archive and Purge step

How long you choose to keep instances in the Tracking Database is really up to you.

For development I have set this up as:

exec dtasp_BackupAndPurgeTrackingDatabase 0, 1, 30, ’<destination path>’, null, 0

On a live server you may want to adjust these figures:

exec dtasp_BackupAndPurgeTrackingDatabase 0, 15, 20, ’<destination path>’, null, 0