AWS for SAP

How to use snapshots to create an automated recovery procedure for SAP ASE databases

More and more customers are looking into migrating SAP to the cloud. While lots of customers have moved to SAP HANA, there are still many cases, where non HANA databases, what SAP calls AnyDB, are being used. These SAP systems can also be critical and it is important to implement a solid backup and restore strategy.

In the previous blog on how to use snapshots for SAP HANA database to create an automated recovery procedure, we described cloud native approach to demonstrate the power and capabilities of AWS for SAP HANA based SAP systems.

In this blog post, we’ll enhance the solution described in the earlier blog post with SAP ASE (previously Sybase ASE) integrating with Amazon EBS snapshot and automated recovery procedure. This will enable you to follow the same backup integration and policy for SAP databases running on AWS.

There are still good reasons for SAP ASE to be backed up with third-party backup solutions (e.g. the need for a single backup tool covering multiple DBs). However, we focus on an alternative approach by using cloud native features, such as  Amazon Elastic Block Store (EBS) Snapshots, Amazon EBS fast snapshot restore and Taking crash-consistent snapshots across multiple Amazon EBS volumes on an Amazon EC2 instance. With these features we build an infrastructure with native backup/restore functionality and automated process.

A fast and automated restore process can provide new capabilities on cloud environments. For example, an automated restore process removes costs of standby instances, regardless of whether they were implemented with the pilot light approach or as hot standby. Furthermore, no additional license costs for third-party software are required.

Solution overview

The described architecture is based on Amazon EBS Snapshots. Snapshots provide a fast and cost-effective backup process, independent of the database size. They are stored in Amazon Simple Storage Service (S3) and can be accessed across Availability Zones automatically. In addition, Amazon EBS snapshots are incremental by default. Only the delta changes are stored since the last snapshot. Amazon EBS fast snapshot restore creates a volume from a snapshot that is fully initialized at creation. This eliminates the latency of I/O operations on a block when it is accessed for the first time. Volumes that are created using fast snapshot restore instantly deliver all of their provisioned performance. To create a resilient high available architecture, automation is key. All steps to recover the database must be automated in case something fails.

The following picture shows the high-level architecture:

Architecture Overview - ASE Snapshot Backup

Set up Amazon EBS Snapshots and SAP ASE database integration

To get started, we installed a distributed SAP NetWeaver installation with SAP ASE 16 SP03 PL08 database and executed the following modifications:

  1. We used /backup-efs NFS share provided by Amazon Elastic File System (Amazon EFS) and configured it for transaction logs backup of SAP ASE.
  2. The SAP ASE configuration needs to be adjusted to set database and transaction dumps. The backups are written directly to Amazon EFS, which is replicated across Availability Zones. Backups, especially log backups, are now securely stored and still available even if an issue affects an Availability Zone.
  3. In addition, the Amazon EFS Infrequent Access (EFS IA) option is activated. This automatically stores data that is not accessed within 90 days in EFS IA storage class. This saves up to 92% of the costs compared to standard Amazon EFS with the recent Amazon EFS price reduction for Infrequent Access storage.

Log backups are written automatically to /backup-efs File System on Amazon EFS. By default, SAP ASE logs are not triggered and therefore, this needs to be configured. We can schedule them using DBA Planning Calendar in the DBA cockpit for every 15 minutes. We can decrease this value to reduce the recovery point objective (RPO) even further.

In addition, SAP recommends to set up a threshold action that triggers a DUMP TRANSACTION command whenever a threshold (fill level) in the log segment has been reached. SAP Note 1801984 – SYB: Automated management of long running transactions describes the necessary steps for setting up a threshold action for the log segment.

Now that the log files are available and securely stored across multiple Availability Zones, we can configure the full database backup.

SAP ASE snapshot script

In the script “aws-sap-ase-snapshot.sh,” the following commands are implemented. The following code examples will explain the most important commands:

    1. We use the SAP ASE prepare database command to put the database in a state that allows us to take a consistent Amazon EBS snapshot of the database device files and create consistent external DB copy. Such an external copy is consistent and can be used to recover the database(s) to a consistent state (See SAP Note 1887068 – SYB: Using external backup and restore with SAP ASE). This will allow us to load transaction log dumps to a database restored from Amazon EBS snapshot and recover from an Amazon EFS File System transaction log destination.
prepare database all_tag hold master, model, $ASE_SERVER, saptools, sybsystemdb, sybsystemprocs, sybmgmtdb for external dump with quiesce
	go

To login to the SAP ASE database, we store the password of the sapsa user in the SAP ASE aseuserstore.

2. Now that the database is in quiesce mode, a roll-forward of the database is possible after the restore. To trigger a snapshot of the Amazon EBS volumes, we use the snapshot feature to create point-in-time and crash-consistent snapshots across multiple EBS volumes. The advantage of this feature is that no manual Input/Output (I/O) freeze on a volume level is required to bring multiple volumes in sync. The following code snippet is used to execute the snapshot of Amazon EBS volumes.

aws ec2 create-snapshots --region $region --instance-specification InstanceId=$instance_id,ExcludeBootVolume=true --description $snapshot_description --tag-specifications "ResourceType=snapshot,Tags=[{Key=Createdby,Value=AWS-ASE-Snapshot_of_${HOSTNAME}}]" | jq -r ".Snapshots[] | .SnapshotId"

3.  We will enable the Amazon EBS Fast Snapshot Restore to reduce the time to initialize newly created volumes by defining parameter for which zone(s) the Amazon EBS FSR will be enabled. In case the parameter will be empty the Amazon EBS FSR will be disabled.

