Configuring the BizTalk SQL Agent Jobs

One of the things people often forget to do when installing BizTalk into an environment for the first time is to configure and enable two critical SQL Server Agent jobs:

  • Backup BizTalk Server
  • DTA Purge and Archive

These jobs are not enabled by default because they both require you to supply UNC paths to the backup and archive file locations, respectively.  While backups and archiving may not seem like the most immediate priority (especially in non-production environments), it is nonetheless essential that these jobs run successfully as they keep the BizTalk databases “lean & mean” by purging old data. I’ve seen client tracking databases grow to over 200GB in size when the latter job is not running properly (the Microsoft recommended size is <5GB!!)

Of course, when the jobs are configured correctly (following the MSDN instructions here and here), this introduces the problem of disk space taken up by the backup and the archive files. Out of the box, BizTalk offers no solution for managing these files, which will continue to accumulate over time. And yes, I’ve seen more than one client run out of disk space on their SQL Server for this very reason!

My very wise friend and co-worker Bill Chesnut (aka “BizTalk Bill”) has created a modified stored procedure that cleans up the backup files, which is a big help. I highly recommend implementing this in all your environments (although if does require xp_cmdshell to be enabled on your SQL Server). I’ve incorporated this script into my configuration below.

Below I have pasted the template for both of these jobs, partly because the formatting in SSMS always seems to obscure the parameters, but mostly because I’m always looking for this template and have to search for it (now I know where to find it!).

Backup BizTalk Server

Note that I’ve used the optional parameters to control the full backup time (by default, this is midnight GMT, which is 10:00am here in Queensland, Australia). I’ve also substituted the OOTB ‘sp_DeleteBackupHistory’ job replacing it with Bill’s modified stored procedure as discussed above.

STEP 1: Set Compression Option:

exec [dbo].[sp_SetBackupCompression] @bCompression = 1 /* 0 – do not use Compression, 1 – Use Compression */

STEP 2: BackupFull:

exec [dbo].[sp_BackupAllFull_Schedule]
‘d’ /* Frequency */,
‘BTS’ /* Name */,
<Backup File Location>‘, /* location of backup files */
0,    /* 1 = force full backup on incomplete set */

23    /* backup at 11:00 pm */

STEP 3: MarkAndBackupLog:

exec [dbo].[sp_MarkAll]
‘BTS’ /*  Log mark name */,
<Backup File Location>‘ /* location of backup files */,
1  /* puts logmark filename in local time */

STEP 4: Clear Backup History:

exec [dbo].[sp_DeleteBackupHistoryAndFiles] @DaysToKeep=14, @UseLocalTime=1

DTA Purge and Archive

I’ve included two samples below. The first one is the default OOTB version that actually archives the purged records off to the file system. This is the one you are likely to use in production:

STEP 1: Archive and Purge:

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.
<Archive File Location>‘, --@nvcFolder nvarchar(1024) = null,
null, --@nvcValidatingServer sysname = null,
0 --@fForceBackup int = 0           --not currently used

This second version calls a different stored procedure that purges the tracking database but does not archive any files (as documented on MSDN here). I always use this on my development machine, and often in lower test environments where there is usually no need to review tracking data over a week old. This is especially handy in environments where you have message body tracking turned on (for debugging support), because this greatly increases the size of the tracking data files:

STEP 1: Archive and Purge:

exec dtasp_PurgeTrackingDatabase
0, --@nHours tinyint,        --Any completed instance older than the live hours +live days
7, --@nDays tinyint = 0,     --will be deleted along with all associated data
8, --@nHardDays tinyint = 0, --all data older than this will be deleted.
null --@dtLastBackup         --just ensures we don’t archive data newer than last backup; leave null if you don’t care

Hope this helps you keep your BizTalk databases trim, taught and terrific! Winking smile

About Dan Toomey
Husband, father, Enterprise integration geek, Microsoft Azure MVP, Pluralsight author, Brisbane Azure User Group leader (@BrisbaneAzureUG), MCPD, MCT, MCTS & former professional musician.

3 Responses to Configuring the BizTalk SQL Agent Jobs

  1. Pingback: Scott Banwart's Blog › Distributed Weekly 198

  2. Pingback: BizTalk SQL Agent Jobs – Configuration Errors | Mind Over Messaging

  3. Pingback: MVP Messages from INTEGRATE 2017 (London) - Mexia

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

John Glisson - Geek of the Cloth

Thoughts on integration, technology and what-not...

prashantbiztalkblogs

My BizTalk Experiences

The CRUCIBLE

THINK: It's not illegal....yet.....

paulbouwer.com

life and technology

Abdul Rafay's BizTalk Blog

My experiences with BizTalk related to architecture, development and performance in my enterprise.

Mike Diiorio

Connected Systems and other thoughts

BizTalk musings

Issues, patterns and useful tips for BizTalk development

EAI Guy.net

Enterprise Applicaiton Integration and SOA 2.0

Connected Pawns

Mainly BizTalk & Little Chess

Man Vs. Machine

Why can't we all just get along?

Adventures inside the Message Box

BizTalk, Azure, and other tools in the Microsoft stack - Johann Cooper

Biz(Talk)2

Talk, talk and more talk about BizTalk

Richard Seroter's Architecture Musings

Blog Featuring Code, Thoughts, and Experiences with Software and Services

Sandro Pereira BizTalk Blog

My notes about BizTalk Server 2004, 2006, 2006 R2, 2009, 2010, 2013 and now also Windows Azure BizTalk Services.

BizTalk Events

Calendar of BizTalk events all over the world!

Mind Over Messaging

Musings on BizTalk, Azure, WCF, and Enterprise Integration

The WordPress.com Blog

The latest news on WordPress.com and the WordPress community.

%d bloggers like this: