BizTalk Server 2013 R2: Installation and Configuration – Configure BizTalk Server SQL Jobs (Part 15)

Posted: January 20, 2015  |  Categories: Administration Advance Configurations BizTalk
Tags: BizTalk BizTalk Server 2013 R2 Configuration Installation SQL Jobs

This next posts will focus on installing and configuring BizTalk Server 2013 R2 and additional components

Make sure that you have installed all the latest critical Windows updates from Microsoft and all the prerequisites before you install BizTalk Server 2013 R2.

Configure BizTalk Server SQL Jobs

BizTalk Server databases and their health are very important for a successful BizTalk Server database messaging environment. Although there can be many settings that we can configure, like auto-growth settings for BizTalk Databases (you can learn more here), there are two main things that we must understand and be aware, especially the database administrators:

  • Execution of the BizTalk Server SQL Agent jobs is crucial for managing the BizTalk Server databases and for maintaining optimal performance.
  • The Backup BizTalk Server job is the only supported method to backup the BizTalk Server databases and requires that all of the BizTalk Server databases are configured to use the SQL Server full recovery model.

BizTalk is shipped out with a total of 13 SQL Agent jobs. By default, the following BizTalk jobs aren’t configured and enabled upon installation:

  • Backup BizTalk Server (BizTalkMgmtDb)
  • DTA Purge and Archive (BizTalkDTADb)
  • MessageBox_Message_Cleanup_BizTalkMsgBoxDb

The two jobs that need configurations are the two most important jobs: the “Backup BizTalk Server” and the “DTA Purge and Archive“. If you want these functionalities you must configure and enable them.

148-BizTalk-Server-2013-R2-sql-server-agent-jobs

How to configure Backup BizTalk Server (BizTalkMgmtDb)

This is the job provided by Microsoft to do a best practice backup of the BizTalk databases. This job has to be configured for it to be able to run.

This Job consists of four steps:

  • Step 1 – Set Compression Option – Enable or disable compression during backup.
  • Step 2 – BackupFull – Performs full database backups of the BizTalk Server databases.
  • Step 3 – MarkAndBackUpLog – Backs up the BizTalk Server database logs.
  • Step 4 – Clear Backup History – Specifies how long the backup history is kept.

To configure the Backup BizTalk Server job:

  • Press the “Windows key” to switch to the Start screen, type “SQL Management” or “SQL” and click on “SQL Server Management Studio” option to switch to the Start screen.
  • In Object Explorer panel, connect to the SQL Server instance and expand the server tree.
    • Expand the “SQL Server Agent” node
    • Expand the “Jobs” node
  • Double click “Backup BizTalk Server (BizTalkMgmtDb)” to open the job properties window.
  • In the Job Properties – Backup BizTalk Server (BizTalkMgmtDb) dialog box, under “Select a page”, click “Steps”.
  • In the “Job step list”, click on the job you want to edit, and then click “Edit”
BizTalk Server 2013 R2 job properties backup biztalk server biztalkmgmtdb

Step 1 – Set Compression Option

Since BizTalk Server 2010, BizTalk Server supports compression for its SQL Server database backups thereby providing some benefits like less space needed for the backup files, fewer I/O operations for the backup and restore and so on.

This job step calls a stored procedure named sp_SetBackupCompression on the BizTalk management database (BizTalkMgmtDb by default) to set the value on the adm_BackupSettings table.

The original script is:

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

The stored procedure has only one parameter:

  • @bCompression: By default, Set Compression Option is set to 0, which makes backup compression off by default. To change the default, change Set Compression Option to 1.

Change the script to:

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

Step 2 – BackupFull

The BackupFull step is responsible for performing a full backup of the database.

The original script is:

exec [dbo].[sp_BackupAllFull_Schedule] 'd' /* Frequency */, 'BTS' /* Name */, '...destination path...' /* location of backup files */

Where:

  • Frequency: The default is d (daily). This is the recommended setting. Other values include h (hourly), w (weekly), m (monthly), or y (yearly).
  • Name: The default is BTS. The name is used as part of the backup file name.
  • Location of backup files: Replace ‘<destination path>’ with the full path (the path must include the single quotes) to the computer and folder where you want to back up the BizTalk Server databases.

