BizTalk & SQL Server

06 August 2017 Written by

BizTalk requires a high-performance and cleanly configured SQL Server to take full advantage of its potential. Here I would like to provide some of my experiences and small tools for you

Configure SQL Agent Jobs

If I am called to a customer already running BizTalk Server and complaining about storage and performance problems I check the SQL Agent jobs. Strange but true, in many cases the jobs are not configured and started - typical novice mistake.
Here are my basic settings I use for configuration. Just copy and modify the settings for your environment

Configure the Backup BizTalk Server Job (more info from Microsoft):

/*+++++++++++++ STEP 1: Set Compression Option +++++++++++++++++++++++*/
exec [dbo].[sp_SetBackupCompression] 
@bCompression = 0   /*0 - Do not use Compression, 1 - Use Compression */
/*+++++++++++++ STEP 2: BackupFull (performs full database backups of the BizTalk Server databases) +++++++++++++++++++++++*/
exec [dbo].[sp_BackupAllFull_Schedule]
'd'                 /* Frequency. Values: d (daily), h (hourly), w (weekly), m (monthly), or y (yearly).*/
,'BTS'              /* Name. Used as part of the backup file name */
,'C:\BT_BCKUP'      /* location of backup files */
,1                  /* auto full backup after failure */
,20                 /* local time hour for the full backup process to run */
/*+++++++++++++ STEP 3: MarkAndBackupLog (backs up the BizTalk Server database logs) +++++++++++++++++++++++*/
exec [dbo].[sp_MarkAll] 
'BTS'               /* Log mark name */
,'C:\BT_BCKUP'      /* location of backup files */
,1                  /* use local time */
/*+++++++++++++ STEP 4: Clear Backup History (specifies for how long the backup history is kept) +++++++++++++++++++++++*/
exec [dbo].[sp_DeleteBackupHistory] 
@DaysToKeep=14      /*The number of days you want to keep the backup history*/

Configure the DTA Purge and Archive Job (more info from Microsoft):

 /*+++++++++++++ STEP 1: Archive and Purge +++++++++++++++++++++++*/
exec dtasp_BackupAndPurgeTrackingDatabase
0,              /*@nLiveHours, Any completed instance older than the (live hours) + (live days) will be deleted along with all associated data.*/
1,              /*@nLiveDays, Any completed instance older than the (live hours) + (live days) will be deleted along with all associated data.*/
30,             /*@nHardDeleteDays, All data (even if incomplete) older than this will be deleted.*/
'C:\BT_BCKUP',  /*@nvcFolder, Folder in which to put the backup files.*/
null,           /*@nvcValidatingServer, Server on which validation will be done. NULL value indicates no validation is being done.*/
0               /*@fForceBackup This is reserved for future use.*/       

Iwe Kardum

Iwe Kardum

Munich – Germany

As BizTalk Consultant I'm also available for training, assessments or implementing any BizTalk project.

Call or send me an email if you have any questions.
+49 (89) 2154 6094


inSyca would like to offer you the best possible service. We use so-called cookies to ensure that you are able to make optimum use of the website. By continuing to use this website, you agree to the use of cookies
More information Accept Decline