SAP on SQL Installation on Mount Points

SAP on SQL Installation on Mount Points

  • Comments 1

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”

 clip_image003

 

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

2011006

HP DL485c G7

AMD Six-Core 2.3GHz 6176

24,530

9,450

2011007

HP ProLiant BL620c

Intel Xeon Processor E7-2870, 2.40 GHz

36,600

6,703

2011014

HP ProLiant BL680c G7

Intel Xeon Processor E7-4870, 2.40 GHz

73,970

13,550

2011016

HP DL980 G7

Intel Xeon Processor E7-4870, 2.40 GHz

137,470

25,160

2011021

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.

 

clip_image004

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.

 clip_image001

 

 

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.

http://support.microsoft.com/kb/280297

http://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.

 

Dependancy

 

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.

http://www.netapp.com/us/solutions/applications/microsoft-sql/

http://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

 

Leave a Comment
  • Please add 4 and 4 and type the answer here:
  • Post
  • Please note that cluster disk containing the shared SQL Server components such as the SQL Server Error Log directory and other system directories should be a direct dependency of the SQL Server Service.  

    If not the error below may occur during an upgrade:

    08/03/2011 02:36:24,spid9s,Unknown,Cannot use file 'I:\BPADATA1\MSSQL10_50.BPA\MSSQL\DATA\temp_MS_AgentSigningCertificate_database.mdf' for clustered server. Only formatted files on which the cluster resource of the server has a dependency can be used. Either the disk resource containing the file is not present in the cluster group or the cluster resource of the Sql Server does not have a dependency on it.

    ***********************************ERRLOG*************************************************************************************

    8/03/2011 02:36:24,spid9s,Unknown,SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.

    08/03/2011 02:36:24,spid9s,Unknown,Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup<c/> repair it<c/> or rebuild it. For more information about how to rebuild the master database<c/> see SQL Server Books Online.

    08/03/2011 02:36:24,spid9s,Unknown,Error: 3417<c/> Severity: 21<c/> State: 3.

    08/03/2011 02:36:24,spid9s,Unknown,Script level upgrade for database 'master' failed because upgrade step 'sqlagent100_msdb_upgrade.sql' encountered error 598<c/> state 1<c/> severity 25. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the 'master' database<c/> it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors<c/> take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.

    08/03/2011 02:36:24,spid9s,Unknown,Error: 912<c/> Severity: 21<c/> State: 2.

    08/03/2011 02:36:24,spid9s,Unknown,CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

    08/03/2011 02:36:24,spid9s,Unknown,Error: 1802<c/> Severity: 16<c/> State: 1.

    08/03/2011 02:36:24,spid9s,Unknown,Cannot use file 'I:\BPADATA1\MSSQL10_50.BPA\MSSQL\DATA\temp_MS_AgentSigningCertificate_database.mdf' for clustered server. Only formatted files on which the cluster resource of the server has a dependency can be used. Either the disk resource containing the file is not present in the cluster group or the cluster resource of the Sql Server does not have a dependency on it.

    08/03/2011 02:36:24,spid9s,Unknown,Error: 5184<c/> Severity: 16<c/> State: 2.

    08/03/2011 02:36:24,spid9s,Unknown,DBCC execution completed. If DBCC printed error messages<c/> contact your system administrator.

    08/03/2011 02:36:24,spid9s,Unknown,DBCC TRACEOFF 4606<c/> server process ID (SPID) 9. This is an informational message only; no user action is required.

    08/03/2011 02:36:24,spid9s,Unknown,DBCC execution completed. If DBCC printed error messages<c/> contact your system administrator.

    08/03/2011 02:36:24,spid9s,Unknown,DBCC TRACEON 4606<c/> server process ID (SPID) 9. This is an informational message only; no user action is required.

    08/03/2011 02:36:24,spid9s,Unknown,Dropping existing Agent certificate ...

Page 1 of 1 (1 items)