Partager via

Frequently Asked Questions we heard on the SAP on SQL 2008 Training course this year

  • Article

Frequently Asked Questions from SAP on SQL Training

During the last 12 months I have taught the official Microsoft SAP on SQL 2008 in many locations around Asia.  On each course there were a lot of excellent questions from the customers and partners attending the course. This blog will cover some of the more important questions that were repeatedly asked during the courses. 

 1. How do I compress a SAP System to save space?

SQL 2008 has powerful table compression technology that dramatically reduces the storage requirements for SAP systems.  SAP support compression on SAP_BASIS release 7.0 Support Pack 15 and higher. 

Many SAP customers would like to compress the database to save space and speed up database activities such as backups, check database and system copies.

There are three common methods for compressing a SAP system:

  1. This blog contains a script that can be run to compress all tables in a database
  2. SAP provide a SAPGUI screen that can be used to compress a table - run RSDD_MSSQL_CUBEANALYZE in SE38.  It is recommended to implement OSS Note 991014 with SNOTE to update this program
  3. Using the T-SQL statements to compress on the top 30-50 tables one table at a time online

 So far most customers have used method #3 to compress their systems.  This is largely due to the fact about 85-95% of the database size is contained in the top 50 tables.  Compression of SAP transparent tables is a fully online operation. 

The T-SQL used is similar to this:

ALTER TABLE [sid].[ <table> ] REBUILD WITH(DATA_COMPRESSION = PAGE ,ONLINE = ON)

 In all cases ensure that this procedure is applied in Development, Test and then Production Environments.  Also please read this whitepaper on compression and ensure there is sufficient Tempdb and Transaction Log space.  If you have any other questions please post them in this blog.

 2. Is it advisable to use DBCC SHRINK on SAP systems?

After compressing a SAP database many customers find they have a lot of freespace inside the datafiles.  However DBCC SHRINKFILE has a very undesirable side effect.  SQL Server Page size is fixed at 8KB (unlike Oracle and DB2 where the Page or "Block" size can be changed).  A group of 8 x 8KB Pages is arranged into one SQL Extent that is 64K in size.

In the example below multiple pages have been written into three Extents.  Each letter represents one 8K Page

 -------- -------- --------      

|ABC EF H| |I KLMN P| |QRSTUV X|     

All pages (and therefore all three extents) belong to the same table.

DBCC Shrink will start from the last page in the last extent and work backwards from right to left.

Therefore the third extent will be copied to some freespace and the new extent will look like:

        XVUTSRQ_      with one last empty page in the new extent (the _ means empty)

 The original pages QRSTUV would have been both physically ordered and logically ordered.  After the DBCC Shrink the physical and the logical order of the pages is reversed.

Shrink then will start copying pages from the tail of the extent again. The result would look like:

        PNMLKI__       with the last two pages empty

This can lead to severe performance problems some of which are documented here.  Unfortunately there is no easy way to determine if DBCC SHRINKFILE has been run on a system. 

 Solution: running the T-SQL command ALTER INDEX/TABLE REBUILD will correct this problem.  Therefore if you wish to reclaim the space after compression please follow this sequence (1) Compress DB (2) Shrink files (3) Rebuild the primary index

 3. Can I still use 32 bit versions of Windows or SQL Server?

No, it is no longer allowed to use 32 bit versions of Windows or SQL for modern SAP releases and it is strongly advised to upgrade all 32 bit versions of Windows to 64 bit immediately.  Any hardware sold in the large 5 years is 64 bit capable.

It is not supported to upgrade from Windows 32 bit to Windows 64 bit "in place".  Neither is it supported to upgrade from Windows 2003 to Windows 2008/2008 R2 "in place". 

In both these cases a "Homogeneous System Copy" must be performed.  This is a very simple procedure for SQL Server customers.  The System Copy guide is available on SAP Service Marketplace and we recommend reviewing these OSS Notes:

  • Note 960769 - Windows Migration from 32-bit to 64-bit (x86_64)
  • Note 996600 - 32 Bit platforms not recommended for productive NW2004s apps
  • Note 1177282 - Windows End of Support for SAP Releases
  • Note 1280759 - SAP recommends only 64-Bit Windows for SAP Appl. Servers
  • Note 151603 - Copying an SQL Server database
  • Note 551915 - R3 won't start after database restore or database copy
  • Note 914020 - Upgrading SAP systems <=4.6 on x86_64 on MS SQL Server

 

