Export (0) Print
Expand All

BizTalk Server Database Optimization

Applies To:

  • Microsoft® BizTalk® Server 2006 and BizTalk Server 2006 R2 with Microsoft SQL Server™ 2005 and SQL Server 2000
  • BizTalk Server 2004 with SQL Server 2000
  • Internet Information Services (IIS) 5.x and 6.x

Published: August 2007

Author: John B. Brockmeyer, P.E.

Acknowledgements: Many thanks to Marty Wasznicky, Tim Wieman, Clint Huffman, and Lee Graber (all from Microsoft), and my fellow Principal Consultants from Neudesic Tom Canter and Brian Loesgen for reviewing this document and their many fine suggestions.

This paper details techniques for optimizing the infrastructure of a BizTalk Server system. The goal is to increase the throughput and reduce the latency of the system, maximizing return on invested hardware and software capital.

The listed techniques have been employed on systems around the world and have yielded significant performance gains in both throughput (30-300%) and latency reduction (50% or more). Most of them are free (except for the cost of the time to make the prescribed changes).

All of the techniques have been tested and documented in the BizTalk Server performance lab in Redmond, and the results of that work are included in Appendix A. The lab test environment provides relative performance documentation; the degree of improvement for individual systems will vary widely based on their configuration, load characteristics, and other factors. Nonetheless, the data provided in Appendix A (and summarized for each tuning section below) can be used to justify implementation in mission critical and other politically “difficult” environments.

There are different types and levels of skills required to implement these changes, which range from Windows® registry tweaks to SAN (or other storage infrastructure) reconfiguration. For example, a database administrator with significant knowledge of operations is recommended for changes to the SQL Server changes. Some of the documented changes are “dangerous”, meaning that they should not be attempted by inexperienced users as there is a palpable risk of rendering a system unusable or worse, unstable. The level of difficulty and risk of each change is indicated using a three-tiered scale:

Symbol Difficulty Level Experience Required
Bb743398.bc4735f2-016f-460b-a7bc-1997b2a9c493(en-US,BTS.10).gif

Easy

Ordinary system-level experience necessary (registry values, etc.).

Bb743398.a43ae13c-8534-4b1e-b5f7-15a13a356660(en-US,BTS.10).gif

Medium

Moderate, application-specific experience recommended (e.g., SQL DBA).

Bb743398.d9b86b0c-14f9-4321-9184-c4a9176ed43c(en-US,BTS.10).gif

Difficult

Should only be performed with exceptional care by a technician with very advanced, domain-specific experience.

The recommendations are divided into groups roughly following common lines of responsibility within large corporations, and arranged in the order they should be implemented.

A word of caution. It’s important to understand each technique, both what it does and why it works. Blind application of the included scripts, registry files, etc. is not recommended and could lead to serious consequences for the target system. A good cook understands why the ingredients are used rather than just following the recipe.

Also be very careful when setting expectations for performance improvements. Because of the large number of variables affecting performance (especially load characteristics), it is impossible to predict exact results proceeding from an individual adjustment; it is wise to avoid making specific predictions if at all possible.

Many of the adjustments and recommendations, especially those which are included in part because they are Windows Best Practices, will only affect performance under certain conditions such as high memory use and will have little or no effect under “normal” loading conditions. Moreover, those that introduce or increase processing parallelism predominately increase throughput rather than reduce the latency for an individual document or transaction. Depending on the adjustment and the type of operations being performed, individual users may or may not perceive a performance difference; in a well-tuned environment a user’s perception of the system’s responsiveness should remain relatively constant regardless of loading conditions.

The goal of the testing associated with this paper was to isolate, document, and enable the specific tuning techniques listed. There are many other excellent papers regarding tuning of BizTalk Server host threading, adding multiple message boxes, etc., each of which details the performance implications of their respective subjects. That work was not duplicated here. Rather, think of the techniques listed here as the base platform – a “multiplier” of higher (generally application) level tuning. They can and have been successfully applied to servers outside the BizTalk Server environment. For example, it’s very common that a BizTalk Server system will “overrun” the systems with which it communicates. Often the BizTalk Server system is then throttled, either manually or automatically. But by applying the techniques listed to other Windows servers that are affiliated with the BizTalk Server system, it is possible to bring their performance up rather than holding BizTalk Server’s down.

Finally, optimization is not a one-time endeavor. Application requirements change, new applications are added, load characteristics morph over time. It is very important not only to regularly check the performance but to ensure the infrastructure is maintained at a high performance level. Apply new BIOS patches and upgrades. Make sure hyperthreading is disabled on servers running BizTalk Server and SQL Server 2005. Keep service packs and other patches up-to-date. When a new “problem” appears, employ the bottom-up methodology to ensure the correct issue is being addressed. The anti-virus profile may have been changed and database files are now being scanned on every modification. Or perhaps those database files auto-grew and are now fragmented. Or the number of locks required to support all of the deployed applications has grown. The point is that infrastructure issues, while not always easy to detect, are often simple fixes that have a disproportionately large performance impact.

Tuning is a bit more art than science. No single tuning technique solves every problem, and very often techniques interact. The approach should always be methodical, including baseline and differential testing particularly when justifying capital expenditures.

Performance limitations should be viewed as a series of roadblocks. Removing one roadblock doesn’t clear the road, but simply makes it easier to drive. Not until all of the roadblocks have been removed does performance truly jump. On a recent project a customer went through the entire list of options described below, save one. Performance improved modestly, some 30% or so. But when the last roadblock was removed, the system performance immediately doubled. On the identically configured UAT (User Acceptance Test) environment they did the “obvious”; they started with the last item, the “one that made the most difference”. To their surprise, removing that obstacle made only a small difference in performance. Again, it was not until they executed the entire list that the UAT environment achieved the performance of the production system. While this pattern won’t always be the case, there is a strong argument to implement all of the recommended adjustments, and to the greatest extent possible, to achieve maximum benefit. Similarly, do not be discouraged if the first recommendation implemented doesn’t double the speed of the system. Other roadblocks are likely blocking the way.

Recommended Methodology

Begin with an “inventory” of the current system. Even for a new installation if it was created from (corporate) standard images. Various applications and system utilities (e.g., Anti-Virus software) make changes to the registry to support their operations. An adjustment calculated for performance improvement may have already been made by another installation, in fact, simply applying the adjustment as part of a registry file (.reg) could actually de-tune the system. Remember, too, that there are no “undo” operations for individual registry changes (not withstanding registry checkpoints). A careful inventory creates a rollback position if one or more changes show adverse effects.

Tuning should generally be performed from the bottom up. As engineers, it’s very tempting to attempt to target individual bottlenecks for performance improvement and attack them on an ad hoc basis. And this methodology has its place; a badly written SQL query can’t be tuned performant. But very often performance problems are merely symptoms of underlying platform issues. For example, one organization had written a query joining nineteen tables which performed very poorly. Their developers tried the usual query tuning with little or no effect, and then began a major rewrite effort to “pre-fetch” all of the information into a separate table using batch updates, etc. However, the “real” problem was neither their architecture nor their query (which had actually been well optimized). Instead, it was a single-threaded infrastructure design that serialized their entire database file I/O through a single data file. Simply changing the database implementation (covered below) allowed the very same query to run in 200 milliseconds instead of 20 seconds, irrespective of the number of parallel query executions. The cost was minimal (a SAN layout change) and the result spectacular.

Optimizing deployed applications and/or database content carries significant risk. Modified application bits usually must be recertified (a process that could span weeks of testing). And very often a change in one part of the application leads to problems (e.g., resource starvation) in another area. Changing applications absent a solid, highly performing infrastructure is usually a zero-sum game that ultimately proves futile. The changes merely mask problems instead of solving them. Far better results are obtained by increasing the size of the resource “pie” by introducing parallelism, expanding and pre-allocating resource pools, etc.; the alternative is to simply keep re-slicing it to accommodate competing and oft-changing application requirements.

The bottom-up methodology starts with the iron and works its way up to the applications as follows (in the recommended order of execution):

Approaches for each of the covered areas are detailed in this document. It’s not necessary to do everything to do anything, but it’s best to do as much as possible. Each section can stand on its own as a worthy project, including costs and benefits. The advantage of proceeding upwards through the list is minimizing the strong possibility of wasting time “fixing” a problem at a higher level when a simple lower level change would eliminate it. Nothing worse than spending a week troubleshooting an application level performance problem only to discover a simple BIOS patch (that should have been installed two months ago) is the culprit. Or that the anti-virus software profile was changed to scan all files with a particular extension, the one being used by a file splitter in a critical application that is suddenly having performance problems “for no reason”.

Testing

It is important to document the performance of the system both before and after each “section” of tuning. But testing is only effective when it means some basic requirements.

Accuracy and precision are the two most important concepts in testing, are frequently poorly understood, and even when understood are often confused. A precise test is one that is repeatable, that yields approximately the same results for the same input across multiple test runs. Precision testing is very hard to achieve in BizTalk Server (or other messaging-based) systems. Outside applications can have widely varied responses. The local system may have periodic, capacity draining scheduled jobs. Test apparatus often lacks the ability to inject messages at a consistent rate. Even basic network variations (e.g., competing load from other systems or applications) can cause an otherwise precise test to produce unusable results.

Accuracy is the degree to which a test reflects reality. The oft used analogy is that precision is the ability to hit the same spot with a dart over and over; accuracy is whether the spot is the bull's-eye or in the next room. For example, it is very important to test with adequate and representative load. But what defines adequate? A high transaction volume system can’t be tested by sending through a single transaction. But how many transactions are required? While it’s always desirable to tune a system so it passes performance requirements with room to spare (headroom), it’s equally important to ensure that the capital expenditures, potential disruption and uncertainty, and other side-effects of tuning changes justify the results when the requirement has already been met.

The usual method of testing is to create a baseline of the current system, make one or more adjustments, then retest and compare the results. But while performance testing isn’t functional testing, a system should be functionally sound before taking the baseline or generating the post-adjustment results. Processing errors can effectively invalidate the baseline and/or the post-change results, rendering them useless in terms of accuracy.

One other comment about testing, specifically regarding an issue that sometimes appears in BizTalk Server or affiliated systems, there is a significant difference between simultaneous and concurrent “users”. A good example is the phone company. Everyone with a phone is a concurrent user of the phone system simply by being connected to the network but only a small number of connections are simultaneously in use, that is, making a call at the same time. Or, while a web site may have many concurrent users (sessions), only a fraction of them is simultaneously requesting a page, posting back information, etc. For tests to be accurate, they must reflect the true ratio of concurrent to simultaneous users where applicable to the system being evaluated.

Appendix A has details on the testing performed on the recommendations below. It specifically addresses the testing issues raised in this section. There is also an excellent white paper, available for download at the BizTalk Server site, titled “Managing a Successful Performance Lab” (http://go.microsoft.com/fwlink/?LinkId=98734); it contains many helpful suggestions that can also be applied outside the lab.

Base Hardware Platform Tuning

Hardware manufacturers (Dell, IBM, HP, etc.) regularly release updates to BIOS, firmware, and drivers. Not all of these need be immediately installed, but many are critical updates that resolve performance and/or availability issues. Network driver and firmware updates in particular often add tuning parameters that tie directly to higher performance; BIOS and SAN drivers and firmware often incorporate performance improvements as well.

It is critical that hyperthreading be turned off for BizTalk Servers. This is a BIOS setting, usually found in the Processor section of the BIOS setup. Hyperthreading makes the server appear to have more processors/processor cores than it actually does; hyperthread processors typically provide between 20 and 30% of the performance of a physical processor/processor core. BizTalk Server counts the (apparent) number of processors and adjusts its self-tuning algorithms accordingly; the “false” processors cause these adjustments to be skewed and are actually detrimental to performance.

It has been reported (for example http://go.microsoft.com/fwlink/?LinkId=96724) that hyperthreading can also reduce the performance of SQL Server 2005. If hyperthreading is to be used with SQL Server applications, it should be performance tested to ensure it is actually improving performance.

Windows Tuning – (10-20% or Greater Performance Improvement)

Microsoft Windows Server® comes out of the box configured as a file server, and a relatively low-bandwidth one at that. The TCP/IP stack is single threaded, the GUI has at least some of the high-presentation (and high resource consumption) features enabled, etc. Presumably this is the most common usage of the platform, but interestingly enough nearly all of the layered server applications (e.g., BizTalk Server, SQL Server, IIS, and Windows SharePoint® Services) are intended for exactly the opposite paradigm. For those, Windows Server should be configured for network applications, high network throughput and parallelism, and no kernel-hogging “fancy” graphics. Realigning Windows for network operations can yield significant performance results with no other cost than a reboot.

Disk Infrastructure

Somewhere in the world there is guidance advising creation of a three-disk RAID 5 configuration that is then Windows partitioned into separate logical volumes for the SYSTEM drive, PAGEFILE, and DATA drives; it must exist because it is impossible that so many systems could be configured this way by accident. Under no circumstances should this be the configuration for a high-performance server. In fact, the terms high-performance and RAID 5 are oil and water; they just don’t mix. For more details on why RAID 5, or any RAID that doesn’t “start” with 1 (e.g., RAID 1, RAID 1+0, etc.) should be avoided at all costs, consult Battle Against Any Raid Five. This site lists all of the ignoble ways RAID 5 steals system performance, as much as 20% or more. If the urge (aka, “corporate policy”) to use RAID 5 is too strong to resist, use the hardware controller to carve the requisite number of drives from the available storage as separate LUNs rather than using Windows partitions. This way the configuration will violate only two rather than all three of the Windows best practices for system partitions (especially those for the PAGEFILE):

  1. Put the PAGEFILE on a separate physical volume.
  2. Avoid RAID 5 or software RAID 1 (H/W RAID 1 mirrors, for availability, are acceptable and encouraged for High Availability environments).
  3. Avoid Windows partitions.

Many corporations create separate logical drives for the Windows O/S (managed by one group) and application software (managed by one or more other groups). This does not by itself present a significant performance issue, so long as the Windows PAGEFILE is not placed on one of the partitions but rather a separate local (not SAN/NAS) physical spindle or mirror set. Be sure there are sufficient drive letters left to perform the remainder of the SQL Server infrastructure tuning (see below).

The suggested minimum configurations for local disks on various types of servers (these configurations all assume a SAN/NAS for the data disks, where needed):

  • BizTalk Server
    • SYSTEM
    • PAGEFILE
    • STREAMING (if used, otherwise omit; may also be combined with MAPPING)
    • MAPPING (if used, otherwise omit; may also be combined with STREAMING)
  • SharePoint Portal and/or Windows SharePoint Services/MOSS
    • SYSTEM
    • PAGEFILE
  • SQL Server/Analysis Services/Reporting Services
    • SYSTEM
    • PAGEFILE
  • IIS
    • SYSTEM
    • PAGEFILE
    • CONTENT (if serving web pages)

For BizTalk Servers that have applications with a heavy use of streaming XML, the temporary data store should be placed on a separate physical volume/mirror set. The same is true for BizTalk Servers that employ large maps. Both of these mechanisms currently use the GetTempPath functionality (searching, in order, the path specified by the TMP environment variable, the path specified by the TEMP environment variable, the path specified by the USERPROFILE environment variable, and the Windows directory). If a separate volume is used, the TMP environment variable for the Windows account context in which the application host runs must be changed to use that volume.

General Performance

There are a number of basic Windows Server settings that can be set using the Windows GUI. Defragmenting the PAGEFILE and pre-allocating the MFTs (Master File Tables) of each disk requires a third-party tool such as fully-enabled Diskeeper (rather than the Diskeeper Lite that comes with Windows Server). There are also some useful defragmenting tools (e.g., PageFileDefrag) available at the Microsoft SysInternals website.

PAGEFILE

Bb743398.11ee8508-206f-40ef-be8a-15e90f5faf68(en-US,BTS.10).gif
Difficulty Level Action Comment

Bb743398.bc4735f2-016f-460b-a7bc-1997b2a9c493(en-US,BTS.10).gif

Placed on a separate local (not SAN/NAS) Physical Drive

Do not place on a Windows partition

Bb743398.a43ae13c-8534-4b1e-b5f7-15a13a356660(en-US,BTS.10).gif

Defragmented (no more than two fragments, preferably one)

Requires a third party tool

Bb743398.bc4735f2-016f-460b-a7bc-1997b2a9c493(en-US,BTS.10).gif

Fixed size (not system managed)

Prevents future fragmentation

UI Performance

Bb743398.11ee8508-206f-40ef-be8a-15e90f5faf68(en-US,BTS.10).gif
Difficulty Level Action Comment

Bb743398.bc4735f2-016f-460b-a7bc-1997b2a9c493(en-US,BTS.10).gif

All accounts and default set to minimize UI impact

GUI impact is Windows kernel cycles, so minimize it

Server Options

Bb743398.11ee8508-206f-40ef-be8a-15e90f5faf68(en-US,BTS.10).gif
Difficulty Level Action Comment

Bb743398.bc4735f2-016f-460b-a7bc-1997b2a9c493(en-US,BTS.10).gif

Configure server to favor background services

This is a server, not a desktop

Bb743398.bc4735f2-016f-460b-a7bc-1997b2a9c493(en-US,BTS.10).gif

Configure server to favor Programs

Unless building a file server, favor programs over system cache

Network Options

Bb743398.11ee8508-206f-40ef-be8a-15e90f5faf68(en-US,BTS.10).gif
Difficulty Level Action Comment

Bb743398.bc4735f2-016f-460b-a7bc-1997b2a9c493(en-US,BTS.10).gif

Maximize data throughput for network applications

Unless building a file server

Disk Optimization

Bb743398.11ee8508-206f-40ef-be8a-15e90f5faf68(en-US,BTS.10).gif
Difficulty Level Action Comment

Bb743398.bc4735f2-016f-460b-a7bc-1997b2a9c493(en-US,BTS.10).gif

Defragment all disks (local and SAN/NAS)

Use a third party tool if more than a few disks

Bb743398.a43ae13c-8534-4b1e-b5f7-15a13a356660(en-US,BTS.10).gif

Defragment MFTs and pre-allocate to appropriate size

Defragment with Windows defragmenter, pre-allocate with a third-party tool

Anti-virus Software

Set the Anti-virus software to scan only on file open or creation, not modification.

Bb743398.11ee8508-206f-40ef-be8a-15e90f5faf68(en-US,BTS.10).gif
Difficulty Level Action Comment

Bb743398.a43ae13c-8534-4b1e-b5f7-15a13a356660(en-US,BTS.10).gif

Disable real-time scanning on SQL Server files (.mdf, .ndf, .ldf, .mdb)

Bb743398.a43ae13c-8534-4b1e-b5f7-15a13a356660(en-US,BTS.10).gif

Disable real-time scanning on non-executable file types in BizTalk Server file-drops (usually .XML, but can also be .csv, etc.)

Intrusion Detection

Network traffic scanning can cause connections to incorrectly fail, resulting in possible dropped messages and poor performance.

Bb743398.11ee8508-206f-40ef-be8a-15e90f5faf68(en-US,BTS.10).gif
Difficulty Level Action Comment

Bb743398.a43ae13c-8534-4b1e-b5f7-15a13a356660(en-US,BTS.10).gif

Disable network scanning between BizTalk Server and SQL Server

Bb743398.a43ae13c-8534-4b1e-b5f7-15a13a356660(en-US,BTS.10).gif

Disable network scanning between BizTalk Server and interface servers (e.g., WebsphereMQ)

Registry Settings

The following registry settings should be applied to the BizTalk Server, SQL, IIS, SharePoint Services, etc. according to the comments for each. Note the difficulty/risk indicator for each, and ensure the appropriate resource understands the ramifications of each setting accordingly.

HKLM\SYSTEM\CurrentControlSet\Control\ Session Manager\Memory Management

Bb743398.11ee8508-206f-40ef-be8a-15e90f5faf68(en-US,BTS.10).gif
Difficulty Level Value Name Value Name Comment

Bb743398.bc4735f2-016f-460b-a7bc-1997b2a9c493(en-US,BTS.10).gif

DisablePagingExecutive

1

Prevent Windows from paging itself out

Bb743398.a43ae13c-8534-4b1e-b5f7-15a13a356660(en-US,BTS.10).gif

SystemPages

0xFFFFFFFF

Mitigates heavy kernel usage

HKLM\System\CurrentControlSet\ Services\LanmanServer\Parameters

Bb743398.11ee8508-206f-40ef-be8a-15e90f5faf68(en-US,BTS.10).gif
Difficulty Level Value Name Value Name Comment

Bb743398.bc4735f2-016f-460b-a7bc-1997b2a9c493(en-US,BTS.10).gif

IRPStackSize

+10 (Decimal)

Use DWORD 0x20 if not present, DWORD 0x32 if heavy usage of administrative shares or anti-virus S/W

Bb743398.bc4735f2-016f-460b-a7bc-1997b2a9c493(en-US,BTS.10).gif

SizReqBuf

0x4000 (16384)

HKLM\SOFTWARE\Microsoft\ MSMQ\Parameters

Bb743398.11ee8508-206f-40ef-be8a-15e90f5faf68(en-US,BTS.10).gif
Difficulty Level Value Name Value Name Comment

Bb743398.a43ae13c-8534-4b1e-b5f7-15a13a356660(en-US,BTS.10).gif

IgnoreOSNameValidation

1

Set this value on clustered MSMQ servers that are not supporting NLB-balanced queues on multiple hosts. Allows access to an aliased clustered queue by the BizTalk Adapter. For more information, see http://support.microsoft.com/kb/899611.

HKLM\SYSTEM\CurrentControlSet\ Services\Tcpip\Parameters

Bb743398.11ee8508-206f-40ef-be8a-15e90f5faf68(en-US,BTS.10).gif
Difficulty Level Value Name Value Name Comment

Bb743398.bc4735f2-016f-460b-a7bc-1997b2a9c493(en-US,BTS.10).gif

DefaultTTL

0x40 (64)

Reduce the time dead packets stay on the network

Bb743398.bc4735f2-016f-460b-a7bc-1997b2a9c493(en-US,BTS.10).gif

EnablePMTUDiscovery

1

Beware of this if server is directly exposed to potential attackers (incoming web connections)

Bb743398.bc4735f2-016f-460b-a7bc-1997b2a9c493(en-US,BTS.10).gif

EnablePMTUBHDetect

1

Important if the system is using a SOAP or HTTP adapter and/or initiating web connections to other systems

Bb743398.bc4735f2-016f-460b-a7bc-1997b2a9c493(en-US,BTS.10).gif

TcpMaxDupAcks

2

Wait for two duplicate acknowledgements before resending packets with lagging sequence numbers

Bb743398.bc4735f2-016f-460b-a7bc-1997b2a9c493(en-US,BTS.10).gif

Tcp1323Opts

1

If experiencing high packet loss/retransmits, set to 3

Bb743398.bc4735f2-016f-460b-a7bc-1997b2a9c493(en-US,BTS.10).gif

SACKOpts

1

Selective Acknowledgements, VERY important for large TCP Window sizes

Bb743398.a43ae13c-8534-4b1e-b5f7-15a13a356660(en-US,BTS.10).gif

TcpWindowSize

(1Gb network only)

0x591C8 (365000)

Default is 17x MSS (Max Segment Size) for 100Mb

Bb743398.bc4735f2-016f-460b-a7bc-1997b2a9c493(en-US,BTS.10).gif

MaxFreeTcbs

0x5000 (20480)

Increase the number of TCP Control Blocks to eliminate delays when creating connections

Bb743398.bc4735f2-016f-460b-a7bc-1997b2a9c493(en-US,BTS.10).gif

TcpMaxSendFree

0xFFFF (65535)

Helps improve performance under high network loads

Bb743398.bc4735f2-016f-460b-a7bc-1997b2a9c493(en-US,BTS.10).gif

MaxHashTableSize

0xFFFF (65535)

Maximize the size of the TCP hash table (stores TCP connection information)

Bb743398.bc4735f2-016f-460b-a7bc-1997b2a9c493(en-US,BTS.10).gif

MaxUserPort

0xFFFF (65535)

Prevents port exhaustion

Bb743398.bc4735f2-016f-460b-a7bc-1997b2a9c493(en-US,BTS.10).gif

TcpTimedWaitDelay

0x1E (30)

Release unused ports as quickly as possible

Bb743398.a43ae13c-8534-4b1e-b5f7-15a13a356660(en-US,BTS.10).gif

GlobalMaxTcpWindowSize

Depends on port speed

0xFFFF (65535) for 100Mb, 0xFFFFFFFF for 1Gb networks

Bb743398.a43ae13c-8534-4b1e-b5f7-15a13a356660(en-US,BTS.10).gif

NumTCBTablePartitions

Depends on processor core count

Four per processor/processor core, do not include hyperthreading

Bb743398.a43ae13c-8534-4b1e-b5f7-15a13a356660(en-US,BTS.10).gif

TcpAckFrequency

Depends on port speed

5 for 100Mb, 13 for 1Gb - Requires Windows Server 2003 Hotfix 815230 if going to be set to zero - can also be set at the interface level if mixed speeds; only set for connections primarily processing data

Bb743398.a43ae13c-8534-4b1e-b5f7-15a13a356660(en-US,BTS.10).gif

SynAttackProtect

0

Set this on all servers of any type that are not directly exposed to the internet

HKLM\SYSTEM\CurrentControlSet\Services\AFD\Parameters

AFD is the Windows File Descriptor management – setting these values allows Windows to vary the size of the socket file descriptor backlog to accommodate “rushes” of connection attempts; it is also used as a TCP/IP stack hardening technique as it compensates for DOS (Denial Of Service) attacks that attempt to start a large number of false connections.

Bb743398.11ee8508-206f-40ef-be8a-15e90f5faf68(en-US,BTS.10).gif
Difficulty Level Value Name Value Name Comment

Bb743398.a43ae13c-8534-4b1e-b5f7-15a13a356660(en-US,BTS.10).gif

EnableDynamicBacklog

1

Bb743398.a43ae13c-8534-4b1e-b5f7-15a13a356660(en-US,BTS.10).gif

MinimumDynamicBacklog

0xc8 (200)

Bb743398.a43ae13c-8534-4b1e-b5f7-15a13a356660(en-US,BTS.10).gif

MaximumDynamicBacklog

0x4e20 (20000)

Bb743398.a43ae13c-8534-4b1e-b5f7-15a13a356660(en-US,BTS.10).gif

DynamicBacklogGrowthDelta

0x64 (100)

HKLM\CurrentControlSet\ Control\FileSystem

Bb743398.11ee8508-206f-40ef-be8a-15e90f5faf68(en-US,BTS.10).gif
Difficulty Level Value Name Value Name Comment

Bb743398.d9b86b0c-14f9-4321-9184-c4a9176ed43c(en-US,BTS.10).gif

ContigFileAllocSize

0x40 (64)

Adjusts the minimum contiguous file allocation to 64KB; this is generally only for database servers, and should be used with great caution as it can cause servers with heavy small file traffic to quickly fill disks. On the positive side, it significantly reduces fragmentation of data files by forcing larger contiguous allocations.

HKLM\SYSTEM\CurrentControlSet\ Control\SecurityProviders\SCHANNEL

Bb743398.11ee8508-206f-40ef-be8a-15e90f5faf68(en-US,BTS.10).gif
Difficulty Level Value Name Value Name Comment

Bb743398.d9b86b0c-14f9-4321-9184-c4a9176ed43c(en-US,BTS.10).gif

ServerCacheTime

Set according to instructions.

For instructions, see http://go.microsoft.com/fwlink/?LinkID=97967.

HKLM\CurrentControlSet\ Services\InetInfo\Parameters

Bb743398.11ee8508-206f-40ef-be8a-15e90f5faf68(en-US,BTS.10).gif
Difficulty Level Value Name Value Name Comment

Bb743398.d9b86b0c-14f9-4321-9184-c4a9176ed43c(en-US,BTS.10).gif

ObjectCacheTTL

Set according to instructions

For instructions, see http://go.microsoft.com/fwlink/?LinkID=97968.

HKLM\CurrentControlSet\Services\ lanmanserver\Parameters

Bb743398.11ee8508-206f-40ef-be8a-15e90f5faf68(en-US,BTS.10).gif
Difficulty Level Value Name Value Name Comment

Bb743398.a43ae13c-8534-4b1e-b5f7-15a13a356660(en-US,BTS.10).gif

DisableStrictNameChecking

1

Set these on all SQL Servers to allow access to SQL Server Analysis Services using Application Names (aliases)

HKLM\SYSTEM\CurrentControlSet\Control\Lsa

Bb743398.11ee8508-206f-40ef-be8a-15e90f5faf68(en-US,BTS.10).gif
Difficulty Level Value Name Value Name Comment

Bb743398.a43ae13c-8534-4b1e-b5f7-15a13a356660(en-US,BTS.10).gif

DisableLoopbackCheck

1

Set these on all SQL Servers to allow access to SQL Server Analysis Services using Application Names (aliases).

Example - 100Mb Network SQL Server Registry merge file

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa]
“DisableLoopbackCheck”=dword:00000001

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Network\Connections\StatMon]
"ShowLanErrors"=dword:00000001

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\Memory Management]
"DisablePagingExecutive"=dword:00000001
"SystemPages"=dword:ffffffff

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\AFD\Parameters]
"EnableDynamicBacklog"=dword:00000001
"MinimumDynamicBacklog"=dword:000000c8
"MaximumDynamicBacklog"=dword:00004e20
"DynamicBacklogGrowthDelta"=dword:00000064

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\lanmanserver\parameters]
"IRPStackSize"=dword:00000020
"SizReqBuf"=dword:00004000
“DisableStrictNameChecking”=dword:00000001

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters]
"DefaultTTL"=dword:00000040
"EnablePMTUBHDetect"=dword:00000001
"EnablePMTUDiscovery"=dword:00000001
"TcpMaxDupAcks"=dword:00000002
"Tcp1323Opts"=dword:00000001
"SACKOpts"=dword:00000001
"MaxFreeTcbs"=dword:00005000
"TcpMaxSendFree"=dword:0000ffff
"MaxHashTableSize"=dword:0000ffff
"MaxUserPort"=dword:0000ffff
"TcpTimedWaitDelay"=dword:0000001e
"GlobalMaxTcpWindowSize"=dword:0000ffff
"NumTCBTablePartitions"=dword:<4 * number of processors/processor-cores>
"TcpAckFrequency"=dword:00000005
"SynAttackProtect"=dword:00000000

