Optimizing Filegroups for the Databases

File input/output (I/O) contention is frequently a limiting factor, or bottleneck, in a production BizTalk Server environment. BizTalk Server is a very database intensive application and in turn, the SQL Server database used by BizTalk Server is very file I/O intensive. This topic describes how to make optimal use of the files and filegroups feature of SQL Server to minimize the occurrence of file I/O contention and improve the overall performance of a BizTalk Server solution.

Every BizTalk Server solution will eventually encounter file I/O contention as throughput is increased. The I/O subsystem, or storage engine, is a key component of any relational database. A successful database implementation typically requires careful planning at the early stages of a project. This planning should include consideration of the following issues:

  • What type of disk hardware to use, such as RAID (redundant array of independent disks) devices. For more information about using a RAID hardware solution, see “About Hardware-based solutions” in the SQL Server 2005 Books online at http://go.microsoft.com/fwlink/?LinkID=113944.

  • How to apportion data on the disks using files and filegroups. For more information about using files and filegroups in SQL Server 2005, see “Using Files and Filegroups” in the SQL Server 2005 Books online at http://go.microsoft.com/fwlink/?LinkID=69369 and “Understanding Files and Filegroups” in the SQL Server Books online at http://go.microsoft.com/fwlink/?LinkID=96447.

  • Implementing the optimal index design for improving performance when accessing data. For more information about designing indexes, see “Designing Indexes” in the SQL Server 2005 books online at http://go.microsoft.com/fwlink/?LinkID=96457.

  • How to set SQL Server configuration parameters for optimal performance. For more information about setting optimal configuration parameters for SQL Server, see “Optimizing Server Performance” in the SQL Server Books online at http://go.microsoft.com/fwlink/?LinkID=71418.

One of the primary design goals of BizTalk Server is to ensure that a message is never lost. In order to mitigate the possibility of message loss, messages are frequently written to the MessageBox database as the message is processed. When messages are processed by an Orchestration, the message is written to the MessageBox database at every persistence point in the orchestration. These persistence points cause the MessageBox to write the message and related state to physical disk. At higher throughputs, this persistence can result in considerable disk contention and can potentially become a bottleneck.

Making optimal use of the files and filegroups feature in SQL Server has been shown to effectively address File IO bottlenecks and improve overall performance in BizTalk Server solutions. This optimization should only be done by an experienced SQL Server database administrator and only after all BizTalk Server databases have been properly backed up. This optimization should be performed on all SQL Server computers in the BizTalk Server environment.

SQL Server files and filegroups can be utilized to improve database performance because this functionality allows a database be created across multiple disks, multiple disk controllers, or RAID (redundant array of independent disks) systems. For example, if your computer has four disks, you can create a database that is made up of three data files and one log file, with one file on each disk. As data is accessed, four read/write heads can concurrently access the data in parallel. This speeds up database operations significantly. For more information about implementing hardware solutions for SQL Server disks, see “Database Performance” in the SQL Server Books online at http://go.microsoft.com/fwlink/?LinkID=71419.

Additionally, files and filegroups enable data placement, because tables can be created in specific filegroups. This improves performance, because all file I/O for a given table can be directed at a specific disk. For example, a heavily used table can be placed on a file in a filegroup, located on one disk, and the other less heavily accessed tables in the database can be located on different files in another filegroup, located on a second disk.

File IO bottlenecks are discussed in considerable detail in the topic Bottlenecks in the Database Tier. The most common indicator that File I/O (Disk I/O) is a bottleneck is the value of the “Physical Disk:Average Disk Queue Length” counter. When the value of the “Physical Disk:Average Disk Queue Length” counter is greater than about 3 for any given disk on any of the SQL Servers, then file I/O is likely a bottleneck.

If applying file or filegroup optimization doesn't resolve a file I/O bottleneck problem, then it may be necessary to increase the throughput of the disk subsystem by adding additional physical or SAN drives.

This topic describes how to manually apply file and filegroup optimizations but these optimizations can also be scripted. A sample SQL script is provided at the end of this topic. It is important to note that this script would need to be modified to accommodate the file, filegroup, and disk configuration used by the SQL Server database(s) for any given BizTalk Server solution.

Cc594529.note(en-US,BTS.10).gifNote
This topic describes how to create multiple files and filegroups for the BizTalk MessageBox database. For an exhaustive list of recommended files and filegroups for all of the BizTalk Server databases, see Appendix B of the excellent "BizTalk Server Database Optimization" whitepaper available at http://go.microsoft.com/fwlink/?LinkID=101578.

