Automate SAP HANA database restore using AWS Systems Manager

Introduction:

For many customers, SAP system copies are one of the routine maintenance activities. SAP system copies are a defined sequence of steps to copy SAP production data to non-production environments. In this blog post, we discuss about automating the SAP HANA database restore.

Amazon Web Services (AWS) Backint Agent for SAP HANA is an SAP-certified backup and restore solution for SAP HANA workloads running on Amazon Elastic Compute Cloud (Amazon EC2) instances. AWS Backint Agent backs up your SAP HANA database to Amazon Simple Storage Service (Amazon S3) and restores it using SAP management tools, such as SAP HANA Cockpit, SAP HANA Studio, or SQL commands. AWS Backint Agent supports full, incremental, differential, and log backup of SAP HANA databases and catalogs to Amazon S3. There is no cost to use AWS Backint Agent. You only pay for the underlying AWS services that you use.

There are several steps and manual efforts involved in the process of restoring a SAP HANA database. This blog is aimed at reducing operational overhead to SAP support staff (such as SAP basis team, database administrators) by automating restore of a SAP HANA database using AWS Systems Manager (SSM) document. You can deploy this SSM document in less than 5 minutes using an AWS CloudFormation template. SSM document needs to be run to restore HANA database (scale-in or scale-out) from a backup stored in Amazon S3 bucket created by AWS Backint. To help customers, we are open sourcing the capability to restore HANA database here.

Systems Manager provides a unified user interface so you can view operational data from multiple AWS services and automate operational tasks across your AWS resources. For operators with a system administration background, this should be easy to configure using a combination of predefined automation playbooks, RunCommand modules, which allow writing simple bash scripts, and the occasional decision step.

Overview:

The following diagram explains the high-level steps involved in the automation of SAP HANA database restore using SSM documents. Backups are stored in Amazon S3 using AWS Backint Agent for SAP. The administrator initiates restore using Systems Manager (SSM) document. AWS Systems Manager runs restore activities on the target SAP HANA database system. AWS Systems Manager records the logs in Amazon CloudWatch.

Prerequisites:

• Setup tags to enable and identify the instances as shown in the following table. Note: using a prefix like “ssmsap:” clearly identifies a purpose for the tags and will reduce the likelihood of unrelated changes.

Source HANA Primary Node:

Target HANA Primary Node:

• HANA Keys
  Create hdbuserstore key on the target HANA instance with root user as shown. SSM document will use this key to execute restore steps.Note: In this example, we used System user. You may create a custom user with restore authorizations, create the keys for that user instead of SYSTEM user.

The Solution:

Sequence of the steps that are performed as part of this solution are shown in the following diagram. This can also be easily customizable by removing or replacing specific step as per your requirements.

Each step is designed to perform a single action or step, allowing the elements to be built, chained together, and reused but also giving improved visibility and control. (This becomes a key element of the framework later on). We chose RunCommand and bash scripts because this aligns with the “command line” use that SAP administrators would be familiar with, we also tried to minimize the configuration and input required, using queries on the host to identify what was running and to derive the parameters required for issuing commands. To tie the execution together and identify instances SSM automation document parameters, outputs, and instance tags were used.

Let’s see what each step in this solution does:

Step 1: Export backup root keys (optional)

This is an optional step and required only if the source system backups are protected by the backup root keys. Using hdbnsutil command, backup root keys are exported to a local file system, and then copied to target instance or upload to Amazon Simple Storage Service (Amazon S3) bucket. In this example, we are uploading to an Amazon S3 bucket which is encrypted.

All the steps following are performed on the target SAP HANA instance which is being restored.

Step 2: Suspend log backups

In this step, we are going to suspend log backup on the target HANA database using following command:

/usr/sap/{{ TARGETSID }}/HDB{{ TARGETDBSYSTEMNO }}/exe/hdbsql -U {{ TARGETDBSYSTEMKEY }} -j "ALTER SYSTEM ALTER CONFIGURATION (''global.ini'', ''SYSTEM'') SET (''persistence'', ''enable_auto_log_backup'') = ''no''"

Step 3: Stop target tenant database

Stop target HANA database before proceeding with restore activities using following command:

/usr/sap/{{ TARGETSID }}/HDB{{ TARGETDBSYSTEMNO }}/exe/hdbsql -U {{ TARGETDBSYSTEMKEY }} -j "ALTER SYSTEM STOP DATABASE {{ TARGETSID }}"

Step 4: Validate and import backup root keys (optional)

This is an optional step and required only if the source system backups are protected by backup root keys. Copy the backup root keys from Amazon S3 bucket which are exported in step 1, validate, and import as shown below.

su - ${SIDLower}adm -c "/usr/sap/{{ TARGETSID }}/SYS/exe/hdb/hdbnsutil -validateRootKeysBackup /hana/shared/{{ SOURCESID }}KEY.rkb --password=${rootkeypassword}"

su - ${SIDLower}adm -c "/usr/sap/{{ TARGETSID }}/SYS/exe/hdb/hdbnsutil -recoverRootKeys /hana/shared/{{ SOURCESID }}KEY.rkb --database_name={{ TARGETSID }} --password=${rootkeypassword}"

Root key password mentioned in above command is stored in AWS Parameter Store and retrieved as show following:

rootkeypassword=`aws --region={{ AWSREGION }} ssm get-parameter --name "{{ SOURCESID }}-ROOT-KEY-PASSWORD" --with-decryption --output text --query Parameter.Value`

Step 5: Restore target tenant database

This is the step to restore target tenant database using source system backup which are stored in Amazon S3 bucket. Please note, target database instance should have backint configured and IAM role assigned to the instance have access to Amazon S3 bucket where source system backups are stored.

Option 1: Restore with BACKUP_ID

/usr/sap/{{ TARGETSID }}/HDB{{ TARGETDBSYSTEMNO }}/exe/hdbsql -U {{ TARGETDBSYSTEMKEY }} -j "RECOVER DATA FOR {{ TARGETSID }} USING SOURCE '{{ SOURCESID }}@{{ SOURCESID }}' USING BACKUP_ID {{ BACKUPID}} USING CATALOG BACKINT USING DATA PATH ('/usr/sap/{{ SOURCESID }}/SYS/global/hdb/backint/DB_{{ SOURCESID }}/') CLEAR LOG"

Option 2: Restore with BACKUP_ID and Log Backups

/usr/sap/{{ TARGETSID }}/HDB{{ TARGETDBSYSTEMNO }}/exe/hdbsql -U {{ TARGETDBSYSTEMKEY }} -j " RECOVER DATABASE FOR {{ TARGETSID }} UNTIL TIMESTAMP '{{ DATEANDTIME }}' CLEAR LOG USING SOURCE '{{ SOURCESID }}@{{ SOURCESID }}' USING CATALOG BACKINT USING LOG PATH ('/usr/sap/{{ SOURCESID }}/SYS/global/hdb/backint/DB_{{ SOURCESID }}') USING DATA PATH ('/usr/sap/{{ SOURCESID }}/SYS/global/hdb/backint/DB_{{ SOURCESID }}/') USING BACKUP_ID {{ BACKUPID }} CHECK ACCESS USING BACKINT "

Step 6: Restore backup root key (optional)

This is an optional step and required only if the source system backups are protected by backup root keys. After the restore is success, set the password for backup root keys on target database using below command:

/usr/sap/{{ TARGETSID }}/HDB{{ TARGETDBSYSTEMNO }}/exe/hdbsql -U {{ TARGETDBSYSTEMKEY }} -j "ALTER SYSTEM SET ENCRYPTION ROOT KEYS BACKUP PASSWORD "${rootkeypassword}""

Root key password mentioned in above command is stored in AWS Parameter store and retrieved as show below:

rootkeypassword=`aws --region={{ AWSREGION }} ssm get-parameter --name "{{ TARGETSID }}-ROOT-KEY-PASSWORD" --with-decryption --output text --query Parameter.Value`

Step 7: Resume log backups

As final step, enable log backups on the target tenant database

/usr/sap/{{ TARGETSID }}/HDB{{ TARGETDBSYSTEMNO }}/exe/hdbsql -U {{ TARGETDBSYSTEMKEY }} -j "ALTER SYSTEM ALTER CONFIGURATION (''global.ini'', ''SYSTEM'') SET (''persistence'', ''enable_auto_log_backup'') = ''yes''"

If there are any additional restore activities, this solution can easily customizable by adding steps to as shown above. Once you have deployed the document you can review the Markup text descriptions to understand the steps in more detail.

Execution:

In CloudFormation, select Create Stack and populate the required parameters or leave them as the defaults, ensuring that they are unique in your account. Select Next, then under configure stack options select Next, review the inputs and select Create Stack. Note: If you are redeploying this template, consider deleting old stacks.

Usage:

Under Systems Manager > Documents > under “Owned by me” > Select the document with the name you specified and click on “Execute automation”. Familiarize yourself with the document by reading through the document and step descriptions.

Provide below input parameters to execute the restore

Option 1: Restore with BACKUP_ID

Option 2: Restore with BACKUP_ID and Log Backups

Click “Execute” and you can see the execution status in the next screen. Start time and End time of each step is displayed as shown below.

Execution status for Restore with BACKUP_ID

Execution status for Restore with BACKUP_ID and Log Backups

Conclusion:

In this blog post, you learned about automating the SAP HANA database restore using the AWS systems manager document. You may use this procedure to reduce your system refresh time and reduce manual efforts.

For more information, Please refer to:
AWS Backint Agent
AWS Systems Manager

We look forward to seeing what our customers build, and if you have questions or would like to know about SAP on AWS innovations, contact the SAP on AWS team or visit aws.com/sap to learn more. Start building on AWS today and have fun.