Example - 1Gb Network SQL Server Registry merge file

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa]
“DisableLoopbackCheck”=dword:00000001

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Network\Connections\StatMon]
"ShowLanErrors"=dword:00000001

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\Memory Management]
"DisablePagingExecutive"=dword:00000001
"SystemPages"=dword:ffffffff

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\AFD\Parameters]
"EnableDynamicBacklog"=dword:00000001
"MinimumDynamicBacklog"=dword:000000c8
"MaximumDynamicBacklog"=dword:00004e20
"DynamicBacklogGrowthDelta"=dword:00000064

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\lanmanserver\parameters]
"IRPStackSize"=dword:00000020
"SizReqBuf"=dword:00004000
“DisableStrictNameChecking”=dword:00000001

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters]
"DefaultTTL"=dword:00000040
"EnablePMTUBHDetect"=dword:00000001
"EnablePMTUDiscovery"=dword:00000001
"TcpMaxDupAcks"=dword:00000002
"Tcp1323Opts"=dword:00000001
"SACKOpts"=dword:00000001
"MaxFreeTcbs"=dword:00005000
"TcpMaxSendFree"=dword:0000ffff
"MaxHashTableSize"=dword:0000ffff
"MaxUserPort"=dword:0000ffff
"TcpTimedWaitDelay"=dword:0000001e
"GlobalMaxTcpWindowSize"=dword:ffffffff
"NumTCBTablePartitions"=dword: <4 * number of processors/processor-cores>
"TcpAckFrequency"=dword:0000000D
"SynAttackProtect"=dword:00000000
"TcpWindowSize"=dword:000591C8

Example - 100Mb Network BizTalk Server Registry merge file

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Network\Connections\StatMon]
"ShowLanErrors"=dword:00000001

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\AFD\Parameters]
"EnableDynamicBacklog"=dword:00000001
"MinimumDynamicBacklog"=dword:000000c8
"MaximumDynamicBacklog"=dword:00004e20
"DynamicBacklogGrowthDelta"=dword:00000064

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\Memory Management]
"DisablePagingExecutive"=dword:00000001
"SystemPages"=dword:ffffffff

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\lanmanserver\parameters]
"IRPStackSize"=dword:00000020
"SizReqBuf"=dword:00004000
"DisableStrictNameChecking"=dword:00000001

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters]
"DefaultTTL"=dword:00000040
"EnablePMTUBHDetect"=dword:00000001
"EnablePMTUDiscovery"=dword:00000001
"TcpMaxDupAcks"=dword:00000002
"Tcp1323Opts"=dword:00000001
"SACKOpts"=dword:00000001
"MaxFreeTcbs"=dword:00005000
"TcpMaxSendFree"=dword:0000ffff
"MaxHashTableSize"=dword:0000ffff
"MaxUserPort"=dword:0000ffff
"TcpTimedWaitDelay"=dword:0000001e
"GlobalMaxTcpWindowSize"=dword:0000ffff
"NumTCBTablePartitions"=dword:<4 * number of processors/processor-cores>
"TcpAckFrequency"=dword:00000005
"SynAttackProtect"=dword:00000000

Example - Registry merge file

Use the following code for registry merge file to apply to any server connecting to SQL Server Analysis Services using an alias where the connection fails with a “Network Name not found” error (or similar).

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\lanmanserver\Parameters]
“DisableStrictNameChecking”=dword:00000001

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa]
“DisableLoopbackCheck”=dword:00000001

Example - 1Gb Network BizTalk Server Registry merge file

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Network\Connections\StatMon]
"ShowLanErrors"=dword:00000001

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\AFD\Parameters]
"EnableDynamicBacklog"=dword:00000001
"MinimumDynamicBacklog"=dword:000000c8
"MaximumDynamicBacklog"=dword:00004e20
"DynamicBacklogGrowthDelta"=dword:00000064

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\Memory Management]
"DisablePagingExecutive"=dword:00000001
"SystemPages"=dword:ffffffff

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\lanmanserver\parameters]
"IRPStackSize"=dword:00000020
"SizReqBuf"=dword:00004000
"DisableStrictNameChecking"=dword:00000001

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters]
"DefaultTTL"=dword:00000040
"EnablePMTUBHDetect"=dword:00000001
"EnablePMTUDiscovery"=dword:00000001
"TcpMaxDupAcks"=dword:00000002
"Tcp1323Opts"=dword:00000001
"SACKOpts"=dword:00000001
"MaxFreeTcbs"=dword:00005000
"TcpMaxSendFree"=dword:0000ffff
"MaxHashTableSize"=dword:0000ffff
"MaxUserPort"=dword:0000ffff
"TcpTimedWaitDelay"=dword:0000001e
"GlobalMaxTcpWindowSize"=dword:0000ffff
"NumTCBTablePartitions"=dword:<4 * number of processors/processor-cores>
"TcpAckFrequency"=dword:0000000D
"SynAttackProtect"=dword:00000000
"TcpWindowSize"=dword:000591C8

IIS Settings

IIS has many tunable parameters, and these are changed/enhanced with each new version of the server. The following is a set of performance and BizTalk Server related IIS parameters and their recommended values for BizTalk Server deployments. Some of the parameters are settable for IIS 5.0 only; these are indicated in the comment section.

Bb743398.note(en-US,BTS.10).gifNote
Many of these parameters are very important for secondary systems (those referenced by BizTalk Server), especially when web services are being used. If the BizTalk Servers are hosting web pages (not recommended), high message load can cause web timeouts and other conditions also addressed by modifying the values of these parameters.

ADSUtil Tool

Bb743398.11ee8508-206f-40ef-be8a-15e90f5faf68(en-US,BTS.10).gif
Difficulty Level Action Comment

Bb743398.a43ae13c-8534-4b1e-b5f7-15a13a356660(en-US,BTS.10).gif

ASPThreadGateEnabled

IIS 5.0 or earlier only: Allows IIS to self-tune based on current load. Test web content performance with this setting turned on.

Bb743398.a43ae13c-8534-4b1e-b5f7-15a13a356660(en-US,BTS.10).gif

ASPThreadGateLow

IIS 5.0 or earlier only. By default this value is 50%. Higher values may improve performance. Test values above 50 when ASPThreadGateEnabled is turned on.

Bb743398.a43ae13c-8534-4b1e-b5f7-15a13a356660(en-US,BTS.10).gif

AspProcessorThreadMax

For guidance on this setting, see http://support.microsoft.com/kb/238583/EN-US/.

Set per instructions to maximize processor usage. Be especially careful with this setting if hosting web content on a BizTalk Server, or the BizTalk application process(es) can become processor starved.

Bb743398.a43ae13c-8534-4b1e-b5f7-15a13a356660(en-US,BTS.10).gif

AspScriptEngineCacheMax

The default is 128, but higher values can improve performance.

Bb743398.a43ae13c-8534-4b1e-b5f7-15a13a356660(en-US,BTS.10).gif

AspScriptFileCacheSize

The goal for web page-based content is 90% cache hits. Monitor the ASP Cache Hit Ratio PerfMon stat to check the hit ratio, and experiment with increasing this value (increasing the cache size) to achieve the highest ratio possible based on the content.

Bb743398.a43ae13c-8534-4b1e-b5f7-15a13a356660(en-US,BTS.10).gif

MaxEndpointConnections

The default for this setting is 100. Set this value to 500 or higher if connect failures or blank screens occur when requesting web content. For more information about this setting, see http://support.microsoft.com/kb/229814/.

Bb743398.a43ae13c-8534-4b1e-b5f7-15a13a356660(en-US,BTS.10).gif

ServerListenBacklog

This setting is used with MaxEndpointConnections; the default value is based on the ServerSize parameter. If IIS connections fail try increasing this value. For more information about this setting, see http://support.microsoft.com/kb/q229814/.

Bb743398.a43ae13c-8534-4b1e-b5f7-15a13a356660(en-US,BTS.10).gif

ServerSize

Be sure this is set to a size appropriate to the load (the default is medium load). Test with a value of 2 (high usage) if appropriate.

Bb743398.note(en-US,BTS.10).gifNote
The value of this setting affects the default values for a number of other settings (see above).

Bb743398.a43ae13c-8534-4b1e-b5f7-15a13a356660(en-US,BTS.10).gif

HcDoDynamicCompression

Enable compression of dynamic web content at the request of the invoking web client; test performance with compression enabled.

Bb743398.a43ae13c-8534-4b1e-b5f7-15a13a356660(en-US,BTS.10).gif

HcDoStaticCompression

Test with compression enabled. Enable compression of static web content at the request of the invoking web client; test performance with compression enabled.

Bb743398.a43ae13c-8534-4b1e-b5f7-15a13a356660(en-US,BTS.10).gif

AppAllowDebugging

Set to False on production systems; debugging is very expenseive in terms of resources and also poses a significant security risk.

Bb743398.a43ae13c-8534-4b1e-b5f7-15a13a356660(en-US,BTS.10).gif

AspBufferingOn

Set this parameter to True Tuning. For more information, see http://go.microsoft.com/fwlink/?LinkID=97969.

Bb743398.a43ae13c-8534-4b1e-b5f7-15a13a356660(en-US,BTS.10).gif

AspQueueConnectionTestTime

Test higher value if connections are dropped prematurely. For more information, see http://go.microsoft.com/fwlink/?LinkID=97970.

Bb743398.a43ae13c-8534-4b1e-b5f7-15a13a356660(en-US,BTS.10).gif

AspRequestQueueMax

Increase this value if “Server Busy” messages are returned for web requests. This will often occur on secondary systems (systems referenced by BizTalk Server) but may also occur on BizTalk Server systems hosting web services or web pages (the latter is not recommended).

Bb743398.a43ae13c-8534-4b1e-b5f7-15a13a356660(en-US,BTS.10).gif

AspDiskTemplateCacheDirectory

By default this is set to %windir%\system32\inetsrv\ASP.

Change the value to move compiled content templates to the dedicated IIS content drive (see above).

Bb743398.a43ae13c-8534-4b1e-b5f7-15a13a356660(en-US,BTS.10).gif

AspSessionTimeout

Increase the default value if user sessions time out prematurely (this is not an issue for web service hosting and is only effective if sessions are being used).

Bb743398.a43ae13c-8534-4b1e-b5f7-15a13a356660(en-US,BTS.10).gif

ASPSessionMax

Set this to unlimited on dedicated IIS servers, but use care if hosting sessions on a BizTalk server.

Bb743398.a43ae13c-8534-4b1e-b5f7-15a13a356660(en-US,BTS.10).gif

ASPTrackThreadingModel

