1 / 32

Presenter

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.

daniel_millan
Download Presentation

Presenter

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Presenter • Nikolay Chorbanov Database Delivery Lead, HP MCITP SQL2005, MCSA Windows 2003

  2. Lessons Learned: SQL Server best practices for BizTalk environment Unofficial: The good, the bad and … Biztalk

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

  4. 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)

  5. Biztalk and SQL overview

  6. 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 

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

  8. Distributed environment Database Servers Application Servers

  9. SQL specifics for Biztalk

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

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

  12. 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/

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

  14. Backups and Restore • Backup • Custom and non-Biztalk databases • Biztalk databases

  15. 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]

  16. Managing the biztalk backups

  17. Managing the biztalk backups

  18. Restore !?!?!?!?

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

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

  21. Performance

  22. Performance

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

  24. Common issues

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

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

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

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

  29. Multiple environments & global DR

  30. Q&A Thank you!

  31. Contact information • Nikolay Chorbanov nchorbanov@gmail.com ICQ# 33484741

More Related