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

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

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

567 εμφανίσεις

Presenter




Nikolay Chorbanov


Database Delivery Lead, HP




MCITP SQL2005,



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

Agenda


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
solution


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


4
-
node Windows Server 2003 cluster with 4 instances
-

active/active


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
-
load
environment (Locking and deadlocking)


Max Degree of Parallelism (MDOP) is set to

1

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
non
-
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.

http://msdn.microsoft.com/en
-
us/library/cc296811.aspx


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



http://support.microsoft.com/kb/301600/

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


Backup


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
synchronized
backups

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


Backups can be done only to disk!


SELECT [ServerName],[DatabaseName] FROM
[BizTalkMgmtDb].[dbo].[admv_BackupDatabases]

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.

“DBCC INDEXDEFRAG” and “ALTER INDEX … REORGANIZE …”
are not supported
since
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

BizTalk


No

SQL differential backup (only full and transaction log)

Differential backups are usually much smaller than the
full backups


No

database mirroring, which is better technology than
log shipping


No

backups directly to tapes


No

tail log backup


Performance

Performance


Performance tuning



Memory configuration


x64 processors


Lock pages in memory; Max memory set
27GB/32GB;
Min memory is not set


Tempdb configuration


Number of datafiles equal to the number of logical CPUs.


Ntfs cluster size







CPU


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


Messagebox

should be kept below 1GB ideally.

DTA

should be kept below 20GB

http://msdn.microsoft.com/en
-
us/library/bb743398(BTS.10).aspx#BKMK_NetInfraTuning


Common issues

Common issues


General network errors
:


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


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


Event ID: 6912
Description:
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
setting
-

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

http://support.microsoft.com/kb/970406

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

Q&A

Thank you!

Contact information


Nikolay Chorbanov

nchorbanov@gmail.com

ICQ# 33484741