Set this to FALSE per Microsoft IIS Best Practices (http://go.microsoft.com/fwlink/?LinkId=98089).

If set to TRUE, be sure that all applications bind the FreeThreadedMarshaller class. A TRUE setting also consumes significant Windows system/kernel resources.

Bb743398.a43ae13c-8534-4b1e-b5f7-15a13a356660(en-US,BTS.10).gif

CacheISAPI

Set this to TRUE. BizTalk Server uses ISAPI for the http and SOAP interfaces. It will normally be turned on (the default) but “standard” images occasionally turn it off to conserve memory.

IIS MMC Snap-in Tool

Bb743398.11ee8508-206f-40ef-be8a-15e90f5faf68(en-US,BTS.10).gif
Difficulty Level Action Comment

Bb743398.a43ae13c-8534-4b1e-b5f7-15a13a356660(en-US,BTS.10).gif

Application Pool Accounts set to appropriate privilege levels

BizTalk Server creates many application pools depending on the configured services. Be sure the accounts for these have correct privileges, especially the pools associated with BAS.

Bb743398.a43ae13c-8534-4b1e-b5f7-15a13a356660(en-US,BTS.10).gif

Extraneous default pages removed

The IIS looks for Default.aspx, Default.asp, Index.htm, etc. Delete all but the one actually being used (may not be any of these).

Bb743398.note(en-US,BTS.10).gifNote
Some web sites don’t publish a default web page for security reasons.

Bb743398.a43ae13c-8534-4b1e-b5f7-15a13a356660(en-US,BTS.10).gif

Web Connection Limit set appropriately

Base this on the size of the expected load. Excess connections waste resources and are a potential security hole.

Bb743398.a43ae13c-8534-4b1e-b5f7-15a13a356660(en-US,BTS.10).gif

Process startup and shutdown time limits set appropriately to application type

The default shutdown timeout after which an ASP worker process is forcibly terminated is very low. Ensure that worker processes servicing long-running requests (e.g., database transactions) have an adequate shutdown time.

Bb743398.a43ae13c-8534-4b1e-b5f7-15a13a356660(en-US,BTS.10).gif

Number of ASP Worker Processes set to at least two

This provides redundancy in case a worker process crashes (the site/service doesn’t go down).

Bb743398.a43ae13c-8534-4b1e-b5f7-15a13a356660(en-US,BTS.10).gif

Application Pool Queue-Length Limit set appropriately

Set this to a higher value if 503 errors are returned for web page/service requests.

Bb743398.a43ae13c-8534-4b1e-b5f7-15a13a356660(en-US,BTS.10).gif

Keepalives Enabled set to TRUE

Discard dead connections quickly; this also helps mitigate DOS attacks.

Bb743398.a43ae13c-8534-4b1e-b5f7-15a13a356660(en-US,BTS.10).gif

ASP.NET version set to current .NET framework version

Make sure the ASP.NET version is set to 2.x for BizTalk Server 2006, and appropriately for all other services and web pages.

Example - Script to print current state of important IIS performance parameters

@ECHO OFF
ECHO.
SETLOCAL
IF "%1"=="" GOTO :DefaultPath
SET INetPubPath=%1%
:DefaultPath
SET INetPubPath=%SYSTEMDRIVE%
:CheckPath
IF EXIST "%INetPubPath%\inetpub\adminscripts\ADSUTIL.VBS" GOTO :GetSiteList
ECHO The specified inetpub path is invalid or the ADSUtil.vbs Utility script was not found in the adminscripts subdirectory
ECHO.
GOTO :Done
:GetSiteList
REM IF "%2"==""
ECHO Configured Web Sites
ECHO -----------------------------
CSCRIPT //NoLogo "%INetPubPath%\inetpub\adminscripts\ADSUTIL.vbs" ENUM W3SVC /P
ECHO.
REM CALL :PrintParameter ASPThreadGateEnabled
REM CALL :PrintParameter ASPThreadGateLow
CALL :PrintParameter ASPProcessorThreadMax
CALL :PrintParameter ASPScriptEngineCacheMax
CALL :PrintParameter ASPScriptFileCacheSize
CALL :PrintParameter MaxEndpointConnections
CALL :PrintParameter ServerListenBacklog
CALL :PrintParameter ServerSize
CALL :PrintParameter Filters/Compression/gzip/HcDoDynamicCompression
CALL :PrintParameter Filters/Compression/gzip/HcDoStaticCompression
CALL :PrintParameter AppAllowDebugging
CALL :PrintParameter ASPBufferingOn
CALL :PrintParameter ASPQueueConnectionTestTime
CALL :PrintParameter ASPRequestQueueMax
CALL :PrintParameter ASPDiskTemplateCacheDirectory
CALL :PrintParameter ASPSessionTimeout
CALL :PrintParameter ASPSessionMax
CALL :PrintParameter ASPTrackThreadingModel
CALL :PrintParameter CacheISAPI

:Done
ENDLOCAL
GOTO :EOF

:PrintParameter
ECHO %1%
ECHO -----------------------------
ECHO W3SVC 1> ASPParmTest.tmp
CSCRIPT //NoLogo "%INetPubPath%\inetpub\adminscripts\ADSUTIL.vbs" GET W3SVC\%1% 1>>ASPParmTest.tmp 2>NUL
IF %ERRORLEVEL%==0 TYPE ASPParmTest.tmp
ECHO. 1>ASPParmTest.tmp
ECHO W3SVC/1/ROOT 1>> ASPParmTest.tmp
CSCRIPT //NoLogo "%INetPubPath%\inetpub\adminscripts\ADSUTIL.vbs" GET W3SVC\1\ROOT\%1% 1>>ASPParmTest.tmp 2>NUL
IF %ERRORLEVEL%==0 TYPE ASPParmTest.tmp
DEL /Q /S ASPParmTest.tmp 1>NUL 2>NUL
@ECHO.
GOTO :EOF

Network Infrastructure Tuning (15-35% Performance Improvement)

When asked to provide high throughput networking for BizTalk Server systems, these are some of the common responses of network engineers:

  • “A 1Gb network should be enough bandwidth to handle any load.”
  • “The 100Mb network isn’t close to saturated, so a 1Gb network is unnecessary.”

Both of the above statements are true enough on their face, but answer the wrong (and hopefully unasked) questions. For example, while it’s true that 100Mb networks are rarely saturated, what’s required by BizTalk Server is speed of communications. Imagine two cars on a trip. One is traveling 10 mph and the other 100mph. Even though the road isn’t “saturated”, the second car is going to arrive at its destination 10 times earlier than the first.

Now consider the same two cars, this time driving at the same speed, but on a single lane highway. No matter how fast they go, the second car is always going to arrive at least one car-length behind the first. Add more cars, and the per-car arrival delay continues to increase. But take the same set of cars and put them on a TWO lane road, and the per-car delays are cut in half. On a three lane road, the delay is one-third that of the single lane road. And so forth. No matter how fast the speed limit, the single lane road is a serialization mechanism.

Similarly, a single network connection, no matter how fast, bit-wise serializes network traffic. Even though the bandwidth is sufficient to handle the total quantity of traffic, the per-packet delay increases significantly as the number of packets increases.

High network performance for Windows-based systems requires three things, buffering (preventing network congestion), offloading (removing the processing from the host CPUs) and parallelism (subnets). BizTalk Server, perhaps more than any other Microsoft product, is an abuser of networks because of its highly parallel nature and horizontal scalability. The following sections discuss in detail how to mitigate the network bottlenecks that can severely hamper BizTalk Server performance.

Network Device Settings

Windows Server has built-in network packet offloading, and top-end NICs (Network Interface Cards) include drivers that not only use this capability, but provide many other high-performance adjustments. Unfortunately, many if not all of these features are turned off by default because they consume memory from the paged or non-paged pools of the Windows kernel memory. Some of the registry settings from the previous section allow Windows to allocate kernel memory appropriately and hence enable use of these features to enhance network performance (again, the advantage of bottom-up tuning). What’s more, this performance enhancement is free, save the time to turn the features on and reboot the server. On highly loaded servers, it is not uncommon to see 20-50% network performance improvement simply by enabling these features. The tests performed for this project (see Appendix A) showed results of approximately 25% improvement.

Bb743398.note(en-US,BTS.10).gifNote
Adjust the network device drivers to maximize the amount of memory available for packet buffering, both incoming and outgoing. Also maximize buffer counts, especially transmit buffers/coalesce buffers. The default values for these parameters, and whether they are even provided, varies between manufacturers and driver versions.

The goal is to maximize the work done by the network interface card on-board hardware, and to allow the greatest possible buffer space for network operations to mitigate traffic bursts.

It is very important that the network speed, duplex, and flow control have fixed settings which correspond to the settings on the switch to which they are connected. This helps ensure the settings match and avoids periodic “auto-synchronization” that takes the connection off-line.

Bb743398.11ee8508-206f-40ef-be8a-15e90f5faf68(en-US,BTS.10).gif
Difficulty Level Setting Comment

Bb743398.bc4735f2-016f-460b-a7bc-1997b2a9c493(en-US,BTS.10).gif

Power Management Disabled

Do not allow the network card to be turned off to save power.

Bb743398.bc4735f2-016f-460b-a7bc-1997b2a9c493(en-US,BTS.10).gif

Fixed Speed/Duplex (do not use AUTO)

See Note preceding table.

Bb743398.bc4735f2-016f-460b-a7bc-1997b2a9c493(en-US,BTS.10).gif

Max Coalesce Buffers

See Note preceding table.

Bb743398.bc4735f2-016f-460b-a7bc-1997b2a9c493(en-US,BTS.10).gif

Max Transmit/Send Buffers

See Note preceding table.

Bb743398.bc4735f2-016f-460b-a7bc-1997b2a9c493(en-US,BTS.10).gif

Max Transmit/Send Descriptors

See Note preceding table.

Bb743398.bc4735f2-016f-460b-a7bc-1997b2a9c493(en-US,BTS.10).gif

Max Receive Buffers

See Note preceding table.

Bb743398.bc4735f2-016f-460b-a7bc-1997b2a9c493(en-US,BTS.10).gif

Max Receive Descriptors

See Note preceding table.

Bb743398.bc4735f2-016f-460b-a7bc-1997b2a9c493(en-US,BTS.10).gif

All offload options ON

See Note preceding table.

Bb743398.bc4735f2-016f-460b-a7bc-1997b2a9c493(en-US,BTS.10).gif

Wake On LAN disabled (unless being used)

See Note preceding table.

Bb743398.bc4735f2-016f-460b-a7bc-1997b2a9c493(en-US,BTS.10).gif

Flow Control Explicitly Set (TX and RX, not AUTO)

See Note preceding table.

Subnets

Subnetting is perhaps the least used yet most effective performance enhancement for a BizTalk Server system. Typical BizTalk Server deployments often include only one subnet for the entire system. Consider that this single subnet must carry the inbound message traffic, the traffic from BizTalk Server to SQL Server to store each message in the message box, the traffic for BizTalk Server to retrieve the message from the message box for orchestration or send operations, the traffic between the message box and the tracking database (in a multi-server environment), the traffic between the tracking database and the BAM (Business Activity Monitoring) database(s), etc. Not to mention the administration traffic, remote desktop traffic, backup traffic, download traffic (anti-virus definitions, etc.). Remembering that networks are effectively bitwise serialization mechanisms, small wonder that additions of subnets and maximizing subnet speeds can result in orders of magnitude performance improvement.

A recent TAP (Technology Adoption Program) lab for a large financial service customer was having difficulty meeting its target SLA (Service Level Agreement). The lab had many BizTalk Server and SQL Servers, distributing the processing load as prescribed in the various white papers on the subject, yet the individual servers were significantly underutilized. Unfortunately, the configuration was using a single subnet for all of its traffic, and the processing servers simply could not receive and send enough messages to stay busy. By adding subnets, and with no other changes to the configuration, a 300% performance gain was achieved and the SLA was met.

Whenever possible, all BizTalk Server subnets should be 1Gb or higher nominal speed. If only 100Mb capability is available, increasing the number of subnets can compensate for the slower nominal speed. Please note that subnetting is definitely not “free”; there is a cost for network cards, cabling, and possibly switch or other network device space. Plan subnets carefully, and with the exception of the DATA subnet, only request them when expecting larger message traffic loads. The DATA subnet is recommended for all BizTalk Server systems of any significant size. Lab testing for this project shows a close parallel between message traffic into BizTalk Server and resulting BizTalk Server to SQL Server traffic. The DATA subnet, dedicated between BizTalk Server and SQL servers, separates this traffic and allows parallel processing and significant performance improvement. For more information, see Appendix A.

Isolating different types of message traffic on separate subnets has several advantages. Traffic types with high performance requirements can be allocated increased bandwidth using teaming (multiple physical connections acting as a single, load-balanced subnet). Often different traffic types have varying QoS (Quality of Service) and high-availability requirements, which can also be easily addressed with multiple subnets.

The following table lists a number of possible subnets for high-performance BizTalk Server systems in the recommended order of implementation. After the DATA subnet, the next priority subnet can change based on the load characteristic of the system, particularly with respect to the load between system components (e.g., BizTalk Server to a backend server vs. BizTalk Server to an external message source/sink).

Bb743398.11ee8508-206f-40ef-be8a-15e90f5faf68(en-US,BTS.10).gif
Difficulty Level Subnet Name Purpose

Bb743398.bc4735f2-016f-460b-a7bc-1997b2a9c493(en-US,BTS.10).gif

DATA

Dedicated subnet between BizTalk Server and SQL Servers (1Gb recommended).

Bb743398.bc4735f2-016f-460b-a7bc-1997b2a9c493(en-US,BTS.10).gif

MGMT

Dedicated subnet to all servers to carry management traffic (Remote Desktop, downloads, etc.); this is separate from the remote management traffic carried by manufacturer-specific mechanisms such as HP iLO (Integrated Lights Out) or DELL Remote Management Services.

Bb743398.bc4735f2-016f-460b-a7bc-1997b2a9c493(en-US,BTS.10).gif

MSG

One or more dedicated subnets between BizTalk Server and various messaging servers/sources (MSMQ or WebsphereMQ, HL7, SWIFT, Web Services, etc.).

Bb743398.a43ae13c-8534-4b1e-b5f7-15a13a356660(en-US,BTS.10).gif

BACKEND

One or more dedicated subnets for communication with various “backend” servers; these may connect the backend servers to intermediate communications servers (e.g., WebsphereMQ) or may be direct connections into BizTalk Server.

Bb743398.a43ae13c-8534-4b1e-b5f7-15a13a356660(en-US,BTS.10).gif

BAM

Dedicated subnet to move traffic from BizTalk Server message and tracking databases to the BAM databases (multi-server configuration only).

Bb743398.bc4735f2-016f-460b-a7bc-1997b2a9c493(en-US,BTS.10).gif

PORTAL

Dedicated subnet to manage BAS, BAM, and other portal traffic.

A final note regarding subnets. If at all possible every BizTalk Server-connected subnet should be extended to each BizTalk server in the configuration. This technique facilitates easy reconfiguration of the system to accommodate additional host instances for greater processing, availability, recovery or other needs without requiring physical reconfiguration.

SQL Server Tuning (40-100% Performance Improvement)

BizTalk Server possibly puts more stress on a SQL Server deployment than any other application ever created. The stress is both memory and I/O based, and is exacerbated by the massively parallel, horizontally scalable architecture of the BizTalk Server product.

Many of SQL Server’s features are designed for “self-tuning”, for example data files “autogrow” by default, memory is dynamically managed by default, the size of the lock table is set automatically by default, etc. BizTalk Server functions in this paradigm but its performance is far from optimal, particularly with multiple BizTalk Server computers.

Pre-allocation and parallelism are the two “Ps” of tuning SQL Server for BizTalk Server support. Pre-allocation means that as many resources as possible are pre-configured to their observed maximum size, including memory, files, locks, etc. Parallelism, especially I/O parallelism, is implemented by multi-threading databases and enhancing system artifacts to take advantage of the highly-parallel SMP servers.

The techniques listed below can be used on any high-performance SQL Server implementation, and should certainly be used for application databases that directly or indirectly support BizTalk Server processes. Because of its highly scalable nature, BizTalk Server commonly overruns the systems with which it interfaces; these systems should be optimized as much as possible to improve the performance of the entire system. The lab work done in support of this document did not include application databases on either local or remote systems because of the imprecision they introduce into the measurement process; in field deployments tuning applied to those databases often yields large performance improvements.

Many of the techniques listed are “free”, that is, they require no additional hardware or software to implement. The exception is the manipulation of database storage, which may require additional disk capacity and can take a significant amount of time to implement well, particularly for application (non-BizTalk Server) databases.

Use of these techniques improved performance in the tests for this project by just over 30%, but many field systems have demonstrated higher returns on investment in this type of tuning, some as much as 100%. A small number of very high-performance systems have required this tuning, especially lock table tuning, to function at all.

Memory Management

Pre-allocating SQL Server memory has many advantages. The overhead of dynamic management, particularly under high stress growth, is avoided. Pre-allocated memory can be significantly less fragmented than memory that grows in small increments. In situations where multiple SQL services are installed (e.g., the SQL Server database engine running alongside Analysis Services, Reporting Services and/or Notification Services) it is essential to limit the amount of memory taken by SQL Server. There are three types of memory allocations important to BizTalk Server with SQL Server systems, locks, cache, and the SQL Server process working set.

Locks

Setting the lock table (rather than allowing it to grow dynamically) often improves performance more than any other memory pre-allocation. BizTalk Server systems commonly generate 500,000 or more lock requests per second. The number of requests increases considerably as additional BizTalk servers are deployed in a system. The default number of locks for SQL Server is 5000, which is adequate and/or easily grown for many types of applications. But the path from 5000 to the allocation required to support BizTalk Server-generated loads is not a pretty one, particularly considering the intensity with which the lock requests are made. BizTalk Server systems often experience excessive lock waits and timeouts as the database engine struggles to keep up with the demand.

To avoid these issues, pre-allocate the lock table. This is not a risk-free strategy. SQL Server treats the pre-allocation as a fixed size rather than a base value from which to grow. As applications and load are added to the BizTalk Server system, the number of lock requests typically grows and the pre-allocation needs to be resized accordingly; this implies monitoring of the SQL Server lock performance counters (see Appendix A).

Lock pre-allocation must be done before determining the cache size configuration, as it reduces available memory. Also, there is a significant difference between allocation in SQL Server 2000 and SQL Server 2005. In SQL Server 2000, the number of locks specified (using either the Enterprise Manager GUI or the sp_configure stored procedure) is the total allocated. In SQL Server 2005, to increase parallelism in the lock table, locks are allocated by NUMA (Non-Uniform Memory Architecture) node. When upgrading from SQL Server 2000 to SQL Server 2005, there is no warning of this change in SSMS (SQL Server Management Studio), the SQL Server 2005 Upgrade Advisor or the SQL Server installer, so it’s very possible that parameters which worked perfectly well in SQL Server 2000 will cause SQL Server 2005 to fail to start. The specified lock count value is allocated for each NUMA node. For example, if 1,000,000 locks are specified on a system with 3 NUMA nodes, 3,000,000 locks are actually allocated. The number of NUMA nodes recognized by SQL Server is determined using a special query.

Example - Determining number of NUMA (Non-Uniform Memory Architecture) nodes on server for SQL Server 2005 lock allocation

SELECT @NUMACount = COUNT(DISTINCT parent_node_id) 
FROM sys.dm_os_schedulers
WHERE parent_node_id <> 32 --32 is the DAC scheduler

Divide the number of locks desired by the number of NUMA nodes to determine the allocation value for sp_configure (or to be set using SSMS).

To determine the total number of locks required, leave the lock allocation at zero (the default) and monitor the total number of requests for a large load representative of the expected load. Use the SQL Server Locks object counters in PerfMon to obtain this count. A good rule of thumb for BizTalk Server is to set the allocation at 15-20% above the peak number of lock requests; this leaves room for growth without allocating an excessive number of locks.

Setting the lock allocation is an iterative process. The first preallocation often results in a significant performance improvement, which then increases the number of lock requests requiring recalibration and another, higher preallocation value. Also, the RECONFIGURE command, while normally applying changes to the SQL Server configuration immediately, does not work for changes to the number of locks (even though the command output indicates that reconfiguration was successfully completed). For the change to take effect, the SQL Server service must be restarted.

Bb743398.11ee8508-206f-40ef-be8a-15e90f5faf68(en-US,BTS.10).gif
Difficulty Level Action Purpose

Bb743398.bc4735f2-016f-460b-a7bc-1997b2a9c493(en-US,BTS.10).gif

Determine NUMA Nodes (SQL Server 2005).

The total number of locks allocated is multiplied by the number of NUMA nodes present in the server.

Bb743398.bc4735f2-016f-460b-a7bc-1997b2a9c493(en-US,BTS.10).gif

Use PerfMon to determine the number of lock requests/second while running a representative load.

The number of requests is an easy way to determine the correct preallocation size.

Bb743398.a43ae13c-8534-4b1e-b5f7-15a13a356660(en-US,BTS.10).gif

Set the number of locks at 15-20% above the observed maximum number of lock requests/second.

This is a “rule-of-thumb” – increase the 15-20% buffer if significant growth is expected.

Bb743398.bc4735f2-016f-460b-a7bc-1997b2a9c493(en-US,BTS.10).gif

Monitor and recalibrate as necessary.

PerfMon alerts or other, similar mechanism should be considered.

Cache

SQL Server performance depends heavily on adequate cache memory. Inadequate cache can cause excessive checkpoint operations as well as a significant increase in I/O operations as pages are read from disk rather than memory. As with the lock table allocation, the dynamic memory management of SQL Server is adequate for many types of applications but problematic given the heavy load imposed by BizTalk Server. Therefore, the SQL Server memory should be set to as large a value as possible while still accommodating other applications on the server (including anti-virus and other “system” applications) and the Windows operating system. In general, a minimum of 1GB of free memory should be left available on the server. If other applications are later installed on the server, the amount of memory reserved by SQL Server may require adjustment.

Be sure to set the SQL Server memory after establishing the number of locks to be allocated, restarting SQL Server, and examining the amount of memory left available for fixed allocation by SQL Server. It’s always better to undershoot rather than overshoot the allocation, that is, establish a target for the desired amount of memory, allocate an amount 1GB below the target and restart the service (take the SQL Server resource off-line in a clustered environment). Check the working set allocation in the SQL Server error log (see below). If successful, raise the allocation and repeat.

Working Set

SQL Server allocates its working set at startup from the memory available after allocation of the fixed cache and locks (see above), Windows operating system, and other started applications. It’s important to check the SQL Server error log to ensure the working set is successfully allocated. This is vital in SQL Server 2000 installations, and recommended (although not always possible) in SQL Server 2005 installations.

Bb743398.note(en-US,BTS.10).gifNote
Be sure at least 1GB of free memory remains on the server after the working set is successfully allocated.

Bb743398.11ee8508-206f-40ef-be8a-15e90f5faf68(en-US,BTS.10).gif
Difficulty Level Action Purpose

Bb743398.a43ae13c-8534-4b1e-b5f7-15a13a356660(en-US,BTS.10).gif

Evaluate available memory.

Set SQL Server to use dynamic memory management and measure the memory available after SQL Server startup.

Bb743398.bc4735f2-016f-460b-a7bc-1997b2a9c493(en-US,BTS.10).gif

Set Cache.

Set SQL Server to use a fixed amount of memory that will leave at least 1GB available for other applications, etc.

Bb743398.bc4735f2-016f-460b-a7bc-1997b2a9c493(en-US,BTS.10).gif

Check Working Set successfully allocated in SQL Startup Log.

If the working set cannot be allocated, reduce the fixed memory size.

Bb743398.note(en-US,BTS.10).gifNote
For SQL Server 2005, it is not always possible for the service to allocate its entire desired working set.

Bb743398.bc4735f2-016f-460b-a7bc-1997b2a9c493(en-US,BTS.10).gif

Adjust cache accordingly.

Account for new applications and change the fixed memory size as appropriate.

TEMPDB

TEMPDB is used by SQL Server 2000 to store intermediate result sets, arbitrate object allocation, and to contain temporary tables. In SQL Server 2005, it is also used for row versioning and the SNAPSHOT isolation levels, in other words, its workload is significantly increased. It therefore is very important to optimize the performance of this critical resource.

One of the most important functions of TEMPDB is its use to arbitrate object management. By default, TEMPDB contains a single file and all SQL Server 2000 object creation is serialized through that file (using the SGAM block). SQL Server 2005 memory management is enhanced over SQL Server 2000, but TEMPDB is still a bottleneck and requires optimization for memory management operations.

The key optimization for TEMPDB is also very easy to accomplish. By simply adding files to the database, one per processor or processor-core (but not hyperthreads), significant parallelism is introduced. In ultra-high performance environments, these additional files can be placed on separate physical drives (LUNs in clustered environments, local drives in non-clustered systems). However, a large benefit is achieved even if all of the files reside on the same drive. In SQL Server 2005 this technique also improves the performance of TEMPDB for its many added roles.

Another optimization, critical to performance of SQL Server 2000 but still important in SQL Server 2005 is to turn on the 1118 trace flag. This flag enables (off) or disables (on) mixed extent objects, that is, objects of different allocation sizes. When set, while there is an increase in the amount of memory consumed by SQL Server, the amount of memory management arbitration is greatly reduced.

Bb743398.11ee8508-206f-40ef-be8a-15e90f5faf68(en-US,BTS.10).gif
Difficulty Level Action Purpose

Bb743398.bc4735f2-016f-460b-a7bc-1997b2a9c493(en-US,BTS.10).gif

Create additional TEMPDB files, one per processor or processor core (not including hyperthreading).

Minimizes SQL Server memory allocation management contention by allowing more than one processor to access the critical SGAM and PFS blocks simultaneously.

Bb743398.a43ae13c-8534-4b1e-b5f7-15a13a356660(en-US,BTS.10).gif

Turn on the T1118 trace flag (optional for SQL Server 2005, but recommended).

Disable mixed extent memory allocation mechanism, eliminating use of the TEMPDB PFS page for object allocation.

Other Parameters

There are a few other SQL Server tuning parameters useful in the environment. If a system is going to generate complex reports or other ad-hoc queries, it’s useful to increase the query timeout from the default of 600 seconds. A minimum of 900 seconds is recommended, but the actual value will depend on the nature of the queries involved. Long running queries should generally not be hosted on the BizTalk Message Box or Tracking database server(s), but rather on a separate application server(s). The reason for this is two-fold. First, the BizTalk Message Box and Tracking database servers need to function in near real-time for maximum performance and guaranteed message delivery. Second, the MAXDOP (Maximum Degree of Parallelism) on any server running a BizTalk Message Box database is set to 1 and cannot be changed, thereby constraining the efficiency of reporting or other types of long-running queries.

Similarly, NT Fibers (“Lightweight threading” in SQL Server 2005) can be enabled for application databases, but must not be turned on for BizTalk Server databases. The exception is for application databases that are backed-up using the BizTalk Server backup process; these databases must have Fibers disabled as well.

Lastly, consider activating the SQL Server query governor for application databases subject to ad-hoc queries. This prevents errant or intentional high-resource-consumption queries from effectively disabling a server by “hogging” all of its resources. If there are periods when high-consumption queries are allowed, use a SQL Server Agent schedule job to enable and/or disable the query governor (or modify the cost threshold) as appropriate. And as one might expect, neither the query governor nor high-resource consumption queries should be executed on the BizTalk Message Box or Tracking database servers.

Bb743398.11ee8508-206f-40ef-be8a-15e90f5faf68(en-US,BTS.10).gif
Difficulty Level Action Purpose

Bb743398.a43ae13c-8534-4b1e-b5f7-15a13a356660(en-US,BTS.10).gif

Increase the query timeout to 900 seconds or greater (depending on the system load).

Set this to a higher value to accommodate low-priority reporting queries; 900 seconds is a good baseline, but a higher value may be required on a case-by-case basis; this should not be required for servers servicing only BizTalk Server operational databases (Message Box, Tracking, and management) but may be required for BAM server(s).

Bb743398.a43ae13c-8534-4b1e-b5f7-15a13a356660(en-US,BTS.10).gif

Turn on NT Fibers (non-BizTalk Server DATABASE Servers Only).

This should only be done on application database servers, servers running reporting services, etc. as it is not supported for BizTalk Servers and will cause the BizTalk Backup Job to fail and the databases to become fragmented.

Bb743398.note(en-US,BTS.10).gifNote
BizTalk Server database servers include servers being backed up as part of the BizTalk Backup Job.

Bb743398.d9b86b0c-14f9-4321-9184-c4a9176ed43c(en-US,BTS.10).gif

Turn on the SQL Server query governor.

Determine the cost of the most expensive query normally run on the system and set the query governor to prevent queries more expensive by 25-50% - this prevents expensive, ad hoc queries from “taking over” a critical database server.

Database Tuning

Database tuning occurs on (at least) two levels, application (e.g., queries) and infrastructure. There are many excellent tools and papers describing the tuning of SQL Server and queries, etc. and that topic is not addressed here. Instead, the focus will be on how the underlying infrastructure, especially DISK infrastructure, can be optimized to provide a high-performance environment.

SQL Server thrives on two types of resources, I/O bandwidth and memory. Memory optimization has been addressed in the previous section. This section will focus on how to maximize performance by tuning the underlying storage infrastructure supporting the SQL Server instance.

In the early days of large scale databases high-availability storage was achieved using RAID 5. This format was appealing because disk drives were relatively expensive on a cost/MB basis, and it offered a means of achieving high-availability with a minimum drive count. Unfortunately, RAID 5 has a number of immutable performance limitations that make it ultimately unsuitable for a high-performance database storage infrastructure.

The biggest issue with RAID 5 is the so-called “write penalty”. This penalty is incurred because any change to an individual drive in the array must be reflected in the drive’s checksum, which is stored on another physical drive in the set. While smart controllers can somewhat mitigate this performance penalty, there is no way to eliminate it. A second issue with RAID 5 involves the process of recovering a drive after a failure. RAID 5 uses a process called rebuilding, where the contents of the drive are reconstructed in a painstakingly slow manner using the checksum and other information stored on the other drives in the set. This process can literally take hours or longer, during which the performance of the entire RAID can be severely degraded (by up to 80% of nominal performance). Finally, depending on how the RAID 5 array is configured, simultaneous failure of two drives (three if a hot-spare is configured) can result in the loss of all data stored on the array. For more details on the perils of using RAID 5 for database applications, see Battle Against Any Raid Five.

A better choice, and the Microsoft recommended best practice for both maximum performance and high-availability is RAID 1+0 (a stripe of mirror sets). The mirror sets provide high-availability, while striping the data across the sets yields high-performance. Normally two-drive mirror sets are sufficient; however, for ultra-high availability a third drive can be added. The probability of all three drives in a single mirror set failing simultaneously is vanishingly small. The very small penalty incurred to write to each drive of the mirror set is virtually eliminated by smart RAID controllers, and the read performance is actually improved over other RAID formats because the controller can choose from any of the drives in the mirror set depending on load and head position. In any case, SQL Server does its own write caching using CHECKPOINT operations and the Write After Log paradigm, so optimized read performance for random disk access is the most important characteristic of a database storage subsystem. RAID 1+0 requires minimal overhead for drive recovery. This process, called “re-silvering”, is orders of magnitude faster than the RAID 5 rebuild because the new drive is merely a copy of the old, an operation executed in hardware by the smart array controller. The performance impact on the remainder of the array during recovery is negligible.

One of the arguments made against RAID 1+0 is that it requires more drives than RAID 5 to achieve the same amount of storage capacity. This is true. But RAID 1+0 can also realize a 30-50% performance improvement over RAID 5 depending on the characteristic of the load, and the additional cost for the hardware is insignificant provided there is sufficient SAN director and cabinet space available. As a compromise, some installations use a hybrid model where slower, infrequently accessed databases (e.g., the SSO database when it’s not used for extensive trading partner credential management) can be placed on the relatively cheaper RAID 5 configuration, while databases with higher performance requirements (e.g., Message Boxes) are deployed to a RAID 1+0 configuration.

Another critical performance enhancement to the storage infrastructure is the use of STORPort rather than SCSIPort drivers. The default for Windows Server 2003, where provided by the vendor, is to install STORPort drivers for all disk devices. However, some organizations have yet to approve this technology and prefer instead to use the legacy equivalent SCSIPort drivers when available. This is very detrimental to performance for two reasons. First, SCSIPort technology is limited to 256 simultaneous I/O operations. While 256 sounds like a large number, in a BizTalk Server system that may be processing hundreds or even thousands of messages per second it is woefully inadequate. Second, SCSIPort drivers allocate one I/O queue per HBA (Host Bus Adapter). This means that all SAN traffic, regardless of the source or number of LUNs allocated, is serialized through one I/O queue (or perhaps two if redundant HBAs are installed). It’s very difficult to fill the pipe of a 4Gb Fiber Channel fabric given these limitations. STORPort was developed by Microsoft to remove these bottlenecks. STORPort provisions an I/O queue for each LUN, and currently services up to 254 simultaneous requests per each. Therefore, the more LUNs employed in the I/O infrastructure, the higher the overall bandwidth and capacity.

Be aware that SAN operations are generally non-trivial. Only a knowledgeable administrator should attempt to create or manage a SAN configuration; it is easy to make mistakes that have negative performance and (perhaps) availability ramifications. The requisite training is generally not difficult, and is well worth the effort if not already available within the organization.

SAN Director Location

Bb743398.11ee8508-206f-40ef-be8a-15e90f5faf68(en-US,BTS.10).gif
Difficulty Level Action Purpose

Bb743398.a43ae13c-8534-4b1e-b5f7-15a13a356660(en-US,BTS.10).gif

Multiple LUNs

Creates multiple I/O queues to improve utilization of the SAN fabric; when combined with multiple filegroups (which create multiple I/O threads in the database engine…see below) can yield very large performance improvements

Bb743398.a43ae13c-8534-4b1e-b5f7-15a13a356660(en-US,BTS.10).gif

Run the DiskPar utility on each LUN for proper partition alignment

Performance improvement of 20% or more may be realized by correctly aligning the LUN partition with the underlying disk cluster(s).

Windows Location

Bb743398.11ee8508-206f-40ef-be8a-15e90f5faf68(en-US,BTS.10).gif
Difficulty Level Action Purpose

Bb743398.a43ae13c-8534-4b1e-b5f7-15a13a356660(en-US,BTS.10).gif

MPIO enabled.

Multi-Path I/O provides both load balancing and multiplied bandwidth access to the SAN fabric (requires the STORPort driver).

Bb743398.a43ae13c-8534-4b1e-b5f7-15a13a356660(en-US,BTS.10).gif

DATA drives NTFS formatted at 16384.

SQL Server pages are 8192 bytes, so the default NTFS block size (4096) reads only ½ a page and effectively doubles the number of I/O operations. Also consider formatting the DATA drives in 64KB blocks, since SQL Server commonly does an eight page read-ahead to improve performance.

Bb743398.a43ae13c-8534-4b1e-b5f7-15a13a356660(en-US,BTS.10).gif

LOG drives NTFS formatted at 4096.

4094 (4K) is the default Windows block size for a newly formatted drive.

Bb743398.bc4735f2-016f-460b-a7bc-1997b2a9c493(en-US,BTS.10).gif

SAN Drive Defragmented.

Any physical file in more than a few fragments is causing performance degradation, potentially very large degradation.

Bb743398.a43ae13c-8534-4b1e-b5f7-15a13a356660(en-US,BTS.10).gif

SAN Drive MFTs pre-allocated and defragmented.

Use a third-party tool to preallocate and (more importantly) defragment the MFT (Master File Table) of each.

Bb743398.a43ae13c-8534-4b1e-b5f7-15a13a356660(en-US,BTS.10).gif

STORPort Driver Configured for HBAs or RAID Controller (DAS).

The legacy Windows SCSIPort driver has a single queue per HBA (rather than a single queue per LUN as with the STORPort driver), and is limited to 256 simultaneous I/O operations.

BizTalk Server Databases

There are two main techniques to increasing the performance of the BizTalk Server data store (which includes all of the various BizTalk Server databases), spread the component databases across multiple servers and/or add parallelism to the databases in place.

The first technique is familiar to anyone who has built a large, multi-server BizTalk Server installation. At a minimum, the primary message box should be placed on its own server. Preferably, the tracking database (DTA) should be placed on a separate server (or cluster in a high-availability environment) from the message box (this is very important for BizTalk Server 2004, less so for BizTalk Server 2006 unless Message Body tracking, Rule tracing, Debug tracing, Call Chain tracing, etc. is required). If there is a heavy BAM load, or if SSAS (SQL Server Analysis Services) is shared between BizTalk Server and other applications, SSAS should have its own server. Finally, all remaining databases, including the BizTalk Server management database, adapter databases (HL7 log, FIX log, etc.), TPM (Trading Partner Management, which should be separated from BizTalkMgmtDb if used extensively), SSO, etc. along with any application databases that have reasonably low usage can be provisioned on yet another server. In high availability environments, the servers mentioned above will be Active-Passive SQL Server 2005 Standard clusters (with the exception of the BAM server if high-performance analytics are required, in which case SQL Server Enterprise Edition is indicated). It is helpful to put the BAMPrimaryImport database on the BAM analysis server (if SSAS is installed on a separate server). A major benefit of using multiple servers, isolating the MessageBox(s) on its own server, is the MAXDOP (Maximum Degrees of Parallelism) restriction (see above) applies only to the MessageBox server.

Bb743398.11ee8508-206f-40ef-be8a-15e90f5faf68(en-US,BTS.10).gif
Difficulty Level Action Purpose

Bb743398.bc4735f2-016f-460b-a7bc-1997b2a9c493(en-US,BTS.10).gif

Primary Message Box on a separate server/cluster.

This is the most important isolation for most systems.

Bb743398.a43ae13c-8534-4b1e-b5f7-15a13a356660(en-US,BTS.10).gif

Set MAXDOP to zero on non-MessageBox database servers.

MAXDOP = 1 is only required on the BizTalk Message Box database server(s), and must not be changed; all other servers hosting other BizTalk Server databases may return this value to 0 if set.

Bb743398.bc4735f2-016f-460b-a7bc-1997b2a9c493(en-US,BTS.10).gif

Tracking Database (DTA) on a separate server.

Less important for BizTalk Server 2006 than BizTalk Server 2004 unless Message Body Tracking, Rule execution tracing, etc. is enabled.

Bb743398.bc4735f2-016f-460b-a7bc-1997b2a9c493(en-US,BTS.10).gif

Management Databases, adapter databases, and low usage application databases on a separate server.

These can be grouped with the Tracking Database depending on the load (see above); they should generally not be grouped with the primary message box.

Bb743398.bc4735f2-016f-460b-a7bc-1997b2a9c493(en-US,BTS.10).gif

TPM (Trading Partner Management) data configured in a separate database from the BizTalk Server Management data (if used extensively).

As trading partner usage grows, the supporting database may need to be separated from the management (and possibly other) databases.

Bb743398.bc4735f2-016f-460b-a7bc-1997b2a9c493(en-US,BTS.10).gif

BAMPrimaryImport on BAM Database Server.

This should only be done on application database servers, servers running reporting services, etc. as it is not supported for BizTalk Servers and will cause the BizTalk Backup Job to fail and the databases to become fragmented.

The second performance optimization is to spread the tables of the BizTalk Server databases across multiple filegroups. Each filegroup adds an I/O thread which, when coupled with a data file on a separate STORPort-driven SAN LUN (as described above) will greatly improve performance (gains from 100-1000% have been observed, depending on the load characteristics of the system). Not only is the table data moved into separate filegroups, but the LOB (TEXT/IMAGE) data and the non-clustered indexes as well. This maximizes parallelism (not within a single query on a Message Box Database server because BizTalk Server sets MAXDOP - Maximum Degrees of Parallelism – of the SQL Server instance to one) between queries and other database operations. Appendix B details a recommended mapping of BizTalk Server databases including filegroups, table assignments, log file locations, etc. Suggested starting sizes for the files in each group are also provided; it is strongly recommended that AutoGrowth be left on to accommodate different mixes of final sizes and extensions as necessary to support the characteristic of a particular system. There are a number of key concepts expressed in the appendix, including the priority with which the BizTalk Server databases should be distributed (especially in situations where the number of SAN LUNs and/or servers is limited). When using this guidance, it is important to understand the ramifications of moving table artifacts, and to consider carefully how separate data files can be shared when LUNs are scarce. Some performance increase can be achieved simply by adding multiple files on a single device to the same filegroup; SQL Server will stripe the data across the files and can achieve parallelism that way as well.

There is one other database optimization that can, depending on the type of load being processed, improve performance by up to 30%. Enabling the TEXT IN ROW option of two key message box (BizTalkMsgBoxDb) tables, Parts and Spool, allows small messages (by default total message payload of 256 bytes or less) to be stored directly in the row data (rather in a separate LOB table – or data file). In-row data does not require a separate I/O operation for retrieval. Unfortunately, testing demonstrates this is only effective for small messages. Although it is possible to set the option for sizes larger than 512 bytes, performance in testing dropped off significantly above this value because as the data rows become larger fewer of them can be stored per page of cache and/or disk, which increases the number of I/O operations overall. Appendix A has test results on the use of this option.

Bb743398.note(en-US,BTS.10).gifNote
Performance in actual systems may vary based on their load characteristics.

Example - Setting the text-in-row option for key tables in the message box

use BizTalkMsgBoxDb
sp_tableoption N'Parts', 'text in row', ‘512’

Bb743398.11ee8508-206f-40ef-be8a-15e90f5faf68(en-US,BTS.10).gif
Difficulty Level Action Comment

Bb743398.a43ae13c-8534-4b1e-b5f7-15a13a356660(en-US,BTS.10).gif

Design a plan for allocating additional filegroups.

Use the information in Appendix B to determine which BizTalk Server databases will be optimized.

Bb743398.a43ae13c-8534-4b1e-b5f7-15a13a356660(en-US,BTS.10).gif

Create SAN LUNs to contain the files for the new filegroups.

Create required LUNs and optimize per the instructions above.

Bb743398.a43ae13c-8534-4b1e-b5f7-15a13a356660(en-US,BTS.10).gif

Create the filegroups, assigning the data files for each to the previously created LUNs.

A one-to-one relationship between data files and LUNs yields the best performance, but where LUN count is short sharing will still perform better than a single filegroup (the default).

Bb743398.a43ae13c-8534-4b1e-b5f7-15a13a356660(en-US,BTS.10).gif

Move the tables into the new filegroups.

The guidance in Appendix B has been tested on many systems, and should be the starting point for placement of BizTalk Server tables in filegroups.

Application Databases

Application (non-BizTalk Server) databases can benefit from most of the above optimizations, as well. They should also be highly optimized using the ITW (Index Tuning Wizard) in SQL Server 2000 or the DTA (Database Tuning Advisor) of SQL Server 2005. Remember that it is common for backend databases and/or the services they support to become the bottlenecks to a highly optimized BizTalk Server system. Don’t forget to tune their infrastructure, too. And avoid putting them on the same SQL Server instance as a BizTalk Message Box.

The techniques presented herein should substantially improve the performance of almost any highly loaded BizTalk Server system, and in fact can be applied to many other types of Windows systems as well. However, with this performance increase comes an increase in responsibility to monitor and adjust the tuned parameters and artifacts over time as load characteristics change. There are three key areas which should be monitored for potential re-tuning, Locks, Memory, and Data File Space. A shortage in any of these areas will once again hamstring performance and, if severe, can cause system failure.

Artifacts

The simplest way to monitor usage is with the PerfMon utility. Appendix A contains a list of the objects monitored during the lab work for this project. Performance monitoring can be live or logged (or both); if live, increase the time between samples to avoid distorting the observed values with the monitoring overhead or impacting the system performance. When recording logs, consider monitoring entire objects rather than just individual counters (any or all of the counters can be displayed on playback, and the additional overhead is generally small). If a particular counter in a group appears interesting, the additional information necessary for investigation is already in the same log; otherwise, another recording with additional counters will need to be made. When troubleshooting intermittent problems that are not reproducible on demand, this can be very time consuming.

Alerts

For long term monitoring, PerfMon is not a suitable tool. Monitoring software, such as MOM (Microsoft Operations Manager) is a much better choice, especially when a system has a large server count. This type of tool can be configured to send alerts (including alert escalation) and even take possible corrective action unattended.

If monitoring software is unavailable, SQL Server provides a means to configure operators and notifications to raise alerts for critical conditions in its part of the system. These must generally be manually configured, but this is far preferable to having no alerts and is built-in to the SQL Server engine.

The laboratory work done in support of the conclusions presented in this paper is documented in this section.

Goals

In December of 2006, Microsoft commissioned a laboratory study of the effects of the tuning techniques described in this paper on the available CTP of BizTalk Server 2006 R2. The goal of the study was to document the effectiveness of various types of tuning. The results could then be used in a number of ways:

  1. To demonstrate the applicability of BizTalk Server in large system environments.
  2. To improve the general performance of both new and previously-deployed BizTalk Server systems.
  3. To remedy existing deployments where BizTalk Server was either deemed insufficiently performant or was exhibiting functional and/or operational problems.

It was not a goal of the lab work to simulate any specific production configuration, though a number of deployed systems were considered as test configuration candidates. Rather, the results of this work are intended to serve as justification for use of the specified techniques when designing and deploying production systems. Consequently, these results should not be used as “performance promises”; the performance gains realized for any specific tuning technique will vary widely based on load characteristic, server hardware, storage and network configuration, and other factors.

As a result of the stated goals, precision (repeatability) became a very important objective while accuracy (accurate simulation of a specific, real-world scenario) was virtually eliminated.

Methodology

The applications of BizTalk Server are diverse and cannot possibly be modeled generically in a laboratory environment. This is especially true of load characteristics (message size, frequency, latency, throughput, etc.). Also, many different types of server and other hardware are used to build production deployments; it would be impossible to simulate even a representative sample of these in the lab.

Tuning is often more art than science. Outcomes, especially for messaging systems, vary widely depending on the load characteristic and external system interfaces; these variations are at odds with the objective of creating precise (repeatable) tests with results usable on a relative basis to justify implementation of various tuning techniques. Therefore, after considering and testing a number of complex configurations taken from actual production deployments, the choice was made to keep the tests simple.

While the “simple” approach eliminated a large number of problems, it raised others. Specific tuning techniques were to be tested in the lab; however, an overly simple configuration would not exercise the various BizTalk Server components sufficiently to yield usable results. Simply adding load (e.g., numbers of files) would not compensate for inadequate feature coverage. The solution was to establish a matrix of tests for each tuning iteration.

Table A-1 – Per-Iteration Test Matrix

Test No Tracking Message Body Tracking Orchestration Event Tracking Rule Event Tracking

STP
(BAM RTA)

X

X

X

STP
(BAM Collection)

X

X

X

Simple Orchestration
(BAM RTA)

X

Simple Orchestration
(BAM Collection)

X

X

X

Rule-based Orchestration
(BAM RTA)

X

Rule-based Orchestration
(BAM Collection)

X

x

X

This table is actually a cube because both implicit and explicit BAM usage was tested; it is rendered in two dimensions for easier use. Also, not all tests were run for all tuning iterations (as detailed below), specifically, the tracking tests were not run for both types of BAM.

The STP (Straight Through Processing) tests were performed using a simple customer message, mapped to an output message where a credit limit was assigned based on the input parameters.

Two types of orchestrations were selected, one where variable population of the output message was accomplished using a simple, multi-branch decision shape. The second type incorporated the BRE (BizTalk Server Rules Engine) so that the effects of tuning on its performance, especially with rule event tracking enabled, could be measured.

The test messages used are relatively small. The STP tests used a single input message, while the orchestration tests used three different messages that varied only by content (not size). The exception is the Text In Row testing, where a larger message was used with the STP (BAM RTA) No Tracking test to perform a sensitivity analysis on the size setting of the Text In Row table option.

Example – STP Test Message (550 Characters, 1100 Bytes)

<ns0:Customer xmlns:ns0="http://BTSR2.PerformanceLab.STP.Schemas.Customer">
  <CustomerId>100</CustomerId>
  <Name>Name_0</Name>
  <ns1:Address xmlns:ns1="http://BTSR2.PerformanceLab.STP.Schemas.Address">
    <Address1>Address1_0</Address1>
    <Address2>Address2_0</Address2>
    <City>City_0</City>
    <StateProvince>StateProvince_0</StateProvince>
    <PostalCode>PostalCode_0</PostalCode>
    <Country>Country_0</Country>
  </ns1:Address>
  <DayPhone>DayPhone_0</DayPhone>
  <HomePhone>HomePhone_0</HomePhone>
  <FaxPhone>FaxPhone_0</FaxPhone>
</ns0:Customer>

Example– STP Long Test Message (1015 Characters, 2030 Bytes)

<ns0:Customer xmlns:ns0="http://BTSR2.PerformanceLab.STP.Schemas.Customer">
  <CustomerId>100</CustomerId>
  <Name>Name_0</Name>
  <ns1:Address xmlns:ns1="http://BTSR2.PerformanceLab.STP.Schemas.Address">
    <Address1>Address1_0</Address1>
    <Address2>Address2_0</Address2>
    <City>City_0</City>
    <StateProvince>StateProvince_0</StateProvince>
    <PostalCode>PostalCode_0</PostalCode>
    <Country>Country_0</Country>
  </ns1:Address>
  <DayPhone>DayPhone_0</DayPhone>
  <HomePhone>HomePhone_0</HomePhone>
  <FaxPhone>FaxPhone_0</FaxPhone>
  <Comments>This is additional test data to pad the length of the message for longer length message testing, allowing sensitivity analysis of the Text In Row settings for the BizTalk Message Box.  This comment section needs to contain enough text to push the message length up to approximately 1000 characters (2000 bytes).  That’s a lot of text that doesn’t really say anything.  It probably would have been easier to just use some random text geenrator</Comments>
</ns0:Customer>

Example – Typical Orchestration Test Message (602 Characters, 1204 Bytes)

<ns0:Customer xmlns:ns0="http://BTSR2.PerformanceLab.Orchestration.Schemas.Customer">
  <CustomerId>100</CustomerId>
  <CustomerType>5</CustomerType>
  <Name>Name_0</Name>
  <ns1:Address xmlns:ns1="http://BTSR2.PerformanceLab.Orchestration.Schemas.Address">
    <Address1>Address1_0</Address1>
    <Address2>Address2_0</Address2>
    <City>City_0</City>
    <StateProvince>StateProvince_0</StateProvince>
    <PostalCode>PostalCode_0</PostalCode>
    <Country>Country_0</Country>
  </ns1:Address>
  <DayPhone>DayPhone_0</DayPhone>
  <HomePhone>HomePhone_0</HomePhone>
  <FaxPhone>FaxPhone_0</FaxPhone>
</ns0:Customer>

Another challenge was to make the testing cycle long enough to produce usable results. This was an especially interesting issue because both pre and post-tuning performance had to be considered. For example, tests that pre-tuning required 10 minutes to complete might only need 30 seconds post-tuning. To have sufficient, measurable precision the minimum test duration was set at 5 minutes (allowing small, uncontrollable factors that might overwhelm a too-short test to average out their effects and avoid invalidating the test results).

Similarly, the tests needed sufficient precision to produce usable results. Although precision can be measured with as few as two tests, a minimum of three tests, varying by no more than 5%, reduces the chances that all of the results are error-biased in the same direction. BizTalk Server contains a number of components that cannot easily be fixed unless totally disabled (e.g., throttling); therefore, a significant factor in producing precise results is not readily controlled. Again, greater test length tends to average out this variability.

Were all of the possible test combinations run three or more times for each tuning iteration, there would be far too many tests to be completed in the limited lab time available. Reducing the number of test runs for each combination would render the test data suspect. The only reasonable solution was to both reduce the number of tuning iterations and the number of tests performed for each iteration. To accommodate this limitation a number of the tested combinations was dropped. The test matrix shown above reflects the reduction in the number of combinations run for each iteration. Also, the tuning iterations were grouped (with the exception of specific techniques, such as Text In Row, where specific results were required). The resulting test iterations were:

  • Windows Tuning
  • Network Infrastructure Tuning
  • Database Server Tuning (Recovery Interval, Text In Row, etc.)
  • Database Multi-threading (filegroups, multiple LUNs)
  • Database Redistribution (multiple servers)

The first three groups were relatively easy to select and order. The choice between groups four and five was made difficult by the interaction of the two techniques and the desire to compare their effect on performance. In the end there was simply insufficient time to re-build the test environment to test each individually. Since multi-threading is by far the least expensive of the two, it was tested first. The results for database redistribution are therefore cumulative with multi-threading rather than independent.

Note that no BizTalk application tuning was tested. BizTalk application tuning (e.g., threading, throttling, etc.) are already well documented and testing them was outside the scope of the lab work. Tuning the BizTalk application(s) is important but should be done after the other tuning techniques described herein have been applied. If there were applied prior to the above tests the performance effects of those tests would be altered and invalidate the results. The only change to the default configuration was to increase the batch size for the MSMQ adapter from the default of 20 to 1000. This was necessary to successfully complete the tests without incurring transaction timeouts. Note that the number of lock requests generated per second in the BizTalk Message Box increased dramatically as the MSMQ batch size was increased as shown in the following table.

Table A-2 – Message Box Lock Requests/Second vs. MSMQ Adapter Batch Size

Batch Size Lock Requests/Second

20

200000

50

500000

100

750000

500

1250000

1000

3000000

1000 W/BAM

4000000

One unexpected result was the significant increase (33%) in lock requests per second when explicit BAM (Tracking Profiles) was enabled. The increase warrants further investigation but is beyond the scope of this document. The increase in lock request count also resulted in a large number of lock timeouts and thereby significantly impacted the performance of the message box (see below).

Message Body tracking was limited in inbound messages, as this is a common field scenario (messages are tracked for recommittal, auditing, and other purposes).

Disk defragmentation is strongly recommended for all SQL Servers, especially those supporting BizTalk Server. Unfortunately, its performance effects are impossible to precisely measure as the act of defragmentation destroys any ability to reproduce the test. Consequently, defragmentation performance improvement was not tested.

Finally, the planned lab work originally included testing multiple message boxes. As it became clear that testing time for other tuning techniques was limited, and because this testing has been part of other efforts, multiple message box testing was dropped from the plan. Similarly, interaction with the BizTalk Server backup job was dropped from testing because it did not bear directly on the tuning techniques being documented.

Configuration

The previous section described the testing methodology to be applied to the laboratory work supporting this document. But, the tests described had to be applied to an actual hardware configuration. This configuration had to remain constant throughout the lab, yet had to support a number of hardware tuning techniques including single and multi-server configurations. Test and monitoring clients were also required.

There were a number of constraints in the lab environment which limited the lab work that could be performed:

  • Only two subnets available, one 100Mb and one 1Gb
  • The Broadcom network cards lacked most tunable driver parameters
  • Only RAID 5 and RAID 1 SAN available (no RAID 1+0)
  • BIOS updates and hardware re-configuration were prohibited

As with the test methodology, a number of actual production configurations were considered. Again, simplicity and precision were the determining factors as to what configuration was ultimately used. For example, the application and interface servers were eliminated from the configuration because they produced no measurable performance effect but did introduce time variations and did not produce precise results.

Figure A-1 – Hardware Initially Requested for fhe BizTalk Server 2006 R2 Performance Lab
Bb743398.e484b5c5-078e-4b81-b123-59054ed71d3c(en-US,BTS.10).gif
Figure A-2 – Hardware Actually Used For The BizTalk Server 2006 R2 Performance Lab
Bb743398.d70714d7-8d14-4391-974b-18dec0b09a2d(en-US,BTS.10).gif
Figure A-3 – BizTalk Server (BTS01-04) Hardware Configuration
Bb743398.d2afb1bb-fdb0-46e6-8dc6-79a2d49a014b(en-US,BTS.10).gif
Figure A-4 – Primary Message Box Server (PMSG) Hardware Configuration
Bb743398.8787b98a-f468-4645-90e7-a77018066f18(en-US,BTS.10).gif
Figure A-5 - Tracking Server (DTA) Hardware Configuration
Bb743398.652c1752-c673-463b-bb6a-437372391bfd(en-US,BTS.10).gif
Figure A-6 – Test Client (TEST1-TEST4) Hardware Configuration
Bb743398.0b49ed18-590f-4d0a-8eca-6a17f61e695d(en-US,BTS.10).gif
Figure A-7 – Administration Database Server (ADMIN) Hardware Configuration
Bb743398.feb47828-9ed5-4aa9-b19b-757b239e90b3(en-US,BTS.10).gif
Figure A-8 – BAM Database Server (BAM) Hardware Configuration
Bb743398.2103c34c-7ed6-45e3-a550-3319c2921f5f(en-US,BTS.10).gif

All servers of the same type (Test Client, BizTalk Server, etc.) have the same hardware characteristics.

The key tasks in configuring the environment were:

  1. Install the appropriate application software on each server
  2. Configure each Test Client identically
  3. Configure each BizTalk Server identically
  4. Validate the environment for basic functionality of chosen tests
  5. Validate various configurations with the chosen tests
  6. Select a configuration for measurement

As is typical, basic functionality for each of the chosen tests was validated first in a single-server, small batch environment. Then the multi-server configuration was validated by introducing additional servers one at a time. The load on each server was then gradually increased until a fully functional environment was certified.

The clocks on the various servers were not synchronized. Proper synchronization is very important; BizTalk Server systems having multiple message boxes and lacking synchronization have a high-probability of failure. In this case, the client and server clocks were synchronized to the NIST clock (time-c.timefreq.bldrdoc.gov).

MSMQ was chosen as the method of delivering messages from the Test Clients to the BizTalk servers. Several combinations of clients and servers were evaluated, including having each test client send messages to a single server and to multiple servers. It was determined that while sending messages from each test client to multiple servers had little or no performance impact, this configuration had significant precision variability and was unsuitable for generating reproducible results. Consequently, the configuration used for all tests is four test clients, each sending 40,000 messages to a single MSMQ queue on one BizTalk server. To guarantee processing of all messages, transactional queues were used; pre-test measurements revealed an approximately 30% performance penalty when using transactional vs. non-transaction queues in the untuned environment.

Separate hosts were created for input, output, and orchestration processing. Each of these hosts was instantiated on all four BizTalk Servers in an effort to maintain as consistent a configuration as possible (identical server hardware, identical BizTalk Server process deployment).

Procedure and Measurements

To create a precise test, strict pre-test procedures are required so that the testing environment remains as uniform as possible. As ever, these procedures will have implications for accuracy of representing a production environment. The following were performed before each test run:

  • Disable SQL Agent jobs (including backup) –
    Bb743398.note(en-US,BTS.10).gifImportant
    DO NOT DO THIS ON A PRODUCTION BIZTALK SERVER!!!

  • Run all “cleanup” SQL Agent jobs manually
  • Clear databases
  • Clear Performance Counters and set new target log
  • Clear Event Logs
  • Clear Message Queues on Test Servers (and BizTalk Servers in case of failure)
  • Clear any artifacts remaining from the previous test (HAT, Group Hub)

The BizTalk Server LoadGen tool was used to generate the test load. The LoadGen configuration was uniform and derived by modifying the default configuration provided with the tool. As indicated above, once a functionally valid test was established the number of files submitted for each test client was set to 40,000 for a total of 160,000.

A test was not considered complete until the last message had been processed. The test was to be considered invalid if any messages were suspended and the results of that test were discarded; note that this situation did not occur during testing.

Since four test clients were used for testing, to retain precision all four must begin their test cycles at exactly the same time. This is impossible to accomplish manually (even with multiple sessions available) so scheduled jobs were used to invoke LoadGen. The jobs were submitted using a single script to guarantee they all had the same start time, which was always at least 3 minutes past the current time.

Measurements were taken using the PerfMon tool in record mode. Entire performance objects were recorded rather than individual counters. This was done both because the overhead of serializing the entire object is relatively small and because it is common to first analyze counter “total” values to identify patterns of interest, then to drill-down into individual artifact detail counters (e.g., processors, databases, etc.). If the entire object is not recorded it may be difficult (if not impossible) to examine the corresponding detail counters because the underlying test conditions may not be immediately reproducible.

Regardless of the measuring technique used, a certain amount of Heisenberg uncertainty is introduced. A significant amount of time was devoted to evaluating, for the test environment, the best method of recording performance data. This evaluation was a cross between introduced performance effects and usability of the data, especially for comparison purposes (for example, comparing simultaneous BizTalk Server and SQL Server performance). The following choices were evaluated:

  • Separate performance counters on the Test Clients, the BizTalk Server and the Database servers
  • Integrated (network) counters on a Test Client
  • Integrated (network) counters on a BizTalk Server
  • Integrated (network) counters on a Database Server
  • Integrated (network) counters on a separate, non-test server

The advantage of scenario #1 is that it induces no network I/O. However, it causes disk I/O on each server (since each must maintain its own counter set). This separate data is either kept in separate binary files or multiple SQL Server databases, which make comparative analysis very difficult (especially in the case of the former option). Each of the remaining scenarios causes a significant increase in network traffic but substantially reduces the disk I/O, especially when totaled across all servers. A hybrid network and segmented configuration was considered and rejected as having all of the disadvantages of both environments. Scenario #5 was ultimately chosen as the best overall solution and one of the application servers (no longer being used in the test configuration) was made the processor and repository of the data. A full installation of SQL Server was used as it allowed centralized collection and management of the data (not possible with separate binary data files).

Data and Conclusions

Based on the configuration and methodology established in the previous sections, tests were developed and executed. The results are summarized in this section. Performance changes are shown as percentages. Changes are calculated as the difference between the average of the three test runs for the current system state and the three test run average of the previous system state.

Summary

The following summary tables represent the baseline and final results (after all recommended tuning techniques were implemented). The baseline table is the basis for the reported results of each subsequent tuning technique application; these results are reported as percentages. The tuning techniques were applied cumulatively; each table describes the results of applying the stated tuning technique to the previous configuration.

Each test consisted of 160,000 messages submitted using LoadGen to MSMQ queues on the BizTalk Servers. Each of the four test clients submitted 40,000 messages to a single queue on a single BizTalk Server. No changes were made to the default BizTalk Server configuration (e.g., orchestration threads, etc.), and that all tests were performed using a single BizTalk Server message box.

Table A-3 – Baseline Test Results

Baseline No Tracking Message Body Tracking Orchestration Event Tracking Rule Event Tracking

STP
(BAM RTA)

16.2 min

STP
(BAM Collection)

16.7 min

Simple Orchestration
(BAM RTA)

21.3 min

25.8 min

26.2 min

Simple Orchestration
(BAM Collection)

21.7 min

Rule-based Orchestration
(BAM RTA)

22.6 min

26.1 min

26.5 min

26.4 min

Rule-based Orchestration
(BAM Collection)

23.2 min

Table A-4 – Final (Post-Tuning) Test Results

Final (Post-Tuning) No Tracking Message Body Tracking Orchestration Event Tracking Rule Event Tracking

STP
(BAM RTA)

5.6 min

STP
(BAM Collection)

5.5 min

Simple Orchestration
(BAM RTA)

12.1 min

13.3 min

13.3 min

Simple Orchestration
(BAM Collection)

12.3 min

Rule-based Orchestration
(BAM RTA)

12.4 min

12.8 min

12.9 min

12.9 min

Rule-based Orchestration
(BAM Collection)

12.9 min

The following general conclusions can be drawn from comparison of the pre and post-tuned environment:

  • STP (BAM RTA, No Tracking) responded very well to the tuning, improving its performance from a rate of approximately 41 TPS/server to 119 TPS/Server (476 TPS aggregate) or approximately 190%; similar gains were realized for STP with BAM collection.
  • Incorporation of explicit BAM data collection (using Tracking Profiles) had surprisingly little effect on processing efficiency, and statistically insignificant effect for STP.
  • Simple Orchestration (BAM RTA, No Tracking) processing also responded well but showed somewhat less performance improvement than STP, improving from 31 TPS/server to 55 TPS/server or approximately 75%.
  • Enabling tracking had an approximately 21% negative performance impact on the untuned system while the impact on the tuned system was not statistically significant – Message Body tracking had the largest impact; incrementally enabling Orchestration Event and then Rule Event tracking had negligible additional impact.
  • Applying tuning techniques to the tracking database and separating it from the message box gave an incremental orchestration performance improvement of nearly 30% of the base processing time for simple orchestrations and 25% for rule-based orchestrations (the difference between the processing improvement of a non-tracked orchestration and that of a tracked orchestration post-tuning).
  • There are a number of limiting factors whose elimination would likely further improve overall system performance, including use of multiple (tuned) message boxes, multiple 1Gb subnets (especially in lieu of the 100Mb messaging subnet), orchestration thread (and other BizTalk application) tuning, and RAID 1+0 database storage rather than RAID 5.

The performance improvement observed in this environment is commensurate with the results of applying these tuning techniques to field-deployed production systems. In fact, many field deployed systems have experienced greater performance improvement than seen here depending on their load characteristics, orchestration complexity and persistence requirements, etc.

Results by Incremental Tuning Technique

A number of significant observations were made (from the performance counters) for the baseline configuration.

  • Lock requests per second was very high (as high as 4,000,000) as was the count and rate of lock timeouts and lock waits
  • Significant BizTalk Server throttling occurred, so that processing was performed in “spurts” rather than continuously; this occurred for both STP and orchestration processing but was more pronounced for the latter, and represented a significant opportunity for performance gains

The following results were produced after applying all Windows tuning techniques (see above) to the base configuration.

Table A-5 – Post Windows Tuning Test Results (% Gain/Loss)

Windows Tuning No Tracking Message Body Tracking Orchestration Event Tracking Rule Event Tracking

STP
(BAM RTA)

10.2%

STP
(BAM Collection)

10.6%

Simple Orchestration
(BAM RTA)

11.5%

10.7%

11.0%

Simple Orchestration
(BAM Collection)

10.7%

Rule-based Orchestration
(BAM RTA)

10.2%

11.5%

12.3%

11.4%

Rule-based Orchestration
(BAM Collection)

11.0%

The following results were produced after applying Network Infrastructure tuning to the previous configuration. These results do not include adding subnets, as the second (DATA) subnet was not available at the time of the tests. Subnets were added later and the resulting performance increase is shown below. The percentage improvements are lower than would normally be expected in a production environment because the device drivers available on the lab hardware had very few tuning options.

Table A-6 – Post Network Infrastructure Tuning Test Results (% Gain/Loss)

Network Infrastructure (No Subnets) No Tracking Message Body Tracking Orchestration Event Tracking Rule Event Tracking

STP
(BAM RTA)

5.0%

STP
(BAM Collection)

4.9%

Simple Orchestration
(BAM RTA)

4.4%

4.0%

4.0%

Simple Orchestration
(BAM Collection)

4.8%

Rule-based Orchestration
(BAM RTA)

4.1%

4.5%

4.4%

4.4%

Rule-based Orchestration
(BAM Collection)

4.0%

When combined with the results for the additional (DATA) subnet, the total network infrastructure tuning performance benefit ranges from 20-25%. Use of high-performance, highly tunable network cards that leverage the full power of the Windows Server 2003 offloading will significantly increase this performance improvement.

The following results were produced after applying SQL Server tuning (memory, locks, cache, working set, worker threads, TEMPDB optimization, etc.). Specialized settings (e.g., Text In Row) were applied in subsequent iterations as indicated below.

Table A-7 – Post Database Server Tuning Test Results (% Gain/Loss)

SQL Server Tuning (Memory, Locks, Threads, TEMPDB) No Tracking Message Body Tracking Orchestration Event Tracking Rule Event Tracking

STP
(BAM RTA)

30.8%

STP
(BAM Collection)

32.1%

Simple Orchestration
(BAM RTA)

12.3%

13.7%

14.1%

Simple Orchestration
(BAM Collection)

14.0%

Rule-based Orchestration
(BAM RTA)

13.9%

14.3%

14.1%

14.1%

Rule-based Orchestration
(BAM Collection)

13.6%

The performance of STP improved significantly, but the improvement in orchestration processing was considerably smaller. This likely indicates that other factors are limiting orchestration throughput (likely the number of threads dedicated to orchestration processing). This is a prime example of how tuning at various levels can interact, and why it is important to tune from the bottom up. In this case, provisioning additional orchestration threads would likely have little effect as the SQL Server constraints (especially the lock table) would still constrain their execution. But with the infrastructure value properly set, it is likely that proper tuning of the BizTalk application will produce results similar to that exhibited by STP.

The Text In Row table option was applied to the Parts and Spool tables of the BizTalkMsgBoxDb database. Various setting option size parameters were tested to determine the best-performing settings. For this test only, two different message sizes were tested using the STP (BAM RTA) No Tracking test case from above.

The following table shows the option size for each message size.

Table A-8 – Text In Row (TIR) Message Size vs. Option Size Test Results

Message Size 256 512 1024 2048 4096

550 Chars (1100 Bytes)

9.6 min

9.5 min

10.9 min

10.9 min

11.0 min

1015 Chars (2030 Bytes)

10.4 min

10.2 min

11.1 min

12.2 min

12.3 min

From these results it is clear that for the characteristic of the load used in this test, the Text In Row option should be enabled and set to 512 bytes. Option values larger than 512 actually degrade performance. The optimal option size may vary for other loads; setting an “optimal” value on systems with multiple load characteristics is likely impossible, therefore, the setting of 512 is generally recommended for this option across all system configurations (remember to set it on both Primary and Secondary message boxes, if present).

The TIR setting made a statistically insignificant difference in the performance of the orchestrations as their larger message size apparently put them outside the TIR setting size (512).

Table A-9 – Post Text-In-Row (512 bytes limit) Tuning Test Results (% Gain/Loss)

SQL Server Tuning (Text In Row) No Tracking Message Body Tracking Orchestration Event Tracking Rule Event Tracking

STP
(BAM RTA)

12.6%

STP
(BAM Collection)

12.4%

Simple Orchestration
(BAM RTA)

1.2%

1.5%

1.5%

Simple Orchestration
(BAM Collection)

1.2%

Rule-based Orchestration
(BAM RTA)

1.2%

1.6%

1.5%

1.5%

Rule-based Orchestration
(BAM Collection)

1.1%

The interaction of Text In Row with database multi-threading (in which the TEXT/LOB data for various tables is provisioned in its own filegroup with a dedicated I/O thread and queue) is an interesting one and bears further scrutiny. Based on the results seen both above and below it would appear that the combination of the two is a powerful performance enhancement, and may show even greater returns depending on the load characteristic of a given system (where, for example, very large messages are serviced side-by-side with smaller messages).

The following results were obtained after adding filegroups and additional data files to the BizTalk Server databases. For this case, the databases were all on the same server, significantly limiting the number of SAN LUNs available to host data files and therefore also the performance improvement obtained through multi-threading. Message Box and Tracking database data files were isolated to separate LUNs, while the management databases were shared on a single LUN. Log files in each case were placed on a dedicated LUN (one each for Message Box, Tracking database, and management database). To properly evaluate the effectiveness of this technique, the results of multi-threading should be combined with those obtained by distributing the BizTalk Server databases across multiple servers where each filegroup file is hosted on a separate SAN LUN. The combination of these two techniques yields maximum performance. Nonetheless, the performance improvement obtained by multi-threading even for a single server is significant. Also, in this case (as above) the improvement in orchestration performance is limited, likely by threading and/or BizTalk application issues.

Table A-10 – Post Database Multi-threading Test Results (% Gain/Loss)

SQL Server Tuning (Multi-threading) No Tracking Message Body Tracking Orchestration Event Tracking Rule Event Tracking

STP
(BAM RTA)

17.3%

STP
(BAM Collection)

16.9%

Simple Orchestration
(BAM RTA)

13.4%

13.5%

14.0%

Simple Orchestration
(BAM Collection)

13.3%

Rule-based Orchestration
(BAM RTA)

13.2%

12.9%

12.7%

13.3%

Rule-based Orchestration
(BAM Collection)

10.8%

There are two very interesting results from distributing the BizTalk Server databases across multiple servers according to the plan detailed in Appendix B. The first is that tracking appears to benefit significantly more than the message box. The second is that the need for additional database servers can be mitigated by proper tuning of the existing server(s). And like above, STP benefits more than orchestration; this indicates other constraints on orchestration processing such that if those constraints are removed, the orchestration performance at this level will also increase.

Table A-11 – Post Database Redistribution Test Results (% Gain/Loss)

SQL Server Tuning (Database Redistribution) No Tracking Message Body Tracking Orchestration Event Tracking Rule Event Tracking

STP
(BAM RTA)

14.1%

STP
(BAM Collection)

15.3%

Simple Orchestration
(BAM RTA)

6.0%

16.3%

16.2%

Simple Orchestration
(BAM Collection)

5.9%

Rule-based Orchestration
(BAM RTA)

6.3%

16.3%

17.7%

16.9%

Rule-based Orchestration
(BAM Collection)

7.5%

An additional, 1Gb subnet became available at the end of the lab work period and was tested on the (previously) “final” configuration. The results are as follows.

Table A-12 – Post Database Redistribution Test Results (% Gain/Loss)

Network Infrastructure Tuning (Subnets) No Tracking Message Body Tracking Orchestration Event Tracking Rule Event Tracking

STP
(BAM RTA)

26.8%

STP
(BAM Collection)

30.9%

Simple Orchestration
(BAM RTA)

10.7%

10.5%

11.3%

Simple Orchestration
(BAM Collection)

9.8%

Rule-based Orchestration
(BAM RTA)

14.5%

14.8%

14.0%

14.7%

Rule-based Orchestration
(BAM Collection)

14.0%

An unexpected but logical result was a larger performance improvement for the orchestrations using the BRE (BizTalk Rules Engine) than for the “simple” orchestrations. As above, orchestration performance improvements would be expected to mirror those of STP once other constraints are removed. Additional subnets, isolating BAM traffic from DTA (tracking) traffic, etc., would also likely have a large, positive impact on performance.

Performance Counters

The following performance counters were used during the lab work.

  • BAM Analysis Database
    • MSAS 2005:Cache\*
    • MSAS 2005:Connection\*
    • MSAS 2005:Data Mining Model Processing\*
    • MSAS 2005:Data Mining Prediction\*
    • MSAS 2005:Locks\*
    • MSAS 2005:MDX\*
    • MSAS 2005:Memory\*
    • MSAS 2005:Proactive Caching\*
    • MSAS 2005:Proc Aggregations\*
    • MSAS 2005:Proc Indexes\*
    • MSAS 2005:Processing\*
    • MSAS 2005:Storage Engine Query\*
    • MSAS 2005:Threads\*
  • BAM Primary Import Database
    • Cache\*
    • Distributed Transaction Coordinator\*
    • Memory\*
    • MSFTESQL:Catalogs(*)\*
    • MSFTESQL:Indexer PlugIn(*)\*
    • MSFTESQL:Service\*
    • NBT Connection(*)\*
    • Network Interface(*)\*
    • Paging File(*)\*
    • PhysicalDisk(*)\*
    • Processor(*)\*
    • Server Work Queues(*)\*
    • Server\*
    • SQLServer:Access Methods\*
    • SQLServer:Buffer Manager\*
    • SQLServer:Buffer Node(*)\*
    • SQLServer:Buffer Partition(*)\*
    • SQLServer:Catalog Metadata(*)\*
    • SQLServer:Cursor Manager by Type(*)\*
    • SQLServer:Cursor Manager Total\*
    • SQLServer:Databases(*)\*
    • SQLServer:Exec Statistics(*)\*
    • SQLServer:General Statistics\*
    • SQLServer:Latches\*
    • SQLServer:Locks(*)\*
    • SQLServer:Memory Manager\*
    • SQLServer:Plan Cache(*)\*
    • SQLServer:SQL Errors(*)\*
    • SQLServer:SQL Statistics\*
    • SQLServer:Transactions\*
    • SQLServer:User Settable(*)\*
    • SQLServer:Wait Statistics(*)\*
    • System\*
    • Thread(*)\*
  • BizTalk Server DTA Database
    • System\*
    • Thread(*)\*
    • Cache\*
    • Distributed Transaction Coordinator\*
    • Memory\*
    • MSFTESQL:Catalogs(*)\*
    • MSFTESQL:Indexer PlugIn(*)\*
    • MSFTESQL:Service\*
    • NBT Connection(*)\*
    • Network Interface(*)\*
    • Paging File(*)\*
    • PhysicalDisk(*)\*
    • Processor(*)\*
    • Server Work Queues(*)\*
    • Server\*
    • SQLAgent:Statistics\*
    • SQLServer:Access Methods\*
    • SQLServer:Buffer Manager\*
    • SQLServer:Buffer Node(*)\*
    • SQLServer:Buffer Partition(*)\*
    • SQLServer:Catalog Metadata(*)\*
    • SQLServer:Cursor Manager by Type(*)\*
    • SQLServer:Cursor Manager Total\*
    • SQLServer:Databases(*)\*
    • SQLServer:Exec Statistics(*)\*
    • SQLServer:General Statistics\*
    • SQLServer:Latches\*
    • SQLServer:Locks(*)\*
    • SQLServer:Memory Manager\*
    • SQLServer:Plan Cache(*)\*
    • SQLServer:SQL Errors(*)\*
    • SQLServer:SQL Statistics\*
    • SQLServer:Transactions\*
    • SQLServer:User Settable(*)\*
    • SQLServer:Wait Statistics(*)\*
    • System\*
    • Thread(*)\*
  • BizTalk Server MessageBox Database
    • Cache\*
    • Distributed Transaction Coordinator\*
    • Memory\*
    • MSFTESQL:Catalogs(*)\*
    • MSFTESQL:Indexer PlugIn(*)\*
    • MSFTESQL:Service\*
    • NBT Connection(*)\*
    • Network Interface(*)\*
    • Paging File(*)\*
    • PhysicalDisk(*)\*
    • Processor(*)\*
    • Server Work Queues(*)\*
    • Server\*
    • SQLAgent:Statistics\*
    • SQLServer:Access Methods\*
    • SQLServer:Buffer Manager\*
    • SQLServer:Buffer Node(*)\*
    • SQLServer:Buffer Partition(*)\*
    • SQLServer:Catalog Metadata(*)\*
    • SQLServer:Cursor Manager by Type(*)\*
    • SQLServer:Cursor Manager Total\*
    • SQLServer:Databases(*)\*
    • SQLServer:Exec Statistics(*)\*
    • SQLServer:General Statistics\*
    • SQLServer:Latches\*
    • SQLServer:Locks(*)\*
    • SQLServer:Memory Manager\*
    • SQLServer:Plan Cache(*)\*
    • SQLServer:SQL Errors(*)\*
    • SQLServer:SQL Statistics\*
    • SQLServer:Transactions\*
    • SQLServer:User Settable(*)\*
    • SQLServer:Wait Statistics(*)\*
    • System\*
    • Thread(*)\*
  • BizTalk Server
    • .NET CLR LocksAndThreads(*)\*
    • .NET CLR Memory(*)\*
    • .NET CLR Networking(*)\*
    • .NET Data Provider for SqlServer(*)\*
    • BizTalk:Message Agent(*)\*
    • BizTalk:Message Box:General Counters(*)\*
    • BizTalk:Message Box:Host Counters(*)\*
    • BizTalk:Messaging Latency(*)\*
    • BizTalk:Messaging(*)\*
    • BizTalk:MSMQ Receive Adapter(*)\*
    • BizTalk:MSMQ Send Adapter(*)\*
    • BizTalk:TDDS(*)\*
    • Distributed Transaction Coordinator\*
    • Memory\*
    • MSMQ Queue(*)\*
    • MSMQ Service\*
    • NBT Connection(*)\*
    • Network Interface(*)\*
    • Paging File(*)\*
    • PhysicalDisk(*)\*
    • Processor(*)\*
    • System\*
    • Thread(*)\*
    • XLANG/s Orchestrations(*)\*

This appendix contains the recommended BizTalk Server database configuration.

Summary

Tuning the physical SQL Server database structure can produce significant performance improvements. Many of the techniques listed in this section can be applied to application (non-BizTalk Server databases), however, the focus here is on the BizTalk Server databases.

The recommended tuning is summarized as follows:

  • Multi-thread BizTalk Server databases by creating filegroups and placing the supporting data file(s) for each in the new structure, pre-allocating and defragmenting the physical files to estimated maximum sizes; this includes both data and log files.
  • Move tables and indexes into the appropriate filegroups.
  • Make adjustments to the sizes of the physical files as they achieve static size based on the system configuration and load characteristic.

Depending on the resources (servers, SAN, etc.) available, it may not be possible to execute the entire database configuration as recommended below. In this case, optimize the databases in the priority indicated. The Tracking database (BizTalkDTADb) is second priority because its load has (by default) been significantly reduced for BizTalk Server 2006, however, if Message Body tracking, Debugging, Rule execution tracking, etc. are enabled it should be given equal or even greater priority than the message boxes. As a rule both the Message Boxes and the Tracking databases should be optimized.

Also, it may be difficult to obtain sufficient LUNs to fully implement the recommended configuration, especially when multiple databases are combined on small number of servers. In this case, too, allocate LUNs for the message box and tracking databases first. It is common for all of the management databases to share LUNs. If TPM (Trading Partner Management) is used, prioritize separating its LUNs from standard management functions. Do the same for SSO (Single Sign-On) if it is being used with affiliates or trading partners. And for the management database if the cross-referencing facility is used (see the BizTalk XREF API for details).

Database Optimization Priority (in constrained resource environments):

  • Message Box(s)
  • Tracking (equal priority with Message Boxes if message body tracking, etc.)
  • TPM (if Trading Partner Management is used)
  • SSO (if Single Sign-on is used with affiliates or partners)
  • MGMT
  • Others

Filegroups

Filegroup creation is very easy using Enterprise Manager (SQL Server 2000) or SSMS/SQL Server Management Studio (SQL Server 2005). Filegroups are part of the properties of a database.

Bb743398.note(en-US,BTS.10).gifNote
With SSMS filegroup, changes can be saved as scripts for replication to multiple environments.

Figure B-1 – Creating new filegroups in SQL Server 2005 SSMS
Bb743398.9b032f55-49cd-460c-90ca-5329b34040f1(en-US,BTS.10).gif

The objective of creating multiple filegroups is to isolate system tables in the default (PRIMARY) filegroup. Databases are created with PRIMARY as the default filegroup; this should be changed to MISC_DATA (or another filegroup, if preferred) so that new tables are not created in PRIMARY.

Pre-allocate the data file(s) supporting the filegroups. Multiple files (on separate physical devices, NOT WINDOWS PARTITIONS) per filegroup is the highest performance configuration, especially for the Message Instances filegroup.

Configure files to allow Autogrowth, especially when a system is new and the static sizes have not been definitively established. The growth increment should generally be no larger than 100MB for large files, 10MB or 1MB for medium and small files, respectively. Both SQL Server 2000 and SQL Server 2005 do demand-zero extension of data files (this can be disabled in SQL Server 2005), that is, the entire extension is zeroed before use. During this period, the file is locked and inaccessible for operations (causing a “pause” in database processing). Excessively large growth increments can cause very long delays. Never grow by percentage; this causes the increment to increase at an increasing rate (exponentially). Be sure to monitor, reallocate, and defragment the data and log files as table sizes change based on a particular system’s load characteristic. The message box and various management databases will eventually establish reasonably stable sizes over time. Configure the Tracking database purging SQL Agent job to achieve a size for that database.

The sizes listed are suggested initial values. If SAN space is at a premium it is possible to set a small initial size and allow the files to grow, however, without careful monitoring and frequent defragmentation this implementation choice will lead to very poor performance.

The following sections list the suggested filegroup names, sizes, and growth increments for each database.

Primary BizTalkMsgBoxDb Database

Filegroup File Name Initial Size (MB) Growth Increment (MB)

PRIMARY

BizTalkMsgBoxDb.mdf

1

1

MISC_DATA

BizTalkMsgBoxDB_MISC_DATA.ndf

100

10

MISC_INDEXES

BizTalkMsgBoxDB_MISC_INDEXES.ndf

10

1

PREDICATE_DATA

BizTalkMsgBoxDB_PREDICATE_DATA.ndf

100

10

PREDICATE_INDEXES

BizTalkMsgBoxDB_PREDICATE_INDEXES.ndf

10

1

MESSAGE_DATA

BizTalkMsgBoxDB_MESSAGE_DATA.ndf

10000

100

MESSAGE_PAYLOAD

BizTalkMsgBoxDB_MESSAGE_PAYLOAD.ndf

25000

100

MESSAGE_INSTANCES

BizTalkMsgBoxDB_MESSAGE_INSTANCES.ndf

10000

100

MESSAGE_INDEXES

BizTalkMsgBoxDB_MESSAGE_INDEXES.ndf

1000

10

Secondary BizTalkMsgBoxDb Database

Filegroup File Name Initial Size (MB) Growth Increment (MB)

PRIMARY

BizTalkMsgBoxDb.mdf

1

1

MISC_DATA

BizTalkMsgBoxDB_MISC_DATA.ndf

100

10

MISC_INDEXES

BizTalkMsgBoxDB_MISC_INDEXES.ndf

10

1

PREDICATE_DATA

BizTalkMsgBoxDB_PREDICATE_DATA.ndf

100

10

PREDICATE_INDEXES

BizTalkMsgBoxDB_PREDICATE_INDEXES.ndf

10

1

MESSAGE_DATA

BizTalkMsgBoxDB_MESSAGE_DATA.ndf

10000

100

MESSAGE_PAYLOAD

BizTalkMsgBoxDB_MESSAGE_PAYLOAD.ndf

25000

100

MESSAGE_INSTANCES

BizTalkMsgBoxDB_MESSAGE_INSTANCES.ndf

10000

100

MESSAGE_INDEXES

BizTalkMsgBoxDB_MESSAGE_INDEXES.ndf

1000

10

BizTalkDTADb Database

Filegroup File Name Initial Size (MB) Growth Increment (MB)

PRIMARY

BizTalkDTADb.mdf

1

1

MISC_DATA

BizTalkDTADb_MISC_DATA.ndf

2000

100

MISC_INDEXES

BizTalkDTADb_MISC_INDEXES.ndf

10

1

CALL_CHAIN_DATA

BizTalkDTADb_CALL_CHAIN.ndf

2000

100

CALL_CHAIN_INDEXES

BizTalkDTADb_CALL_CHAIN_INDEXES.ndf

100

10

DEBUG_TRACE_DATA

BizTalkDTADb_DEBUG_TRACE_DATA.ndf

2000

100

DEBUG_TRACE_INDEXES

BizTalkDTADb_DEBUG_TRACE_INDEXES.ndf

100

10

HWS_DATA

BizTalkDTADb_HWS_DATA.ndf

1000***

100

HWS_INDEXES

BizTalkDTADb_HWS_INDEXES.ndf

100***

10

RULE_DATA

BizTalkDTADb_RULE_DATA.ndf

12000**

100

RULE_INDEXES

BizTalkDTADb_RULE_INDEXES.ndf

1000**

10

MESSAGE_DATA

BizTalkDTADb_MESSAGE_DATA.ndf

15000

100

MESSAGE_PAYLOAD

BizTalkDTADb_MESSAGE_PAYLOAD.ndf

10000*

100

MESSAGE_INDEXES

BizTalkDTADb_MESSAGE_INDEXES.ndf

1000

10

*SQL Server 2000 only

**Sizes can be very small if the rules engine is not being used.

***Make larger if HWS events are being tracked

SSODB Database

Filegroup File Name Initial Size (MB) Growth Increment (MB)

PRIMARY

SSODB.mdf

1

1

MISC_DATA

SSODB_MISC_DATA.ndf

1

1

MISC_INDEXES

SSODB_MISC_INDEXES.ndf

1

1

BizTalkRuleEngineDb Database

Filegroup File Name Initial Size (MB) Growth Increment (MB)

PRIMARY

BizTalkRuleEngineDb.mdf

1

1

MISC_DATA

BizTalkRuleEngineDb_MISC_DATA.ndf

100

10

MISC_INDEXES

BizTalkRuleEngineDb_MISC_INDEXES.ndf

10

1

BizTalkHWSDb Database

Filegroup File Name Initial Size (MB) Growth Increment (MB)

PRIMARY

BizTalkHwsDb.mdf

1

1

MISC_DATA

BizTalkHwsDb_MISC_DATA.ndf

100

10

MISC_PAYLOAD

BizTalkHwsDb_MISC_PAYLOAD.ndf

100

10

MISC_INDEXES

BizTalkHwsDb_MISC_INDEXES.ndf

10

1

BizTalkEDIDb Database

Filegroup File Name Initial Size (MB) Growth Increment (MB)

PRIMARY

BizTalkEDIDb.mdf

1

1

MISC_DATA

BizTalkEDIDb_MISC_DATA.ndf

100

10

MISC_INDEXES

BizTalkEDIDb_MISC_INDEXES.ndf

10

1

BizTalkTPMDb Database

These settings assume TPM data is created in the BizTalkTPMDb database rather than BizTalkMgmtDb.

Filegroup File Name Initial Size (MB) Growth Increment (MB)

PRIMARY

BizTalkTPMDb.mdf

1

1

MISC_DATA

BizTalkTPMDb _MISC_DATA.ndf

10

1

MISC_INDEXES

BizTalkTPMDb _MISC_INDEXES.ndf

1

1

BizTalkAnalysisDb Database

These settings are for SQL Server 2000 only.

Filegroup File Name Initial Size (MB) Growth Increment (MB)

PRIMARY

BizTalkAnalysisDb.mdf

1

1

MISC_DATA

BizTalkAnalysisDb_MISC_DATA.ndf

100

10

MISC_INDEXES

BizTalkAnalysisDb_MISC_INDEXES.ndf

10

1

BizTalkMgmtDb Database

Filegroup File Name Initial Size (MB) Growth Increment (MB)

PRIMARY

BizTalkMgmtDb.mdf

1

1

MISC_DATA

BizTalkMgmtDb_MISC_DATA.ndf

100

10

MISC_INDEXES

BizTalkMgmtDb_MISC_INDEXES.ndf

10

1

XREF_DATA

BizTalkMgmtDb_XREF_DATA.ndf

10*

1

XREF_INDEXES

BizTalkMgmtDb_XREF_INDEXES.ndf

1*

1

*Adjust based on usage of these tables in applications

BAMPrimaryImport Database

Filegroup File Name Initial Size (MB) Growth Increment (MB)

PRIMARY

BAMPrimaryImport.mdf

1

1

MISC_DATA

BAMPrimaryImport_MISC_DATA.ndf

10000*

100

MISC_INDEXES

BAMPrimaryImport_MISC_INDEXES.ndf

100

10

*Varies greatly depending on how much BAM data is being tracked

BAMStarSchema Database

Filegroup File Name Initial Size (MB) Growth Increment (MB)

PRIMARY

BAMStarSchema.mdf

1

1

MISC_DATA

BAMStarSchema_MISC_DATA.ndf

1000

10

MISC_INDEXES

BAMStarSchema.MISC_INDEXES.ndf

100

10

BAMArchive Database

Filegroup File Name Initial Size (MB) Growth Increment (MB)

PRIMARY

BAMArchive.mdf

1

1

MISC_DATA

BAMArchive_MISC_DATA.ndr

100

10

MISC_INDEXES

BAMArchive_MISC_INDEXES.ndf

10

1

Log Files

There is no performance benefit to allocating multiple transaction log files as SQL Server uses them sequentially; only do this if there is insufficient space to put the entire log on a single physical device. The initial and growth increment sizes listed are suggestions; like data files, the transaction logs will establish a relatively stable size over time. To keep the log files smaller, configure the BizTalk Backup SQL Agent job to run more often (the default id ‘d’/Daily, consider ‘h’/Hourly).

Database File Name Initial Size (MB) Growth Increment (MB)

BizTalkMsgBoxDb

BizTalkMsgBoxDb.ldf

40000

100

BizTalkDTADb

BizTalkDTADb.ldf

25000

100

SSODB

SSODB.ldf

1

1

BizTalkRuleEngineDb

BizTalkRuleEngineDb.ldf

10

1

BizTalkHwsDb

BizTalkHwsDb.ldf

10

1

BizTalkEDIDb

BizTalkEDIDb.ldf

10

1

BizTalkTPMDb

BizTalkTPMDb.ldf

10

1

BizTalkAnalysisDb *

BizTalkAnalysisDb.ldf

100

10

BizTalkMgmtDb

BizTalkMgmtDb.ldf

100

10

BAMPrimaryImport

BAMPrimaryImport.ldf

5000

10

BAMStarSchema

BAMStarSchema.ldf

100

10

BAMArchive

BAMArchive.ldf

5000

10

*SQL Server 2K only

Tables

There are a number of techniques available for moving tables within the database. If a table has a clustered index (or one is temporarily added), moving the index (which can be accomplished using T-SQL) moves the table data as well. Note that Text/LOB data is not moved using this technique, nor are non-clustered indexes (these must be dropped and recreated in the target filegroup). Tables can be assigned directly to filegroups, including their non-clustered indexes and Text/LOB data, using the diagram tool. Adding a table to a database diagram makes its filegroup assignments available through the table properties; a script of the move operation can also be generated.

Bb743398.note(en-US,BTS.10).gifNote
These scripts are specific to an individual version of BizTalk Server, including Service Pack releases, and should only be generated and used by experienced database administrators.

The mapping of tables to filegroups listed below has been tested both in the lab and in many production deployments. Nonetheless it is not sacrosanct; and database administrators are certainly free to make changes as they set fit. It is strongly recommended that any such changes be tested for efficacy. The mapping is specified for BizTalk Server 2004 (including Service Pack 1), BizTalk Server 2006, and the BizTalk Server 2006 R2 Beta release. Any tables not specifically mapped to a filegroup should be moved to the default file group, which should be set to MISC_DATA for each data and Text/LOB data, and MISC_INDEXES for non-clustered indexes.

One last note. Moving tables to which views are schema bound will cause the schema binding to be dropped. SQL Server will issue a warning before taking this action; proceed then restore the schema binding to each view using T-SQL or the VIEW definition GUI.

BizTalkMsgBoxDb Database

BizTalk Server 2004 BizTalk Server 2006 BizTalk Server 2006 R2 Table Data Filegroup Text Filegroup Index Filegroup

X

X

X

ActiveRefCountLog

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

AddRef

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

ApplicationProps

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

Applications

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

BitwiseANDPredicates

PREDICATE_DATA

PREDICATE_DATA

PREDICATE_INDEXES

X

X

X

BizTalkDBVersion

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

BizTalkServerApplication_DequeueBatches

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

BizTalkServerApplication_MessageRefCountLog

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

BizTalkServerApplicationQ

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

BizTalkServerApplicationQ_Scheduled

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

BizTalkServerApplicationQ_Suspended

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

BizTalkServerIsolatedHost_DequeueBatches

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

BizTalkServerIsolatedHost_MessageRefCountLog

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

BizTalkServerIsolatedHostQ

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

BizTalkServerIsolatedHostQ_Scheduled

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

BizTalkServerIsolatedHostQ_Suspended

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

ConvoySetInstances

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

ConvoySets

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

DynamicStateInfo_BizTalkServerApplication

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

DynamicStateInfo_BizTalkServerIsolatedHost

MISC_DATA

MISC_DATA

MISC_INDEXES

X

EdiInt_Mic

MISC_DATA

MISC_DATA

MISC_INDEXES

X

EdiPartnerIncomingEdifactICN

MISC_DATA

MISC_DATA

MISC_INDEXES

X

EdiPartnerIncomingX12ICN

MISC_DATA

MISC_DATA

MISC_INDEXES

X

EdiSequenceNumbers

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

EqualsPredicates

PREDICATE_DATA

PREDICATE_DATA

PREDICATE_INDEXES

X

X

X

EqualsPredicates2ndPass

PREDICATE_DATA

PREDICATE_DATA

PREDICATE_INDEXES

X

X

X

ExistsPredicates

PREDICATE_DATA

PREDICATE_DATA

PREDICATE_INDEXES

X

X

X

FirstPassPredicates

PREDICATE_DATA

PREDICATE_DATA

PREDICATE_INDEXES

X

X

X

Fragments

MESSAGE_DATA

MESSAGE_PAYLOAD

MESSAGE_INDEXES

X

X

X

GreaterThanOrEqualsPredicates

PREDICATE_DATA

PREDICATE_DATA

PREDICATE_INDEXES

X

X

X

GreaterThanPredicates

PREDICATE_DATA

PREDICATE_DATA

PREDICATE_INDEXES

X

X

X

Instances

MESSAGE_INSTANCES

MESSAGE_INSTANCES

MESSAGE_INDEXES

X

X

InstancesOperatedOn

MESSAGE_INSTANCES

MESSAGE_INSTANCES

MESSAGE_INDEXES

X

X

X

InstancesPendingOperations

MESSAGE_INSTANCES

MESSAGE_INSTANCES

MESSAGE_INDEXES

X

X

InstancesSuspended

MESSAGE_INSTANCES

MESSAGE_INSTANCES

MESSAGE_INDEXES

X

X

X

InstanceStateMessageReferences_BizTalkServerApplication

MESSAGE_INSTANCES

MESSAGE_INSTANCES

MESSAGE_INDEXES

X

X

X

InstanceStateMessageReferences_BizTalkServerIsolatedHost

MESSAGE_INSTANCES

MESSAGE_INSTANCES

MESSAGE_INDEXES

X

X

X

LessThanOrEqualsPredicates

PREDICATE_DATA

PREDICATE_DATA

PREDICATE_INDEXES

X

X

X

LessThanPredicates

PREDICATE_DATA

PREDICATE_DATA

PREDICATE_INDEXES

X

X

X

LocalizedErrorStrings

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

MarkLog

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

MessageParts

MESSAGE_DATA

MESSAGE_DATA

MESSAGE_INDEXES

X

X

X

MessagePredicates

PREDICATE_DATA

PREDICATE_DATA

PREDICATE_INDEXES

X

X

X

MessageProps

MESSAGE_DATA

MESSAGE_DATA

MESSAGE_INDEXES

X

X

X

MessageRefCountLog1

MESSAGE_DATA

MESSAGE_DATA

MESSAGE_INDEXES

X

X

X

MessageRefCountLog2

MESSAGE_DATA

MESSAGE_DATA

MESSAGE_INDEXES

X

X

X

MessageRefCountLogTotals

MESSAGE_DATA

MESSAGE_DATA

MESSAGE_INDEXES

X

X

X

MessageZeroSum

MESSAGE_DATA

MESSAGE_DATA

MESSAGE_INDEXES

X

X

Modules

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

NotEqualsPredicates

PREDICATE_DATA

PREDICATE_DATA

PREDICATE_INDEXES

X

X

OperationsProgress

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

PartRefCountLog1

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

PartRefCountLog2

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

PartRefCountLogTotals

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

Parts

MESSAGE_DATA

MESSAGE_PAYLOAD

MESSAGE_INDEXES

X

X

X

PartZeroSum

MESSAGE_DATA

MESSAGE_DATA

MESSAGE_INDEXES

X

X

X

PredicateGroup

PREDICATE_DATA

PREDICATE_DATA

PREDICATE_INDEXES

X

X

X

PredicateGroupNames

PREDICATE_DATA

PREDICATE_DATA

PREDICATE_INDEXES

X

X

X

PredicateGroupZeroSum1

PREDICATE_DATA

PREDICATE_DATA

PREDICATE_INDEXES

X

X

X

PredicateGroupZeroSum2

PREDICATE_DATA

PREDICATE_DATA

PREDICATE_INDEXES

X

X

X

ProcessHeartbeats

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

Release

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

ServiceClasses

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

Services

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

Spool

MESSAGE_DATA

MESSAGE_PAYLOAD

MESSAGE_INDEXES

X

X

X

StaticStateInfo

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

Subscription

MISC_DATA

MISC_DATA

MISC_INDEXES

X

TrackedMessages

MESSAGE_DATA

MESSAGE_DATA

MESSAGE_INDEXES

X

X

X

Tracking_Fragments1

MESSAGE_DATA

MESSAGE_PAYLOAD

MESSAGE_INDEXES

X

X

X

Tracking_Fragments2

MESSAGE_DATA

MESSAGE_PAYLOAD

MESSAGE_INDEXES

X

X

X

Tracking_Parts1

MESSAGE_DATA

MESSAGE_PAYLOAD

MESSAGE_INDEXES

X

X

X

Tracking_Parts2

MESSAGE_DATA

MESSAGE_PAYLOAD

MESSAGE_INDEXES

X

X

X

Tracking_Spool1

MESSAGE_DATA

MESSAGE_PAYLOAD

MESSAGE_INDEXES

X

X

X

Tracking_Spool2

MESSAGE_DATA

MESSAGE_PAYLOAD

MESSAGE_INDEXES

X

TrackingData

MESSAGE_DATA

MESSAGE_PAYLOAD

MESSAGE_INDEXES

X

X

TrackingData_0_0

MESSAGE_DATA

MESSAGE_PAYLOAD

MESSAGE_INDEXES

X

X

TrackingData_0_1

MESSAGE_DATA

MESSAGE_PAYLOAD

MESSAGE_INDEXES

X

X

TrackingData_0_2

MESSAGE_DATA

MESSAGE_PAYLOAD

MESSAGE_INDEXES

X

X

TrackingData_0_3

MESSAGE_DATA

MESSAGE_PAYLOAD

MESSAGE_INDEXES

X

X

TrackingData_1_0

MESSAGE_DATA

MESSAGE_PAYLOAD

MESSAGE_INDEXES

X

X

TrackingData_1_1

MESSAGE_DATA

MESSAGE_PAYLOAD

MESSAGE_INDEXES

X

X

TrackingData_1_2

MESSAGE_DATA

MESSAGE_PAYLOAD

MESSAGE_INDEXES

X

X

TrackingData_1_3

MESSAGE_DATA

MESSAGE_PAYLOAD

MESSAGE_INDEXES

X

X

X

TrackingDataPartitions

MESSAGE_DATA

MESSAGE_DATA

MESSAGE_INDEXES

X

X

X

TrackingMessageReferences

MESSAGE_DATA

MESSAGE_DATA

MESSAGE_INDEXES

X

X

X

TrackingSpoolInfo

MESSAGE_DATA

MESSAGE_DATA

MESSAGE_INDEXES

X

X

X

TruncateRefCountLog

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

TrustedUsers

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

UniqueSubscription

MISC_DATA

MISC_DATA

MISC_INDEXES

SSODB Database

BizTalk Server 2004 BizTalk Server 2006 BizTalk Server 2006 R2 Table Data Filegroup Text Filegroup Index Filegroup

X

X

X

All User Tables

MISC_DATA

MISC_DATA

MISC_INDEXES

BizTalkRuleEngineDb Database

BizTalk Server 2004 BizTalk Server 2006 BizTalk Server 2006 R2 Table Data Filegroup Text Filegroup Index Filegroup

X

X

X

All User Tables

MISC_DATA

MISC_DATA

MISC_INDEXES

BizTalkEDIDb Database

BizTalk Server 2004 BizTalk Server 2006 BizTalk Server 2006 R2 Table Data Filegroup Text Filegroup Index Filegroup

X

X

X

All User Tables

MISC_DATA

MISC_DATA

MISC_INDEXES

BizTalkTPMDb Database

Use the following settings if separate from BizTalkMgmtDb.

BizTalk Server 2004 BizTalk Server 2006 BizTalk Server 2006 R2 Table Data Filegroup Text Filegroup Index Filegroup

X

X

X

All User Tables

MISC_DATA

MISC_DATA

MISC_INDEXES

BizTalkAnalysisDb Database

The following settings are for SQL Server 2000 only.

BizTalk Server 2004 BizTalk Server 2006 BizTalk Server 2006 R2 Table Data Filegroup Text Filegroup Index Filegroup

X

X

X

All User Tables

MISC_DATA

MISC_DATA

MISC_INDEXES

BizTalkHWSDb Database

BizTalk Server 2004 BizTalk Server 2006 BizTalk Server 2006 R2 Table Data Filegroup Text Filegroup Index Filegroup

X

X

X

All User Tables

MISC_DATA

MISC_DATA

MISC_INDEXES

BAMPrimaryImport Database

BizTalk Server 2004 BizTalk Server 2006 BizTalk Server 2006 R2 Table Data Filegroup Text Filegroup Index Filegroup

X

X

X

All User Tables

MISC_DATA

MISC_DATA

MISC_INDEXES

BAMStarSchema Database

BizTalk Server 2004 BizTalk Server 2006 BizTalk Server 2006 R2 Table Data Filegroup Text Filegroup Index Filegroup

X

X

X

All User Tables

MISC_DATA

MISC_DATA

MISC_INDEXES

BizTalkMgmtDb Database

BizTalk Server 2004 BizTalk Server 2006 BizTalk Server 2006 R2 Table Data Filegroup Text Filegroup Index Filegroup

X

X

X

adm_Adapter

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

adm_AdapterAlias

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

adm_BackupHistory

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

adm_BackupSetId

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

adm_BackupWriterLock

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

adm_ForceFullBackup

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

adm_Group

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

adm_Host

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

adm_HostInstance

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

adm_HostInstance_SubServices

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

adm_HostInstanceZombie

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

adm_MessageBox

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

adm_OtherBackupDatabases

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

adm_OtherDatabases

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

adm_ReceiveHandler

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

adm_ReceiveLocation

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

adm_SendHandler

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

adm_Server

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

adm_Server2HostMapping

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

adm_ServiceClass

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

adpl_sat

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

bam_ActivityToOrchestrationMapping

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

bam_TrackingProfiles

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

bam_TrackPoints

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

bas_Properties

BAS_DATA

BAS_DATA

BAS_INDEXES

X

X

X

BizTalkDBVersion

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

bt_DocumentSpec

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

bt_MapSpec

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

bt_Properties

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

bt_SensitiveProperties

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

bt_XMLShare

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

bt_XMLShareReferences

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

btf_message_receiver

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

btf_message_sender

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

bts_application

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

bts_application_reference

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

bts_assembly

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

bts_component

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

bts_dynamicport_subids

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

bts_enlistedparty

BAS_DATA

BAS_DATA

BAS_INDEXES

X

X

X

bts_enlistedparty_operation_mapping

BAS_DATA

BAS_DATA

BAS_INDEXES

X

X

X

bts_enlistedparty_port_mapping

BAS_DATA

BAS_DATA

BAS_INDEXES

X

X

X

bts_item

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

bts_itemreference

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

bts_libreference

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

bts_messagetype

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

bts_messagetype_part

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

bts_operation_msgtype

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

bts_orchestration

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

bts_orchestration_invocation

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

bts_orchestration_port

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

bts_orchestration_port_binding

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

bts_party

BAS_DATA

BAS_DATA

BAS_INDEXES

X

X

X

bts_party_alias

BAS_DATA

BAS_DATA

BAS_INDEXES

X

X

X

bts_party_sendport

BAS_DATA

BAS_DATA

BAS_INDEXES

X

X

X

bts_pipeline

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

bts_pipeline_config

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

bts_pipeline_stage

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

bts_port_activation_operation

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

bts_porttype

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

bts_porttype_operation

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

bts_receiveport

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

bts_receiveport_transform

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

bts_role

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

bts_role_porttype

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

bts_rolelink

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

bts_rolelink_type

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

bts_sendport

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

bts_sendport_transform

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

bts_sendport_transport

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

bts_sendportgroup

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

bts_spg_sendport

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

bts_stage_config

MISC_DATA

MISC_DATA

MISC_INDEXES

X

edi_DbConfig

MISC_DATA

MISC_DATA

MISC_INDEXES

X

EdiAgreementAttachments

MISC_DATA

MISC_DATA

MISC_INDEXES

X

EdiGlobalCommon

MISC_DATA

MISC_DATA

MISC_INDEXES

X

EdiGlobalEdifact

MISC_DATA

MISC_DATA

MISC_INDEXES

X

EdiGlobalX12

MISC_DATA

MISC_DATA

MISC_INDEXES

X

EdiInt_Partner

MISC_DATA

MISC_DATA

MISC_INDEXES

X

EdiInt_PartnerCommon

MISC_DATA

MISC_DATA

MISC_INDEXES

X

EdiMessageContent

MISC_DATA

MISC_DATA

MISC_INDEXES

X

EdiPartnerAckValidation

MISC_DATA

MISC_DATA

MISC_INDEXES

X

EdiPartnerAgreement

MISC_DATA

MISC_DATA

MISC_INDEXES

X

EdiPartnerBatchSchedule

MISC_DATA

MISC_DATA

MISC_INDEXES

X

EdiPartnerContacts

MISC_DATA

MISC_DATA

MISC_INDEXES

X

EdiPartnerEdifactInterchange

MISC_DATA

MISC_DATA

MISC_INDEXES

X

EdiPartnerEdifactReceiverAppSender

MISC_DATA

MISC_DATA

MISC_INDEXES

X

EdiPartnerEdifactReceiverGroup

MISC_DATA

MISC_DATA

MISC_INDEXES

X

EdiPartnerEdifactSenderAppSender

MISC_DATA

MISC_DATA

MISC_INDEXES

X

EdiPartnerEdifactSenderGroup

MISC_DATA

MISC_DATA

MISC_INDEXES

X

EdiPartnerX12Interchange

MISC_DATA

MISC_DATA

MISC_INDEXES

X

EdiPartnerX12ReceiverGroup

MISC_DATA

MISC_DATA

MISC_INDEXES

X

EdiPartnerX12SenderGroup

MISC_DATA

MISC_DATA

MISC_INDEXES

X

EDIX12ST01GS01Mapping

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

MarkLog

MISC_DATA

MISC_DATA

MISC_INDEXES

X

PAM_Batching_Log

MISC_DATA

MISC_DATA

MISC_INDEXES

X

PAM_Control

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

StaticTrackingInfo

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

TDDS_CustomFormats

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

TDDS_Destinations

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

TDDS_Heartbeats

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

TDDS_Services

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

TDDS_Settings

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

TDDS_Sources

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

Trackinginterceptor

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

TrackinginterceptorVersions

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

xref_AppInstance

XREF_DATA

XREF_DATA

XREF_INDEXES

X

X

X

xref_AppType

XREF_DATA

XREF_DATA

XREF_INDEXES

X

X

X

xref_IDXRef

XREF_DATA

XREF_DATA

XREF_INDEXES

X

X

X

xref_IDXRefData

XREF_DATA

XREF_DATA

XREF_INDEXES

X

X

X

xref_MessageArgument

XREF_DATA

XREF_DATA

XREF_INDEXES

X

X

X

xref_MessageDef

XREF_DATA

XREF_DATA

XREF_INDEXES

X

X

X

xref_MessageText

XREF_DATA

XREF_DATA

XREF_INDEXES

X

X

X

xref_ValueXRef

XREF_DATA

XREF_DATA

XREF_INDEXES

X

X

X

xref_ValueXRefData

XREF_DATA

XREF_DATA

XREF_INDEXES

BizTalkDTADb Database

BizTalk Server 2004 BizTalk Server 2006 BizTalk Server 2006 R2 Table Data Filegroup Text Filegroup Index Filegroup

X

X

X

BizTalkDBVersion

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

dta_Adapter

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

dta_ArchiveHistory

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

dta_CallChain

CALLCHAIN_DATA

CALLCHAIN_DATA

CALLCHAIN_INDEXES

X

X

dta_CallChainTemp

CALLCHAIN_DATA

CALLCHAIN_DATA

CALLCHAIN_INDEXES

X

X

X

dta_Cubes

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

dta_DebugTrace

DEBUG_TRACE_DATA

DEBUG_TRACE_DATA

DEBUG_TRACE_INDEXES

X

X

X

dta_DebugTraceTemp

DEBUG_TRACE_DATA

DEBUG_TRACE_DATA

DEBUG_TRACE_INDEXES

X

X

X

dta_DecryptionSubject

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

dta_Group

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

dta_Host

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

dta_Items

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

dta_ItemTypes

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

dta_MessageBox

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

dta_MessageFields

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

dta_MessageFieldValues

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

dta_MessageFieldValuesTemp

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

dta_MessageInOutEvents

MESSAGE_DATA

MESSAGE_PAYLOAD

MESSAGE_INDEXES

X

X

dta_MessageInOutEventsTemp

MESSAGE_DATA

MESSAGE_PAYLOAD

MESSAGE_INDEXES

X

dta_MessageInstances

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

dta_MessageStatus

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

dta_PartyName

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

dta_PortName

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

dta_ProcessState

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

dta_Rules

RULE_DATA

RULE_DATA

RULE_INDEXES

X

X

X

dta_RulesAgendaUpdates

RULE_DATA

RULE_DATA

RULE_INDEXES

X

X

X

dta_RulesConditionEvaluation

RULE_DATA

RULE_DATA

RULE_INDEXES

X

X

X

dta_RuleSetEngineAssociation

RULE_DATA

RULE_DATA

RULE_INDEXES

X

X

X

dta_RuleSets

RULE_DATA

RULE_DATA

RULE_INDEXES

X

X

X

dta_RulesFactActivity

RULE_DATA

RULE_DATA

RULE_INDEXES

X

X

X

dta_RulesFired

RULE_DATA

RULE_DATA

RULE_INDEXES

X

X

X

dta_SchemaName

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

dta_ServiceInstanceExceptions

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

dta_ServiceInstances

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

dta_ServiceInstancesTemp

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

dta_Services

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

dta_ServiceState

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

dta_ServiceSymbols

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

dta_SigningSubject

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

Hws_ActionInstanceLinks

HWS_DATA

HWS_DATA

HWS_INDEXES

X

X

Hws_ActionInstances

HWS_DATA

HWS_DATA

HWS_INDEXES

X

X

Hws_ActivityFlows

HWS_DATA

HWS_DATA

HWS_INDEXES

X

X

Hws_ActivityFlowsActorLinks

HWS_DATA

HWS_DATA

HWS_INDEXES

X

X

Hws_ActivityModelActivationInstances

HWS_DATA

HWS_DATA

HWS_INDEXES

X

X

Hws_Actors

HWS_DATA

HWS_DATA

HWS_INDEXES

X

X

Hws_ParentActionInstanceActivationBlocks

HWS_DATA

HWS_DATA

HWS_INDEXES

X

X

Hws_TaskReassignment

HWS_DATA

HWS_DATA

HWS_INDEXES

X

X

Hws_TaskResponses

HWS_DATA

HWS_DATA

HWS_INDEXES

X

X

Hws_Tasks

HWS_DATA

HWS_DATA

HWS_INDEXES

X

dta_Url

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

MarkLog

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

TDDS_FailedTrackingData

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

TDDS_StreamStatus

MISC_DATA

MISC_DATA

MISC_INDEXES

X

TrackedMessages

MESSAGE_DATA

MESSAGE_DATA

MESSAGE_INDEXES

X

X

X

Tracking_Fragments1

MESSAGE_DATA

MESSAGE_PAYLOAD

MESSAGE_INDEXES

X

X

X

Tracking_Fragments2

MESSAGE_DATA

MESSAGE_PAYLOAD

MESSAGE_INDEXES

X

X

X

Tracking_Parts1

MESSAGE_DATA

MESSAGE_PAYLOAD

MESSAGE_INDEXES

X

X

X

Tracking_Parts2

MESSAGE_DATA

MESSAGE_PAYLOAD

MESSAGE_INDEXES

X

X

X

Tracking_Spool1

MESSAGE_DATA

MESSAGE_PAYLOAD

MESSAGE_INDEXES

X

X

X

Tracking_Spool2

MESSAGE_DATA

MESSAGE_PAYLOAD

MESSAGE_INDEXES

X

TrackingData

MESSAGE_DATA

MESSAGE_PAYLOAD

MESSAGE_INDEXES

X

X

TrackingData_0_0

MESSAGE_DATA

MESSAGE_PAYLOAD

MESSAGE_INDEXES

X

X

TrackingData_0_1

MESSAGE_DATA

MESSAGE_PAYLOAD

MESSAGE_INDEXES

X

X

TrackingData_0_2

MESSAGE_DATA

MESSAGE_PAYLOAD

MESSAGE_INDEXES

X

X

TrackingData_0_3

MESSAGE_DATA

MESSAGE_PAYLOAD

MESSAGE_INDEXES

X

X

TrackingData_1_0

MESSAGE_DATA

MESSAGE_PAYLOAD

MESSAGE_INDEXES

X

X

TrackingData_1_1

MESSAGE_DATA

MESSAGE_PAYLOAD

MESSAGE_INDEXES

X

X

TrackingData_1_2

MESSAGE_DATA

MESSAGE_PAYLOAD

MESSAGE_INDEXES

X

X

TrackingData_1_3

MESSAGE_DATA

MESSAGE_PAYLOAD

MESSAGE_INDEXES

X

X

X

TrackingDataPartitions

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

TrackingMessageReferences

MISC_DATA

MISC_DATA

MISC_INDEXES

X

X

X

TrackingSpoolInfo

MISC_DATA

MISC_DATA

MISC_INDEXES

A common problem in BizTalk Server deployment is the need to reconfigure applications, and especially bindings, as the deployed BizTalk Server components are migrated between environments. The problem occurs because developers typically bind to the name of the server(s) in the development environment, which are of course different from those of QA, STAGING, UAT, PRODUCTION, DR. There are two solutions to this problem. The first is to manually edit binding and configuration files, changing each server specification (and hoping not to miss any). The second is to avoid binding to physical servers, and instead bind to logical servers.

While logical servers can be created several ways, the method with the greatest flexibility in terms of applicability across all BizTalk components (e.g., WSS/SPS, SQL Server, third party servers, etc.) is DNS CNAMEs. CNAMEs are DNS entries that reference other DNS entries (e.g., A records) rather than specific IP addresses. These CNAME aliases are used in the place of server names in all BizTalk application configuration artifacts, including binding files, database connection strings, UNC paths, etc. The CNAMEs are defined in each environment with translations specific to that environment. The deployed application(s) and included artifacts – the physical bits - are not changed in any way as they are migrated between environments.

A key consideration when employing DNS aliases is how to partition the domain namespace. Ideally, each environment would define a separate namespace serviced by its own DNS infrastructure; in reality this is rarely the case. DR configurations in particular are often configured to replicate the primary environment. For Windows, DNS is typically bound to Active Directory®. To facilitate the use of CNAMEs in AD-integrated, replicated DNS environments, configure a local, non-AD-integrated DNS server (which may also be replicated independent of the main environment). Use this second environment to define the CNAME aliases. For example, if the primary domain is my.domain.com, the domain for the aliases might be my.domain.lcl.

Alternatively (and preferably), configure the replication to function only within each environment rather than across environments. The FQDN (Fully Qualified Domain Name) of the each alias is the same regardless of the environment. This configuration requires a bit more work to maintain, but once created it should be relatively stable, and the maintenance is considerably less than that required to manually migrate applications across environments.

Aliasing is completely compatible with subnets, and can in fact make subnet management easier. In the same way that aliases abstract physical servers, they can abstract subnets, too. This is important because QA and Development environments may not have the same subnet configuration as Production. For example, in production the BTSDB_BAMPI alias, which abstracts the server hosting the BAM Primary Import database, might reference a separate BAM subnet as bam.server.my.domain.com, while in development there is likely only a single subnet (such as dev.my.domain.com). Using the alias in the configuration file, the BAM tools, etc. allows the same artifacts to be deployed in either environment unchanged.

The following script was developed to create aliases for all of the standard BizTalk Server artifacts (both system and adapter databases), as well as web services, external access, individual BizTalk server(s) and other optional configuration items. It requires the DNSCMD utility, which is a DNS command-line based configuration tool included with the (freely downloaded) Windows Support Tools, which are available at msdn.microsoft.com. It works in both 32 and 64-bit environments and is easily extended for other adapters, additional web services, etc. Create and maintain a separate copy of this script for each environment to be configured, and use it as a change tool (when a new alias is to be added, add it to the script and use the script to create the alias; this helps facilitate compliance auditing (e.g., S/OX) as well as consistency across environments.

One oddity has occasionally been observed when running the BizTalk Backup job in an environment with a single SQL Server. This configuration sometimes experiences backup failure with an error that the server could not link to itself. The only effective workaround found to date is to add the server to itself as a linked server. This is easily done with T-SQL using SQL Server Management Studio as follows.

Example – Adding a Server to Itself as a Linked Server

EXECUTE sp_addlinkedserver N’<my server name>’

Sample Code for BizTalkCNAMEAliases.bat

@ECHO off
REM =======================================================================
REM
REM   Copyright 2007-Present, Neudesic, LLC.  All Rights Reserved.
REM
REM   v1.1, Authored by John B. Brockmeyer, P.E.
REM
REM -------------------------------------------------------------------
REM
REM   Name: 
REM
REM      BizTalkCNAMEAliases.bat
REM
REM -------------------------------------------------------------------
REM
REM   Description:
REM
REM      Create CNAME aliases for all BizTalk databases in the 
REM         specified domain.
REM      Note that the alias target can be an individual server, the 
REM         virtual server of a cluster, or a HW/SW load balancing 
REM         appliance.  The purpose of aliasing is to isolate the 
REM         BizTalk configuration from individual server
REM      FQDN's so that replacing a server, moving a database, 
REM         incorporating a BizTalk adapter on a new or different 
REM         server, etc. does not affect the BizTalk configuration (the 
REM         BizTalk configuration does not need to be modified).
REM
REM      The application database section is especially important 
REM         because the aliases defined there should be incorporated 
REM         into connection strings throughout BizTalk (orchestrations, 
REM         maps, adapters, etc.).  Using the aliases rather than the 
REM         actual server/cluster names allows the referenced databases
REM      to be moved without requiring refactoring.  They are also 
REM         VERY useful for establishing multiple environments (DEV, 
REM         QA, STAGING, PROD) without requiring code changes to 
REM         account for changing environments (each environment can 
REM         have its own alias definitions, in AD, HOST files, etc.).
REM
REM      Database aliases (application or BizTalk) are created for 
REM         each individual database, NOT FOR DATABASE SERVERS.  This 
REM         allows a database to be moved between different servers 
REM         with no changes to connection strings, BizTalk 
REM         configuration, etc.
REM
REM      Once modified, this script can be used to duplicate the 
REM         BizTalk configuration for DR, QA and/or Staging 
REM         environments, etc.
REM
REM -------------------------------------------------------------------
REM
REM   Requires:
REM
REM      Windows Server 2003 Support Tools
REM
REM -------------------------------------------------------------------
REM
REM   Instructions:
REM
REM      (1) Set the FQDN of the domain where the aliases are to be 
REM          created
REM
REM      (2) Set the FQDN of the default target servers (one for 
REM          BizTalk, one for SQL Server) - this FQDN will be used as 
REM          the default for the creation of all aliases unless another 
REM          FQDN is specified, and provides an easy mechanism to 
REM          direct all of the aliases to a single BizTalk or SQL 
REM          Server, respectively.
REM
REM      (3) Set the BizTalk system database alias target FQDN's 
REM          (these default to the SQL Server FQDN set in (2)) - if 
REM          databases are not required (the corresponnding BizTalk 
REM          feature is not installed) comment out the FQDN definition 
REM          statement and the alias will not be created.
REM
REM      (4) Set the BizTalk adapter database alias target FQDN's 
REM          (these default to the SQL Server FQDN set in (2)) - if 
REM          databases are not required (the corresponnding BizTalk 
REM          feature is not installed) comment out the FQDN definition 
REM          statement and the alias will not be created.
REM
REM      (5) Set the alias for the BizTalk SMTP Server - this server 
REM          must have port 25 open and relaying enabled for internal 
REM          users; it is used for BAM notifications and other BizTalk 
REM          alerting.
REM
REM      (6) Set the BizTalk server alias target FQDN's (these 
REM          default to the BizTalk FQDN set in (2)) - if server 
REM          aliases are not required (the corresponding BizTalk 
REM          feature is not installed) comment out the FQDN definition
REM          statement and the alias will not be created.
REM
REM      (7) Set the alias and target FQDN for each BizTalk 
REM          interface (comment out the FQDN definition for any 
REM          interfaces not needed) - by default the FQDN's are the 
REM          BizTalk Server default FQDN as defined above, however, 
REM          any target FQDN is acceptable (even one in a different 
REM          domain), however, the alias will always be part of the 
REM          domain specified by the DomainFQDN value - aliases may 
REM          refer to physical servers OR a H/W or S/W (e.g., NLB)
REM          load balanced virtual cluster.
REM
REM      (8) Set the alias and target FQDN for each of the application 
REM          web services- NOT WEB SERVERS (comment out the FQDN 
REM          definition for any web services not needed) - by default REM          the FQDN's are the BizTalk Server default FQDN as defined REM          above, however, any FQDN is acceptable (even one in a 
REM          different domain) but the alias will always be part of the REM          domain specified by the DomainFQDN value
REM
REM      (9) Set the alias and target FQDN for each of the application REM          databases - NOT DATABASE SERVERS (comment out the FQDN 
REM          definition for any databases not needed) - by default the
REM          FQDN's are the SQL Server default FQDN as defined above,
REM          however, any FQDN is acceptable (even one in a different 
REM          domain) but the alias will always be part of the domain
REM          specified by the DomainFQDN value
REM
REM      (10) Set the alias and target FQDN for each of the SharePoint 
REM          databases - NOT DATABASE SERVERS (comment out the FQDN
REM          definition for any databases not needed) - by default the
REM          FQDN's are the SQL Server default FQDN as defined above, 
REM          however, any FQDN is acceptable (even one in a different 
REM          domain) but the alias will always be part of the domain
REM          specified by the DomainFQDN value.
REM
REM          NOTE THAT THE SPS COnfiguration Database Server 
REM          (SPSDB_CONFIG) is required if SPS is being configured
REM          (does not already exist in the system).
REM
REM      (11) Set the path to the DNSCMD utility (from the Windows 
REM          Server 2003 support tools), if not installed on the
REM          default path
REM
REM -------------------------------------------------------------------
REM
REM      History:
REM
REM       Date      Name             Description
REM       _____________________________________________________________
REM
REM       3/8/2007      John B. Brockmeyer, P.E.    v1.0
REM       3/12/2007     John B. Brockmeyer, P.E.    v1.1
REM       4/7/2007      John B. Brockmeyer, P.E.    v1.2
REM       5/17/2007     John B. Brockmeyer, P.E.    v1.3
REM
REM =======================================================================================

