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

Posted: January 22, 2015 in BizTalk
Tags: , , , ,

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 needs configuration 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 stop to work!

You can solve this problem by implementing you 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 to change 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 this files.

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

Maintenance Plans allows DBA’s to have flexibility to create a workflow to execute several tasks required to make sure that database 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 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 as “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

153-BizTalk-Server-2013-R2-Services-automatic

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

154-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 in “View local services” option rom the Search menu.

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

153-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)”

153-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

153-BizTalk-Server-2013-R2-Services-Bam-Alerts-recovery

    • Click “OK” to apply the changes and close the window
  • Repeat the steps for the other services.

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 configure as “Take No Action”

171-BizTalk-Server-2013-R2-SQL-Services

And should be configured as:

  • Startup type: “Automatic”
  • and First, Second and Subsequent failures: “Restart the Service”

172-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 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
Comments
  1. Eliassal says:

    Wonderful learning and help materials Sandro

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s