Depending on which features are enabled when configuring BizTalk Server, up to 13 different databases may be created in SQL Server and all of these databases are created in the default file group. The default filegroup for SQL Server is the PRIMARY filegroup unless the default filegroup is changed by using the ALTER DATABASE command. The table below lists the databases that are created in SQL Server if all features are enabled when configuring BizTalk Server.

BizTalk Server Databases

Database

Default Database Name

Description

Configuration database

BizTalkMgmtDb

The central meta-information store for all instances of BizTalk Server in the BizTalk Server group.

BizTalk MessageBox database

BizTalkMsgBoxDb

Stores subscriptions predicates. It is a host platform, and keeps queues and state tables for each BizTalk Server host. The MessageBox database also stores the messages and message properties.

BizTalk Tracking database

BizTalkDTADb

Stores business and health monitoring data tracked by the BizTalk Server tracking engine.

BAM Analysis database

BAMAnalysis

SQL Server Analysis Services database that keeps the aggregated historical data for Business Activities.

BAM Star Schema database

BAMStarSchema

Transforms the data collected from Business Activity Monitoring for OLAP Processing. This database is required when using the BAM Analysis database.

BAM Primary Import database

BAMPrimaryImport

Stores the events from Business Activities and then queries for the progress and data after activity instances. This database also performs real-time aggregations.

BAM Archive database

BAMArchive

Stores subscription predicates. The BAM Archive database minimizes the accumulation of Business Activity data in the BAM Primary Import database.

SSO database

SSODB

Securely stores the configuration information for receive locations. Stores information for SSO affiliate applications, as well as the encrypted user credentials to all the affiliate applications.

Rule Engine database

BizTalkRuleEngineDb

Repository for:

  • Policies, which are sets of related rules.

  • Vocabularies, which are collections of user-friendly, domain-specific names for data references in rules.

Base EDI database

BizTalkEDIDb

Stores EDI document tracking and processing data.

Human Workflow Services Administration database

BizTalkHwsDb

Stores administrative information required by the BizTalk Human Workflow Services.

Trading Partner Management database

TPM

Stores trading partner data for Business Activity Services (BAS).

Tracking Analysis Server Administration database

BizTalkAnalysisDb

Stores both business and health monitoring OLAP cubes.

As noted above, a default BizTalk Server configuration places the MessageBox Database into a single file in the default filegroup. By default, the data and transaction logs for the MessageBox database are placed on the same drive and path. This is done to accommodate systems with a single disk. A single file/filegroup/disk configuration is not optimal in a production environment. For optimal performance, the data files and log files should be placed on separate disks.

Cc594529.note(en-US,BTS.10).gifNote
Log files are never part of a filegroup. Log space is managed separately from data space.

The main source of contention in most BizTalk Server solutions, either because of disk I/O contention or database contention, is the BizTalk Server MessageBox database. This is true in both single and multi-MessageBox scenarios. It is reasonable to assume that as much as 80% of the value of distributing BizTalk databases will be derived from optimizing the MessageBox data files and log file. The sample scenario detailed below is focused on optimizing the data files for a MessageBox database. These steps can then be followed for other databases as needed, for example, if the solution requires extensive tracking, then the Tracking database can also be optimized.

This section describes the steps that can be followed to manually add files to the MessageBox database. In this example three filegroups are added and then a file is added to each filegroup to distribute the files for the MessageBox across multiple disks. In this example, the steps are performed on SQL Server 2005.

  1. Click Start, point to Programs, point to Microsoft SQL Server 2005, and then click SQL Server Management Studio to display the Connect to Server dialog box.

    SQL Server 2005 Administration login screen
  2. Enter the name of the SQL Server instance that houses the BizTalk Server MessageBox databases in the Server name edit box of the Connect to Server dialog box, and click the Connect button to display the SQL Server Management Studio dialog box. In the Object Explorer pane of SQL Server Management Studio, expand Databases to view the databases for this instance of SQL Server.

    SQL Server 2005 Management Studio, Object Explorer
  3. Right-click the database for which to add the files, and then click Properties to display the Database Properties dialog box for the database.

    SQL Server 2005 Database Properties dialog box
  4. In the Database Properties dialog box, select the Filegroups page. Click the Add button to create additional filegroups for the BizTalkMsgBoxDb databases. In the example below, three additional filegroups are added.

    SQL Server 2005, adding filegroups to a database
  5. In the Database Properties dialog box, select the Files page.

    Click the Add button to create additional files to add to the filegroups, and then click OK. The MessageBox database is now distributed across multiple disks, which will provide a significant performance advantage over a single disk configuration.

    In the example below, a file is created for each of the filegroups that were created earlier and each file is placed on a separate disk.

    SQL Server 2005, adding files to a filegroup

