SQL Server best practices for BizTalk environment - BG SQL Group ...

quicksandwalleyeInternet και Εφαρμογές Web

31 Οκτ 2013 (πριν από 4 χρόνια και 8 μήνες)

633 εμφανίσεις


Nikolay Chorbanov

Database Delivery Lead, HP


MCSA Windows 2003

Lessons Learned:

SQL Server best practices for

BizTalk environment

Unofficial: The good, the bad and … Biztalk

Objectives & takeaways

Sharing experience and knowledge from
implementing and maintaining multi node

SQL server
environment for Biztalk application

Specific requirements of Biztalk and some common
issues that can be met


Biztalk and SQL overview

SQL specifics for Biztalk

Performance of the SQL Servers

Common issues

Overview of multiple environments in two
geographically separated regions (Global DR)

Biztalk and SQL overview

Biztalk and SQL together

BizTalk Server is Microsoft’s
Integration and connectivity server

Through the use of "adapters“, which are tailored to communicate with
different software systems used in a large enterprise, it enables
companies to
automate business processes

The BizTalk Server runtime is built on a
publish/subscribe architecture

And last but not least it uses
SQL as backend database

The environment

Distributed environment

node Windows Server 2003 cluster with 4 instances


Hardware configuration for the production environment

4 database servers

HP ProLiant DL585 G2: 4 Dual
Core AMD Opteron(tm)
Processor 8220 SE 2.8 GHz, 32 GB RAM, HP XP storage,

1 Gbit/s network

19 application servers

ProLiant DL385 G2: 2 Dual
Core AMD Opteron(tm) Processor
2218, 12 GB RAM

Distributed environment

Database Servers

Application Servers

SQL specifics for Biztalk

SQL specifics for Biztalk

Most of the Biztalk data is volatile

Biztalk is like an application bridge

It is important Biztalk to be processing …

Data is moved from one database to another via SQL jobs and
stored procedures

BizTalk Server does very selective, short and fast queries

Stored procedures should not cause table or index scans.

They have
exact joins and lock hints
specified in the queries in
order to ensure that the
optimal query plan
is used.

These stored procedures provide consistent query executions by
constructing the queries such that the
query optimizer is taken
out of the picture

as much as possible.

Statistics are generally not needed

Database options and SQL instance

settings for the MessageBoxDb

“Auto create statistics” & “Auto update statistics” are
turned off
by default in the BizTalk
Server MessageBox database when it is created.

Do not enable these options on MessageBox databases.

Enabling the options can cause undesirable query execution delays, especially in a high
environment (Locking and deadlocking)

Max Degree of Parallelism (MDOP) is set to


during the configuration of BizTalk Server
for the SQL Server instance(s) that host the BizTalk Server MessageBox database(s).

This setting should not be changed!

Changing it can have a significant negative impact on the BizTalk Server (poor query
performance or even deadlocks).

The MessageBox database should be treated like
Microsoft application source code

That is, you
should not “tweak”

the MessageBox database via changes to tables, indexes,
stored procedures, and most SQL Server database settings.


Distributed Transactions and MS DTC

A distributed transaction:

is a transaction that updates data on two or more
networked computer systems

is managed by Microsoft Distributed Transaction
Coordinator (MS DTC)

MS DTC configuration in a clustered environment


Marked Transactions

Are transactions that place a mark into the transaction
log of all databases participating in the transaction

When you make related updates to two or more
related databases, you can use transaction marks to
recover them to a logically consistent point

Backups and Restore


Custom and non
Biztalk databases

Biztalk databases

Backup Biztalk databases

Backup via specific SQL job called “Backup BizTalk Server”

the only supported method by MS

The Backup BizTalk Server Job creates

of all BizTalk Server databases by using full and
transaction log backups, in conjunction with a marked

Backups can be done only to disk!

SELECT [ServerName],[DatabaseName] FROM

Managing the biztalk backups

Managing the biztalk backups

Restore !?!?!?!?

Index Rebuild

Index fragmentation is not as much of a performance issue for BizTalk Server as it
would be for a DSS system or an OLTP system that performs index scans.

BizTalk Server does very selective queries and updates and BizTalk Server stored
procedures should not cause table or index scans.

BizTalk Server does not support defragmenting indexes.

are not supported
they use page locking, which can cause blocking and deadlocks with BizTalk Server.

BizTalk Server does support database index rebuilds (“DBCC DBREINDEX” and “ALTER
INDEX … REBUILD …”), but they should only be done during maintenance windows
when BizTalk Server is not processing data.

Index rebuilds while BizTalk Server is processing data
are not supported

Other restrictions coming from



SQL differential backup (only full and transaction log)

Differential backups are usually much smaller than the
full backups


database mirroring, which is better technology than
log shipping


backups directly to tapes


tail log backup



Performance tuning

Memory configuration

x64 processors

Lock pages in memory; Max memory set
Min memory is not set

Tempdb configuration

Number of datafiles equal to the number of logical CPUs.

Ntfs cluster size


Make sure hyperthreading is disabled on servers running BizTalk
Server and SQL Server 2005


should be kept below 1GB ideally.


should be kept below 20GB


Common issues

Common issues

General network errors

Event ID: 6913
Description: SQL Server,
BizTalkMsgBoxDb, [DBNETLIB][ConnectionWrite
(send()).]General network error. Check your network

Event ID: 5410
[DBNETLIB][ConnectionWrite (send()).]General
network error. Check your network documentation.

Event ID: 6912
DBNETLIB][ConnectionRead (recv()).]General network
error. Check your network documentation.

General network errors

Turn OFF SQLNET inspection on Cisco firewalls. By default it is ON.

Apply TCP/IP registry settings:

Disable the Scalable Networking Pack (SNP) features


this needs to be done on the Biztalk servers and the SQL servers

Disable SynAttackProtect

this needs to be done only on the SQL servers

Increase the ephemeral ports and reduce the TCP re
use timeout

this needs to be done only on the Biztalk servers

Disable Privilege Attribute Certificate (PAC)


this needs to be done on the Biztalk servers and the SQL servers


Patching a 4
node active/active

SQL cluster with SP3

Move all SQL cluster groups to one of the nodes and
start the patch from this one

The patch rolled off for 40 minutes

Overview of multiple environments in
two geographically separated regions
(Global DR)

Multiple environments & global DR


Thank you!

Contact information

Nikolay Chorbanov


ICQ# 33484741