Siebel on SQL Server 2005
SQL Server Technical Article
Writers: Peter Samson
Technical Reviewers: Frank McBath and Siva Raghupathy
Project Editor: Anu Chawla
Designer: Anu Chawla
Published: November 2005
Applies To: SQL Server 2005
Summary: This document describes how system architects and database administrators design and install solid and reliable Siebel deployments with SQL Server2005. The document describes how customers, systems integrators, and partners can develop Siebel deployments with SQL Server2005, including typical network architectures, configurations, and operations and monitoring considerations. The Siebel on SQL Server2005 recommendations herein were developed using the proven experience of hundreds of Siebel customer and partner installations worldwide, and Microsoft internal Siebel deployments that use SQL Server2005.
Copyright
The information contained in this document represents the current view of Microsoft Corporation on the issues discussed as of the date of publication. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information presented after the date of publication.
This White Paper is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS DOCUMENT.
Complying with all applicable copyright laws is the responsibility of the user. Without limiting the rights under copyright, no part of this document may be reproduced, stored in or introduced into a retrieval system, or transmitted in any form or by any means (electronic, mechanical, photocopying, recording, or otherwise), or for any purpose, without the express written permission of Microsoft Corporation.
Microsoft may have patents, patent applications, trademarks, copyrights, or other intellectual property rights covering subject matter in this document. Except as expressly provided in any written license agreement from Microsoft, the furnishing of this document does not give you any license to these patents, trademarks, copyrights, or other intellectual property.
Unless otherwise noted, the example companies, organizations, products, domain names, e-mail addresses, logos, people, places and events depicted herein are fictitious, and no association with any real company, organization, product, domain name, email address, logo, person, place or event is intended or should be inferred.
Ó 2005 Microsoft Corporation. All rights reserved.
Microsoft, Windows, and Windows Server are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries.
The names of actual companies and products mentioned herein may be the trademarks of their respective owners.
Table of Contents
Executive Summary 1
Microsoft-Siebel Overview 2
A strong partnership 2
Siebel with SQL Server 2
Siebel architecture 2
What’s New in SQL Server 2005 4
Configuring SQL Server 2005 for Siebel 5
Before creating the database 5
Volume/growth projections 5
Number and size of SQL Server data files 6
Number of log files for Siebel databases 8
tempdb considerations 9
SQL Server 2005 options 9
AWE enabled 9
Max degree of parallelism 10
Min and max server memory 10
Max worker threads 11
Priority boost 11
Recovery interval 11
When creating the Siebel database 12
Selecting a code page 12
Automatically creating and updating statistics 12
Updating statistics 12
Migration Considerations 13
Upgrading the Siebel product 13
Upgrading from SQL Server2000 to SQL Server 2005 14
Migrating from DB2 or Oracle 14
Data-Availability Features 14
Backup and recovery improvements 14
Online backups 15
Recovery phase 15
Media reliability 16
Database snapshots 16
Online indexing 17
High Availability Considerations 19
Database mirroring 19
Asynchronous mirroring 19
Synchronous mirroring with failover 19
Log shipping 20
Operations and Monitoring 20
Tools 20
Dynamic management views 20
System Monitor 20
Recommended activities 21
Monitor log performance 21
Monitor Wait Types 21
Monitor long-running SQL 21
Monitor index usage 23
Perform online indexing 23
Collect statistics 24
Performance Tuning 24
Tools 24
SQL Server Profiler 24
Database Tuning Advisor 24
Plan guides 25
Be aware of… 25
Estimated versus actual 25
Cursor modes and execution plans 25
Bind variables 25
Table Partitioning 26
Range partitioning 27
Choosing the partition column 28
Possible uses 28
EIM tables 28
Time-sensitive tables 28
Very large tables 29
Conclusion 30
Siebel on SQL Server 2005 2
Executive Summary
Microsoft® SQL Server™2005 is a superior database platform for Siebel deployments. SQL Server2005 offers numerous enhanced or new features to increase availability, improve performance, and further simplify overall ease of operations. This document offers guidance to ensure that your Siebel deployment maximizes the features and performance of SQL Server2005.
SQL Server2005 will initially be certified with the following Siebel enterprise products:
· Siebel 7.7
· Siebel 7.8
· Siebel 7.8 Analytics
In addition, Siebel has announced a Statement of Direction for Siebel 8.0. The key highlights are:
· Timeframe for the release is the summer of 2006
· Support for the Tablet PC platform
· Flexibility to use the Siebel Component Assembly user interface
· Support for SQL Server2005
· Support for Internet Explorer (IE) 7.0 on Microsoft Windows®XP
· Availability of Siebel Handheld exclusively on Windows Mobile and “Magneto” devices
· Enhanced SOA enablement
The information and guidance in the document is subject to change. For an updated version of the document, see the Microsoft-Siebel Alliance Web site (http://www.siebelonmicrosoft.com!href(http://www.siebelonmicrosoft.com/)).
Microsoft-Siebel Overview
A strong partnership
Siebel and Microsoft have a long-standing technical and engineering alliance. As a result of this alliance, Siebel has selected Microsoft® .NET connection software (in addition to J2EE) as the best way to deliver the new generation of Web services applications. Based on this collaboration, Microsoft is now the preferred platform for Siebel deployments.
Siebel has leading SQL Server benchmarks with 8,000, 14,000, 20,000, and 30,000 concurrent users, thereby demonstrating the scalability of the Microsoft platform. Siebel supports both the 32-bit and 64-bit Microsoft platforms.
· 1,300 Siebel application deployments use SQL Server for the database.
· SQL Server is the fastest growing database among Siebel customers; 40% of new Siebel deployments choose SQL Server for their database.
· More than 80% of the customers run Microsoft Windows® on the Siebel application layer.
· An Accenture study concluded that running Siebel on SQL Server generates average savings of 38.7% when compared to similar UNIX solutions.
Siebel with SQL Server
Siebel workload tests proved that SQL Server2005 is able to take full advantage of the newest developments in hardware architectures. Siebel customers can today run a workload on four-processor commodity servers, that four years ago required a 32-processor server and one-million dollar investment.
SQL Server2005 (64-bit) edition and Windows Server™2003 offer record breaking performance for Siebel customers at a very compelling total cost of ownership (TCO). A recent example is the Siebel-certified benchmark of 30,000concurrent users on a single database server. For details on the benchmarks, see the Siebel benchmark Web site at http://www.siebel.com/resource-library/search-result.shtm?content_type=benchmark.
Siebel architecture
The diagram in Figure1 illustrates a sample Siebel deployment. The minimum set of components will always include the following:
· Database. Stores data.
· File System. Stores attachments such as documents or presentations.
· Siebel Application Server. Hosts client sessions and asynchronous Siebel components.
· Gateway Server. Holds configuration data and component availability.
· Web Server. Facilitates communication between the user’s Internet Explorer (IE) Web browser and the Siebel deployment.
Figure 1
The actual topology, components, and number of servers for a Siebel deployment are influenced by any of the following considerations:
· Desired business functionality, and Siebel components in use
· Business expectations for system availability
· Planned workload, including concurrent users and asynchronous processes such as workflow or Enterprise Application Integration (EAI)
The processor, memory, and storage capacity for SQL Server 2005 must be aligned with these considerations.
What’s New in SQL Server 2005
SQL Server2005 Enterprise Edition contains new features and improvements including:
· Enterprise data management. SQL Server2005 offers the benefits of reduced application downtime, increased scalability and performance, availability, manageability, and security.
· Developer productivity. SQL Server2005 includes many new technologies that significantly increase developer productivity.
· Business intelligence. SQL Server2005 furthers Microsoft leadership in business intelligence through innovations in scalability, data integration, development tools, and rich analytics including comprehensive integration, analysis, and reporting capabilities.
In particular, Siebel with SQL Server2005 Enterprise Edition delivers:
· Enterprise-class high availability and scalability. SQL Server2005 supports the most demanding Siebel deployments out-of-the-box. SQL Server2005 has no database size, partition, hardware, or CPU limitations. SQL Server2005 capabilities for high availability can minimize downtime in Siebel deployments.
· Best performance. Siebel running on SQL Server2005 natively improves performance while simplifying manageability.
· Easy installation and management. SQL Server2005 contains built-in tools that simplify installation and make it easy to deploy and manage Siebel deployments. The core SQL Server2005 engine dynamically tunes database options to respond to changing usage characteristics.
· Highly productive developer environment. SQL Server2005 provides a rich, powerful, and integrated single development environment that allows developers to more easily create secure, robust database applications at a lower cost.
Moreover, the following will be of particular interest for Siebel deployments:
· Cursor enhancements. SQL Server2005 has reduced the overall memory footprint for cursors, and no longer performs an implicit conversion on text columns.
· Large row support. New features for large-row support allow SQL Server2005 to automatically push data off-row if the size exceeds 8,060bytes. Siebel deployments need no longer have the limitation of rows that exceed 8,060bytes.
· Online Indexing. SQL Server2005 can transparently create a new index or rebuild an existing index while the application remains online.
“We can now rebuild indexes while keeping Siebel online. We have greater user satisfaction from improved system performance and higher system availability.”
-- Sam Atwater, Senior Support Analyst, Microsoft.
· Plan Stability. New features such as plan guides and plan stability provide the database administrator (DBA) with more options for tuning complex Transact-SQL statements.
· Table Partitioning. SQL Server2005 allows a table to be partitioned into manageable segments. This provides greater flexibility for a Siebel deployment that includes very large tables or has high-availability requirements.
· Powerful Transact-SQL enhancements. The new set-based operations EXCEPT and INTERSECT provide enhanced logic for Transact-SQL statements.
· Enhanced concurrency. SQL Server2005 offers the new snapshot isolations, Read-Committed Snapshot and Snapshot Isolation. Each offers increased data availability during simultaneous read/write operations.
· Multiple Active Result Sets (MARS). MARS provides the ability to have more than one pending request per connection; in particular, more than one default result set open per connection. Default result sets are forward-only, read-only result sets. For default result sets, the client drivers transparently retrieve the data in large chunks (Tabular Data Stream buffer-sized chunks) so that application requests are satisfied without a roundtrip to the server (as in the case of server cursors). The application can use a simple row-at-a-time programming model without compromising performance. The multiple active result sets feature removes the current restriction in which an open default result set blocks the driver from sending requests to the server until the entire result set is consumed.
For more information, see “What's New in SQL Server 2005” at
http://www.microsoft.com/sql/2005/productinfo/overview.mspx#ECAA!href(http://www.microsoft.com/sql/2005/productinfo/overview.mspx#ECAA)
Configuring SQL Server 2005 for Siebel
Before creating the database
The database administrator (DBA) should review the following guidelines and seek supplemental information as required. SQL Server2005 offers great performance, availability, and features for a Siebel deployment when you adhere to these guidelines.
Volume/growth projections
Projections for the volume and growth of Siebel product databases over time are often underestimated. The growth rate is typically higher than expected. When the growth projections are in error, Siebel products may require reconfiguration and adaptation on the hardware or on the database in order to satisfy hardware growth requirements.
For the initial configuration, ensure that the size of the database is sufficient to sustain the first six months of production. In addition, assume that each subsequent release of a Siebel product will tend to increase the weekly/monthly growth rate, as compared to its predecessor.
Do not use SQL Server features to shrink the database or data file. Archiving data from a Siebel database by using the SQL Server2005 shrink functionality causes the data to be stored in the opposite of the logical-to-physical order within one extent (a 64-KB block of 8pages). The shrink functionality is also problematic for the Storage Area Network backend because it places the data pages in a physical order that is the exact opposite of their logical order.
Number and size of SQL Server data files
The DBA must choose the number of data files. In SQL Server2005, the performance of data access within a data file is not related to the size of the data file, but to its concurrency on internal pages within the data file.
For the best distribution, create data files of the same size and share the files evenly. Each data file can be assigned to the same number of spindles, making the disk I/O system easy to maintain. When increases are needed, increase each of the data files by the same amount. This allows you to maintain an approximately equal amount of free space in each file.
In addition, if data files are added to an established database, there may be I/O contention as SQL Server automatically fills the new files until each is filled equally with the original set. In this case, add at least three data files to ensure that the load is distributed among the new files.
The considerations for defining the number and size of data files include:
· Evaluating disk configurations. Evaluate the available storage hardware including the number of disk arrays and partitions and the amount of available storage space on disk arrays.
· Estimating database growth. Consider the estimated database growth during production. In order to stay flexible in disk configurations, avoid using huge data files. Huge data files can create problems in handling—for example, when setting up sandbox systems or copying.
· Evaluating non-production environments. Keep in mind that each Siebel production system is supported by a test and development system. This means the test system needs to be periodically synchronized with the production database. A sandbox system that is based on the Siebel production system may also be required.