SAP and Microsoft are continuously adding new features and functionalities to the SAP on SQL Server platform. The key objective of the SAP on Windows SQL port is to deliver the best performance and availability at the lowest TCO. This blog includes updates, fixes, enhancements and best practice recommendations collated over recent months.
1. SQL Server 2014 is now Generally Available
SQL Server 2014 is now Generally Available to customers and almost all NetWeaver 7.0 and higher systems are supported.
The minimum support pack levels are documented in Note 1966681 - Release planning for Microsoft SQL Server 2014
The latest SWPM can be found here: Software Logistics on SQL Server 2014
More information SQL Server 2014 now released by SAP for SAP NetWeaver 7.xx
The SQL Server ODBC driver required on SAP application servers can be downloaded here
2. SAP on Azure is now fully supported
Running SAP production systems on Azure public cloud is now fully supported for:
1. SAP NetWeaver 7.0 and higher systems
2. SAP Business Objects
3. SAP TREX
In addition Azure now provides SSD backed Premium Storage for better performance
It is strongly recommended to download and read the implementation documentation as this contains important concepts and prerequisites for running SAP on Azure.
1928533 - SAP Applications on Azure: Supported Products and Azure VM types
2015553 - SAP on Microsoft Azure: Support prerequisites
1380654 - SAP support in public cloud environments
Azure has proved to be a very popular platform for Development and Test systems as it is very quick to provision and de-provision systems.
Azure also offers Azure Site Recovery – a low cost and comprehensive DR solution.
A whitepaper has been released Protecting SAP Solutions with Azure Site Recovery
Customer Case Studies for Zespri , SCT and Softtek are available
3. SQL Server 2014 Column Store is now fully supported
SQL Server 2014 includes new column store functionality that is fully integrated to BW. BW customers are strongly recommended to implement this technology. More information can be found in these blogs
SQL Server 2014 Columnstore: Released for SAP BW
Concepts of SQL Server 2014 Columnstore
Columnstore Optimized Flat Cube in SAP BW
Column Store technology for SAP BW has two main benefits. User query and BW load performance is greatly improved. Storage efficiency is dramatically improved.
The performance gains are large enough that several customers have terminated using SAP Business Warehouse Accelerator (BWA) and switched to SQL Server Column Store.
Notes: SQL Server Column Store does not work with Realtime cubes. Customers migrating to SQL Server should implement all the notes listed in the SQL Server specific section of 888210 - NW 7.**: System copy (supplementary note) in the source system prior to export
4. Important Service Pack + CU for SQL Server 2012! If you are running SQL Server 2012 read this!
We have previously blogged about this topic however some customers continue to be impacted by this issue. Customers running SQL Server 2012 please read this!
SQL Server 2012 Service Pack 1 CU3, CU4, CU5 & CU6 contains a bug that can impact performance on SAP systems.
It is strongly recommended to update to SQL Server 2012 Service Pack 2 and the latest CU (the bug is fixed in CU7 & CU8 as well).
As at April 2015 it is recommended to apply SQL Server 2012 SP2 CU5
SQL Service Packs and Cumulative Updates can be found here: http://blogs.msdn.com/b/sqlreleaseservices/
Bug is documented here http://support.microsoft.com/kb/2895494
5. Windows Server 2003 is End of Life and is an obsolete product!
In this blogpost we discuss the official End of Life of Windows 2003 more than 12 years after this operating system was released. The vast majority of SAP on Windows customers have already upgraded to modern Windows releases and only a small minority of SAP customers are running on Windows Server 2003.
Extended Support for Windows Server 2003 ends in July 2015 and this will materially impact the supportability of SAP software on this platform.
Windows Server 2012 R2 and SQL Server 2014 or 2012 are the recommended upgrade targets. It is not recommended to deploy SAP systems onto Windows 2008 R2 or SQL Server 2008 R2.
6. Recommended Hardware Configurations
Intel and AMD continue to increase the performance and improve the price/performance of Intel x86 commodity based systems.
SAP SD 2 tier Benchmarks on 2 socket commodity servers are now nearly 90,000 SAPS
Increasing numbers of customers are consolidating SAP infrastructures onto clusters of 3-6 commodity 2 socket Intel servers running Hyper-V. SAP 3 tier on Hyper-V has demonstrated more than 320,000 SAPS
Recommended configuration for 2 socket systems are:
|
SAPS |
RAM (GB) |
Cores/Threads |
Comments |
|
|
2 Socket APP Server |
~ 87,000 |
384 |
10,12,16 or 18 cores per CPU |
Recommended platform for SAP application servers. 10G network |
|
2 Socket DB Server |
~ 87,000 |
768 |
10,12,16 or 18 cores per CPU |
785GB FusionIO or similar card recommended. 10G network |
|
4 Socket DB Server Only |
|
2-3,000 |
10-15 cores per CPU |
785GB FusionIO or similar card recommended. 10G network |
|
8 Socket DB Server Only |
|
3-4,000 |
10-15 cores per CPU |
785GB FusionIO or similar card recommended. 10G network |
*It is strongly recommended to follow the minimum RAM guidance above
Customers are strongly encouraged to ensure hardware purchases comply with Note 1612283 - Hardware Configuration Standards and Guidance
7. How to pinpoint which number range is causing block on NRIV
Some customers that have deployed very powerful servers with large amounts of RAM and ultra-fast disk storage like FusionIO have reported that their systems speed up dramatically. However these same customers also report that they observe the most expensive query in their system is now a SELECT on NRIV.
Some background is required to understand why this is happening.
NRIV is a table that holds number ranges. A number range is required for many objects in SAP applications. Some examples include “Invoice Number” or “Sales Order Number”. Sometimes these number ranges are required by law to be contiguous (meaning the numbers must follow in sequence with no gaps). This is usually required for tax and audit reasons.
After upgrading to powerful new Intel servers with large amounts of RAM the vast majority of the database can be stored in memory, meaning access is extremely fast. Write operation to the transaction log are extremely fast because they are now onto FusionIO cards.
The “problem” is that the SAP application is now hitting an internal application bottleneck because all the infrastructure bottlenecks have been removed.
Detecting and optimizing this is possible, but requires some care, detective work and involvement from the SAP functional team.
The steps are as follows:
1. Run ST04 -> Performance -> SQL Statements and observe the consumption by % Time of statements on the Number Range Table NRIV (see Figure 1 below)
2. Run ST04 -> Performance -> History -> Blocking Lock Monitor (see Figure 2 below). If this feature is not switched on switch this feature on with the button on the menu bar (it is switched off by default) and wait about 1 day.
3. Run ST04 -> Performance -> History -> Blocking Lock Monitor and check for blocking events. If many NRIV events are seen further investigation is needed (see Figure 3)
4. Run ST04 -> Performance -> SQL Statements and click on the “SQL Statement” button. This will NOT tell you which number range is causing the problem. This will only tell you which number range was queried the first time this SQL statement was executed. Therefore the information displayed is not useful for debugging this problem
5. Copy the SQL text from this blog below and paste into SQL Server Management Studio and run. Be careful to ensure the tracefile path exists and can be written to
6. Open the trace XEL file in SQL Management Studio and step through. Carefully note down the number range value(s) that are causing waits. Typically one or two can be seen. (see Figure 4)
7. Compile the information and pass to the SAP application team. Typically the configuration and buffering settings can be changed to allow buffering of some number ranges.
Figure 1
Figure 2
Figure 3
Figure 4
CREATEEVENTSESSION [nriv_blocking] ONSERVER
ADDEVENT sqlserver.rpc_starting(
ACTION(sqlserver.session_id,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.transaction_sequence)
where sql_text like'%NRIV%UPDLOCK,REPEATABLEREAD%'or sql_text like'%NRIV_LOKAL%UPDLOCK,REPEATABLEREAD%'
),
ADDEVENT sqlos.wait_info(
ACTION(sqlserver.session_id,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.transaction_sequence)
where (sql_text like'%NRIV%UPDLOCK,REPEATABLEREAD%'or sql_text like'%NRIV_LOKAL%UPDLOCK,REPEATABLEREAD%')and wait_type =(8 )
and duration >(0)
)
ADDTARGET package0.event_file(SETfilename=N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\nriv_blocking.xel')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,
MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO
504875 - Buffering number ranges
599157 - Number ranges: new buffering method
141497 - Consulting: Buffering of number ranges
8. Change to Select for All Entries
As discussed in an earlier blog post on SELECT FOR ALL ENTRIES (FAE) there is no such statement in ANSI SQL as SELECT FOR ALL ENTRIES. The SAP ABAP engine builds statements that perform the SELECT FOR ALL ENTRIES SAP ABAP open SQL statement functionality in the native DBMS ANSI SQL syntax.
There are several different ways to construct the SQL statements that are passed down to the DBMS. SAP has recently improved the performance of FAE statement.
This functionality is shipped in new DBSL for 7.41 and 7.42. DBSL 7.21 and 7.22 is still in testing and will be released soon. For more information review this note:
2144575 - An improved prefer_join method for FOR ALL ENTRIES
After applying the new kernel it is recommended to remove this profile parameter from any profiles
rsdb/prefer_join = 0
9. Shared Disk Clustering Support on Physical, VMWare and Hyper-V
Many customers have reported problems with VMWare support of the SAP ASCS because VMWare does not fully support SCSI Shared Disks in a cluster.
VMWare publish an article where they detail which versions and patches of VMWare support different Windows Server releases .
This article has proved exceedingly difficult for most customers to interpret.
A brief summary:
a. Windows is the only Operating System where SAP directly document and support a High Availability solution for the SAP ASCS. The solution SAP implemented uses a shared disk
b. The SAP ASCS can leverage a SCSI shared disk or iSCSI
c. There are many restrictions on VMWare releases earlier than vSphere 5.5 update 1
d. If the VMWare release is too old to support Windows Server 2012 R2 with SCSI shared disks then there are 3 options: (a) use iSCSI (b) use Windows 2008 R2 [not recommended] (c) Run the SAP ASCS on physical or Hyper-V
Windows 2012 R2 Hyper-V supports vHBA and Shared VHDX to provide shared disks between guest cluster nodes. This is simple and easy to configure and is always supported across different Windows Server releases.
|
Clustering
|
Support
|
Clustering Version |
vSphere
|
Notes |
|
MSCS with
|
Supported |
Windows Server 20031
|
4.x/5.x |
See additional considerations |
Table notes:
1. This table lists the support status by VMware on vSphere. Check with your vendor as the status of third-party software vendor support may differ. For example, while VMware supports configurations using MSCS on clustered Windows Server 2003 virtual machines, Microsoft does not support it. The same applies for the support status of the operating system version. Support for software that has reached end-of-life may be limited or non-existent depending on the life cycle policies of the respective software vendor. VMware advises against using end-of-life products in production environments.
2. Supported only with in-guest SMB and in-guest iSCSI for vSphere 5.1 and earlier. This restriction does not apply to vSphere 5.1 Update 2 and 5.0 Update 3. (See relevant footnotes under preceding table 1)
3. In-guest clustering solutions that do not use a shared-disk configuration, such as SQL Mirroring, SQL Server AlwaysOn Availability Group (Non-shared Disk), and Exchange Database Availability Group (DAG), except when the system disk's VMDK is located on NFS datastore, do not require explicit support statements from VMware.
4. vSphere 5.0 Update 1 and later, 5.1 Update 1 and later, and 5.5 and later (where Guest OS is supported)
5. vSphere 5.5 Update 1 and later.
10. Support Matrix for SAP JVM, SQL JDBC and AlwaysOn & Mirroring
The table below shows the support matrix for SAP ABAP and Java applications.
SAP ABAP based systems all support full automatic failover using SQL Server AlwaysOn and Mirroring.
SAP Java component support matrix is more complex.
This is due to the fact that the JVM 4.0 specification used by SAP for 7.0x is very old. Support for the JDBC drivers for this old VM has been terminated many years ago. Only SAP customers may continue to use these drivers in productive systems.
SAP Systems based on 7.30 or higher are using modern JVM with modern JDBC drivers that fully support AlwaysOn and Mirroring.
|
Auto Failover AlwaysOn |
Manual Failover AlwaysOn |
Auto Failover Database Mirroring |
Auto Failover Database Mirroring |
|
|
NW 7.xx ABAP |
||||
|
NW Java Stack <= JVM5 àMS JDBC driver 1.2, 2.0, 3.0 |
Should work, but not tested |
Automatic application reconnect works with using Database Mirroring Failover Parameter |
||
|
NW Stack JVM 6, 7 with MS JDBC driver 4.0/4.1 |
Review this important Note 2142959 - How to check the version of Microsoft JDBC Driver for SQL Server
The DB reconnect parameter is below
MSSQL_SERVER=<principal>;FailoverPartner=<mirror>;Database=<DBname>
11. SAP Start Service Should be set to Automatic (Delayed)
Windows 2012 R2 servers running Services that have Domain logon credentials can in rare circumstances create a deadlock in the Windows Service Control Manager and hang with a message on screen "Please wait for the User Profile Service".
SAP has released Note 1869813 - Windows Server 2012 (R2) hangs after reboot
As of Release 721 Patch Level 418, Release 722 Patch Level XXX, Release 741 Patch Level 213, Release 742 Patch Level 110, and Release 743 Patch Level 14, "sapstarstrv" is installed on Windows with the startup type "Automatic (Delayed Start)".
Customers running SAP development, test or production systems on Azure are also advised to change Service startup to “Automatic (Delayed)”
It is recommended to manually change the Service startup type for non-SAP Services that use Domain logon credentials such as Backup Software.
Links
Gartner Magic Quadrant for SQL Server
http://www.gartner.com/technology/reprints.do?id=1-237UHKQ&ct=141016&st=sb
Content from VMWare, SAP and other sources reproduced in accordance with Fair Use criticism, comment, news reporting, teaching, scholarship, and research
SAP Benchmarks discussed in this blog are fully disclosed below
October 23, 2014, by Hitachi in Tokyo, Japan, was certified by SAP on behalf of the
SAP Benchmark Council on March 30, 2015, with the following data:
Number of SAP SD benchmark users: 45,700
Average dialog response time: 0.95 seconds
Throughput:
Fully processed order line items per hour: 5,008,000
Dialog steps per hour: 15,024,000
SAPS: 250,400
Average database request time (dialog/update): 0.013 sec / 0.033 sec
CPU utilization of central server: 98%
Operating system, central server: Windows Server 2012 Datacenter Edition
RDBMS: SQL Server 2012
SAP Business Suite software: SAP enhancement package 5 for
SAP ERP 6.0
Configuration:
Central server: Hitachi Compute Blade 520X, 8 processors / 120 cores / 240 threads,
Intel Xeon Processor E7-8890 v2, 2.80 GHz, 64 KB L1 cache and
256 KB L2 cache per core, 37.5 MB L3 cache per processor,
1024 GB main memory
The SAP Sales and Distribution (SD) Standard Application Benchmark performed on
July 6, 2014 by Cisco Systems in San Jose, CA, USA, was certified by SAP on behalf
of the SAP Benchmark Council on July 22, 2014, with the following data:
Number of SAP SD benchmark users: 24,460
Average dialog response time: 0.97 seconds
Throughput:
Fully processed order line items per hour: 2,676,330
Dialog steps per hour: 8,029,000
SAPS: 133,820
Average database request time (dialog/update): 0.016 sec / 0.035 sec
CPU utilization of central server: 99%
Operating system, central server: Windows Server 2012 Datacenter Edition
RDBMS: SQL Server 2012
SAP Business Suite software: SAP enhancement package 5 for
SAP ERP 6.0
Configuration:
Central server: Cisco UCS C460 M4, 4 processors / 60 cores / 120 threads,
Intel Xeon Processor E7-4890 v2, 2.80 GHz, 64 KB L1 cache
and 256 KB L2 cache per core, 37.5 MB L3 cache per processor,
512 GB main memory
The SAP Sales and Distribution (SD) Standard Application Benchmark performed on
February 14, 2015, by Cisco Systems in San Jose, CA, USA, was certified by SAP on
behalf of the SAP Benchmark Council on March 4, 2015, with the following data:
Number of SAP SD benchmark users: 16,025
Average dialog response time: 0.98 seconds
Throughput:
Fully processed order line items per hour: 1,751,330
Dialog steps per hour: 5,254,000
SAPS: 87,570
Average database request time (dialog/update): 0.016 sec / 0.033 sec
CPU utilization of central server: 99%
Operating system, central server: Windows Server 2012 Datacenter Edition
RDBMS: SQL Server 2012
SAP Business Suite software: SAP enhancement package 5 for
SAP ERP 6.0
Configuration:
Central server: Cisco UCS B200 M4, 2 processors / 36 cores / 72 threads,
Intel Xeon Processor E5-2699 v3, 2.30 GHz, 64 KB L1 cache and
256 KB L2 cache per core, 45 MB L3 cache per processor,
256 GB main memory
Microsoft