SAP on SQL General Update for Customers & Partners April 2014
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.
Key topics in this blog: SQL Server 2014 First Customer Shipment program now available, Intel release new powerful E7 v2 processors, Windows 2012 R2 is now GA and customers are recommended to upgrade SQL Server 2012 CU to avoid a bug that can impact performance.
1. SQL Server 2014 Release & Integration with SAP NetWeaver
SQL Server 2014 is now released and publically available. SAP are proceeding with the certification and integration of SQL Server 2014 with NetWeaver applications and other applications such as Business Objects.
Customers planning to implement SQL Server 2014 are advised to target upgrading to the following support pack stacks. Customers who wish to start projects on SQL Server 2014 or if early production support on SQL Server 2014 is required please follow the instructions in Note 1966681 – Release planning for Microsoft SQL Server 2014
Required SAP ABAP NetWeaver Support Package Stacks for SQL Server 2014
|SAP SOFTWARE||SUPPORT PACKAGE STACK (SPS)|
|SAP NETWEAVER 7.0||SPS 29, for SAP BW: SPS 30 (7.0 SPS 30 contains SAP_BASIS SP 30 and SAP_BW SP 32)|
|SAP EHP1 FOR SAP NETWEAVER 7.0||SPS 15, for SAP BW: SPS 15|
|SAP EHP2 FOR SAP NETWEAVER 7.0||SPS 14, for SAP BW: SPS 15|
|SAP EHP3 FOR SAP NETWEAVER 7.0||SPS 9|
|SAP NETWEAVER 7.1||SPS17|
|SAP EHP1 FOR SAP NETWEAVER 7.1||SPS 12, for SAP BW: SPS13|
|SAP NETWEAVER 7.3||SPS 10, for SAP BW: SPS11|
|SAP EHP1 FOR SAP NETWEAVER 7.3||SPS 9, for SAP BW: SPS11|
|SAP NETWEAVER 7.4||SPS 4, for SAP BW: SPS06|
Java only systems do in general not require a dedicated support package stack for SQL Server 2014.
Note 1966701 – Setting up Microsoft SQL Server 2014
Note 1966681 – Release planning for Microsoft SQL Server 2014
New features in SQL Server 2014 are documented in a five part blog series here
Also see key new SQL Server Engine Features and SQL Server Managed Backups to Azure. Free trial Azure accounts can be created for backup testing. To test latency to the nearest Azure Region use this tool http://azurespeedtest.azurewebsites.net/
2. New Enhancements for Performance and Functionality for SAP BW Systems
SAP BW customers can reduce database size, improve query performance and improve load/process chain performance by implementing SAP Support Packs, Notes and SQL Server Column Store.
All SAP on BW customers are strongly recommended to review these blogs:
SQL Server Column-Store: Updated SAP BW code
Optimizing BW Query Performance
Increasing BW cube compression performance
SQL Server Column-Store with SAP BW Aggregates
Performance of index creation
It is recommended to upgrade SAP BW systems to SQL Server 2012 or SQL Server 2014 and implement Column Store. Customer experiences so far have shown dramatic space savings of 20-45% and very good performance improvements.
BW customers with poor cube compression performance are highly encouraged to implement the fixes in Increasing BW cube compression performance
3. SAP Note 1612283 Updated – IvyBridge EX E7 v2 Processor Released
Intel has released a new processor for 4 socket and 8 socket servers. The Intel E7 v2 IvyBridge EX processors almost doubles the performance compared to the previous Westmere EX E7 processor.
Even the largest SAP systems in the world are able to run on standard low cost Intel based hardware. Intel based systems are in fact considerably more powerful than high cost proprietary UNIX systems such as IBM pSeries. In addition to performance enhancements many reliability features have been added onto modern servers and built into the Windows operating system to allow Windows customers to meet the same service levels on Wintel servers that previously required high cost proprietary hardware.
- 4 socket HP DL580 Intel E7 v2 = 133,570 SAPSor 24,450 users
4 socket IBM pSeries Power7+ = 68,380 SAPS or 12,528 users
8 socket Fujitsu Intel E7 v2 = 259,680 SAPS or 47,500 users
Source: SAP SD 2 Tier Benchmarks http://global.sap.com/solutions/benchmark/sd2tier.epx See end of blog for full disclosure
SAP Note 1612283 – Hardware Configuration Standards and Guidance has been updated to include recommendations for new Intel E5 v2, E7 v2 and AMD equivalent based systems. Customers are advised to ensure that new hardware is purchased with sufficient RAM as per the guidance in this SAP Note.
Most SAP Systems exhibit a ratio of Database SAPS to SAP application server SAPS of about 20% for DB and 80% for SAP application server. An 8 socket Intel server can deliver more than 200,000 SAPS, meaning one SAP on SQL Server system can deliver more than 1,000,000 SAPS. There are few if any single SAP systems in the world that are more than 1,000,000 SAPS, therefore these powerful platforms are recommended as consolidation platforms. This is explained in attachment to SAP Note 1612283. Many SQL databases and SAP systems can be consolidated onto a single powerful Windows & SQL Server infrastructure.
4. SQL Server 2012 Cumulative Update Recommended
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 1 CU 9 (the bug is fixed in CU7 & CU8 as well).
Microsoft will release a Service Pack 2 for SQL Server 2012 in the future
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 2012 R2 – Shared Virtual Hard Disk for SAP ASCS Cluster
Windows Server 2012 R2 is now Generally Available for most NetWeaver applications and includes new features for Hyper-V Virtualization.
Feedback from customers has indicated that the vHBA feature offered in Windows 2012 requires that the OEM device drivers and firmware on the HBA card be up to date.
If the device drivers and firmware are not up to date, the vHBA can hang.
The SAP Central Services Highly Available cluster requires a shared disk to hold the /SAPMNT share. Therefore a shared disk is required inside a Hyper-V Virtual Machine.
There are now three options:
- iSCSI – generally only for small customers
vHBA – suitable for customers of any size, but driver/firmware must be up to date
Shared Virtual Hard Disk – available now in Windows 2012 R2. Simple to setup and configure RECOMMENDED
Windows 2012 R2 offers this feature and it is generally recommended for customers wanting to create guest clusters on Hyper-V to protect the SAP Central Services.
SQL Server can also utilize Shared Virtual Hard Disk, however we generally recommend using SQL Server 2012 AlwaysOn for providing high availability
Aidan Finn provides a useful blog on configuring Shared Virtual Hard Disk
For more information about SAP on Hyper-V see this blog series How to Deploy SAP on Microsoft Private Cloud with Hyper-V 3.0 and SAP Note 1409608 – Virtualization on Windows
6. Important Notes for SAP BW Migration Customers
Customers migrating SAP BW systems using R3load must pay particular attention to the SAP System Copy Notes and the supplementary SAP Note 888210 – NW 7.**: System copy (supplementary note)
SAP BW and some other SAP components have special properties on some tables. These special properties are defined in DBMS specific definition files generated by SMIGR_CREATE_DDL.
Prior to exporting a SAP BW system SMIGR_CREATE_DDL must be run. There are some important updates for the program SMIGR_CREATE_DDL that must be applied in the source system before the export. SAP Note 888210 will list all required notes. BW Systems running very old support packs must be checked very carefully and possibly other notes should be applied. The following notes should be implemented:
1901705 – Long import runtime with certain tables on MSSQL
1747330 – Missing data base indexes after system copy to MSSQL
1993315 – SMIGR_CREATE_DDL: double columns in create index statements
1771177 – SQL Server 2012 column-store support for SAP BW
Customers migrating to SQL Server should review this blog: SAP OS/DB Migration to SQL Server–FAQ v5.2 April 2014
7. SQL Server AlwaysOn – Parameter AlwaysOn HealthCheckTimeout & LeaseTimeout. What are these values?
SQL Server AlwaysOn leverages Windows Server Failover Cluster (WSFC) technology to determine resource health, quorum and control the status of a SQL Server Availability Group. The WSFC resource DLL of the availability group performs a health check of the primary replica by calling the sp_server_diagnostics stored procedure on the instance of SQL Server that hosts the primary replica. sp_server_diagnostics returns results at an interval that equals 1/3 of the health-check timeout threshold for the availability group. The default health-check timeout threshold is 30 seconds, which causes sp_server_diagnostics to return at a 10-second interval. If sp_server_diagnostics is slow or is not returning information, the resource DLL will wait for the full interval of the health-check timeout threshold before determining that the primary replica is unresponsive. If the primary replica is unresponsive or if the sp_server_diagnostics returns a failure level equal to or in excess of the configured failure level, an automatic failover is initiated.
In addition to the above there is a further layer of logic to prevent another scenario:
- SQL Server Primary replica becomes extremely busy, so busy the operating system or SQL Server is saturated and cannot reply to the WSFC resource DLL within the configured period of time (default 30 seconds)
Windows Failover Cluster tries to stop SQL Server on the busy node, but is unable to communicate as the server is so busy. WSFC will assume the node has become isolated from the network and will start the failover process to and start SQL Server on another node. SQL Server is now running on another host
Eventually the condition causing the original host to be extremely busy finishes and client connections might continue to process on the first node (a very bad thing because we now have two “Primaries”)
To prevent this the Primary Node must connect to the WSFC resource DLL and obtain a lease periodically. This is controlled by the parameter LeaseTimeout. The Primary AlwaysOn Node must renew this lease otherwise the Primary will offline the database.
Therefore there are two important parameters – HealthCheckTimeout and LeaseTimeout.
Some customers have encountered problems with unexplained AlwaysOn failovers during activities such as initializing new Log Shipping or AlwaysOn Nodes via network or network backup while SQL Server is very busy. It is strongly recommended to use good quality 10G network cards, run Windows 2012 or Windows 2012 R2 and avoid using 3rd party network teaming utilities like HP Network Configuration Utility (NCU). In rare cases increasing both of these parameters may be needed.
Additional Blog: SQL Server 2012 AlwaysOn – Part 7 – Details behind an AlwaysOn Availability Group
8. FusionIO Format Settings
FusionIO or other in server SSD devices are now very common and are strongly recommended for customers that require high performance SQL Server infrastructure. The use of FusionIO and SSD is further recommended and detailed in Note 1612283 – Hardware Configuration Standards and Guidance and Infrastructure Recommendations for SAP on SQL Server: “in-memory” Performance.
FusionIO devices are usually used for holding SQL Server transaction logs and tempdb. If the transaction log and tempdb datafiles and log files are placed on FusionIO we recommend:
- FusionIO card is formatted for maximum WRITE. This will reduce the usable space significantly
FusionIO physical level format should be 4k (not 8k – it is a proprietary format size)
Make sure server BIOS is set for MAX Fan blow out – FusionIO will slow down if it becomes hot (FusionIO device will throttle IO if temperature increases)
Update FusionIO and Server BIOS to latest
Format Windows disks NTFS Allocation Unit Size 64k
9. VHDX/VHD Format Settings
Windows NTFS File System Allocation Unit Size default size is 4096 bytes. Smaller Allocation Unit Sizes (AUS) is more efficient for storing many small files. Larger AUS sizes such as 64k are more efficient for larger files.
The file system holding the VHDX files for SQL Server virtual machines running on Hyper-V may benefit from a 64 kilobyte NTFS AUS size.
The NTFS AUS of the file system inside the VHDX file must be 64 kilobytes
The AUS size can be checked with the command below:
fsutil fsinfo ntfsinfo <Drive letter>:
10. Do Not Install SAPGUI on SAP Servers
Windows Servers have the ability to run many desktop PC applications such as SAPGUI and Internet Explorer however it is strongly recommended not to install this software on SAP servers, particularly production servers.
- To improve reliability of an operating system it is recommended to install as few software packages as possible. This will not only improve reliability and performance, but will also make debugging any issues considerably simpler
SAPGUI is in practice almost impossible to remove completely. SAPGUI installation installs DLLs into Windows directory
“A server is a server, a PC is a PC”. Customers are encouraged to restrict access to production servers by implementing Server Hardening Procedure. SAP Servers should not be used as administration consoles and there should be no need to directly connect to a server. Almost all administration can be done remotely
How It Works: SQL Server AlwaysOn Lease Timeout
Flexible Failover Policy for Automatic Failover of an Availability Group (SQL Server)
Configure HealthCheckTimeout Property Settings