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:
- 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
Despite being configured as “Automatic“, the Recovery properties are configured properly to restart the service after failures.
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.
- In the Services window, on the Services (Local) panel, select the service for example “BAMAlerts”, right-click and select “Properties” option.
- On the BAMAlerts Properties (Local Computer) window:
- On the General tab apply the following configuration.
- Startup type: “Automatic (Delayed Start)”
- On the General tab apply the following configuration.
- 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
- 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”
And should be configured as:
- Startup type: “Automatic”
- and First, Second and Subsequent failures: “Restart the Service”
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
- 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
- BizTalk Server 2013 R2: Installation and Configuration – Important considerations before set up the server (Part 1)
- BizTalk Server 2013 R2: Installation and Configuration – Install Windows Identity Foundation (WIF) (Part 3)
- BizTalk Server 2013 R2: Installation and Configuration – Install and configure SMTP Server Feature (Part 4)
- BizTalk Server 2013 R2: Installation and Configuration – Install Microsoft Office Excel 2013 (Part 5)
- BizTalk Server 2013 R2: Installation and Configuration – Install Visual Studio 2013 (Part 6)
- BizTalk Server 2013 R2: Installation and Configuration – Install SQL Server 2014 (Part 7)
- BizTalk Server 2013 R2: Installation and Configuration – Configure SQL Server Database Mail feature (Part 8)
- BizTalk Server 2013 R2: Installation and Configuration – Install and Configure BizTalk Server 2013 R2 (Part 9)
- BizTalk Server 2013 R2: Installation and Configuration – Configure SQL Server Network Configuration protocols (Part 10)
- BizTalk Server 2013 R2: Installation and Configuration – Validate Mail account used by BizTalk to send BAM Alerts (Part 11)
- BizTalk Server 2013 R2: Installation and Configuration – Installing BizTalk Adapter Pack (Part 12)
- BizTalk Server 2013 R2: Installation and Configuration – Install and Configure Microsoft UDDI Services (Part 13)
- BizTalk Server 2013 R2: Installation and Configuration – Install and Configure the Microsoft BizTalk ESB Toolkit (Part 14)
- BizTalk Server 2013 R2: Installation and Configuration – Configure BizTalk Server SQL Jobs (Part 15)
- BizTalk Server 2013 R2: Installation and Configuration – Optimize the BizTalk Server 2013 R2 environment (Part 17)
- BizTalk Server 2013 R2: Installation and Configuration – Optimize the BizTalk Server 2013 R2 environment (Part 18)
- BizTalk Server 2013 R2: Installation and Configuration – Install additional Developer tools (Part 19)