# Requirements in which zone(s) the the fast snapshot restore separated by spaces not comma! If empty Amazon EBS FSR will be disabled
	azs="us-east-1a us-east-1b"
aws ec2 enable-fast-snapshot-restores --region $region --availability-zones ${azs} --source-snapshot-ids ${recent_snapshot_list_new[$i]} --output table

4. By default, there is no entry into the dump history file of the database because the dump history feature cannot be used. The entry to the backup history is optional and the database can handle the log recovery process after the snapshot restore.

Now we have a backup process in place with full backups via the Amazon EBS snapshots and log backups written to Amazon EFS. Both storage locations are independent of the Availability Zone and can be accessed from another Availability Zone. Because of that, we can re-create the entire database for example with the AWS Auto Scaling group in another Availability Zone later on.

SAP ASE restore script

Let’s have a closer look at the restore script and each step during the restore process.

As a prerequisite, the parameter in the AWS Parameter Store is required. The AWS Parameter Store lists the volume ids for SAP ASE volumes.

It is important to consider the prerequisites for the SAP ASE snapshot script. Especially to

start the SAP ASE in quiesce mode with the command line option ‘-q’ and apply transaction log dumps that have been saved after the databases were quiesced.

    1. To create the parameters with command line interface, we use the command:
aws ssm put-parameter --name <name-for-sapdata> --type StringList --value vol-1,vol-2,vol-3, …, vol-n
aws ssm put-parameter --name <name-for-log> --type StringList --value vol-1,vol-2,vol-3, …, vol-n

This step is required only once during the setup to create the parameters in the AWS Parameter Store. The values get updated later on automatically by the script.

2. To create new volumes out of the latest snapshot, the script is looking for the latest snapshot created by the snapshot script and the snapshot-id.

aws ec2 describe-snapshots --region $REGION --filters Name=volume-id,Values=${DATAVOLID[$i]} Name=status,Values=completed Name=tag:CreatedBy,Values= AWS-ASE-Snapshot_of_${HOSTNAME} | jq -r ".Snapshots[] | .StartTime" | sort -r | awk 'NR ==1'
aws ec2 describe-snapshots --region $REGION --filters Name=start-time,Values=${LATESTSNAPDATEDATA[$i]} Name=volume-id,Values=${DATAVOLID[$i]} | jq -r ".Snapshots[] | .SnapshotId"

The restore process should use the latest snapshot to reduce the number of log files to recover. The AMI was created without SAP ASE volumes, and these volumes must be created out of the Amazon EBS snapshot.

3. Create a new volume out of the snapshot.

aws ec2 create-volume --region $REGION --availability-zone $AZ --snapshot-id ${SNAPIDDATA[$i]} --volume-type gp2 --output=text --query VolumeId

We recommend to delay the procedure and wait until the volumes are available, before attaching them to the instance. This is implemented in the restore script when checking availability of each newly created Amazon EBS volume.

4. Attach volumes to instance:

aws ec2 attach-volume --volume-id ${NEWVOLDATA[$i]} --instance-id $INSTANCEID --device ${DATADEVICEINFO[$i]}

If we started the database now, it would have a crash consistent state, based on the time the snapshot was taken. To recover the database to the most recent state, we can use the log files stored on Amazon EFS. The AMI automatically mounts the Amazon EFS file system during startup.

/etc/fstab
[…]
# EFS
<efs-fs-id>:/backup-efs    /backup-efs            efs    _netdev,tls 0 0

5. Update Amazon Route53 DNS entries for reverse and forward DNS lookup. We used the adaptive computing with virtual IP and virtual hostname for SAP ASE database host

aws route53 change-resource-record-sets --hosted-zone-id $DNS_ZONE_ID --cli-input-json "$INPUT_JSON_A" --region ${REGION}

6. To recover SAP ASE to the most recent state, we must iterate over each transaction log newer than last Amazon EBS snapshot timestamp and apply the logs to database in quiesce mode one by one to perform point-in-time recovery.

sudo -u $SYBADM -i isql -k${SYBKEYNAME} -w 1024 -X -e -b --retserverror -o apply_log &lt;&lt; EOSQL
set nocount on
go
load transaction ${ASE_SERVER} from '${f}''
go
EOSQL

The restore time depends on two main aspects: 1) time to create new volumes from the snapshot and 2) the number of log files to recover. The volume initialization when created from EBS snapshots depends on the volume size and the volume type. With Amazon EBS Fast Snapshot Restore, it is possible to reduce the time to initialize created volumes from EBS snapshots. The database recovery process depends on the number of log files and change rate of the database since the snapshot was created.

Additional things to consider

An additional implementation is to use the Amazon EC2 instance failover with AWS Auto Scaling group that can launch the new instance in another Availability Zone. With this configuration, the application server might remain in another Availability Zone and cross Availability Zone traffic with slightly higher latency gets created.

Recommendation: Perform a file system backup of the software installation directory $SYBASE at the same time. Important files are the SAP ASE configuration file <DBSID>.cfg, the interfaces file, and the dumphist file.

Conclusion

While many SAP systems are now running SAP HANA as a database, some systems still use anyDB such as SAP ASE and customers using these products still require an effective backup and restore process.

In this blog we demonstrated the integration into EBS snapshots to create a fast backup of the database, which is stored across different Availability Zones. To implement this solution please have a look at the github repository aws-ebs-snapshot-sap-ase to get the sample code.

For higher availability, SAP ASE Replication Server can be used to operate a synchronous replication of the primary database and to reduce the recovery point objective (RPO).