REM Initialize the environment
@SETLOCAL ENABLEEXTENSIONS

REM =======================================================================================
REM (1) Set the FQDN of the domain where the aliases are to be created - this is the FQDN 
REM   of the domain where the aliases will be created and must have an associated domain
REM   controller(s)
REM =======================================================================================

SET DomainFQDN=my.domain.com


REM =======================================================================================
REM (2) Set the FQDN of the default target servers - these FQDN's may extend the Domain
REM   FQDN or be completely separate (providing a trust relationship has been established
REM   between the respective domain controllers), for example, if the Domain FQDN is
REM   mydomain.com, the default BizTalk server FQDN could be server01.bts.mydomain.com 
REM =======================================================================================

SET DefaultBTSServerFQDN=BTS01.%DomainFQDN%
SET DefaultSQLServerFQDN=SQL01.%DomainFQDN%


REM =======================================================================================
REM (3) Set the alias and target FQDN for each of the BizTalk system databases - NOT 
REM   DATABASE SERVERS (comment out the FQDN definition for any databases not needed) - 
REM   by default the FQDN's are the SQL Server default FQDN as defined above, however, 
REM   any target FQDN is acceptable (even one in a different domain) but the alias will 
REM   always be part of the domain specified by the DomainFQDN value
REM =======================================================================
SET BTSDB_MGMT_ALIAS=BTSDB_MGMT
SET BTSDB_MGMT_FQDN=%DefaultSQLServerFQDN%
REM ------------------------------------------------------------
SET BTSDB_PMSG_ALIAS=BTSDB_PMSG
SET BTSDB_PMSG_FQDN=%DefaultSQLServerFQDN%
REM ------------------------------------------------------------
SET BTSDB_SMSG01_ALIAS=BTSDB_SMSG01
REM SET BTSDB_SMSG01_FQDN=%DefaultSQLServerFQDN%
REM ------------------------------------------------------------
SET BTSDB_SMSG02_ALIAS=BTSDB_SMSG02
REM SET BTSDB_SMSG02_FQDN=%DefaultSQLServerFQDN%
REM ------------------------------------------------------------
SET BTSDB_SMSG03_ALIAS=BTSDB_SMSG03
REM SET BTSDB_SMSG03_FQDN=%DefaultSQLServerFQDN%
REM ------------------------------------------------------------
SET BTSDB_SMSG04_ALIAS=BTSDB_SMSG04
REM SET BTSDB_SMSG04_FQDN=%DefaultSQLServerFQDN%
REM ------------------------------------------------------------
SET BTSDB_SMSG05_ALIAS=BTSDB_SMSG05
REM SET BTSDB_SMSG05_FQDN=%DefaultSQLServerFQDN%
REM ------------------------------------------------------------
SET BTSDB_DTA_ALIAS=BTSDB_DTA
SET BTSDB_DTA_FQDN=%DefaultSQLServerFQDN%
REM ------------------------------------------------------------
SET BTSDB_SSO_ALIAS=BTSDB_SSO
SET BTSDB_SSO_FQDN=%DefaultSQLServerFQDN%
REM ------------------------------------------------------------
SET BTSDB_RE_ALIAS=BTSDB_RE
SET BTSDB_RE_FQDN=%DefaultSQLServerFQDN%
REM ------------------------------------------------------------
SET BTSDB_HWS_ALIAS=BTSDB_HWS
REM SET BTSDB_HWS_FQDN=%DefaultSQLServerFQDN%
REM ------------------------------------------------------------
SET BTSDB_TPM_ALIAS=BTSDB_TPM
SET BTSDB_TPM_FQDN=%DefaultSQLServerFQDN%
REM ------------------------------------------------------------
SET BTSDB_BAMPI_ALIAS=BTSDB_BAMPI
SET BTSDB_BAMPI_FQDN=%DefaultSQLServerFQDN%
REM ------------------------------------------------------------
SET BTSDB_BAMSS_ALIAS=BTSDB_BAMSS
SET BTSDB_BAMSS_FQDN=%DefaultSQLServerFQDN%
REM ------------------------------------------------------------
SET BTSDB_BAMARCH_ALIAS=BTSDB_BAMARCH
SET BTSDB_BAMARCH_FQDN=%DefaultSQLServerFQDN%
REM ------------------------------------------------------------
SET BTSDB_BAMAN_ALIAS=BTSDB_BAMAN
SET BTSDB_BAMAN_FQDN=%DefaultSQLServerFQDN%
REM ------------------------------------------------------------
SET BTSDB_BAMALERTS_ALIAS=BTSDB_BAMALERTS
SET BTSDB_BAMALERTS_FQDN=%DefaultSQLServerFQDN%
REM ------------------------------------------------------------
SET BTSDB_RPTSVR_ALIAS=BTSDB_RPTSVR
SET BTSDB_RPTSVR_FQDN=%DefaultSQLServerFQDN%
REM ------------------------------------------------------------