The sample SQL script below performs the same tasks that were completed manually in the previous section. This sample script assumes the existence of distinct logical drives G through J. The script creates filegroups and files for each filegroup and places the log files on the J drive.

Cc594529.note(en-US,BTS.10).gifNote
Because SQL Server writes to its log files sequentially, there is no performance advantage realized by creating multiple log files for a SQL Server database.

-- Filegroup changes are made using the master database
USE [master]
GO

-- Script-wide declarations
DECLARE @CommandBuffer nvarchar(2048)
DECLARE @FG1_Path nvarchar(1024)
DECLARE @FG2_Path nvarchar(1024)
DECLARE @FG3_Path nvarchar(1024)
DECLARE @Log_Path nvarchar(1024)

-- Set the default path for all filegroups
SET @FG1_Path = N'G:\BizTalkMsgBoxDATA\'
SET @FG2_Path = N'H:\BizTalkMsgBoxDATA\'
SET @FG3_Path = N'I:\BizTalkMsgBoxDATA\'
SET @Log_Path = N'J:\BizTalkMsgBoxLog\'

ALTER DATABASE [BizTalkMsgBoxDb] ADD FILEGROUP [BTS_MsgBox_FG1]
SET @CommandBuffer = N'ALTER DATABASE [BizTalkMsgBoxDb] ADD FILE ( NAME = N''BizTalkMsgBoxDb_FG1'', FILENAME = N''' + @FG1_Path + 
N'BizTalkMsgBoxDb_FG1.ndf'' , SIZE = 102400KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10240KB ) TO FILEGROUP [BTS_MsgBox_FG1]'
EXECUTE (@CommandBuffer)

ALTER DATABASE [BizTalkMsgBoxDb] ADD FILEGROUP [BTS_MsgBox_FG2]
SET @CommandBuffer = N'ALTER DATABASE [BizTalkMsgBoxDb] ADD FILE ( NAME = N''BizTalkMsgBoxDb_FG1'', FILENAME = N''' + @FG2_Path + 
N'BizTalkMsgBoxDb_FG2.ndf'' , SIZE = 102400KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10240KB ) TO FILEGROUP [BTS_MsgBox_FG2]'
EXECUTE (@CommandBuffer)

ALTER DATABASE [BizTalkMsgBoxDb] ADD FILEGROUP [BTS_MsgBox_FG3]
SET @CommandBuffer = N'ALTER DATABASE [BizTalkMsgBoxDb] ADD FILE ( NAME = N''BizTalkMsgBoxDb_FG1'', FILENAME = N''' + @FG3_Path + 
N'BizTalkMsgBoxDb_FG3.ndf'' , SIZE = 102400KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10240KB ) TO FILEGROUP [BTS_MsgBox_FG3]'
EXECUTE (@CommandBuffer)

ALTER DATABASE [BizTalkMsgBoxDb] MODIFY FILE ( NAME = N'BizTalkMsgBoxDb_log', SIZE = 10240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10240KB )

GO -- Completes the previous batch, as necessary

The sample SQL script below could be used to set a particular filegroup as the default filegroup:

USE [BizTalkMsgBoxDb]
GO
declare @isdefault bit
SELECT @isdefault=convert(bit, (status & 0x10)) FROM sysfilegroups WHERE groupname=N'BTS_MsgBox_FG1'
if(@isdefault=0)
ALTER DATABASE [BizTalkMsgBoxDb] MODIFY FILEGROUP [BTS_MsgBox_FG1] DEFAULT
GO

The advantage to scripting is that scripts can perform multiple tasks quickly, can be reproduced precisely, and reduce the possibility of human error. The disadvantage of scripting is that the execution of an incorrectly written script can potentially cause serious problems that could require the BizTalk Server databases to be re-configured from scratch. Therefore, it is of utmost importance that SQL scripts such as the sample script listed in this topic are thoroughly tested before being executed in a production environment.

Show: