BizTalk Server 2013 R2: Installation and Configuration – Optimize the BizTalk Server 2013 R2 environment (Part 16)

BizTalk
  • Sandro Pereira
  • Jan 22, 2015
  • 9 min read
Tags: BizTalk, BizTalk Server 2013 R2, Configuration, Installation, Optimization

The next posts will focus on optimizing some BizTalk Server 2013 R2 configurations. The following recommendations can be used to increase BizTalk Server performance or just to make the platform more resilient to failures. The optimizations listed in this topic are applied after BizTalk Server has been installed and configured.

📝 One-Minute Brief

BizTalk Server 2013 R2, BizTalk Optimization, SQL Agent Jobs, Backup BizTalk Server, DTA Purge and Archive, Database Auto-Growth, MessageBox, Tracking (DTA), Maintenance Plan, Service Recovery, Automatic Delayed Start, BizTalk Health Monitor (BHM)

Deleting BizTalk backup files

The health of BizTalk Server databases is very important for a successful messaging environment. BizTalk ships with a total of 13 SQL Agent jobs. By default, the following BizTalk jobs aren’t configured or enabled upon installation, and the two jobs that require configuration are the most important: the Backup BizTalk Server and the DTA Purge and Archive. This is nothing new, and everybody knows!

However, what we often forget is that these two jobs, by default, don’t provide functionalities for deleting backup files that have accumulated over time on our file system, and we normally forget to create a “process” or a “job” to accomplish this until it’s too late. The result of that is… we often just remember when the disks are full, and everything stops working!

You can solve this problem by implementing your custom sp_DeleteBackupHistory or by creating a Maintenance Plan to delete BizTalk Database Backup files.

Implementing a custom sp_DeleteBackupHistory

To implement a custom sp_DeleteBackupHistory, you need to:

  • Start SQL Server Management Studio, and then connect to the BizTalk Management Database. By default, this database is named BizTalkMgmtDb.
  • Click New Query to open a Query Editor window.
  • Run the following Transact-SQL script to create the sp_DeleteBackupHistoryAndFiles stored procedure:
CREATE PROCEDURE [dbo].[sp_DeleteBackupHistoryAndFiles] @DaysToKeep smallint = null
 AS
 BEGIN
    set nocount on
    IF @DaysToKeep IS NULL OR @DaysToKeep <= 1       RETURN    /* Only delete full sets    If a set spans a day in such a way that some items fall into the deleted group and the other does not, do not delete the set */    DECLARE DeleteBackupFiles CURSOR    FOR SELECT 'del "' + [BackupFileLocation] + '\' + [BackupFileName] + '"' FROM [adm_BackupHistory]    WHERE  datediff(dd, [BackupDateTime], getdate()) >= @DaysToKeep
    AND [BackupSetId] NOT IN (SELECT [BackupSetId] FROM [dbo].[adm_BackupHistory] [h2] WHERE [h2].[BackupSetId] = [BackupSetId] AND datediff(dd, [h2].[BackupDateTime], getdate()) < @DaysToKeep)    DECLARE @cmd varchar(400)    OPEN DeleteBackupFiles    FETCH NEXT FROM DeleteBackupFiles INTO @cmd    WHILE (@@fetch_status <> -1)
    BEGIN
       IF (@@fetch_status <> -2)
       BEGIN
          EXEC master.dbo.xp_cmdshell @cmd, NO_OUTPUT
          delete from [adm_BackupHistory] WHERE CURRENT OF DeleteBackupFiles
          print @cmd
       END
       FETCH NEXT FROM DeleteBackupFiles INTO @cmd
    END
    CLOSE DeleteBackupFiles
    DEALLOCATE DeleteBackupFiles
 END
 GO
  • Change the Clear Backup History step of the Backup BizTalk Server job to call the sp_DeleteBackupHistoryAndFiles stored procedure instead of sp_DeleteBackupHistory.

However, I personally don’t like this approach for two main reasons:

  • I avoid changing the scripts of BizTalk standard jobs.
  • And I also believe that this approach is very limited and doesn’t allow the flexibility that we all want.

Instead, I prefer to use all the features SQL provides and create a maintenance plan with a Cleanup task to remove these files.

Implementing a Maintenance Plan to clean BizTalk Database backup files

Maintenance Plans allow DBAs to have the flexibility to create workflows to execute the tasks required to ensure that databases are optimized, regularly backed up, and free of inconsistencies. However, almost all of these tasks are warranted by the existing BizTalk jobs, and I will not use them. The only thing we need is to create a task to clean the BizTalk Database backup files from our file system.

The main advantage of this approach is that it will allow us more flexibility for further changes, and we can also use it to other backups that we have.

Note: You should ensure that SQL Server Agent service is running because the maintenance plans depend on the Microsoft SQL Server Agent in order to run on a regular basis.

Important Note: this example is created to run in a developer machine, so if you are implementing this approach in production environments make sure to keep the backups for the time you see that fit your requirements or make sure the backups are save in an external storage before you delete this files from the file system.

To create the maintenance check, follow the post: How to create a Maintenance Plan to delete BizTalk Database Backups files

Pre-allocate space and define auto-growth settings for BizTalk Server databases

By default, BizTalk Server databases are defined with a small file size and with the parameter Autogrowth set to:

  • Increase the database file by 1 MB.
  • And by 10% for the log file.

So what’s the problem with these settings?

Auto growth setting plays an important role in BizTalk configuration for performance reasons. Why?

SQL Server database auto-growth is a blocking operation that hinders performance in BizTalk Server databases. When SQL Server increases the size of a file, it must first initialize the new space before it can be used. This is a blocking operation that involves filling the new space with empty pages.

Therefore, it’s recommended to:

  • Set this value (databases auto-growth) to a fixed value of megabytes instead of to a percentage, so SQL Server doesn’t waste its resources expanding the data and log files during heavy processing. This is especially true for the MessageBox and Tracking (DTA) databases:
    • In a high-throughput BizTalk Server environment, the MessageBox and Tracking databases can significantly increase. If auto-growth is set to a percentage, then auto-growth will be substantial as well.
    • As a guideline for auto-growth, for large files, the increment should be no larger than 100 MB, for medium-sized files, 10 MB, or for small files, 1 MB.
    • This should be done so that, if auto-growth occurs, it does so in a measured fashion. This reduces the likelihood of excessive database growth.
  • Also, allocate sufficient space for the BizTalk Server databases in advance to minimize database auto-growth.

To define auto-growth and Pre-allocate space for BizTalk Server databases, check the following post: Pre-allocate space and define auto-growth settings for BizTalk Server databases

Configure BizTalk Server Windows Services

By default, the Startup type property of BizTalk Windows Services, the Enterprise Single Sign-On Service, the Microsoft UDDI Notification Service, and the BAMAlerts service is set to Automatic; some of these Windows Services may not start automatically after a system restart. To avoid this behavior, you must configure the Startup type to Automatic (Delayed Start) option in these services:

  • BAMAlerts.
  • Microsoft UDDI Notification Service.
  • Rule Engine Update Service.

The previous version of BizTalk Server also had this problem:

  • Enterprise Single Sign-On Service.
  • BizTalk Service BizTalk Group : BizTalkServerApplication Service.
BizTalk Server 2013 R2 Services automatic

Despite being set to Automatic, the Recovery properties are configured properly to restart the service after failures.

BizTalk Server 2013 R2 Services

To properly configure the BizTalk Services, Enterprise Single Sign-On Service, BAMAlerts, and Microsoft UDDI Notification Service Windows service:

  • Press the Windows key to switch to the Start screen, type Services, and click on the View local services option from the Search menu.
BizTalk Server 2013 R2 Services search menu
  • In the Services window, on the Services (Local) panel, select the service, for example, BAMAlerts, right-click, and select the Properties option.
BizTalk Server 2013 R2 Services BamAlerts
  • On the BAMAlerts Properties (Local Computer) window:
    • On the General tab, apply the following configuration.
      • Startup type: Automatic (Delayed Start).
BizTalk Server 2013 R2 Services Bam Alerts automatic delay
  • On the Recovery tab, apply the following configuration:
    • First failure: Restart the Service.
    • Second failure: Restart the Service.
    • Subsequent failures: Restart the Service.
    • Reset fail count after … day: 1.
    • Restart service after … minutes: 1.
BizTalk Server 2013 R2 Services Bam Alerts recovery
  • Click OK to apply the changes and close the window.

You could and should apply the same Recovery properties configurations for the SQL Server services:

  • SQL Full-text Filter Daemon Launcher.
  • SQL Server.
  • SQL Server Agent.
  • SQL Server Analysis Services.
  • SQL Server Browser.
  • SQL Server Integration Services 12.0.
  • SQL Server Reporting Services.
  • SQL Server VSS Writer.

By default, they are configured as Take No Action.

BizTalk Server 2013 R2 SQL Services

And should be configured as:

  • Startup type: Automatic.
  • And First, Second, and Subsequent failures: Restart the Service.
BizTalk Server 2013 R2 SQL Services

Install and configure BizTalk Health Monitor V2

BizTalk Health Monitor is a snap-in, basically it’s the same of BizTalk MsgBoxViewer tool that we used to monitor a BizTalk environment by gathering all information of a BizTalk group and detecting any issues, non-critical or critical warnings to detect any potential problems in advance, but in this case is integrated more closely with the BizTalk Administration Console to provide BizTalk administrators a quick and complete dashboard of a BizTalk group which will help them monitor the health of their BizTalk platform.

You can download the standalone version of BHM from the Microsoft Download Center here: BizTalk Health Monitor

Prerequisites:

  • BizTalk Server 2010, 2013, or 2013 R2 should be installed and configured.

To install and configure BizTalk Health Monitor V2, check the following post: Installing the new BizTalk Health Monitor snap-in on BizTalk Server 2010 or BizTalk Server 2013.

Related links

Hope you find this helpful! If you liked the content or found it useful and would like to support me in writing more, consider buying (or helping to buy) a Star Wars Lego set for my son. 

Thanks for Buying me a coffe
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.

1 thought on “BizTalk Server 2013 R2: Installation and Configuration – Optimize the BizTalk Server 2013 R2 environment (Part 16)”

Leave a Reply

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

The Ultimate Cloud
Management Platform for Azure

Supercharge your Azure Cost Saving

Learn More
Turbo360 Widget

Get integration tips every day. Follow us on:

Back to Top