Custom ASE Database Refresh with SAP Landscape Management (LaMa) Automation Studio

[naeem_maqsud]

Introduction

This blog will cover the scenario of creating a custom database refresh process in SAP LaMa. We will use Automation Studio and the database will be SAP Adaptive Server Enterprise (ASE) or also informally known as Sybase ASE. The steps can also apply to other databases with some slight variations. An example for another database can be found here.

A quick reminder about the refresh options supported in LaMa Enterprise Edition:

Option 1: Using storage based approach where a storage snapshot is taken of the source. AnyDB refresh (e.g. HANA, ASE, MS-SQL, DB2, Oracle etc.) is supported out of the box when using this option. Prerequisite is the use of partner integrations that are included with LaMa Enterprise.

Option 2: Restore based approach where backup of the source is used for the refresh. HANA is supported out of the box. Other databases require a customization which is what I am covering in this blog

Option 3: Replication based approach. HANA is supported out of the box. For other databases, a custom refresh can be created to support other replication tools.

The test environment is as follows:

LaMa Enterprise 3.0 SP11 (recommendation is to use the latest SP)

PCA license installed on source and target

Source System: All in one ECC 6.0 with ASE version 16.0 running on SuSE Linux Enterprise 12 SP2; Hostname: nm-ecc-1; SID: PNM

Target System: Identical to source system; Hostname: nm-ecc-2; SID: QNM

Shared NFS: Mounted on both hosts as /backups (where the database backup files are stored)

SAP Host Agent 7.21 PL41 (latest recommended)

SAP ACEXT Package 7.21 PL45 (latest recommended)

Software Provisioning Manager 1.0 SP26 (latest recommended)

Disclaimer

This blog is published "AS IS". Any software coding and/or code lines / strings ("Code") included are only examples and are not intended to be used in any productive system environment. The Code is only intended to better explain and visualize the features of the SAP Landscape Management Automation Studio. No effort has been made to make the code production quality (e.g. security, error handling, robustness, etc). If you use any of the code shown, you are doing it at your own risk.

Information in this blog could include technical inaccuracies or typographical errors. Changes may be periodically made.

Assumptions

It is assumed that your environment is already setup correctly for creating custom processes in LaMa. This blog does not go over all the configuration steps. If this is your first time using LaMa Automation Studio then I recommend that you start with something simple and then move on to more complex setups like custom provisioning. Please refer to this blog for other examples.

Flow

The high-level flow of the database refresh is:

1. Create backup of source database (If you want LaMa to take a fresh backup then this step is needed. If you already have backups then this will become a dummy step)


2. Fence the target system


3. Load the database backup on target


4. Execute post steps including PCA on target


5. Un-fence the target system

Configuration Steps

1. Create the action configuration files on source for database backup (OS Level)


2. Create ASE backup script on source (OS Level)


3. Create the action configuration files on target for custom SWPM (OS Level)


4. Create Dark Mode SWPM script on target for custom SWPM (OS Level)


5. Create Provider Definitions (LaMa)


6. Design Custom Provisioning (LaMa)


7. Assign custom provisioning process to instance (LaMa)

Configuration File -- Source

We rely on the SAP Host Agent and therefore the configuration files are located in directory /usr/sap/hostctrl/exe/operations.d and end with ".conf"

Create file DB_backup.conf

