SAP on SQL: General Update for Customers & Partners March 2017

This post has been republished via RSS; it originally appeared at: Running SAP Applications on the Microsoft Platform articles.

First published on MSDN on Apr 04, 2017
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 and simplest operation. This blog includes updates, fixes, enhancements and best practice recommendations collated over recent months.

1. Urgent Kernel Update Required on 7.4x & 7.5x Systems

A bug in the SAP Kernel can cause this error message in the SQL Server Errorlog and can cause SQL Server to freeze.

A bug in the SAP opens a cursor to read from the database, but this cursor is never closed. Eventually all worker threads are occupied and SQL Server may freeze

It is recommended to update the SAP Kernel on any NetWeaver 7.4x or 7.5x system!

All schedulers on Node 3 appear deadlocked due to a large number of worker threads waiting on ASYNC_NETWORK_IO. Process Utilization 4%.

All schedulers on Node 3 appear deadlocked due to a large number of worker threads waiting on ASYNC_NETWORK_IO. Process Utilization 5%.

All schedulers on Node 3 appear deadlocked due to a large number of worker threads waiting on ASYNC_NETWORK_IO. Process Utilization 5%.

SAP Note 2333478 - Database cursors not closed when table buffer is full

2. Very Useful SQL Server DMV & Logging for Detecting Issues

The procedures below are useful for diagnosing problems such as those described above

1. Useful script to display blocking, long running queries and how many active requests are running at any given instant

select session_id, request_id,start_time,
status
,

command, wait_type, wait_resource, wait_time, last_wait_type, blocking_session_id

from
sys.dm_exec_requests
where session_id >49 order
by wait_time desc;

To show the SQL Server internal engine processes remove where session_id >49

2. Useful script for displaying issues related to memory consumption

-- current memory grants per query/session

select

session_id, request_time, grant_time ,

requested_memory_kb /
( 1024.0 * 1024 ) as requested_memory_gb ,

granted_memory_kb /
( 1024.0 * 1024 ) as
granted_memory_gb ,

used_memory_kb /
( 1024.0 * 1024 ) as used_memory_gb ,

st.text

from

sys.dm_exec_query_memory_grants g  cross
apply sys.dm_exec_sql_text(sql_handle) as  st

-- uncomment the where conditions as needed

-- where grant_time is not null  -- these sessions are using memory allocations

-- where grant_time is     null  -- these sessions are waiting for memory allocations

SQL Server Column Store systems with a high value for Max Degree of Parallelism can experience high memory usage

The formula for calculating the amount of memory to run a query on a Column Store table is:

Memory Grant Request in MB = ((4.2 * number of columns in the columnstore index) + 68) * Degree of Parallelism + (number of string columns * 34)

The DMV above is useful for diagnosing this issues. Remember to UNCOMMENT where grant_time is or is not null.

3. Diagnostic tool for collecting logs to attach to an OSS Message - Hangman.vbs

It is recommended to be familiar with running this utility. Hangman.vbs captures many useful parameters on a system and should always be run when sending OSS messages for performance or stability problems.

948633 - Hangman.vbs

2142889 - How to Run the Hangman Tool [VIDEO]

Two very good blogs explaining Hangman.vbs analysis

https://blogs.msdn.microsoft.com/saponsqlserver/2008/10/24/analyzing-a-hangman-log-file-part-1/

https://blogs.msdn.microsoft.com/saponsqlserver/2008/11/23/analyzing-a-hangman-log-file-part-2/

4. SQL Server 2016 Query Store


SQL Server Query Store is switched off by default but is a very useful tool for pinpointing expensive queries or queries that are sometimes fast and sometime slow depending on input parameters (such as BUKRS – Company Code).

This feature is only available in SQL 2016 and higher. A good video is available on Channel9

3. Very Useful Windows Perfmon Template for Diagnosing Issues

Attached to this blog is a XML template file that contains a recommended set of Perfmon counters to run on SQL Server Database servers and SAP Application servers. The template file has been renamed from " zsapbaseline " to "zSAPBaseline.txt" as downloading XML files is blocked by some firewalls and proxies. The template is built with a SQL Server named instance "PRD".  Do a find & replace to change this to the SQL instance name in use.  After downloading this file, rename the file to zSAPBaseline.xml and follow the following steps

