SAP on SQL General Update for Customers & Partners April 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 NOTtell 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

 3. Update 1

 

Figure 2

3. Update 2

 

Figure 3

3. update 3

 

Figure 4

3. Update 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
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 20122
Windows Server 2012 R25

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