What’s New in SQL Server 2008 for SAP
Juergen Thomas Principal Program Manager Microsoft Corporation
What We’ll Cover …
• Where is SQL Server with SAP today • SQL Server Success Factors in SAP • Platform Tendencies • SQL Server Feature Roadmap for SAP
2
SQL Server Committed to Success with SAP
• Microsoft has a number of people working with SAP on a technical basis – One SQL Server Program Manager dedicated to SAP placed in SQL Server development – One SQL Server Program Manager placed in Walldorf specifically for BW development – Three dedicated Microsoft PSS engineers placed in Walldorf and Redmond delivering • SAP on the other side has 5 developers placed in Redmond – Developers are hosted in SQL Server – Have impact on new releases in an early stage – Test features of new releases way before Beta I – Do implementations in their code way before SQL Server ships – Support Microsoft IT running their SAP systems on SQL Server Beta software in production – Goal: SAP to support most used releases at the point SQL Server does ship – Worked better than with every other platform
3
SQL Server Committed to Success with SAP
• Goal achieved with SQL Server 2005? • SAP supports SQL Server 2005 2 months after we release SQL Server 2005
– Shortest time for any new database release supported by SAP – Oracle 10 it took 18 months for SAP to support
• Meanwhile even 4.6C is supported on SQL Server 2005
– 90+% of SAP/SQL Server customers can run SQL Server 2005
• SQL Server Table Partitioning is implemented in BW3.5 already • Microsoft runs their productive multi-TeraByte R/3 system on SQL Server 2005 Beta Releases for 15 months • Microsoft was Beta Tester for SAP’s x64 Netweaver implementation. Running in production for more than 6 months before SAP releases x64 platform for Netweaver Application Server • Microsoft will move next release of SQL Server ‘2008’ in production on MS SAP R/3 of around 4.0-45.TB in November 2007 • Work with AMD on Beta-hardware of Opteron Quadcore servers
4
How does the customer base look today?
• Typical Server Hardware used: – 4/8/16/32way IA64 servers with up to 256GB of RAM – 2/4 Xeon, Pentium with 8-16GB RAM – 2/4way Opteron Servers up to 64GB Customers database volume: – Over hundred customers beyond Terabyte volume, max. over ~10-11TB with R/3 or BW databases – A few hundred R/3, BW, CRM systems over 500GB SAP workload on the systems (dialog steps/day): – 3,500,000 to 4,500,000
– Running around 2400 Development, Test, Demo and Production systems on Windows/SQL Server. Accounts to nearly half of all their systems
•
•
• Biggest single customer: SAP
5
What We’ll Cover …
• Where is SQL Server with SAP today
• SQL Server Success Factors in SAP • Platform Tendencies • SQL Server Feature Roadmap for SAP
6
What Makes SQL Server Successful
• SQL Server Design Focus in SQL Server 7.0: – Minimum Knobs to configure • Cover as many Workloads as possible with same settings • Keep most of the configurations changed Online (e.g. Memory) – SQL Server 2005: ALL global SQL Server Configurations changeable ONLINE (includes # of processors used – changeable on the FLY) • One Block/Page size fits all. Only 8K Pages/Blocks – No knobs to change – SAP distrusted in 1999 – Meanwhile SAP does not even recommend changes of Block Sizes in Oracle – Perform required Tasks automatically • Why performing a query well knowing that Statistics are missing Build statistics first
7
What Makes SQL Server Successful
• Design Principles advanced in SQL Server 2000 with emphasis on Online Operations and Parallel Maintenance Operations – Parallel Index Creation – Support SAN vendors with alternative Backup Methods (SnapShot, MirrorSplit, SnapClone) – Online Data Re-Organization • Use after Archiving Data • Free Lists and B-Trees maintained on the fly No reason to reorganize – Enhance Scalability • Performance work for new Hardware Architectures (NUMA-Light)
8
What Makes SQL Server Successful
• Focus of SQL Server 2005 on online Operations and High Availability – Index Creation works Online on ALL type of Indexes – Database Mirroring as Third High Availability Method – Complete Adaption to NUMA Hardware Architectures (Second Step after Optimization in SQL Server 2000) – For SAP: Table Partitioning – Integration of .NET CLR
9
SAP Leverages ‘Ease of Use’ of SQL Server
• No Usage of File Groups (Table Spaces) because – SQL Server ‘Proportional Fill’ does leverage I/O Bandwidth best – Table Spaces usually experience different I/O Load • High effort in Monitoring • High effort in balancing I/O SQL Server configuration the same for every SAP Product – SAP only changes two parameters of global settings compared to default SAP relies on SQL Server Automatic Update Statistics, exception: No extra Backup/Restore Tool by SAP (brbackup) SAP relies on SQL Server Scheduler and on SQL Server Tools Mission: Provide least and easiest Management efforts with Highly Demanding SAP Application
• • •
•
10
Scalability of SQL Server 2005 under SAP Workload
SAP 3-Tier SD Benchmark Scalability
100000
90000
80000
70000
60000 8way PIII 700MHz 50000 ES7000 16way, PIII 700MHz ES7000 24way, PIII 700MHz ES7000 32way, PIII 700MHz 40000 ES7000 32way, PIII 900MHz ES7000-PD2 32way, PIII 900MHz 30000 HP Superdome 64 x Itanium II
20000
10000
0 8way PIII 700MHz ES7000 16way, PIII 700MHz ES7000 24way, PIII 700MHz ES7000 32way, PIII 700MHz ES7000 32way, PIII 900MHz ES7000HP PD2 32way, Superdome PIII 900MHz 64 x Itanium II
11
What We’ll Cover …
• Where is SQL Server with SAP today
• SQL Server Success Factors in SAP • Platform Tendencies • SQL Server Feature Roadmap for SAP
12
Platform Tendencies
• 32Bit x86
– Legacy and not worth any investments – No more support for future SAP products – Not supported for SQL Server with future SAP products – – – – – – – – – – – – Intel proprietary Mostly seen in SAP customers in High-End database servers Hardly seen in commodity size as application server Suffering wide range of software supply Price/performance ratio is a problem compared to x64 Became default on commodity platform Full support from SAP and Microsoft software Can run 32Bit software as well Seen in most customers as database servers with up to 10,000 SAPS Majority case of new application servers Extreme good Price/Performance ratio Throughput doubling every 18 months
• IA64
• X64
13
Performance on Commodity in SAPS
SAPS
Intel Xeon Dual Core 3,4GHz 16 MB
Year
Xeon MP Dual Core 3,0 GHz 2 MB
Opteron 2,6 GHz 1 MB Xeon MP 3,3 GHz 8 MB
Xeon MP 3,0 GHz 4 MB Xeon MP 2,0 GHz 2 MB
P.Pro 200 MHz
PII Xeon 400 MHz
PIII Xeon 700 MHz 1 MB
PIII Xeon 900 MHz 2 MB
Xeon MP 1,6 GHz 2 MB
What We’ll Cover …
• Where is SQL Server with SAP today
• SQL Server Success Factors in SAP • Platform Tendencies • SQL Server Feature Roadmap for SAP
15
SQL Server Roadmap for SAP Schedule
2005 - 2006 2007 Q1 Q2 Q3 Q4 2008 Q2 2010 - 2011
Q4/05
Q2/06
SQL Server 2005
SQL Server 2005 SP1
SQL SQL Server Server 2008 2005 SP2SQL Server TAP 2008 Begins Announcement, First CTP
SQL Server 2008 Launch
SQL Server “vNext++”
16 http://www.microsoft.com/sql/prodinfo/futureversion/default.mspx
SQL Server Roadmap for SAP SQL Server 2005 RTM - 2005
• What got delivered to SAP in Q4/05 with SQL Server 2005 RTM?
– Table Partitioning for SAP BW (see later) – Online Index Maintenance (see later) – Include clause for indexes – allows more flexible query tuning – Supported by SAP – Dozens of Monitoring Views – Adapted in some SAP release. Widely used in new SAP DBA Cockpit
• More info on SAP DBA Cockpit: https://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/1062428c -f1df-2910-b08f-c322feddcd10
– Independent Deployable Client Layer (SNAC) – Stability in deployment – Multiple Active Result Sets – Severe code simplification – New BLOB types – Severe code simplification
17
Delete Rows from a partitioned Table in SQL Server 2005 part I
SQL syntax to „remove“ a partition : Alter table BigTable switch partition 1 to NewTable
NewTable BigTable
Data won‘t be physically moved or deleted. It‘s just a „logical“ delete by changing meta data in the system tables of the database server. The former partition no longer belongs to the original table.
1994
1995
1996
1997
1998
1999
“Deleted“ partition becomes a new separate unpartitioned table 18
Range-Partitioning in SAP BW
• Range-Partitioning will be mainly used for three types of BW tables : – PSA tables ( persistent staging area ) – F-fact tables ( part of a BW InfoCube ) – E-fact tables ( part of a BW InfoCube ) Partitioning in SAP BW will improve query performance in certain cases but the main benefit is about maintaining huge tables (inserting and especially deleting data rows). Please check out OSS note #869407
•
•
19
Deletion of a „load job“ in SAP BW
• • • • • SAP NetWeaver BW (Business Warehouse) test system Task: Delete 500,000 rows out of a SAP BW „InfoCube“. Data created by a certain „load job“ SAP BW offers possibility to delete specific „load jobs“ including their data Comparison of deletion method: – „traditional approach“ vs. – Table partitioning
20
Deletion of Rows – “Traditional approach“
5 * deletion of 100,000 rows, each delete bundle takes approx. 15 seconds = 75 seconds for 500,000 rows
21
Deletion of Rows – using table partitioning
1 * deletion of 500,000 rows via drop of partition = 1.2 seconds for 500,000 rows
22
Index Operation in SQL 2005
Table VBAP
MANDT VBELN POSNR MATNR …
1. Change Request
SPID 875 in SQL: Create index VBAP~ZCUST on VBAP (MANDT,VBELN,…)
300
0002900013
000010
TRN001
…
300 300 300 300
0002900013 0002900014 0002900015 0002900015
000020 000010 000010 000020
MCS002 MCS001 MCS001 TRN001
… … … …
2. Change Request
300
300 300 300 300 300
0002900015
0002900015 0002900016 0002900020 0002900021 0002900022
000030
000040 000010 000010 000010 000010
MCS002
PSS001 MCS001 PSS001 PSS001 TRN001
…
… … … … …
SPID 1245 in SQL: Insert into VBAP values (‘300’,’002900023’,’000010’, ’MSC001’,…)
OK -> can be executed in parallel to the running index creation request
300
0002900022
000020
TRN001
…
23
Online Index Operations
• Index creation in SQL 2005 in Offline or Online mode possible – Offline: table is locked exclusively, default mode – Online: parallel change activity on table is allowed Online index maintenance possible for – Create, rebuild, drop – Reorganize (including BLOBs) – Index-based constraints (Primary key, unique) Index creation via online option takes up to 3 times longer
•
•
24
SQL Server Roadmap for SAP SQL Server 2005 SP1 - 2006
• What got delivered to SAP in Q2/06 with SQL Server 2005 SP1?
– SQL Server 2005 Database Mirroring (next slides) – SSRS Integration into SAP BW
• Directly design and run Reports out of SSRS against SAP BW • Uses SAP BW XML/A interface • Solution Certified by SAP
25
Database Mirroring in SQL Server 2005
Witness (optional) Principal
`
Clients connecting to Principal
Mirror
Network
`
DB
Log Records to Mirror
DB
`
Disks Principal
Disks Mirror
26
How does Database Mirroring Work?
Sequence of Execution depends on Mirroring scenario
*
`
1 Commit
5* Acknowledge 4* Acknowledge
Primary Database Server Secondary Database Server
2 Transmit to Mirror 3
Write to Remote Log
Log
2 Write to
Local Log
DB
Log
3
Committed in Log
DB
27
Synchronous vs. Asynchronous DB Mirroring
Asynchronous Mirroring Synchronous Mirroring
Automatic failover
No
Yes (with Witness)
Data loss during failover
Possible, if data is not sent fast enough to Mirror server
No
Performance Impact on Application
None
Possible (e.g. Network, Hardware)
Distances
Longer Distance
Short Distance
28
Administration of Database Mirroring
• • • Suspend Mirroring for planned maintenance on Mirror Server – Exercised already quite a few times by MS IT for Server maintenance Resume Mirroring after maintenance is finished Long Downtime of Mirror Server? – Stop Mirroring – Start up Logshipping when Mirror Server available – After Mirror Server has caught up – Stop Logshipping – Start Mirroring Forced Failover possible? – Yes possible, however open transactions will be rolled back, SAP Batch Jobs break. But feasible.
•
• Additional sources: OSS note #965908
29
SQL Server Roadmap for SAP SQL Server 2005 SP2 - 2007
• What got delivered to SAP in Q1/07 with SQL Server 2005 SP2?
– SQL Server 2005 vardecimal storage
• • • • Store decimal data types dynamic long according to their values Should compensate disadvantage compared to Oracle Usable with SAP BW More info on: https://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid /a09bdc69-edd4-2910-b090-83ef3a1f4b51 OSS note: #991014
– Introduction of IAM cache for faster extent allocation
• Speeds up allocations on large tables spread throughout the database
– Off-Schedule from SP2: JDBC driver 1.2
• Will be used by 7.10 SAP Basis and 7.00 SR3 Basis (end of 2007) • Will allow automatic failover of SAP JAVA instances as well
30
SQL Server Roadmap for SAP SQL Server 2008 - 2008
• What will be delivered to SAP in 2008 with SQL Server 2008?
– SAP BI scenarios
• Extension of vardecimal compression to new Row Format storing all number related data types in a dynamic fashion
– Savings with MS ERP DB: ~15% » Some tables no savings at all. Extreme cases one ¼ of the size – Savings on SAP BW Benchmark Cube ~35% – Performance Impact: Too early to state on it since development is ongoing – Performance Goal: Make it usable for SAP OLTP and OLAP products
• Page Level compression
– No tests conducted yet
• • • •
31
Query Handling optimizations for Star Join Handling Grouping Sets Parallelism improvements on Partitioned Tables More integration work on some other SQL Components
SQL Server Roadmap for SAP SQL Server 2008 - 2008
• What will be delivered to SAP in 2008 with SQL Server 2008?
– SAP BI scenarios
• Query Handling optimizations for Star Join Handling
– More efficient Query Plans for typical SAP aggregation queries or ad-hoc queries touching Fact tables
• Grouping Sets
– Request of SAP BI
• Parallelism improvements on Partitioned Tables
– Should speed up built of aggregates in SAP BI
• More integration work on some other SQL Components
– Might be delivered off band before 2008. Decision still needs to be made
• Some smaller delighters for SAP Bi development
32
SQL Server Roadmap for SAP SQL Server 2008 - 2008
• What will be delivered to SAP in 2008 with SQL Server 2008?
– SAP general scenarios
• Merge/Upsert – code simplification
– Got introduced in SAP Basis 7.10 as database operation
• Backup Compression
– – – – Compression can be enabled by instance global parameter Can be enabled via backup command VDI and VSS Interfaces will support it At least as good as a compression rate as 3rd party products
• Encryption of SQL Server Datafiles transparent to SAP
– Data is getting encrypted in I/O path – Not usable as access control – Thought to hinder data theft in form of copying data files, getting hands on disks with databases on or getting hands on backups – Backup will contain encrypted data if database is encrypted
33
• Compression of network Traffic in Database Mirroring • Multiple improvements on some DMVs
SQL Server Roadmap for SAP SQL Server 2008 - 2008
• What will be delivered to SAP in 2008 with SQL Server 2008?
– SAP general scenarios
• Compression of network Traffic in Database Mirroring
– Reduce infrastructure requirements for DBM
• Auto Repair when in sync. Database Mirroring
– Corrupt pages on principle are repaired with consistent pages from mirror
• Improvement on controls of Lock Escalation – disable Lock escalation on table level
– Be able to completely disable Lock escalation of Dynamic Locking on a per table basis
• Performance Data Collection – SAP will leverage base infrastructure
– Complete data collection, Warehouse and UI Interface for recording, storing and aggregating performance data – Data which is collected not only relates to SQL Server data, but can be OS perfmon data as well
• Minimal Logging improvements for SAP platform migrations
– Should reduce T-Log space requirement in Unicode migrations – Intend to release QFE for SQL 2005 in Fall 2005 34
SQL Server Roadmap for SAP SQL Server ‘?’ - 2009
• What will be delivered to SAP in 2009 with SQL Server ‘?’
– There are executions taking place already. But too early to talk about.
• Interested being a SQL Server 2008 TAP customer with your SAP implementation?
• Contact juergent@microsoft.com
35
More Resources
• http://www.sdn.sap.com/irj/sdn/mss • http://www.microsoft.com/isv/sap/technolo gy/platform/sql.aspx • http://blogs.msdn.com/saponsqlserver/
Thanks a lot Juergen Thomas juergent@microsoft.com
36
www.microsoft.com/sap