The Microsoft Platform and SQL Server 2005: Providing World Record Scalability for PeopleSoft and Oracle Enterprise Applications: Peggy Seymour Burzin Patel Anu Chawla Microsoft Corporation The Microsoft Platform and SQL Server 2005: Providing World Record Scalability for PeopleSoft and Oracle Enterprise Applications


Agenda: Agenda Overview of Microsoft and Oracle Relationship SQL Server 2005 Overview Packaging & Pricing New & Improved Features PeopleSoft – SQL Server 2005 support Siebel 7.7/7.8 and SQL Server 2005 Q & A


Microsoft And Oracle Relationship: Microsoft And Oracle Relationship Business Development Look for areas where we can collaborate Facilitate the business and technical dialog Technical Collaboration Oracle database and applications Microsoft platform and tools Common Customer Support Co-Marketing and Events Oracle was PDC 05 Silver Sponsor Microsoft is Oracle OpenWorld Gold Sponsor


On Windows, With .NET & For Office: On Windows, With .NET & For Office Focus on Windows as Key Platform Concurrent Testing & Delivery on MSFT-Windows AD/Windows Security: Simpler Windows Native Authentication IIS: Better perf. w/ Web Cache, Using IIS at HTTP tier Clusterware: MSFT Cluster Services & MSFT NLB Support Broad Product Integration with MS.NET Web Services/Protocols: WS-I Basic Profile, Dime, Serializers, etc. Managing .NET WS: Enforce policies w/ .NET agent and OWSM UDDI Support: MSFT UDDI Browser Support Queuing: JMS Bridge to MSMQ Legacy Support: C++ Web Services to J2EE Interop Orchestration: BizTalk Interoperability Human Workflow: MSFT WinForms, InfoPath Integration Portals: Sharing WS & Portlets across SharePoint, Oracle Portal Directory Services: Simpler Active Directory Sync Office Office 2003: Using InfoPath, Word, Excel as “front-end” Orchestrating Office 2003: Incorporate into Workflows with BPEL PM Alerting through Office: Oracle BAM to Outlook Publish to Office docs: XML Publisher, Oracle BI Excel plugin


Interoperability between Fusion Middleware and Microsoft: Interoperability between Fusion Middleware and Microsoft Interoperate w/ .NET Web Services DBs: SQL Server Windows | .NET/Windows Server System | Office Interoperability


Slide6: Windows Technology Center at Oracle


Slide7: .NET Developer center at Oracle


Microsoft Office Interoperability Center at Oracle : Microsoft Office Interoperability Center at Oracle


SQL Server 2005 Overview: ‘SQL Server 2005’ – next version of SQL Server. Released November 7, 2005 Focuses on the Enterprise Key tenets: Secure Available Platforms supported: 32-bit Xeon 64-bit Itanium x64 AMD64 / x86ES SQL Server 2005 Overview - Reliable - Durable


Packaging & Pricing: Packaging & Pricing Note: All higher editions include functionality from edition below it Express Workgroup Standard Enterprise Fastest way for developers to learn, build & deploy simple data driven applications Easiest to use & most affordable database solution for smaller departments & growing businesses Complete data management & analysis platform for medium businesses and large departments Fully integrated data management and analysis platform for business critical enterprise applications Free $3.9K per proc or $739 (Server + 5 users) $6K per proc or $2,799 (Server + 10 users) $25K per proc or $13.5K (Server + 25 users) 4 CPU Unlimited RAM (64-bit) Database Mirroring OLAP Server Reporting Server New Integration Services Data Mining Full Replication & SSB Publishing


New & Improved Features: New & Improved Features SQL Server 2005 includes 100’s of new and enhanced features This presentation covers only on a very small sub-set of these that most relevant to PeopleSoft applications Data Partitioning Snapshot Isolation Index Enhancements New Optimizations DMVs Plan Guides Security


Data Partitioning: Data Partitioning Partitioning breaks a single object into multiple manageable pieces Transparent to the PeopleSoft application Allows easy management of large tables and indexes The row is the unit of partitioning (horizontal partitioning) Range partitioning supported (single-level) All partitions run on a single SQL Server database Partitions can be created or dropped with virtually no loss of availability to the table “Sliding window” scenarios supported


Blocking - Example: Row-n X-Lock S-Lock  Blocked! Row-n Blocking - Example


Snapshot Isolation: Snapshot Isolation New flavor of Read Committed transaction isolation level Turned ON/OFF at database level ALTER DATABASE SET READ_COMMITTED_SNAPSHOT ON Readers see committed values as of beginning of statement Writers do not block Readers Readers do not block Writers Writers do block writers Can greatly reduce blocking / deadlocking


Snapshot Isolation - Example: Snapshot Isolation - Example CREATE TABLE t1 (c1 INT, c2 INT) INSERT INTO t1 VALUES(1, 5) Transaction 1 BEGIN TRAN UPDATE t1 SET c2 = 9 WHERE c1 = 1 COMMIT TRAN Time Transaction 2 BEGIN TRAN SELECT c2 FROM t1 WHERE c1 = 1 -- SQL Server returns 5 SELECT c2 FROM t1 WHERE c1 = 1 -- SQL Server returns 9 COMMIT TRAN


Index Enhancements: Online Index Operations Online index operations now allow concurrent modification of the underlying table or index Updates incur some additional cost during an online index operation Disabled Indexes Stops index from being used and maintained Deallocates the storage space, but retains metadata Index needs to be rebuilt before it can be used Easily rebuilt / reactivated with retained metadata ALTER INDEX … REBUILD Index Enhancements


New Optimizations: Forced Parameterization Database level option to enable parameterization for all queries automatically without application code changes Implied Predicates Optimization Optimizer automatically optimizes queries with ‘implied’ predicates for equalities and inequalities Function based index Optimizer automatically considers index on computed column New Optimizations


Dynamic Management Views (DMV): Dramatically increased information with minimal overhead on database server Surface SQL engines operational counters More than 75 DMVs Some important DMV groups: dm_exec_* Query execution and connections dm_os_* Memory, locking and scheduling dm_tran_* Transactions and isolation dm_io_* IO on network and disk dm_db_* Databases and db objects Dynamic Management Views (DMV)


Plan Guides: Plan Guides Allows controlling optimization with query hints when the application query cannot be changed Create: sp_create_plan_guide Drop/Disable/Enable: sp_control_plan_guide


Security: Implementation Robustness Reduced Surface Area (‘Off’ by default) Highly customizable installation options Password Policy enforcement Password expiration Account lockouts expiration Password strength Security


PeopleSoft - SQL Server 2005 Support: PeopleSoft - SQL Server 2005 Support PeopleTools 8.48 will support SQL Server 2005 Tentative release date: mid-2006 Release 9.0 applications will only be supported on SQL Server 2005 New features like Snapshot Isolation will be adopted and supported New applications will take advantage of new data types


Benchmark Results: Benchmark Results PeopleSoft Campus Solutions 8.9 4,000 concurrent online users PeopleSoft Global Payroll 8.8 82,743 payees/hour PeopleSoft Financials 8.4 13,000 concurrent online users PeopleSoft Learning Solutions 8 SP1 5,000 concurrent online users PeopleSoft HR (CRM) 8.8 1,250 concurrent online users PeopleSoft 8 CRM SSCS 8.4 25,200 concurrent online users PeopleSoft Financials 8.4 8,955,224 Journal Lines/Hour


SQL Server 2005 – The Comprehensive, Integrated Data Platform: SQL Server 2005 – The Comprehensive, Integrated Data Platform


On Windows, With .NET & For Office: On Windows, With .NET & For Office Focus on Windows as Key Platform Concurrent Testing & Delivery on MSFT-Windows AD/Windows Security: Simpler Windows Native Authentication IIS: Better perf. w/ Web Cache, Using IIS at HTTP tier Clusterware: MSFT Cluster Services & MSFT NLB Support Broad Product Integration with MS.NET Web Services/Protocols: WS-I Basic Profile, Dime, Serializers, etc. Managing .NET WS: Enforce policies w/ .NET agent and OWSM UDDI Support: MSFT UDDI Browser Support Queuing: JMS Bridge to MSMQ Legacy Support: C++ Web Services to J2EE Interop Orchestration: BizTalk Interoperability Human Workflow: MSFT WinForms, InfoPath Integration Portals: Sharing WS & Portlets across SharePoint, Oracle Portal Directory Services: Simpler Active Directory Sync Office Office 2003: Using InfoPath, Word, Excel as “front-end” Orchestrating Office 2003: Incorporate into Workflows with BPEL PM Alerting through Office: Oracle BAM to Outlook Publish to Office docs: XML Publisher, Oracle BI Excel plugin


Interoperability between Fusion Middleware and Microsoft: Interoperability between Fusion Middleware and Microsoft Interoperate w/ .NET Web Services DBs: SQL Server Windows | .NET/Windows Server System | Office Interoperability


SQL Server 2005 Achieves World-Class Scalability for Siebel : 20,000 concurrent Users SQL Server 2005 Achieves World-Class Scalability for Siebel Siebel 7.7 Platform Scalability World Record Record Breaking Performance of SQL Server 2005 on HP Integrity 16 x Itanium2 shows the scalability beyond 8 CPU's of ANY Siebel platform 30,000 concurrent Users Siebel 7.7 Platform Unprecedented Performance/Price SQL Server 2005 on HP 4 X DL585 AMD Dual Core Opteron provides best performance for price


Largest Global Customer Choosing Siebel on Windows and SQLCustomer Reference Calls Available: Largest Global Customer Choosing Siebel on Windows and SQL Customer Reference Calls Available


Additional Information & Resources: SQL Server 2005 - Books Online http://www.peoplesoft.com/media/en/pdf/red_paper_sql.pdf http://www4.peoplesoft.com/PSDB.NSF/ServersByDBMS?ReadForm&Start=59 http://www.microsoft.com/sql/2005/default.mspx http://msdn.microsoft.com/SQL/2005/default.aspx http://www.microsoft.com/technet/prodtechnol/sql/2005/default.mspx Additional Information & Resources Microsoft Technical Champs Program for Oracle at microsoft-oracle.com Whitepapers, Benchmarks Contact the Oracle team at Microsoft – orclhelp@microsoft.com


Q & A: Questions? Q & A


Slide30: BACKUP SIEBEL SLIDES


Siebel Architecture: Siebel Architecture


Top 7 SQL Server 2005 Features for Siebel: Top 7 SQL Server 2005 Features for Siebel Certification for SQL Server 2005 Cursor Enhancements Large Row Support Index Enhancements Table and Index Partitioning Powerful T-SQL Enhanced Platform Support Powerful Monitoring and Tuning Tools


Cursor Enhancements: Cursor Enhancements SQL Server 2000: Cursor Issues FFO cursor implicitly convert to dynamic cursor if result set contains text column Dynamic cursor has plan limitations Cursor scalability limited by 3GB virtual address space SQL Server 2005 No more implicit cursor conversion on text column Reduced cursor memory footprint This is a HUGE fix for Siebel queries.


Tuning & Tools: Tuning & Tools SQL Server 2005: Dynamic Management Views “sys.dm_exec_query_stats” – what’s the top 5 query with the most cpu consumption? “sys.dm_db_index_usage_stats” – what are the rare/never used indexes? HUGE in Siebel. 80 Indexes and 75 are 100% NULL, etc… SQL Server 2000: Index Tuning Wizard Of little help on 20 to 80+ table joins SQL Server 2005: Database Tuning Advisor Huge improvements. Actually usable.


Index Enhancements: Index Enhancements SQL Server 2000: Index issues Siebel uses one size fits all schema, unused indexes create performance / on-disk storage overhead Index maintenance requires “long-term” table lock SQL Server 2005 Disable index feature removes the need for maintaining unused indexes while preserving the index definition Online index rebuild, reorganize and drop allow index access during maintenance


Large Row Support: Large Row Support SQL Server 2000: 8k page limitation Moving the UNICODE double the size of character columns Worktable for sort operation failed due to result row size greater than 8060 byte “Work Around” was TEXT/IMAGE. 1 row per fetch. SQL Server 2005 Varchar Large Row Support automatically pushes varchar data off-row if row size is greater than 8060 bytes New Max data types provide T-SQL functionalities analogous to traditional varchar


Table and Index Partitioning: Table and Index Partitioning Separate data in a table into multiple manageable pieces Big improvement for EIM. Can now break up batches physically. Allow easy management of very large tables and indexes Fast Insert or Delete of large quantities of data (per-partition) Helps EIM Index defragmentation or rebuild on one partition using ALTER INDEX … PARTITION ()


Powerful T-SQL: Powerful T-SQL EXCEPT and INTERSECT Powerful new set-based operations SELECT CustomerName FROM Customers.Customer EXCEPT SELECT Name FROM Remote.CDB.Customers.Customer Ranking Functions and OVER Clause Row_Number, Rank, Dense_Rank, NTile Powerful Partitioning and Ordering within a single query


Siebel Marketing Server Performance: SQL Server 2005: Siebel Marketing Server Performance: SQL Server 2005


SQL Server 2005: x64 : SQL Server 2005: x64 New native support for x64 platform 32-bit SQL Server 2005 allows 4GB Virtual Address Space running underneath x64 Windows OS Siebel: More room for more ODBC cursors! Nearly unlimited Virtual Address Space for x64 SQL Server 2005