BizTalk 2009 - SQL Server Job Configuration
- by StuartBrierley
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
