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

Posted: January 22, 2015  |  Categories: Administration Advance Configurations BizTalk Tools
Tags: BizTalk BizTalk Server 2013 R2 Configuration Installation Optimization

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

Deleting BizTalk backup files

BizTalk Server database databases and their health are very important for a successful BizTalk Server database messaging environment. 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 and the two jobs that need configurations are the two most important jobs: the “Backup BizTalk Server” and the “DTA Purge and Archive“. This is nothing new and everybody knows!

However what many times we forget is that this 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 is too late. The result of that is… lots of times we just remember when the disks are full and everything stops to work!

You can solve this problem by implementing your custom sp_DeleteBackupHistory or by creating a Maintenance Plan to delete BizTalk Database Backups 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 so that it calls the sp_DeleteBackupHistoryAndFiles stored procedure instead of the sp_DeleteBackupHistory stored procedure.

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 really prefer to take advantage of all features that SQL gives me and create a maintenance plan with a Cleanup task to remove these files.

Implementing a Maintenance Plan to clean BizTalk Database backup’s files

Maintenance Plans allows DBA’s to have the flexibility to create a workflow to execute several tasks required to make sure 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 BizTalk Database backup’s files from our file system.

The main advantage of this approach is that will allow us more flexibility for further changes and we can also use them to other backup’s 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 the following 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 small files size and with the parameter “Autogrowth” set to:

  • Increase by 1 MB for the database file
  • And by 10% for 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 which hinders BizTalk Server database performance. 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 is 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 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 the occurrence of 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” propriety of BizTalk Windows Services, Enterprise Single Sign-On Service, Microsoft UDDI Notification Service, and the BAMAlerts service are set to “Automatic”, however some of this 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 this services:

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

The previous version of BizTalk Server also had this problem for:

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

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

BizTalk Server 2013 R2 Services

To proper 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 “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 “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 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

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 *

turbo360

Get integration tips every day. Follow us on:

Back to Top