1. Open perfmon.msc from the Run menu

2. Navigate to Data Collector Sets -> User Defined

3. Right click on User Defined -> New

4. Create from template from the zSAPBaseline.xml file



5. Ensure the properties below are set to avoid filling up the file system



6. Set the schedule so that the collector will automatically restart if the server is restarted



Note: for those who prefer graphing in Excel a *.blg file can be converted into a csv with relog -f csv inputfile.blg -o outputFile.csv

3. Please Use Windows 2016 for New Installation

Windows 2016 is now generally available for most SAP software including all releases of NetWeaver based applications from 7.00 through to 7.51.

We recommend all new installations use this operating system. If you are unsure about the exact release status of a SAP application post a question in this blog.

Windows 2016 is now Generally Available for SAP

4. Windows 2016 Server Hardening Recommendations

Windows 2016 does not have the Security Configuration Wizard. Many of the hardening tasks required on previous Windows releases are not required.

It is still recommended to:

1. Remove Internet Explorer and follow SAP Note 2055981 – Removing Internet Explorer & Remotely Managing Windows Servers

dism /online /Disable-Feature /FeatureName:Internet-Explorer-Optional-amd64

2. Always activate the Windows Firewall and configure ports via Group Policy Object

3. Review the security resources here: https://www.microsoft.com/en-us/cloud-platform/windows-server-security

4. Use the Security Baseline for Windows Server 2016

5. SQL Server 2016 Backup to URL Settings

To backup database to URL (Azure Blob), especially for VLDB, please use below best practices:

1. Backup to multiple URL targets. Prior to SQL 2016 only one URL target was supported.

2. Specify MAXTRANSFERSIZE = 4194304 to tell SQL server to use 4MB as max transfer size. Without this parameter, most of network IO to Azure blob is 1MB.  The test below shows that it can reduce 70% of blocks consumed.

3. Use COMPRESSION to reduce the number of block write requests to Azure Blob. The test below shows this option can help reduce 65% of backup size, and 2~4 times faster.  However please be aware of the CPU usage when using compression. If on your server CPU usage is already very high (say, >80%) please monitor CPU usage closely when using compression.

4. You can also increase BUFFERCOUNT if your target storage account has enough bandwidth, to increase backup throughput. Say, you can use 20~500, and choose the best one to meet your needs.

Please note that below issue has been reported when you  backup VLDB to azure blob:

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

10 percent processed.

20 percent processed.

30 percent processed.

Msg 3202, Level 16, State 1, Line 78

Write on " https://customer.blob.core.windows.net/dbw-db-backups/DBW_20170102111029_FULL_X22.bak " failed: 1117(The request could not be performed because of an I/O device error.)

Msg 3013, Level 16, State 1, Line 78

BACKUP DATABASE is terminating abnormally.

If you enable trace by below trace command:

DBCC TRACEON(3004, 3051, 3212,3014, 3605, 1816)

(To turn the trace off, you can run "DBCC TRACEOFF(3004, 3051, 3212,3014, 3605, 1816)")

You can then get more diagnostic information in errorlog:

Write to backup block blob device https://storageaccount.blob.core.windows.net/backup/xx.bak failed. Device has reached its limit of allowed blocks.

The above error is because that azure blob has 50,000 blocks limitation. Use above best practices can help avoid this error.

Below is the testing result for your reference. My testing database is small, it is about 2GB so your database may behavior difference if it is very large. But the above best practices apply.

Non-TDE database means the database is without TDE. TDE database means the database is with TDE encryption.

Use the below backup command for your reference:

DECLARE @Database varchar(3)
DECLARE @BackupPath varchar(100)
DECLARE @TimeStamp varchar(15)
DECLARE @Filename1 VARCHAR(MAX)
DECLARE @Full_Filename1 AS
VARCHAR (300)
DECLARE @Full_Filename2 AS
VARCHAR (300)
DECLARE @Full_Filename3 AS
VARCHAR (300)
DECLARE @Full_Filename4 AS
VARCHAR (300)
DECLARE @Full_Filename5 AS
VARCHAR (300)
DECLARE @Full_Filename6 AS
VARCHAR (300)
DECLARE @Full_Filename7 AS
VARCHAR (300)
DECLARE @Full_Filename8 AS
VARCHAR (300)