REM =======================================================================================
REM (4) Set the alias and target FQDN for each of the BizTalk adapter databases - NOT 
REM   DATABASE SERVERS (comment out the FQDN definition for any databases not needed) - 
REM   by default the FQDN's are the SQL Server default FQDN as defined above, however, 
REM   any target FQDN is acceptable (even one in a different domain) but the alias will 
REM   always be part of the domain specified by the DomainFQDN value
REM =======================================================================================

SET BTSDB_EDI_ALIAS=BTSDB_EDI
REM SET BTSDB_EDI_FQDN=%DefaultSQLServerFQDN%
REM ------------------------------------------------------------
SET BTSDB_FIX_ALIAS=BTSDB_FIX
REM SET BTSDB_FIX_FQDN=%DefaultSQLServerFQDN%
REM ------------------------------------------------------------
SET BTSDB_HL7_ALIAS=BTSDB_HL7
REM SET BTSDB_HL7_FQDN=%DefaultSQLServerFQDN%
REM ------------------------------------------------------------
SET BTSDB_SWIFT_ALIAS=BTSDB_SWIFT
REM SET BTSDB_SWIFT_FQDN=%DefaultSQLServerFQDN%
REM ------------------------------------------------------------
SET BTSDB_ADAPTER_1_ALIAS=BTSDB_ADAPTER_1
REM SET BTSDB_ADAPTER_1_FQDN=%DefaultSQLServerFQDN%
REM ------------------------------------------------------------
SET BTSDB_ADAPTER_2_ALIAS=BTSDB_ADAPTER_2
REM SET BTSDB_ADAPTER_2_FQDN=%DefaultSQLServerFQDN%
REM ------------------------------------------------------------
SET BTSDB_ADAPTER_3_ALIAS=BTSDB_ADAPTER_3
REM SET BTSDB_ADAPTER_3_FQDN=%DefaultSQLServerFQDN%
REM ------------------------------------------------------------
SET BTSDB_ADAPTER_4_ALIAS=BTSDB_ADAPTER_4
REM SET BTSDB_ADAPTER_4_FQDN=%DefaultSQLServerFQDN%
REM ------------------------------------------------------------
SET BTSDB_ADAPTER_5_ALIAS=BTSDB_ADAPTER_5
REM SET BTSDB_ADAPTER_5_FQDN=%DefaultSQLServerFQDN%
REM ------------------------------------------------------------