Username: $[PARAM-PRISAPSYSTEM:#tolower#required]adm
Name:DB_backup
Description: Create DB backup
Command: /usr/sap/scripts/syb_dump.sh $[PARAM-PRISAPSYSTEM:#required]
ResultConverter: flat
Platform: Unix

Line 1: Specifies the username to execute the script. The SID is passed as a variable (i.e. PNM) and this is converted to lower case and "adm" appended to it. Therefore username will be "pnmadm". #required indicates that the parameter is mandatory for execution of this operation.

Line 2: Name of the operation

Line 3: Description

Line 4: The full path of the script and the arguments to be passed. In this case script is called syb_dump.sh located in /usr/sap/scripts and we are passing the argument PNM (SID name that is entered when kicking off the operation in LaMa)

Line 5: No special handling is performed

Line 6: Only applicable to Unix/Linux hosts (will not be available on Windows hosts)

Refer to the LaMa guide section for detailed explanation of syntax: https://help.sap.com/viewer/e7dead4286c545808b3bd24feee7448c/3.0.14.0/en-US/250dfc5eef4047a38bab466c...

Set the right permissions:

• chown root:root <.conf file>


• chmod 755 <.conf file>

ASE Backup Script -- Source

We will be using the "isql" command to perform the backup. CAUTION: Best practice for secure handling of passwords is to use "sybctrl". Refer to https://launchpad.support.sap.com/#/notes/1899984

Since this blog is only for demonstration purpose we will stick with the insecure way but once this is working you should follow the SAP note above and alter your script to make it secure.

#!/bin/bash

SID=$1

sidadm=`echo $SID | awk '{print tolower($0)}'`adm

dump_file=`echo $SID`_DB_DUMP

log_file=/tmp/dump$SID\.log

path1=`eval echo "~$sidadm"`

. $path1/.sapenv.sh

. /sybase/$SID/SYBASE.sh

isql -X -Usapsa -S$SID -P<password_of_sapsa> -o$log_file <<EOF

use master

go

select getdate()

go

sp_dump_history @database_name = $SID

go

dump database $SID to '/backups/$dump_file'

go

EOF

echo "Script Completed" >> $log_file

date >> $log_file

If you already have a backup and do not want to execute another backup then you can edit the above script and comment out the dump command as below. Remember to ensure the dump file is of syntax <SID>_DB_DUMP so that our database load does not fail.

/*dump database $SID to '/backups/$dump_file'*/

You can also decide to skip this step altogether if you never want to execute a backup as part of this custom operation. Remember to make the necessary adjustment when designing the custom provisioning process. You can replace the "clone volume" step with a dummy step that just executes and echo command. You will understand this when you go to next steps especially the custom provisioning design section.

Configuration File -- Target

Go to directory /usr/sap/hostctrl/exe/operations.d

Create the file DB_custom_swpm.conf

Name:DB_custom_swpm

Username: root

Description: Dark mode SWPM

Command: /usr/sap/scripts/dark_mode_swpm.sh

ResultConverter: flat

Platform: Unix

Set the right permissions:

• chown root:root <.conf file>


• chmod 755 <.conf file>

The above configuration file will run as root and the script will execute software provisioning manager  (SWPM) in dark mode. This means it is an unattended run of SWPM and we will not go through the usual dialog when running it. The section below describes how to set this up and then create the script.

Note that restore of ASE backup file is part of SWPM so we do not need to perform a separate database restore.

We need to also create a configuration file for a dummy step to stop the default software provisioning manager from executing. This will just execute an echo command. The real software provisioning manager execution will be done as replacement to the restore backup step when designing custom provisioning.

Create the file custom_swpm_echo.conf

Name:custom_swpm_echo

Command: echo 'executing dummy SWPM'

ResultConverter: flat

Platform: Unix

Set the right permissions:

• chown root:root <.conf file>


• chmod 755 <.conf file>

Create Parameter-File for Dark Mode SWPM  -- Target

Before we can run unattended swpm in a script we need to create the parameter-file. To do this run swpm in interactive mode and when you reach the summary screen do not proceed further.

My swpm was installed in /work/SWPM1.

cd /work/SWPM1

./sapinst

In a web browser go to URL https://<hostname>:4237/sapinst/docs/index.html

In my case I used Firefox directly on the target Linux host (using a remote desktop client) but you can use a browser on your laptop as well. Since the browser was on the same host for me I replaced the hostname "nm-ecc-2" with "localhost".

The server is using a self-signed certificate, so you will need to accept the connection as insecure.

Login as root

After login select the SWPM option Generic -> SAP ASE -> Refresh Database Content

Continue and accept default setting for profile. Next choose homogeneous system copy

Enter password for target database administrator -- sybqnm

Indicate "load a database dump" as method for content load

Enter password for sapsa

Enter location of database dump file

I left transaction dump field as empty since I was not using stripe files. Also no resizing in my case.

When you reach parameter summary screen -- STOP

Don't exit the screen and don't press Next.

Update 2022: With newer versions of SWPM, it was found that the inifile.params is generated after you exit the screen.

At the command prompt on target host, go into the directory /tmp/sapinst_instdir/GENERIC/SYB/DBREFRESH/<log dir>/

Here you will find the file "inifile.params". Copy this file to "QNM_inifile.params" (e.g. cp -p inifile.params QNM_inifile.params)

Now go ahead and exit the software provisioning manager.

Copy the above directory structure from /tmp to a directory that is part of your SAP environment.

e.g. cp -pr /tmp/sapinst_instdir /work

Before we go and create the script and use it in the custom provisioning operation, it is a good idea to test the unattended mode so we know it will work.

Search the sapsinst_dev.log file for the product ID. In this case it is NW_DBRefreshOnly:GENERIC.SYB.PD

Shutdown ABAP (Central and AS instances). This is needed otherwise the unattended swpm will fail.

As user root issue the command:

cd <swpm_dir>



./sapinst 'SAPINST_INPUT_PARAMETERS_URL=/work/sapinst_instdir/GENERIC/SYB/DBREFRESH/<log_dir>/QNM_inifile.params' 'SAPINST_EXECUTE_PRODUCT_ID=NW_DBRefreshOnly:GENERIC.SYB.PD' 'SAPINST_SKIP_DIALOGS=true' 'SAPINST_SLP_MODE=true' 'SAPINST_START_GUI=false' 'SAPINST_START_GUISERVER=false'

The above command will take a while to run. If it completes without issues then you are ready to go to the next step.

Start ABAP (Central and AS instances)

Dark Mode SWPM Script -- Target

Below is a sample of the script to launch the unattended swpm.

#!/bin/sh

#Dark mode swpm

# This where DB load happens plus all other post load steps

SWPM_DIR=/work/SWPM1

LOG_DIR=/tmp

LOG_FILE=lama_swpm_log

# Call sapinst to perform the actual load from dump and post steps

$SWPM_DIR/sapinst 'SAPINST_INPUT_PARAMETERS_URL=/work/sapinst_instdir/GENERIC/SYB/DBREFRESH/<log_dir>/QNM_inifile.params' 'SAPINST_EXECUTE_PRODUCT_ID=NW_DBRefreshOnly:GENERIC.SYB.PD' 'SAPINST_SKIP_DIALOGS=true' 'SAPINST_SLP_MODE=true' 'SAPINST_START_GUI=false' 'SAPINST_START_GUISERVER=false'

#

echo "Script Completed" >> $LOG_DIR/$LOG_FILE

date >> $LOG_DIR/$LOG_FILE

 

Provider Definition for ASE Backup

Login to SAP LaMa and create a new provider definition as "Script Registered with Host Agent"

Add the host nm-ecc-1 (source) from the drop down and click "Retrieve Scripts". The host agent fills the drop list.

Choose the script (the configuration file) "DB_backup" and give Name of "DB_backup". Also check mark Instance and Provisioning Replacement boxes. Click on Create Provider.

Click on the newly created provider definition

Provider Definition for Custom SWPM

Repeat the definition creation process for custom swpm on host nm-ecc-2 (target)

We also need to create a dummy provider definition to replace the default software provisioning manager step.

 

Design Custom Provisioning Process

Open Automation Studio -> Custom Provisioning and click on "Create"

The custom provisioning has predefined steps. We need to replace some of these steps with our own custom steps.

• Software Provisioning Manager needs to be replaced by our dummy step -- custom_swpm_echo


• Clone Volumes (Source System) needs to be replaced by our DB_backup step


• Restore Backup (Target System) needs to be replaced by our DB_custom_swpm step

Note that the steps listed in automation studio are not necessarily in the right sequence. For example the software provisioning step shows at the top but is not the first step. Instead just focus on the steps that need to be replaced.

 

Don't click on Create .... yet. We will do that once all replacement steps are added. The next step we replace is the "Clone Volumes (Source System)". Since we are doing a database refresh using backup and restore we do not need to clone any volumes.

If no volume is cloned on source then clone steps on target are irrelevant.

Now click on "Create Custom Provisioning Process"

 

Assign Custom Provisioning Process to Instance

We now need to assign this custom provisioning process to the instance which should use this process rather than the default process.

Open the LaMa Configuration screen. Configuration -> Systems

Find the source system PNM and click edit. Navigate to Provisioning & RFC tab. Select the above custom provisioning process from the drop down and check mark the box. Click save.

Repeat the above for the target system QNM.

Verify that you now see 2 systems referencing the custom provisioning process.

 

Execution

Select Provisioning -> Systems -> QNM -> Provisioning -> Refresh Processes -> Refresh Database

Ensure that the export directory exists if not create one and <sid>adm should have write permission. In my case this was /tmp/PCA

You are now ready to perform the execution. Before you do this you may also want to save this as a Provisioning Blueprint. If so, click on "Create Provisioning Blueprint" which you can use to run this again.

Click Execute

You can now monitor the progress. It took over 4 hours to complete the process in my environment so let it run but check periodically for any errors.

Log files for host agent are also useful to check on progress (located in /usr/sap/hostctrl/work)

 

The refresh is completed. This custom provisioning operation can now be used multiple times. If you saved a blueprint then it becomes even easier to run again.

For more information please refer to:

1. SAP Note 1465491 - Provider Implementation Definition: https://launchpad.support.sap.com/#/notes/1465491


2. https://blogs.sap.com/2020/02/28/sap-landscape-management-lama-automation-studio/


3. System Copy for SAP Systems Based on the Application Server ABAP