Note: although there is no native x64 SAP Kernel for 4.6C/4.6D it is still strongly recommended to run SAP 4.6C on Windows x64.  This is because the SAP kernel has been compiled with /LARGEADDRESSAWARE and will perform substantially better on Windows x64 - far better and more stable than using /3GB and other 32bit options.

  4. Where do I find the latest SAP Benchmarks?

SAP on Windows/SQL has shown excellent improvements in performance and scalability over the last 5 years.

SAP publish the SD 2 Tier benchmark results. 

As of June 2010 the follow general trend can be observed for commodity industry standard servers 

Number of CPU

Approximate Number of SD Benchmark Users possible

Approximate Number of SAPS

2 CPU

5,000

27,000

4 CPU

10,000

57,000

8 CPU

18,000

100,000

 5. What is NUMA & what does this mean for my SAP system?

NUMA is "Non-Uniform Memory Access" and simply explained means a CPU has local memory directly attached and remote memory attached via a high speed memory bus.  Typically the access times for local memory are very fast but the access time for remotely attached memory is somewhat slower.  Windows and SQL Server are fully NUMA aware and allocate memory efficiently in order to minimize remote memory calls.

The SAP Kernel however is completely unaware of NUMA technologies.  This has some severe consequences that we have seen in the customer base so far.  Because the SAP kernel does not know if a memory address is local or remote, the chance of a local memory access on a 2CPU server is 50%.  This is because each CPU controls 50% of the installed memory.  The statistics get much worse for 4CPU = 25% and 8CPU = 12.5%.  Therefore it is our recommendation not to run SAP application servers on large scale up servers such as a DL 785.  It is recommended to deploy multiple 2CPU application servers rather than attempting to create a scale up SAP application server.

Note: All new Intel Nehalem systems and all AMD systems are now NUMA architecture (previous Intel Front Side Bus was not NUMA, at least for systems with 4 or less CPU sockets)

For a detailed explanation of the way the Nehalem EX memory architecture is implemented please review the IBM Redbook for the new IBM x5 servers.   Section 3.4 on page 30 illustrates the concept very well. 

 6. How large does my Windows Pagefile need to be?

The SAP recommendation for Windows Pagefile sizing has changed many times over recent years.  The recommendations of "3 or 4 times physical memory" date back from 32bit days.  This no longer applies today.  As a minimum a 64 bit ABAP application server consumes around 20GB of Pagefile.  In general it is recommended to ensure the Pagefile is large enough to enable a full memory dump in case a system fails or you need to create a memory dump.  Although it is technically possible to redirect the crashdump to another disk on Win2008, it is easier to configure this on the boot disk (usually C: Drive).  Therefore it is recommended to create a large boot disk with sufficient space to catch a full memory dump.  If possible set one large contiguous pagefile the same size as physical memory on the boot disk, usually C: drive.

 7. How many datafiles do I need on a new Multi-core CPU?

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

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 30GB for a medium sized system.  Do not configure more than 32 datafiles.

 8. What SQL Server traceflags do you recommend for SAP?

Unless requested to do so by SAP or Microsoft support we strongly encourage customers not to run SQL Server with traceflags.  The one exception to this is traceflag 1117 which controls how SQL Server autogrows datafiles.  This is explained in OSS Note 1238993 - Proportional File Auto-Growth with SQL Server 2008 (this feature is only available in SQL 2008 or higher and is highly recommended for all customers).

The traceflags that control SQL Server locking escalation should only be used in rare cases and it is generally advised to try to pinpoint the root cause of the locking behavior. 

Note: To capture deadlock information it is preferable to use the script attached to OSS Note 32129 - Deadlock analysis for the SQL server (script is called sap_deadlock_trace.sql.txt)

 9. How does Windows Security & Patching compare to UNIX?

On modern versions of Windows Server there should be little or no difference between the patching requirements for Windows vs. UNIX.  Microsoft has greatly improved the design of the Windows operating system and enhanced the default installation of Windows reducing the attack surface area.

There are some basic security guidelines that we recommend SAP customers follow:

  1. If at all possible run SAP on Windows 2008/Windows 2008 R2
  2. Place SAP Servers in their own VLAN and own subnet. Create a ACL/rule to allow inbound connections on SAP ports and block most common Windows ports on the network switch
  3. Do not deploy any other Windows Roles (SAP Installer will install File Sharing)
  4. Do not deploy the "Desktop Experience" Role - this will deploy Windows Media Player and other applications that are not required on SAP Servers
  5. Run the Windows Security Configuration Wizard after installing SAP and disable unused services
  6. Ensure the Windows Firewall is configured and running - this can be configured via an Active Directory Policy
  7. Do not use SAP servers as file servers, printer servers or as FTP hosts - for file transfers for interfaces configure a host with a share outside of the SAP VLAN and create a ABAP Logical File to this path using transaction FILE
  8. Do not install Winzip, Adobe Reader, Office or any other software that is not essential for the operation of the SAP system. The less software that is installed the lower the maintenance and the smaller the attack surface area.  NEVER UNDER ANY CIRCUMSTANCES install SAPGUI onto a SAP Server.  SAPGUI distributes DLLs including LIBRFC.DLL into the Windows system directory.  

 Windows 2008 R2 has some features such as the complete removal of Internet Explorer that further reduces the need for security patching. 

 The Microsoft Technet Security Center can be used to search and display patches.  This website shows that for Windows 2008 R2 there are 21 security bulletins. 

  

 However closer examination shows that very few of these security patches would be relevant for SAP systems.  For example the security patches below should be carefully evaluated as to whether they are relevant for a SAP Server.   Applications such as Outlook Express, Windows Mail, IIS, Internet Explorer, SMTP Service and XML Services should not be installed on SAP servers.  Therefore carefully review the security bulletin to see if it applies to your particular environment. Note: this approach applies to SAP systems only.

Note: many SAP customers have followed this deployment model and protect their SAP infrastructure with a combination of Security Configuration Wizard templates transformed into an Active Directory Policy, a separate dedicated VLAN for SAP and they selectively apply security patches after assessing the impact of each patch.

Comments

  • Anonymous
    July 08, 2010
    Hi Cameron, First of all, excellent blog!  Very informative! Just to clarify your comment:- "Neither is it supported to upgrade from Windows 2003 to Windows 2008/2008 R2 "in place".  " Can you please point me towards the oss note and/or documentation that back this statement up? If you could , please send your response back to mdalrymple@seek.com.au Many thanks, Matt SAP Basis - Seek

  • Anonymous
    July 09, 2010
    Questions 8 & 9 contain this information. www.sdn.sap.com/.../windows

  • Anonymous
    January 12, 2011
    Do you know about installation of SAP running SQL2008 for retail (350 stores, 300 users placing purchase orders, 2 distribution centers with 200 scanners picking on a 24x7 environment), currently we have a similar system but running under Oracle and not sure if going to SQL2008 will be as stable as Oracle env

  • Anonymous
    January 22, 2011
    Hello Adalberto, Sure - we have many customers running SAP IS-RETAIL solutions on Win/SQL.  I am in close contact with several larger customers in the Asia region that run SAP IS-RETAIL on Win/SQL with >1,000 users, several hundred stores, each store with several POS devices. For sizing RETAIL solutions I recommend to focus on the number of SKU, POS devices, replenishments and other metrics.  Also check www.sap.com/.../sd2tier.epx There are no performance limits on Win/SQL relative to any other OS/DB combination. Thanks

  • Anonymous
    August 04, 2014
    The comment has been removed

  • Anonymous
    September 17, 2014
    I wish I can get furthered info from you.I am very glad to say thanks for this info. thank you very much sir sapbodsonlinetraining.in

  • Anonymous
    December 15, 2014
    Thank you sir,It is nice and more informative. We are providing online training on & for more info <a href="http://sapsdolt.blogspot.com"> sap sd online training </a> it gives the career plan. It is in simple language and understandable to every freshers.

  • Anonymous
    December 29, 2014
    Thank you sir,I recently came across your blog and have been reading along. nice explanation.We are providing sap wm online training  . It is more effective and interest for new learners. I thought I would leave my first comment. I feel great after reading this information.