Export (0) Print
Expand All

Pre-Configuration Database Optimization

BizTalk Server is an extremely database-intensive application that may require the creation of up to 13 separate databases in Microsoft SQL Server. Because of the critical role that SQL Server plays in any BizTalk Server environment, it is of paramount importance that SQL Server is configured/tuned for optimal performance. If SQL Server is not tuned to perform well, then the databases used by BizTalk Server will become a bottleneck and the overall performance of the BizTalk Server environment will suffer. This topic describes several SQL Server performance optimizations that should be followed before installing BizTalk Server and configuring the BizTalk Server databases.

Various versions and editions of SQL Server provide different features that can impact the performance of your BizTalk Server environment. For example, under high-load conditions, the number of available database locks that are available for the 32-bit version of SQL Server may be exceeded, which is detrimental to the performance of the BizTalk solution. Consider housing your MessageBox database on a 64-bit version of SQL Server if you are experiencing "out of lock" errors in your test environment. The number of available locks is significantly higher on the 64-bit version of SQL Server.

Consider the table below when deciding on the database engine features that you will need for your BizTalk environment. For small-scale solutions, for example when running BizTalk Server 2006 R2 Branch Edition, SQL Server 2000 Desktop Edition (MSDE) may be adequate for housing the BizTalk Server databases. For large scale, enterprise-level solutions that require clustering support, BizTalk Server log shipping support, or Analysis Services support, then you will need SQL Server 2005 or SQL Server 2000 Enterprise Edition to house the SQL Server databases.

Cc558606.note(en-US,BTS.10).gifNote
Running BizTalk Server databases on SQL Server 2005 typically provides superior performance to running BizTalk Server databases on SQL Server 2000.

Version and Edition of SQL Server 64-bit support Multi-Instance Support Clustering support Analysis Services

SQL Server 2005 Enterprise Edition

Yes

Yes

Yes

Yes

SQL Server 2005 Standard Edition

Yes

Yes

Yes (2 node)

Yes

SQL Server 2005 Workgroup Edition

No

Yes

No

No

SQL Server 2000 Enterprise Edition

No

Yes

Yes

Yes

SQL Server 2000 Standard Edition

No

Yes

No

Yes

SQL Server 2000 Desktop Edition (MSDE)

No

Yes

No

No

For a complete list of the features supported by the editions of SQL Server 2005, see "Features Supported by the Editions of SQL Server 2005" in the SQL Server 2005 documentation at http://go.microsoft.com/fwlink/?LinkId=72045. For a complete list of the features supported by the editions of SQL Server 2000, see "Features Supported by the Editions of SQL Server 2000" in the SQL Server 2000 documentation at http://go.microsoft.com/fwlink/?LinkId=104156.

We recommend that you host your SQL databases on fast storage (for example, fast SAN disks or fast SCSI disks). We recommend RAID 10 (1+0) instead of RAID 5 since raid 5 is slower at writing. Newer SAN disks have very large memory caches, so in these cases the raid selection is not as important. To increase performance, databases and their log files can reside on different physical disks.

There is an issue with SQL Server 2000 Service Pack 4 whereby not all memory is available when AWE is enabled on a computer running the 32-bit version of SQL Server 2000 SP4. For more information about this issue see Microsoft Knowledge Base article 899761, “FIX: Not all memory is available when AWE is enabled on a computer that is running a 32-bit version of SQL Server 2000 SP4” at http://support.microsoft.com/kb/899761. To resolve this issue. download and install the cumulative hotfix package for SQL Server 2000 SP4 build 2187 that is documented in Microsoft Knowledge Base Article 916287, “A cumulative hotfix package is available for SQL Server 2000 Service Pack 4 build 2187” at http://support.microsoft.com/kb/916287.

We recommend installing the latest service packs and the latest cumulative updates for SQL Server 2005 as well as the latest .NET Framework service packs.

When installing service packs for SQL Server, also install the service pack on the BizTalk computer. BizTalk Server uses SQL Client components that are updated by the SQL Server service packs.

On an x86 computer, SQL Server should be configured to use more than 2 GB of physical memory. SQL Server Enterprise Edition introduced support for the use of Microsoft Windows Address Windowing Extensions (AWE) to address up to 32 GB of memory. With AWE, SQL Server can reserve memory that is not in use for other applications and the operating system. Each instance that uses this memory, however, must statically allocate the memory it needs. SQL Server can only use this AWE allocated memory for the data cache and not for executables, drivers, DLLs, and so forth. For more information, see Microsoft Knowledge Base article 274750, "How to configure SQL Server to use more than 2 GB of physical memory" at http://support.microsoft.com/kb/274750.

The computers running SQL Server that host the BizTalk Server databases should be dedicated to running SQL Server. When the computers running SQL Server that host the BizTalk Server databases are dedicated to running SQL Server, we recommend that the 'min server memory' and 'max server memory' options on each SQL Server instance are set to specify the fixed amount of memory to allocate to SQL Server. In this case, you should set the “min server memory” and “max server memory” to the same value (equal to the maximum amount of physical memory that SQL Server will use). This will reduce overhead that would otherwise be used by SQL Server dynamically managing these values. Run the following T-SQL commands on each computer running SQL Server to specify the fixed amount of memory to allocate to SQL Server:

sp_configure ‘Max Server memory (MB)’,(max size in MB)
sp_configure ‘Min Server memory (MB)’,(min size in MB)

Before you set the amount of memory for SQL Server, determine the appropriate memory setting by subtracting the memory required for Windows Server from the total physical memory. This is the maximum amount of memory you can assign to SQL Server.

Cc558606.note(en-US,BTS.10).gifNote
If the computer(s) running SQL Server that host the BizTalk Server databases also host the Enterprise Single Sign-On Master Secret Server then you may need to adjust this value to ensure that there is sufficient memory available to run the Enterprise Single Sign-On Service. It is not an uncommon practice to run a clustered instance of the Enterprise Single Sign-On service on a SQL Server cluster to provide high availability for the Master Secret Server. For more information about clustering the Enterprise Single Sign-On Master Secret Server, see the topic “How to Cluster the Master Secret Server” in the BizTalk Server 2006 R2 documentation at http://go.microsoft.com/fwlink/?LinkID=106874.

Ensuring that the data files used for the tempdb are of equal size is critical because the proportional fill algorithm used by SQL Server is based on the size of the data files. If data files are created with unequal sizes, the proportional fill algorithm will use the largest file more for GAM allocations rather than spreading the allocations between all the files, thereby defeating the purpose of creating multiple data files. The number of data files for the tempdb database should be configured to be at least equal to the number of processors assigned for SQL Server.

Implementing Trace Flag –T1118 helps reduce contention across the SQL Server instances by removing almost all single page allocations. For more information, see Microsoft Knowledge Base article 328551, "PRB: Concurrency enhancements for the tempdb database" at http://support.microsoft.com/kb/328551.

If a SQL Server instance will house BizTalk Server databases, then there are certain SQL Server settings should not be changed. Specifically, the SQL Server max degree of parallelism, the SQL Server statistics on the MessageBox database, and the settings for the database index rebuilds and defragmentation should not be modified. For more information, see the topic “SQL Server Settings That Should Not Be Changed” in the BizTalk Server Operations Guide at http://go.microsoft.com/fwlink/?LinkId=114358.

Show:
© 2014 Microsoft