SAP on SQL Installation on Mount Points
Several customers have recently asked about extending a disk online and installing SAP systems on Windows Mount Points.
Multi-SID Clusters, Windows Mount Points & SAPInst Stops Stating “Not sufficient space is available on drive”
Customers installing SAP systems onto servers with Windows Mount Points will frequently receive an error such as the one depicted below:
“Not sufficient space is available on drive. xxx MB are required, only yyy MB are free.
SOLUTION: Provide enough space”
Before discussing the resolution to this problem it is best to explain the reason why customers should try to use Mount Points on modern powerful Intel & AMD servers.
Scalability & Performance Capabilities of Modern Intel/AMD Servers
The table below shows the SAPS values for a selection of servers from Intel & AMD
Server Model |
Processor Details |
SAPS |
SAP SD 2 Tier Users |
SAP Benchmark Number |
HP DL585 G7 |
AMD Six-Core 2.5GHz 6180 SE |
51,580 |
4,496 |
|
HP DL485c G7 |
AMD Six-Core 2.3GHz 6176 |
24,530 |
9,450 |
|
HP ProLiant BL620c |
Intel Xeon Processor E7-2870, 2.40 GHz |
36,600 |
6,703 |
|
HP ProLiant BL680c G7 |
Intel Xeon Processor E7-4870, 2.40 GHz |
73,970 |
13,550 |
|
HP DL980 G7 |
Intel Xeon Processor E7-4870, 2.40 GHz |
137,470 |
25,160 |
Reviewing these benchmarks most customers observe that modern hardware far exceeds the performance requirements for each single SAP system.
For example a small SAP ECC 6.0 customer might require 30,000 SAPS. Experience has shown that on almost all OLTP customer systems, even those with heavy customer ABAP programs the workload distribution is approximately 30% for Database and 70% for Application server. Even SAP BW when correctly configured should be ~30/70
A majority of SAP customers run at least some of their Windows SAP systems on Windows Cluster configurations.
Also a majority of customers run in three tier configuration (meaning a separate DB server and several dedicated application servers).
In this particular case the customers requirement for 30,000 SAPS would be split approximately 10,000 SAPS for Database layer (~30%) and 20,000 SAPS for the Application Server layer (~70%)
So in this scenario a customer might need to buy 4 x 2 processor commodity servers to run one small 30,000 SAPS ECC 6.0 system. 2 servers would be needed to cluster the Database and 2 application servers. The total SAPS of the hardware is over 100,000 SAPS and yet the requirement for the Database is only 10,000 SAPS. Often the Database cluster is Active/Passive - clearly a very poor solution as the capacity is much greater than 100,000 SAPS yet the actual requirement is only 30,000 SAPS
How to Reduce Server Sprawl & Improve Performance
Many customers questions are faced with the following situation:
1. Intel/AMD hardware is far more powerful than the SAPS requirement for a single system such as ECC, BW or CRM
2. High Availability (MSCS) and 3 Tier architectures mean that the number of servers and SAPS values are far higher than needed as each system (ECC, BW, CRM) requires its own dedicated hardware, own two node cluster and own application servers
3. The average utilization on each server is very low
SAP & Microsoft have supported a solution to this problem for many years. SAP fully support Multi-SID clustering. In the Asia Pacific region at least > 85% of all new SAP installations are Multi-SID and this has become the default deployment model for most customers and partners.
Benefits: It is Recommended to Evaluate Multi-SID Clusters
Multi-SID clustering allows customers to create a 2 – 16 node MSCS cluster (2-5 nodes is common) and consolidate all SQL Databases and SAP Single Point of Failure (typically 2-6 is common) to a single cluster.
The benefit of Multi-SID clustering is
1. Reduction in the total number of servers required (particularly reducing the number of the relatively more expensive scale up 4 processor / 8 processor DB servers)
2. Increase the average utilization on each server
3. Improve availability as the cluster can be 3 or more nodes (up to a max. of 16)
4. Allow a customer to consolidate a large SAP landscape onto one manageable infrastructure. Example: ECC 6.0, BW, CRM, PI, EP, SCM, Solution Manager and Business Objects can (and there are numerous successfully customers running without incident) to a single 3-4 node SQL cluster
5. SAP systems that are busier than others can be isolated to run on a dedicated cluster node
6. SAP systems that are not busy (such as Solution Manager, PI, EP, SRM) can be consolidated and their SQL Server databases moved via Cluster Administrator onto a single cluster node
Another benefit of Multi-SID clusters is that they *DO NOT* link, aggregate or combine SAP or SQL in terms of version management or operations. Each SQL Server on a Multi-SID cluster is a Named Instance
Each SQL Server Named Instance has its own completely independent:
1. Virtual IP Address
2. Virtual Hostname
3. Disk resources
4. SQL Server Executables
5. SQL Server Configuration Settings
6. SQL Server Data Cache and performance counters
In summary each SQL Server cluster is completely isolated from each other. It is technically possible to run SQL 2005, SQL 2008, SQL 2008 R2 and SQL 11 on the same cluster. Similarly it would be possible to run 10 named instances of SQL Server on a “n” node cluster each with a different SQL Service Pack or CU. Each SQL Server can be started, stopped, reconfigured, upgraded* or even uninstalled independently without interrupting the other SQL Servers on the same cluster or same node.
Multi-SID clustering will be the topic of another blog about server consolidation. In addition Multi-SID application servers will be discussed.
Why Do Multi-SID Clusters Often Require Windows Mount Points?
Multi-SID clusters are almost always installed in combination with Windows Mount Points for two reasons:
1. In order to deliver adequate IO performance in all cases (regardless of SAN vendor) multiple LUNs are required for each Database
2. Multi-SID clusters run out of drive letters unless mount points are used
In order to Parallelize IO it is strictly mandatory in all cases, regardless of SAN vendor to create multiple LUNs for SQL Server Databases. It is completely incorrect and wrong to assume that because the IO is being distributed onto a single large array of disks on the SAN that a single large LUN/disk will deliver the same performance. If you SAN vendor or H/W partner is recommending a single large LUN for storing SQL Data Files for a SAP system please post a comment in this blog.
A single large LUN leads to severe IO queuing inside the Windows OS before the IO ever leaves the HBA card in the server. SQL Server executes many asynchronous IO requests and benefits from parallel IO paths. A single LUN creates a “one lane highway” between server and SAN.
How Many Windows Mount Points Do I Need?
It is recommended to have only 2 SQL Server Data Files per LUN. Therefore the number LUNs is determined by the number of SQL Data Files, which is in turn determined by the size of the system.
Previously some Microsoft documentation recommended a 1:1 ratio between CPU cores and datafiles. This recommendation is now obsolete due to the fact modern CPU such as the AMD Magny cours has 12 cores. This would lead to far too many Data Files. The chances of GAM page contention and other issues related to too few datafiles becomes very unlikely after about 16 datafiles.
With SAP application we must use multiple data files each of the same size
SQL Server allocates space for new data proportional to the free space in each of the files
It is strongly recommended to set Trace Flag 1117 as per SAP Note 1238993 - Proportional File Auto-Growth with SQL Server 2008
Our recommendation
Small sized systems: 4 data files.
Systems usually run on dedicated database servers which have between 4 and
8 CPU cores.
Medium sized systems: 8 or 16 data files.
Systems usually run on dedicated database servers which have between 8 and
16 CPU cores.
Large sized systems: A minimum of 16 data files and maximum of 32.
Systems which today run on hardware between 16 and 32 CPU cores or up to
64 threads.
Xtra large sized systems: 32 Datafiles for systems with 32 to 256 cores (Contact Microsoft for very large >15TB databases)
In all cases only one Transaction Log file is needed. This should be sized at least 50GB for a medium sized system. Do not configure more than 32 datafiles for SAP systems.
How to Configure Mount Points & How to Resolve the Error in SAPInst?
Care must be taken when configuring Mount Points, specifically the dependencies must be verified.
https://support.microsoft.com/kb/280297
https://support.microsoft.com/kb/819546
After installing SQL Server in a cluster make sure to run the Dependency Viewer. The Dependencies should look like this (click on this picture for higher resolution copy.
How to resolve the error in SAPInst?
The error in SAPInst is cause by the SAP Installer checking the freespace on the Mount Point Root Disk and not on the individual Mounted Disks.
There are two easy solutions to this problem:
1. Create the Database manually before starting SAPInst using the script below – SAPInst will bypass this screen and install
2. Set the size of the Database to something very small (such as 1GB) and the installer will continue. SQL Server Data Files will grow automatically during the installation or you can manually increase them (ensure Note 1238993 is implemented).
-- Sample script to create SAP Database
-- Most Medium Size SAP systems need 8 or 16 datafiles, 16 is generally quite common
-- Note: only 2 datafiles per LUN/disk and separate disk for Transaction Log
CREATE DATABASE [SID] ON PRIMARY
( NAME = N'SIDDATA1', FILENAME = N'P:\SIDDATA1\SIDDATA1.mdf' , SIZE = 100GB , FILEGROWTH = 1GB ),
( NAME = N'SIDDATA2', FILENAME = N'P:\SIDDATA2\SIDDATA2.ndf' , SIZE = 100GB , FILEGROWTH = 1GB ),
( NAME = N'SIDDATA3', FILENAME = N'P:\SIDDATA3\SIDDATA3.ndf' , SIZE = 100GB , FILEGROWTH = 1GB ),
( NAME = N'SIDDATA4', FILENAME = N'P:\SIDDATA4\SIDDATA4.ndf' , SIZE = 100GB , FILEGROWTH = 1GB ),
( NAME = N'SIDDATA5', FILENAME = N'P:\SIDDATA5\SIDDATA5.ndf' , SIZE = 100GB , FILEGROWTH = 1GB ),
( NAME = N'SIDDATA6', FILENAME = N'P:\SIDDATA6\SIDDATA6.ndf' , SIZE = 100GB , FILEGROWTH = 1GB ),
( NAME = N'SIDDATA7', FILENAME = N'P:\SIDDATA7\SIDDATA7.ndf' , SIZE = 100GB , FILEGROWTH = 1GB ),
( NAME = N'SIDDATA8', FILENAME = N'P:\SIDDATA8\SIDDATA8.ndf' , SIZE = 100GB , FILEGROWTH = 1GB )
LOG ON
( NAME = N'SIDLOG1', FILENAME = N'P:\SIDLOG1\SIDLOG1.ldf' , SIZE = 80GB , FILEGROWTH = 5GB)
In addition to problem with SAPInst, SAP Go Live Check and SAP EarlyWatch reports will mistakenly issue warnings that the Transaction Log and Data Files are on the same “disk”. In fact the Transaction Log and Data Files are *not* on the same disk, they only share a drive letter.
Conclusion on Mount Points – Use them
Mount Points are a prerequisite for beginning the necessary journey towards a consolidated landscape.
It is highly recommended to start making a strategy for consolidating SAP on SQL systems.
Intel/AMD based servers will continue to become more and more powerful and the benefits from consolidation even greater
It is highly recommended that customers terminate the old “Active/Passive 2 node cluster for each and every SAP component” as soon as possible. This approach is out-dated, underperforming, expensive and leads to server sprawl.
* There is one rare circumstance where a Service Pack upgrade may require a restart, but this is easily avoided with rolling upgrades.
https://www.netapp.com/us/solutions/applications/microsoft-sql/
https://www.redbooks.ibm.com/abstracts/redp4245.html?Open
https://service.sap.com/sap/support/notes/1082356
The SAP Sales and Distribution (SD) Standard Application Benchmark performed on March 21, 2011, by HP in Houston, TX, USA, was certified on April 05, 2011, with the following data: Number of SAP SD benchmark users: 6,703 Average dialog response time: 0.99 seconds Throughput: Fully processed order line items per hour: 732,000 Dialog steps per hour: 2,196,000 SAPS: 36,600 Average database request time (dialog/update): 0.011 sec / 0.014 sec CPU utilization of central server: 99% Operating system, central server: Windows Server 2008 R2 Enterprise Edition RDBMS: SQL Server 2008 SAP Business Suite software: SAP enhancement package 4 for SAP ERP 6.0 Configuration: Central server: HP ProLiant BL620c G7, 2 processors / 20 cores / 40 threads, Intel Xeon Processor E7-2870, 2.40 GHz, 64 KB L1 cache and 256 KB L2 cache per core, 30 MB L3 cache per processor, 256 GB main memory
The SAP Sales and Distribution (SD) Standard Application Benchmark performed on March 23, 2011, by HP in Houston, TX, USA, was certified on April 05, 2011, with the following data: Number of SAP SD benchmark users: 13,550 Average dialog response time: 0.99 seconds Throughput: Fully processed order line items per hour: 1,479,330 Dialog steps per hour: 4,438,000 SAPS: 73,970 Average database request time (dialog/update): 0.017 sec / 0.020 sec CPU utilization of central server: 99% Operating system, central server: Windows Server 2008 R2 Enterprise Edition RDBMS: SQL Server 2008 SAP Business Suite software: SAP enhancement package 4 for SAP ERP 6.0 Configuration: Central server: HP ProLiant BL680c G7, 4 processors / 40 cores / 80 threads, Intel Xeon Processor E7-4870, 2.40 GHz, 64 KB L1 cache and 256 KB L2 cache per core, 30 MB L3 cache per processor, 256 GB main memory
The SAP Sales and Distribution (SD) Standard Application Benchmark performed on May 24, 2011, by HP in Houston, TX, USA, was certified on June 03, 2011, with the following data: Number of SAP SD benchmark users: 25,160 Average dialog response time: 0.98 seconds Throughput: Fully processed order line items per hour: 2,749,330 Dialog steps per hour: 8,248,000 SAPS: 137,470 Average database request time (dialog/update): 0.022 sec / 0.021 sec CPU utilization of central server: 99% Operating system, central server: Windows Server 2008 R2 Datacenter Edition RDBMS: SQL Server 2008 SAP Business Suite software: SAP enhancement package 4 for SAP ERP 6.0 Configuration: Central server: HP ProLiant DL980 G7, 8 processors / 80 cores / 160 threads, Intel Xeon Processor E7-4870, 2.40 GHz, 64 KB L1 cache and 256 KB L2 cache per core, 30 MB L3 cache per processor, 512 GB main memory