SET @Database =
'DBW'
SET @BackupPath =
'https://customer.blob.core.windows.net/'
+
Lower(@Database)
+
'-db-backups/'
SET @TimeStamp =
REPLACE(CONVERT(VARCHAR(10),
GETDATE(), 112),
'/',
'')
+
REPLACE(CONVERT(VARCHAR(10),
GETDATE(), 108)
,
':',
'')
SET @Full_Filename1 = @BackupPath + @Database +
'_'
+ @TimeStamp +
'_FULL_X1.bak'
SET @Full_Filename2 = @BackupPath + @Database +
'_'
+ @TimeStamp +
'_FULL_X2.bak'
SET @Full_Filename3 = @BackupPath + @Database +
'_'
+ @TimeStamp +
'_FULL_X3.bak'
SET @Full_Filename4 = @BackupPath + @Database +
'_'
+ @TimeStamp +
'_FULL_X4.bak'
SET @Full_Filename5 = @BackupPath + @Database +
'_'
+ @TimeStamp +
'_FULL_X5.bak'
SET @Full_Filename6 = @BackupPath + @Database +
'_'
+ @TimeStamp +
'_FULL_X6.bak'
SET @Full_Filename7 = @BackupPath + @Database +
'_'
+ @TimeStamp +
'_FULL_X7.bak'
SET @Full_Filename8 = @BackupPath + @Database +
'_'
+ @TimeStamp +
'_FULL_X8.bak'


--Backup database


BACKUP
DATABASE @Database TO
URL
= @Full_Filename1,
URL
=  @Full_Filename2,
URL
=  @Full_Filename3,
URL
=  @Full_Filename4,
URL
=  @Full_Filename5,
URL
= @Full_Filename6,
URL
=  @Full_Filename7,
URL
=  @Full_Filename8
WITH
STATS
= 10,
FORMAT, MAXTRANSFERSIZE
= 4194304, COMPRESSION
GO



SQLCAT team provide a blog on backing up VLDB here .

Thanks to Simon Su, Escalation Engineer, Microsoft CSS, Asia Pacific & Greater China for contributing this article on Backup to URL after resolving this problem for a customer.

6. Obsolete Windows Server, SQL Server & SAP Kernels – Please Upgrade

The majority of SAP on Windows and SQL Server customers have modernized their Operating System and Database releases to at least Windows 2012 R2 and SQL Server 2012. However there are some customers running very old operating systems and database releases. Here is a list:

1. Windows 2003 – now over 14 years old and out of support by both Microsoft and SAP. Please update immediately! 2135423 - Support of SAP Products on Windows Server 2003 after 14-Jul-2015

2. SQL Server 2005 – out of support by Microsoft since last year. Please update immediately!

3. Windows 2008 & Windows 2008 R2 – both of these operating system versions are approaching end of life soon and we recommend planning to upgrade to the latest available operating system

4. SQL Server 2008 & SQL Server 2008 R2 – both these database versions are near end of life and we recommend upgrading to SQL Server 2014 or SQL Server 2016. Customers running SAP BW can benefit from performance gains measured in many hundreds of percent due to improvements in modern SQL Server versions

5. SAP Kernels 7.00, 7.01, 7.21, 7.40, 7.42 are end of life. It is recommended to run either 7.22_EXT or 7.49 Kernels as at March 2017

Recommendation : As at March 2017 deploy Windows 2016 with all the latest updates & SQL Server 2016 with the latest Service Pack & Cumulative Update. SQL Server patches can be downloaded from here .

2254428 - Error while upgrading to SAP NetWeaver 7.5 based systems: OS version 6.1 out of range (too low)

Downward Compatible Kernel Documentation :

2350788 - Using kernel 7.49 instead of kernel 7.40, 7.41, 7.42 or 7.45

2133909 - SAP Kernel 722 (EXT): General Information and Usage

7. Windows Server 2016 Cloud Witness – How to Change Port Number Used

Windows 2016 includes a very useful feature called Cloud Witness.

