BizTalk & SQL Server

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.*/