This blog provides a brief summary of experiences and lessons learned following a recent customer migration from Oracle to SAP ASE 15.7 and is aimed at technical consultants involved with migration to SAP ASE and senior managers interested in impacts or benefits of the move. Although the main driver for the migration was not the switch to ASE, the move from Oracle was commercially attractive.
Lesson #1 – Business Suite Compatibility
SAP ASE is certified for use with SAP Business Suite or as a standalone database platform. There are always two delivery channels for the SAP ASE software binaries – one for Business Suite and one for standalone. Always check that your use case is supported by validating within the SAP notes and ensure that the relevant software version is downloaded. If you’re planning to use SAP Replication Server for your disaster recovery setup, check the support matrix carefully in note 1891560.
- 2087322 – SYB: Where to find information about SAP applications on SAP ASE
- 1891560 – Disaster Recovery Setup with SAP Replication Server
Lesson #2 – Download the Latest
Download the latest ASE SP from the SAP Support Launchpad that is certified and applicable to your scenario. The version of SAP ASE will make a big difference when considering the project implementation. For example, at the time of writing this blog (September 2016), there is no Business Suite support for SAP ASE 16 with SAP SRS 15.7. If you’re planning to utilize SRS for your disaster recovery setup, then you’re restricted to ASE 15.7 with the latest SP137.
Consult with your SAP Technical Quality Manager (TQM) to ensure you plan to be on the optimum version and patch level of SAP ASE for your project timelines and software components being deployed. SAP ASE patches are released as frequently as every 3 months which can potentially contain fixes for a possible data loss or data corruption scenario that you would want to be included in your deployment.
Lesson #3 – Stay Current
Regularly check this useful WIKI for the bug fix CR list – Targeted ASE 15.x Release Schedule and CR list Information. Revisions to SAP ASE software are performed rapidly so plan to patch at least every 3 to 6 months at minimum until such time as the product stabilizes. Failing to remain current may create issues with other tightly associated software areas (such as SAP Replication Server mentioned previously). However, revisions can be delayed dramatically (by months) only to be superseded almost immediately by a later revision, which can be frustrating and disrupt your planning. Be wary that “Hot fixes” exist, whereby a current revision receives an additional increment in between the previous and the latest.
- 2075653 – Targeted ASE 16.0 Release Schedule and CR list Information -SAP ASE
- 2077419 – Targeted ASE 15.x Release Schedule and CR list Information
Lesson #4 – Ensure Correct Parameterization
Configuration of the SAP ASE database parameters is performed against one core SAP note – 1749935 for ASE 15.7 and 1581695 for ASE 16.0. Unfortunately, these notes have a slightly cumbersome layout and can be complex to digest manually so we’d recommend either extracting into your own documentation and even scripting them for applying to your ASE databases.
Changing the parameters to those recommended by SAP is highly recommended because the “out-of-the-box” configuration is never optimal or potentially even unstable, especially the memory-related ones. Please resist the temptation to just blindly apply the settings as per the notes.
Some parameters listed in the SAP notes will be specific to SAP BW or SAP ERP with the “old” OLAP versus OLTP tuning need still being relevant.
When parameterizing your ASE databases, remember SAP DBA Cockpit is your “friend” and allows easy validation of the parameters depending on your NetWeaver release.
- 1539124 – SYB: Database Configuration for SAP applications on SAP ASE
- 1581695 – SYB: Configuration Guide for SAP ASE 16.0
- 1619967 – SYB: DBA Cockpit Correction Collection SAP Basis 7.31
- 1749935 – SYB: Configuration Guide for SAP ASE 15.7
Lesson #5 – Confirm Statements with Your TQM
You may encounter erroneous or conflicting support statements within SAP notes that can cause confusion and sometimes the SAP ASE standalone community forget that SAP ASE is also a supporting platform for SAP Business Suite. If you see an SAP note stating you’re not supported if you do “X” or you have “Y” installed, query it with your TQM as it may not be relevant to your use case.
Lesson #6 – Perform Rigorous Testing
Patching SAP ASE is reasonably straight forward and well-integrated with the SAP Host Control agent so expend the time saved by testing thoroughly. Include functional, technical and operational testing during test cycles and incorporate the system copy process as this will need to be revised with the change of database. Any issues detected during testing may take time to resolve, with workarounds possible, but the issue may be fixed in a later SAP ASE revision so keep an eye on the important notes.
Last but not least, performance testing is a must with the change in database as this will highlight any SQL statements that may be adversely affected by the database switch, parameterization issues, locking issues and transaction log sizing issues.
Lesson #7 – Implement Good Housekeeping
The log files for the database, jobserver and backupserver do not rotate until the SAP ASE instance is restarted so keep these files tidy and compressed with your own housekeeping scripts. On Linux, make use of the standard log rotation functionality.
Recommendations exist for retaining certain files such as the last config file, the dumphist file and export of sysdevices table, on a separate file system.
- 1585981 – SYB: Ensuring Recoverability for SAP ASE
- 1618817 – SYB: How to restore an SAP ASE database server (UNIX)
Lesson #8 – Tune the Backup
Out-of-the-box the performance of backups and restores is adequate but will start to become unacceptable for larger databases. This will of course be different for each SAP customer depending on your tolerance and SLA. For example, a 1.3TB database with 1 stripe (parallel process) can take in excess of 4 hours to a Data Domain appliance such as EMC Avamar because its API doesn’t support multiple stripes at present.
However, if your third-party backup tool does support multiple stripes or you’re planning to backup to disk before dumping the data to your third-party backup tool, then reductions by as much as 30% can be achieved. So, spend some time to performance tune your backup by adjusting just one SAP ASE parameter. Of course, make sure that you test the restore capability afterwards.
Allocate adequate disk space for emergency backups (especially transaction dumps) to disk if you’re planning to backup to a third-party tool, in case there are unforeseen problems or outages with your third-party tool.
Lesson #9 – Re-Visit NetWeaver Parameters
When moving from Oracle to ASE, we recommend you re-visit the “rsdb” prefixed profile parameters that influence the SELECT….FOR ALL ENTRIES ABAP statement. Failure to set these parameters correctly will lead to performance problems during SELECT with IN lists.
During a database platform migration ensure that you re-visit the relevance of any other database specific parameters, especially those concerned with DBSL level interactions. Search for notes in component BC-DB-SYB and order in date descending then filter for relevancy against your NW release and SP level
Lesson #10 – Re-Visit Any SQL Hints
Failure to re-visit any hints you had previously specified for your source database may lead to unexpected performance problems. Your source database platform hints will be ineffective on the target database and may even be detrimental to the performance on target database.
This should be evident during your test cycles but consider validating whether hints for actually required by ASE because the new optimizer may automatically cope with the hinted SQL.
Ensure that you know how to “EXPLAIN PLAN” as you’ll need it and budget project time for performance tuning of SQL especially in custom code.
Other Useful Notes
There are literally hundreds of SAP notes related to ASE because the product is recently adopted by SAP and adapted for the Business Suite. We’ve listed these additional notes here as they were very relevant in a recent customer ASE migration project.
- 2162183 – SYB: Frequently Asked Questions for SAP ASE
- good starting point for other notes
- 1946048 – Too many UPDATES to Table SWNCMONI
- without this note, high transaction log volumes may be experienced
- 2276031 – Deactivation of BAdi ICF_STAT_COLLECTOR
- without this note, high transaction log volumes may also be experienced
If you’e considering a migration to SAP ASE either on-premise or in the Cloud and would like to learn more about the journey, drop us a line with no obligation mailto:firstname.lastname@example.org