REM =======================================================================================
REM (5) Set the alias for the BAM SMTP Server - this server must have port 25 open and 
REM   relaying enabled for internal users; it is used for BAM notifications and other
REM   BizTalk alerting
REM =======================================================================================

SET BTS_MAILSERVER_ALIAS=BTS_MAILSERVER
SET BTS_MAILSERVER_FQDN=SERVER1.%DomainFQDN%
REM ------------------------------------------------------------


REM =======================================================================================
REM (6) Set the alias and target FQDN for each BizTalk Server (comment out the FQDN 
REM   definition for any servers not needed) - by default the FQDN's are the BizTalk 
REM   Server default FQDN as defined above, however, any target FQDN is acceptable (even 
REM   one in a different domain), however, the alias will always be part of the domain 
REM   specified by the DomainFQDN value - note that server aliases refer to actual 
REM   physical servers or Windows clusters; it is rare that an external reference will
REM   make use of a BizTalk server aliase (they make reference to interface aliases)
REM =======================================================================================

SET BTS_01_ALIAS=BTS_01
SET BTS_01_FQDN=%DefaultBTSServerFQDN%
REM ------------------------------------------------------------
SET BTS_02_ALIAS=BTS_02
REM SET BTS_02_FQDN=%DefaultBTSServerFQDN%
REM ------------------------------------------------------------
SET BTS_03_ALIAS=BTS_03
REM SET BTS_03_FQDN=%DefaultBTSServerFQDN%
REM ------------------------------------------------------------
SET BTS_04_ALIAS=BTS_04
REM SET BTS_04_FQDN=%DefaultBTSServerFQDN%
REM ------------------------------------------------------------
SET BTS_05_ALIAS=BTS_05
REM SET BTS_05_FQDN=%DefaultBTSServerFQDN%
REM ------------------------------------------------------------
SET BTS_06_ALIAS=BTS_06
REM SET BTS_06_FQDN=%DefaultBTSServerFQDN%
REM ------------------------------------------------------------
SET BTS_07_ALIAS=BTS_07
REM SET BTS_07_FQDN=%DefaultBTSServerFQDN%
REM ------------------------------------------------------------
SET BTS_08_ALIAS=BTS_08
REM SET BTS_08_FQDN=%DefaultBTSServerFQDN%
REM ------------------------------------------------------------
SET BTS_09_ALIAS=BTS_09
REM SET BTS_09_FQDN=%DefaultBTSServerFQDN%
REM ------------------------------------------------------------
SET BTS_10_ALIAS=BTS_10
REM SET BTS_10_FQDN=%DefaultBTSServerFQDN%
REM ------------------------------------------------------------


REM =======================================================================================
REM (7) Set the alias and target FQDN for each BizTalk interface (comment out the FQDN 
REMdefinition for any interfaces not needed) - by default the FQDN's are the BizTalk 
REM   Server default FQDN as defined above, however, any target FQDN is acceptable (even 
REM   one in a different domain), however, the alias will always be part of the domain 
REM   specified by the DomainFQDN value - aliases may refer to physical servers OR a H/W 
REM   or S/W (e.g., NLB) load balanced virtual cluster
REM =======================================================================================

SET BTS_EDI_ALIAS=BTS_EDI
REM SET BTS_EDI_FQDN=%DefaultBTSServerFQDN%
REM ------------------------------------------------------------
SET BTS_FILE_ALIAS=BTS_FILE
REM SET BTS_FILE_FQDN=%DefaultBTSServerFQDN%
REM ------------------------------------------------------------
SET BTS_FIX_ALIAS=BTS_FIX
REM SET BTS_FIX_FQDN=%DefaultBTSServerFQDN%
REM ------------------------------------------------------------
SET BTS_FTP_ALIAS=BTS_FTP
REM SET BTS_FTP_FQDN=%DefaultBTSServerFQDN%
REM ------------------------------------------------------------
SET BTS_HTTP_ALIAS=BTS_HTTP
REM SET BTS_HTTP_FQDN=%DefaultBTSServerFQDN%
REM ------------------------------------------------------------
SET BTS_MLLP_ALIAS=BTS_MLLP
REM SET BTS_MLLP_FQDN=%DefaultBTSServerFQDN%
REM ------------------------------------------------------------
SET BTS_MQ_ALIAS=BTS_MQ
REM SET BTS_MQ_FQDN=%DefaultBTSServerFQDN%
REM ------------------------------------------------------------
SET BTS_MSMQ_ALIAS=BTS_MSMQ
REM SET BTS_MSMQ_FQDN=%DefaultBTSServerFQDN%
REM ------------------------------------------------------------
SET BTS_SMTP_ALIAS=BTS_SMTP
REM SET BTS_SMTP_FQDN=%DefaultBTSServerFQDN%
REM ------------------------------------------------------------
SET BTS_SOAP_ALIAS=BTS_SOAP
REM SET BTS_SOAP_FQDN=%DefaultBTSServerFQDN%
REM ------------------------------------------------------------
SET BTS_SWIFT_ALIAS=BTS_SWIFT
REM SET BTS_SWIFT_FQDN=%DefaultBTSServerFQDN%
REM ------------------------------------------------------------


REM =======================================================================================
REM (8) Set the alias and target FQDN for each of the application web services- NOT WEB
REM   SERVERS (comment out the FQDN definition for any web services not needed) - by 
REM   default the FQDN's are the BizTalk Server default FQDN as defined above, however, 
REM   any FQDN is acceptable (even one in a different domain) but the alias will always 
REM   be part of the domain specified by the DomainFQDN value
REM =======================================================================================