Cloud Witness is an enhancement on the previous File Share Witness and has much better costs and functionality.

Cloud Witness is implemented inside the process rhs.exe and issues a https call on port 443 to the address <storage-account-name>.blob.core.windows.net

Some customers with high security requirements have requested a process to change the default port used by Cloud Witness

rhs.exe can be routed via a proxy server. To do this run the following command:

netsh winhttp set proxy proxy-server="https=SERVER:PORT"


To configure the settings to enable PowerShell or the UI to work, enable the .net proxy settings. The easiest way to do this is by setting the proxy settings in Control Panel -> Internet Options (Internet Explorer should already be removed)

If additional security is required the address <storage-account-name>.blob.core.windows.net can be added to the firewall whitelist

The proxy server and any other supporting infrastructure (such as firewalls) become critical to the quorum calculation if the default behavior of the cloud witness is changed (meaning a failure of the proxy server could cause the cluster to lose majority and this would mean the cluster would deliberating offline the cluster role).

8. Important Notes for SAP BW on SQL Server Customers & Column Store on SAP ERP Systems

SAP BW on SQL Server Customers can benefit from several new technologies.

1. SAP BW 7.00 to 7.50 customers – SQL Server Column Store on F Fact and E Fact tables
https://launchpad.support.sap.com/#/notes/2116639

2. SAP BW 7.40 to 7.50 customers – SQL Server Flat Cube

3. SAP BW 7.50 SPS 04 or higher can leverage FEMS pushdown

Additional performance improvements for SAP BW are documented here

It is recommended to update the report MSSCOMPRESS. This blog discusses the new version of this report

9. SQL Server on AlwaysOn Post Installation Steps – SWPM

The SAP Installation tools are not fully aware of SQL Server AlwaysOn. In general it is recommended to install SAP applications prior to establishing an AlwaysOn availability group.

After adding an AlwaysOn replica the following SAPInst option can be run to create the required users and jobs.

An example high level install procedure for installing a NetWeaver

Note: This option needs to be run on each replica while the replica is online as the Primary node. SAP applications must be shutdown prior to running this option.



Review this Blog – Script sap_synchronize_always_on can be used

https://blogs.msdn.microsoft.com/saponsqlserver/2016/02/25/always-on-synchronize-sap-login-jobs-and-objects/

SAP Note 1294762 - SCHEMA4SAP.VBS

SAP Note 683447 - SAP Tools for MS SQL Server

10. SQL Server 2016 SP1 – Transaction Log Writing to NVDIMM

It is possible to drastically speed up SQL Server transaction log write performance by using NVDIMMs.

This is a new technology released in SQL Server 2016 SP1.

Customers who have an ultra-low downtime requirement for OS/DB migrations may wish to test this new capability.

Information about this feature can be found below

https://blogs.msdn.microsoft.com/psssql/2016/04/19/sql-2016-it-just-runs-faster-multiple-log-writer-workers/

https://blogs.msdn.microsoft.com/bobsql/2016/11/08/how-it-works-it-just-runs-faster-non-volatile-memory-sql-server-tail-of-log-caching-on-nvdimm/

Recommended Notes & Links

555223 - FAQ: Microsoft SQL Server in NetWeaver based

1676665 - Setting up Microsoft SQL Server 2012

1966701 - Setting up Microsoft SQL Server 2014

2201060 - Setting up Microsoft SQL Server 2016

1294762 - SCHEMA4SAP.VBS

1744217 - MSSQL: Improving the database performance

2447884 - VMware vSphere with VMware Tools 9.10.0 up to 10.1.5: Performance Degradation on Windows

2381942 - Virtual Machines Hanging with VMware ESXi 5.5 p08 and p09

2287140 - Support of Failover Cluster Continuous Availabilty feature (CA)

2046718 - Time Synchronization on Windows

2325651 - Required Windows Patches for SAP Operations

2438832 - Network problems if firewall used between database and application servers

1911507 - Background Jobs canceled during failover of (A)SCS instance in windows failover cluster

Netsh config

https://parsiya.net/blog/2016-06-07-windows-netsh-interface-portproxy/

Content from third party websites, SAP and other sources reproduced in accordance with Fair Use criticism, comment, news reporting, teaching, scholarship, and research