There are also three optional parameters:

  • Force full backup after partial backup failures (@ForceFullBackupAfterPartialSetFailure): The default is 0 when not specified, which means that if a log backup fails, no full backups are done until the next full backup frequency interval is reached.
    • Replace with 1 if you want a full backup to be made whenever a log backup failure occurs.
  • Local time hour for the backup process to run (@BackupHour): The default is NULL when not specified, which means that backup job will not be associated with the time zone of the BizTalk Server computer and will run at midnight UTC time (0000).
    • If you want to backup to run at a particular hour in the time zone of the BizTalk Server computer, specify an integer value from 0 (midnight) to 23 (11 PM) as the local time hour for the BackupHour parameter.
  • Use local time (@UseLocalTime): This is an extra parameter that you can also add that tells the procedure to use local time. The default value is 0.
    • If set to 0, then it uses current UTC time – GETUTCDATE() – 2007-05-04 01:34:11.933
    • If set to 1, then it uses local time – GETDATE() – 2007-05-03 18:34:11.933

Change the script to:

exec [dbo].[sp_BackupAllFull_Schedule] 'd' /* Frequency */, 'BTS' /* Name */, '...your_destination_path\BizTalk Database\Full' /* location of backup files */

Step 3 – MarkAndBackUpLog

The MarkAndBackupLog step is responsible for marking the logs for backup, and then backing them up.

The original script is:

exec [dbo].[sp_MarkAll] 'BTS' /*  Log mark name */, '...destination path...' /* location of backup files */

Where:

  • @MarkName: Log mark name is part of the naming convention for backup files:
    • <Server Name>_<Database Name>_Log_< Log Mark Name >_<Timestamp>
  • @BackupPath: You must change the destination path this to a valid one. It may be local or a UNC path to another server.

There is also one optional parameter:

  • @UseLocalTime: This is an extra parameter that you can also add that tells the procedure to use local time 
exec [dbo].[sp_MarkAll] 'BTS' /* Log mark name */,'...destination path...'  /*location of backup files */ , 1

Change the script to:

exec [dbo].[sp_MarkAll] 'BTS' /* Log mark name */, '... your_destination_path\BizTalk Database\Logs' /* location of backup files */

Step 4 – Clear Backup History

The Clear Backup History step is responsible for performing a cleanup of the backup history according to how long a backup should be kept.

The original script clears out the instances in the MarkLog table older than 14 days:

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

Where:

  • @DaysToKeep: specifies how long the backup history is kept in the Adm_BackupHistory table. Periodically clearing the backup history helps to maintain the Adm_BackupHistory table at an appropriate size.
    • The default value for the DaysToKeep parameter is 14 days.

There is also one optional parameter:

  • @UseLocalTime: This is an extra parameter that you can also add that tells the procedure to use local time.
exec [dbo].[sp_DeleteBackupHistory] @DaysToKeep=14 , @UseLocalTime =1

In this particular case, I like to leave the default settings.

Important Note: This job step does not provide functionality for deleting backup files that have accumulated over time. You can solve this problem by implementing you custom sp_DeleteBackupHistory or by creating a Maintenance Plan to delete BizTalk Database Backups files (see more details in BizTalk Server 2013 R2: Installation and Configuration – Optimize the BizTalk Server 2013 R2 environment (Part 17))

After properly configure, to enable the Backup BizTalk Server job:

  • Under “Jobs” in SQL Server Management Studio Object Explorer, right-click on the name of the job “Backup BizTalk Server (BizTalkMgmtDb)” and select “Enable” option.
BizTalk Server 2013 R2 enable backup biztalk server biztalkmgmtdb
  • In the result screen, click “Close”.
BizTalk Server 2013 R2 enable backup biztalk server biztalkmgmtdb result screen

How to configure DTA Purge and Archive (BizTalkDTADb)

This job automates the archiving of tracked messages and the purging of the BizTalk Tracking database to maintain a healthy system and to keep the tracking data archived for future use.

And it’s configured to call the stored procedure dtasp_BackupAndPurgeTrackingDatabase, which uses the six parameters you must configure in this job:

  • @nLiveHours: Any completed instance older than the (live hours) + (live days) will be deleted along with all associated data. The default is 0 hours.
  • @nLiveDays: Any completed instance older than the (live hours) + (live days) will be deleted along with all associated data. The default interval is 1 day.
  • @nHardDeleteDays: All data (even if incomplete) older than this will be deleted. The time interval specified for HardDeleteDays should be greater than the live window of data. The live window of data is the interval of time for which you want to maintain tracking data in the BizTalk Tracking (BizTalkDTADb) database. Anything older than this interval is eligible to be archived at the next archive and then purged. The default is 30 days.
  • @nvcFolder: Folder in which to put the backup files.
  • @nvcValidatingServer: Server on which validation will be done. A null value indicates no validation is being done. The default is NULL.
  • @fForceBackup: Default is 0. This is reserved for future use.

To configure the DTA Purge and Archive job:

  • Start by running the “SQL Server Management Studio”, if it is not already open, press the “Windows key” to switch to the Start screen, type “SQL Management” or “SQL” and click on “SQL Server Management Studio” option from the Search menu.
  • In Object Explorer panel, connect to the SQL Server instance and expand the server tree.
    • Expand the “SQL Server Agent” node
    • Expand the “Jobs” node
  • Double click “DTA Purge and Archive (BizTalkDTADb)” to open the job properties window.
  • In the Job Properties – DTA Purge and Archive (BizTalkDTADb) dialog box, under “Select a page”, click “Steps”.
  • In the “Job step list”, click Archive and Purge, and then click Edit.
BizTalk Server 2013 R2 job properties dta purge and archive biztalkdtadb

The original script after installing BizTalk looks like this:

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.
null, --@nvcFolder nvarchar(1024) = null, --folder for backup files
null, --@nvcValidatingServer sysname = null,
0 --@fForceBackup int = 0 --

This means that:

  • 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.

Normally I use these configurations for production environments:

exec dtasp_BackupAndPurgeTrackingDatabase 0, 10, 20, '...destination path...', null, 0

However in a development machine, we don’t need to maintain the archived tracking data, so I just purge it periodically. BizTalk gives you the option to Archive and Purge the tracking data or just simple purge the data without archiving:

  • Change the SQL Statement inside “DTA Purge and Achieve” SQL Job to
declare @dtLastBackup datetime set @dtLastBackup = GetUTCDate() exec dtasp_PurgeTrackingDatabase 1, 0, 7, @dtLastBackup

After properly configure, to enable the Backup BizTalk Server job:

  • Under “Jobs” in SQL Server Management Studio Object Explorer, right-click on the name of the job “DTA Purge and Archive (BizTalkDTADb)” and select “Enable” option.
  • In the result screen, click “Close”.

MessageBox_Message_Cleanup_BizTalkMsgBoxDb

This job removes all messages that are not referenced by any subscribers in the BizTalkMsgBoxDb database tables.

Important Note: This job is also started by the MessageBox_Message_ManageRefCountLog_BizTalkMsgBoxDb job. Therefore, we recommend that you disable this job.

Related links

Author: Sandro Pereira

Sandro Pereira lives in Portugal and works as a consultant at DevScope. In the past years, he has been working on implementing Integration scenarios both on-premises and cloud for various clients, each with different scenarios from a technical point of view, size, and criticality, using Microsoft Azure, Microsoft BizTalk Server and different technologies like AS2, EDI, RosettaNet, SAP, TIBCO etc. He is a regular blogger, international speaker, and technical reviewer of several BizTalk books all focused on Integration. He is also the author of the book “BizTalk Mapping Patterns & Best Practices”. He has been awarded MVP since 2011 for his contributions to the integration community.

Leave a Reply

Your email address will not be published. Required fields are marked *

turbo360

Get integration tips every day. Follow us on:

Back to Top