SET BTS_WS_01_ALIAS=BTS_WS_01
REM SET BTS_WS_01_FQDN=%DefaultBTSServerFQDN%
REM ------------------------------------------------------------
SET BTS_WS_02_ALIAS=BTS_WS_02
REM SET BTS_WS_02_FQDN=%DefaultBTSServerFQDN%
REM ------------------------------------------------------------
SET BTS_WS_03_ALIAS=BTS_WS_03
REM SET BTS_WS_03_FQDN=%DefaultBTSServerFQDN%
REM ------------------------------------------------------------
SET BTS_WS_04_ALIAS=BTS_WS_04
REM SET BTS_WS_04_FQDN=%DefaultBTSServerFQDN%
REM ------------------------------------------------------------
SET BTS_WS_05_ALIAS=BTS_WS_05
REM SET BTS_WS_05_FQDN=%DefaultBTSServerFQDN%
REM ------------------------------------------------------------
SET BTS_WS_06_ALIAS=BTS_WS_06
REM SET BTS_WS_06_FQDN=%DefaultBTSServerFQDN%
REM ------------------------------------------------------------
SET BTS_WS_07_ALIAS=BTS_WS_07
REM SET BTS_WS_07_FQDN=%DefaultBTSServerFQDN%
REM ------------------------------------------------------------
SET BTS_WS_08_ALIAS=BTS_WS_08
REM SET BTS_WS_08_FQDN=%DefaultBTSServerFQDN%
REM ------------------------------------------------------------
SET BTS_WS_09_ALIAS=BTS_WS_09
REM SET BTS_WS_09_FQDN=%DefaultBTSServerFQDN%
REM ------------------------------------------------------------
SET BTS_WS_10_ALIAS=BTS_WS_10
REM SET BTS_WS_10_FQDN=%DefaultBTSServerFQDN%
REM ------------------------------------------------------------


REM =======================================================================================
REM (9) Set the alias and target FQDN for each of the application databases - NOT DATABASE
REM   SERVERS (comment out the FQDN definition for any databases not needed) - by default 
REM   the FQDN's are the SQL Server default FQDN as defined above, however, any FQDN is 
REM   acceptable (even one in a different domain) but the alias will always be part of 
REM   the domain specified by the DomainFQDN value
REM =======================================================================================

SET APPDB_01_ALIAS=APPDB_01
REM SET APPDB_01_FQDN=SERVER1.%DomainFQDN%
REM ------------------------------------------------------------
SET APPDB_02_ALIAS=APPDB_02
REM SET APPDB_02_FQDN=SERVER2.%DomainFQDN%
REM ------------------------------------------------------------
SET APPDB_03_ALIAS=APPDB_03
REM SET APPDB_03_FQDN=SERVER3.%DomainFQDN%
REM ------------------------------------------------------------
SET APPDB_04_ALIAS=APPDB_04
REM SET APPDB_04_FQDN=SERVER4.%DomainFQDN%
REM ------------------------------------------------------------
SET APPDB_05_ALIAS=APPDB_05
REM SET APPDB_05_FQDN=SERVER5.%DomainFQDN%
REM ------------------------------------------------------------
SET APPDB_06_ALIAS=APPDB_06
REM SET APPDB_06_FQDN=SERVER6.%DomainFQDN%
REM ------------------------------------------------------------
SET APPDB_07_ALIAS=APPDB_07
REM SET APPDB_07_FQDN=SERVER7.%DomainFQDN%
REM ------------------------------------------------------------
SET APPDB_08_ALIAS=APPDB_08
REM SET APPDB_08_FQDN=SERVER8.%DomainFQDN%
REM ------------------------------------------------------------
SET APPDB_09_ALIAS=APPDB_09
REM SET APPDB_09_FQDN=SERVER9.%DomainFQDN%
REM ------------------------------------------------------------
SET APPDB_10_ALIAS=APPDB_10
REM SET APPDB_10_FQDN=SERVER10.%DomainFQDN%
REM ------------------------------------------------------------
SET APPDB_11_ALIAS=APPDB_11
REM SET APPDB_11_FQDN=SERVER11.%DomainFQDN%
REM ------------------------------------------------------------
SET APPDB_12_ALIAS=APPDB_12
REM SET APPDB_12_FQDN=SERVER12.%DomainFQDN%
REM ------------------------------------------------------------


REM =======================================================================================
REM (10) Set the alias and target FQDN for each of the SharePoint databases - NOT DATABASE
REM   SERVERS (comment out the FQDN definition for any databases not needed) - by 
REM   default the FQDN's are the SQL Server default FQDN as defined above, however, any 
REM   FQDN is acceptable (even one in a different domain) but the alias will always be 
REM   part of the domain specified by the DomainFQDN value.
REM
REM   NOTE THAT THE SPS COnfiguration Database Server (SPSDB_CONFIG) is required if SPS
REM   is being configured (does not already exist in the system.
REM =======================================================================================

SET SPSDB_CONFIG_ALIAS=SPSDB_CONFIG
REM SET SPSDB_CONFIG_FQDN=%DefaultSQLServerFQDN%
REM ------------------------------------------------------------
SET SPSDB_01_ALIAS=SPSDB_01
REM SET SPSDB_01_FQDN=%DefaultSQLServerFQDN%
REM ------------------------------------------------------------
SET SPSDB_02_ALIAS=SPSDB_02
REM SET SPSDB_02_FQDN=%DefaultSQLServerFQDN%
REM ------------------------------------------------------------
SET SPSDB_03_ALIAS=SPSDB_03
REM SET SPSDB_03_FQDN=%DefaultSQLServerFQDN%
REM ------------------------------------------------------------
SET SPSDB_04_ALIAS=SPSDB_04
REM SET SPSDB_04_FQDN=%DefaultSQLServerFQDN%
REM ------------------------------------------------------------
SET SPSDB_05_ALIAS=SPSDB_05
REM SET SPSDB_05_FQDN=%DefaultSQLServerFQDN%
REM ------------------------------------------------------------
SET SPSDB_06_ALIAS=SPSDB_06
REM SET SPSDB_06_FQDN=%DefaultSQLServerFQDN%
REM ------------------------------------------------------------
SET SPSDB_07_ALIAS=SPSDB_07
REM SET SPSDB_07_FQDN=%DefaultSQLServerFQDN%
REM ------------------------------------------------------------
SET SPSDB_08_ALIAS=SPSDB_08
REM SET SPSDB_08_FQDN=%DefaultSQLServerFQDN%
REM ------------------------------------------------------------
SET SPSDB_09_ALIAS=SPSDB_09
REM SET SPSDB_09_FQDN=%DefaultSQLServerFQDN%
REM ------------------------------------------------------------
SET SPSDB_10_ALIAS=SPSDB_10
REM SET SPSDB_10_FQDN=%DefaultSQLServerFQDN%
REM ------------------------------------------------------------


REM =======================================================================================
REM (11) Set the path to the DNSCMD utility, if not installed in the default location
REM =======================================================================================

REM Windows32 Path
SET DNSCMDPath="C:\Program Files\Support Tools\DNSCMD.exe"

REM Windows64 Path
REM SET DNSCMDPath="C:\Program Files (x86)\Support Tools\DNSCMD.exe"



REM =======================================================================================
REM =======================================================================================
REM =======================================================================================
REM DO NOT MODIFY CODE BELOW THIS LINE
REM =======================================================================================
REM =======================================================================================
REM =======================================================================================

REM Do an existance check on the DNSCMD utility
@ECHO.
IF NOT EXIST %DNSCMDPath% (
    @ECHO The DNSCMD utility was not found at %DNSCMDPath%.  Please change
    @ECHO the value of DNSCMDPath in script Step 6 or install the current Windows Server 2003 Support
    @ECHO Tools, available as a free download from the MSDN web site.
    EXIT /B 2
)

REM Output a marker line
@ECHO Creating BizTalk CNAME Aliases in domain %DomainFQDN%

REM Create the BizTalk System Database CNAME aliases
@IF DEFINED BTSDB_MGMT_FQDN CALL :CreateAlias %BTSDB_MGMT_ALIAS% %BTSDB_MGMT_FQDN%
@IF DEFINED BTSDB_PMSG_FQDN CALL :CreateAlias %BTSDB_PMSG_ALIAS% %BTSDB_PMSG_FQDN%
@IF DEFINED BTSDB_SMSG01_FQDN CALL :CreateAlias %BTSDB_SMSG01_ALIAS% %BTSDB_SMSG01_FQDN%
@IF DEFINED BTSDB_SMSG02_FQDN CALL :CreateAlias %BTSDB_SMSG02_ALIAS% %BTSDB_SMSG02_FQDN%
@IF DEFINED BTSDB_SMSG03_FQDN CALL :CreateAlias %BTSDB_SMSG03_ALIAS% %BTSDB_SMSG03_FQDN%
@IF DEFINED BTSDB_SMSG04_FQDN CALL :CreateAlias %BTSDB_SMSG04_ALIAS% %BTSDB_SMSG04_FQDN%
@IF DEFINED BTSDB_SMSG05_FQDN CALL :CreateAlias %BTSDB_SMSG05_ALIAS% %BTSDB_SMSG05_FQDN%
@IF DEFINED BTSDB_DTA_FQDN CALL :CreateAlias %BTSDB_DTA_ALIAS% %BTSDB_DTA_FQDN%
@IF DEFINED BTSDB_SSO_FQDN CALL :CreateAlias %BTSDB_SSO_ALIAS% %BTSDB_SSO_FQDN%
@IF DEFINED BTSDB_RE_FQDN CALL :CreateAlias %BTSDB_RE_ALIAS% %BTSDB_RE_FQDN%
@IF DEFINED BTSDB_HWS_FQDN CALL :CreateAlias %BTSDB_HWS_ALIAS% %BTSDB_HWS_FQDN%
@IF DEFINED BTSDB_TPM_FQDN CALL :CreateAlias %BTSDB_TPM_ALIAS% %BTSDB_TPM_FQDN%
@IF DEFINED BTSDB_BAMPI_FQDN CALL :CreateAlias %BTSDB_BAMPI_ALIAS% %BTSDB_BAMPI_FQDN%
@IF DEFINED BTSDB_BAMSS_FQDN CALL :CreateAlias %BTSDB_BAMSS_ALIAS% %BTSDB_BAMSS_FQDN%
@IF DEFINED BTSDB_BAMARCH_FQDN CALL :CreateAlias %BTSDB_BAMARCH_ALIAS% %BTSDB_BAMARCH_FQDN%
@IF DEFINED BTSDB_BAMAN_FQDN CALL :CreateAlias %BTSDB_BAMAN_ALIAS% %BTSDB_BAMAN_FQDN%
@IF DEFINED BTSDB_BAMALERTS_FQDN CALL :CreateAlias %BTSDB_BAMALERTS_ALIAS% %BTSDB_BAMALERTS_FQDN%
@IF DEFINED BTSDB_RPTSVR_FQDN CALL :CreateAlias %BTSDB_RPTSVR_ALIAS% %BTSDB_RPTSVR_FQDN%

REM Create the BizTalk Adapter Database CNAME aliases
@IF DEFINED BTSDB_EDI_FQDN CALL :CreateAlias %BTSDB_EDI_ALIAS% %BTSDB_EDI_FQDN%
@IF DEFINED BTSDB_FIX_FQDN CALL :CreateAlias %BTSDB_FIX_ALIAS% %BTSDB_FIX_FQDN%
@IF DEFINED BTSDB_SWIFT_FQDN CALL :CreateAlias %BTSDB_SWIFT_ALIAS% %BTSDB_FIX_FQDN%
@IF DEFINED BTSDB_HL7_FQDN CALL :CreateAlias %BTSDB_HL7_ALIAS% %BTSDB_HL7_FQDN%
@IF DEFINED BTSDB_ADAPTER_1_FQDNCALL :CreateAlias %BTSDB_ADAPTER_1_ALIAS% %BTSDB_ADAPTER_1_FQDN%
@IF DEFINED BTSDB_ADAPTER_2_FQDNCALL :CreateAlias %BTSDB_ADAPTER_2_ALIAS% %BTSDB_ADAPTER_2_FQDN%
@IF DEFINED BTSDB_ADAPTER_3_FQDNCALL :CreateAlias %BTSDB_ADAPTER_3_ALIAS% %BTSDB_ADAPTER_3_FQDN%
@IF DEFINED BTSDB_ADAPTER_4_FQDNCALL :CreateAlias %BTSDB_ADAPTER_4_ALIAS% %BTSDB_ADAPTER_4_FQDN%
@IF DEFINED BTSDB_ADAPTER_5_FQDNCALL :CreateAlias %BTSDB_ADAPTER_5_ALIAS% %BTSDB_ADAPTER_5_FQDN%

REM Create the BAM SMTP CNAME aliase
@IF DEFINED BTS_MAILSERVER_FQDNCALL :CreateAlias %BTS_MAILSERVER_ALIAS% %BTS_MAILSERVER_FQDN%

REM Create the BizTalk Server CNAME aliases
@IF DEFINED BTS_01_FQDN CALL :CreateAlias %BTS_01_ALIAS% %BTS_01_FQDN%
@IF DEFINED BTS_02_FQDN CALL :CreateAlias %BTS_02_ALIAS% %BTS_02_FQDN%
@IF DEFINED BTS_03_FQDN CALL :CreateAlias %BTS_03_ALIAS% %BTS_03_FQDN%
@IF DEFINED BTS_04_FQDN CALL :CreateAlias %BTS_04_ALIAS% %BTS_04_FQDN%
@IF DEFINED BTS_05_FQDN CALL :CreateAlias %BTS_05_ALIAS% %BTS_05_FQDN%
@IF DEFINED BTS_06_FQDN CALL :CreateAlias %BTS_06_ALIAS% %BTS_06_FQDN%
@IF DEFINED BTS_07_FQDN CALL :CreateAlias %BTS_07_ALIAS% %BTS_07_FQDN%
@IF DEFINED BTS_08_FQDN CALL :CreateAlias %BTS_08_ALIAS% %BTS_08_FQDN%
@IF DEFINED BTS_09_FQDN CALL :CreateAlias %BTS_09_ALIAS% %BTS_09_FQDN%
@IF DEFINED BTS_10_FQDN CALL :CreateAlias %BTS_10_ALIAS% %BTS_10_FQDN%
REM Create the BizTalk interface CNAME aliases
@IF DEFINED BTS_EDI_FQDN CALL :CreateAlias %BTS_EDI_ALIAS% %BTS_EDI_FQDN%
@IF DEFINED BTS_FILE_FQDN CALL :CreateAlias %BTS_FILE_ALIAS% %BTS_FILE_FQDN%
@IF DEFINED BTS_FIX_FQDN CALL :CreateAlias %BTS_FIX_ALIAS% %BTS_FIX_FQDN%
@IF DEFINED BTS_FTP_FQDN CALL :CreateAlias %BTS_FTP_ALIAS% %BTS_FTP_FQDN%
@IF DEFINED BTS_HTTP_FQDN CALL :CreateAlias %BTS_HTTP_ALIAS% %BTS_HTTP_FQDN%
@IF DEFINED BTS_MLLP_FQDN CALL :CreateAlias %BTS_MLLP_ALIAS% %BTS_MLLP_FQDN%
@IF DEFINED BTS_MQ_FQDN CALL :CreateAlias %BTS_MQ_ALIAS% %BTS_MQ_FQDN%
@IF DEFINED BTS_MSMQ_FQDN CALL :CreateAlias %BTS_MSMQ_ALIAS% %BTS_MSMQ_FQDN%
@IF DEFINED BTS_SMTP_FQDN CALL :CreateAlias %BTS_SMTP_ALIAS% %BTS_SMTP_FQDN%
@IF DEFINED BTS_SOAP_FQDN CALL :CreateAlias %BTS_SOAP_ALIAS% %BTS_SOAP_FQDN%
@IF DEFINED BTS_SWIFT_FQDN CALL :CreateAlias %BTS_SWIFT_ALIAS% %BTS_SWIFT_FQDN%

REM Create the Web Service CNAME aliases
@IF DEFINED BTS_WS_01_FQDN CALL :CreateAlias %BTS_WS_01_ALIAS% %BTS_WS_01_FQDN%
@IF DEFINED BTS_WS_02_FQDN CALL :CreateAlias %BTS_WS_02_ALIAS% %BTS_WS_02_FQDN%
@IF DEFINED BTS_WS_03_FQDN CALL :CreateAlias %BTS_WS_03_ALIAS% %BTS_WS_03_FQDN%
@IF DEFINED BTS_WS_04_FQDN CALL :CreateAlias %BTS_WS_04_ALIAS% %BTS_WS_04_FQDN%
@IF DEFINED BTS_WS_05_FQDN CALL :CreateAlias %BTS_WS_05_ALIAS% %BTS_WS_05_FQDN%
@IF DEFINED BTS_WS_06_FQDN CALL :CreateAlias %BTS_WS_06_ALIAS% %BTS_WS_06_FQDN%
@IF DEFINED BTS_WS_07_FQDN CALL :CreateAlias %BTS_WS_07_ALIAS% %BTS_WS_07_FQDN%
@IF DEFINED BTS_WS_08_FQDN CALL :CreateAlias %BTS_WS_08_ALIAS% %BTS_WS_08_FQDN%
@IF DEFINED BTS_WS_09_FQDN CALL :CreateAlias %BTS_WS_09_ALIAS% %BTS_WS_09_FQDN%
@IF DEFINED BTS_WS_10_FQDN CALL :CreateAlias %BTS_WS_10_ALIAS% %BTS_WS_10_FQDN%

REM Create the application database CNAME aliases
@IF DEFINED APPDB_01_FQDN CALL :CreateAlias %APPDB_01_ALIAS% %APPDB_01_FQDN%
@IF DEFINED APPDB_02_FQDN CALL :CreateAlias %APPDB_02_ALIAS% %APPDB_02_FQDN%
@IF DEFINED APPDB_03_FQDN CALL :CreateAlias %APPDB_03_ALIAS% %APPDB_03_FQDN%
@IF DEFINED APPDB_04_FQDN CALL :CreateAlias %APPDB_04_ALIAS% %APPDB_04_FQDN%
@IF DEFINED APPDB_05_FQDN CALL :CreateAlias %APPDB_05_ALIAS% %APPDB_05_FQDN%
@IF DEFINED APPDB_06_FQDN CALL :CreateAlias %APPDB_06_ALIAS% %APPDB_06_FQDN%
@IF DEFINED APPDB_07_FQDN CALL :CreateAlias %APPDB_07_ALIAS% %APPDB_07_FQDN%
@IF DEFINED APPDB_08_FQDN CALL :CreateAlias %APPDB_08_ALIAS% %APPDB_08_FQDN%
@IF DEFINED APPDB_09_FQDN CALL :CreateAlias %APPDB_09_ALIAS% %APPDB_09_FQDN%
@IF DEFINED APPDB_10_FQDN CALL :CreateAlias %APPDB_10_ALIAS% %APPDB_10_FQDN%
@IF DEFINED APPDB_11_FQDN CALL :CreateAlias %APPDB_11_ALIAS% %APPDB_11_FQDN%
@IF DEFINED APPDB_12_FQDN CALL :CreateAlias %APPDB_12_ALIAS% %APPDB_12_FQDN%

REM Create the SharePoint database CNAME aliases
@IF DEFINED SPSDB_CONFIG_FQDN CALL :CreateAlias %SPSDB_CONFIG_ALIAS% %SPSDB_CONFIG_FQDN%
@IF DEFINED SPSDB_01_FQDN CALL :CreateAlias %SPSDB_01_ALIAS% %SPSDB_01_FQDN%
@IF DEFINED SPSDB_02_FQDN CALL :CreateAlias %SPSDB_02_ALIAS% %SPSDB_02_FQDN%
@IF DEFINED SPSDB_03_FQDN CALL :CreateAlias %SPSDB_03_ALIAS% %SPSDB_03_FQDN%
@IF DEFINED SPSDB_04_FQDN CALL :CreateAlias %SPSDB_04_ALIAS% %SPSDB_04_FQDN%
@IF DEFINED SPSDB_05_FQDN CALL :CreateAlias %SPSDB_05_ALIAS% %SPSDB_05_FQDN%
@IF DEFINED SPSDB_06_FQDN CALL :CreateAlias %SPSDB_06_ALIAS% %SPSDB_06_FQDN%
@IF DEFINED SPSDB_07_FQDN CALL :CreateAlias %SPSDB_07_ALIAS% %SPSDB_07_FQDN%
@IF DEFINED SPSDB_08_FQDN CALL :CreateAlias %SPSDB_08_ALIAS% %SPSDB_08_FQDN%
@IF DEFINED SPSDB_09_FQDN CALL :CreateAlias %SPSDB_09_ALIAS% %SPSDB_09_FQDN%
@IF DEFINED SPSDB_10_FQDN CALL :CreateAlias %SPSDB_10_ALIAS% %SPSDB_10_FQDN%

@ENDLOCAL
@GOTO :EOF

:CreateAlias

    @ECHO.
    @ECHO ------------------------------------------------------------------------------------
    @ECHO.
    @ECHO %1 (%2)
    @%DNSCMDPath% localhost /RecordAdd %DomainFQDN% %1 CNAME %2
    GOTO :EOF

This appendix lists a number of potentially useful tools and references contributed by the author and reviewers. Please e-mail any suggestions for additional tools and/or references to the author: John Brockmeyer [mailto:John.Brockmeyer@neudesic.com].

Server Performance Advisor

The Microsoft Server Performance Advisor (SPA) tool can help isolate performance problems, especially disk I/O issues. Clint Huffman has written a white paper on how to use this tool "How to Identify a Disk Performance Bottleneck Using the Microsoft Server Performance Advisor (SPA) Tool" (http://go.microsoft.com/fwlink/?LinkId=98096).

Windows Kernel Memory Usage

The following table shows the base (default) configuration kernel memory use for Windows server operating systems:

Memory Default ( /PAE for 6-16GB ) /3GB Terminal Services TS+Q312362

1GB

  • Free System PTE: 51k
  • Paged Pool: 282MB
  • Non Paged Pool: 212MB
  • Free System PTE: 32k
  • Paged Pool: 163MB
  • Non Paged Pool: 131MB
  • Free System PTE: 257k
  • Paged Pool: 172MB
  • Non Paged Pool: 212MB
  • Free System PTE: 210k
  • Paged Pool: 536MB
  • Non Paged Pool: 212MB

2GB

  • Free System PTE: 196k
  • Paged Pool: 360MB
  • Non Paged Pool: 262MB
  • Free System PTE: 16k
  • Paged Pool: 262MB
  • Non Paged Pool: 131MB
  • Free System PTE: 218k
  • Paged Pool: 274MB
  • Non Paged Pool: 262MB
  • Free System PTE: 196k
  • Paged Pool: 536MB
  • Non Paged Pool: 262MB

3GB

  • Free System PTE: 195k
  • Paged Pool: 360MB
  • Non Paged Pool: 262MB
  • Free System PTE: 14k
  • Paged Pool: 262MB
  • Non Paged Pool: 131MB
  • Free System PTE: 217k
  • Paged Pool: 274MB
  • Non Paged Pool: 262MB
  • Free System PTE: 195k
  • Paged Pool: 536MB
  • Non Paged Pool: 262MB

4GB

  • Free System PTE: 186k
  • Paged Pool: 366MB
  • Non Paged Pool: 285MB
  • Free System PTE: 15k
  • Paged Pool: 258MB
  • Non Paged Pool: 154MB
  • Free System PTE: 217k
  • Paged Pool: 274MB
  • Non Paged Pool: 262MB
  • Free System PTE: 186k
  • Paged Pool: 536MB
  • Non Paged Pool: 285MB

6GB

  • Free System PTE: 186k
  • Paged Pool: 366MB
  • Non Paged Pool: 262MB
  • Free System PTE: 12k
  • Paged Pool: 239MB
  • Non Paged Pool: 131MB
  • Free System PTE: 211k
  • Paged Pool: 266MB
  • Non Paged Pool: 262MB
  • Free System PTE: 186k
  • Paged Pool: 540MB
  • Non Paged Pool: 262MB

8GB

  • Free System PTE: 182k
  • Paged Pool: 366MB
  • Non Paged Pool: 262MB
  • Free System PTE: 12k
  • Paged Pool: 225MB
  • Non Paged Pool: 131MB
  • Free System PTE: 208k
  • Paged Pool: 262MB
  • Non Paged Pool: 262MB
  • Free System PTE: 182k
  • Paged Pool: 540MB
  • Non Paged Pool: 262MB

12GB

  • Free System PTE: 175k
  • Paged Pool: 366MB
  • Non Paged Pool: 262MB
  • Free System PTE: 12k
  • Paged Pool: 196MB
  • Non Paged Pool: 131MB
  • Free System PTE: 202k
  • Paged Pool: 256MB
  • Non Paged Pool: 262MB
  • Free System PTE: 175k
  • Paged Pool: 540MB
  • Non Paged Pool: 262MB

16GB

  • Free System PTE: 167k
  • Paged Pool: 366MB
  • Non Paged Pool: 262MB
  • Free System PTE: 12k
  • Paged Pool: 169MB
  • Non Paged Pool: 131MB
  • Free System PTE: 197k
  • Paged Pool: 249MB
  • Non Paged Pool: 262MB
  • Free System PTE: 168k
  • Paged Pool: 540MB
  • Non Paged Pool: 262MB

TCP Port Exhaustion Detection

This script from Clint Huffman detects TCP port exhaustion http://go.microsoft.com/fwlink/?LinkId=98097.

BizTalk Best Practices Analyzer

The Best Practices Analyzer is a very valuable tool, especially for multi-server systems. You can download the tool from http://go.microsoft.com/fwlink/?LinkId=86622.

Performance Alert Tool

Another tool from Clint Huffman, this will help with monitoring and alerting on performance statistics: http://go.microsoft.com/fwlink/?LinkId=98098.

Microsoft SysInternals

Many helpful tools, most written by current or former Microsoft Windows Kernel Team members: http://go.microsoft.com/fwlink/?LinkId=98099.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft