SAP on SQL General Update for Customers & Partners April 2015
Published Mar 13 2019 10:29 AM 191 Views
First published on MSDN on Apr 21, 2015


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



133,000



2-3,000



10-15 cores per CPU



785GB FusionIO or similar card recommended. 10G network



8 Socket DB Server Only



250,000



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


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
Solution



Support
Status



Clustering Version



vSphere
Version



Notes



MSCS with
shared disk



Supported



Windows Server 20031
Windows Server 2008
Windows Server 2008 R2
Windows Server 2012 2
Windows Server 2012 R2 5



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

















Version history
Last update:
‎Mar 13 2019 10:29 AM
Updated by: