Skip to main content
Contact Us
Register
Pure Technical Services

Creating Data Snapshots using HDBSQL

  1. Last updated
  2. Save as PDF

Currently viewing public documentation. Please login to access the full scope of documentation.

It is possible to create SAP HANA Data Snapshots (previously known as storage snapshots) only using HDBSQL (HANA Database structured query language). Typically this can be done from the following interfaces:

  • The SAP HANA Client installed on a system sending commands in the form <hdbsql> -n <hostname:3<instance number (00)><port number>> -i <instance number (00)> -u <user> -p <password> <Command>
  • SAP HANA Studio SQL Query Console 
  • SAP HANA Cockpit Database Explorer

 

Step 1. Check if the System is Set-up for Multiple Database Containers (MDC)

Execute the following on any database in the instance:

SELECT VALUE FROM M_INIFILE_CONTENTS WHERE FILE_NAME = 'global.ini' AND SECTION = 'multidb' AND KEY = 'mode'

The result will return a field "VALUE" which, if it contains the value "multidb", then the database is an MDC database and the System Database must be connected to in order to create a storage snapshot. In the event of the value being "singledb" then this is a single container system. 

All databases from SAP HANA 2.0 are installed as MDC. Even an instance with a single tenant is still set up for MDC. 

clipboard_e4f9943e881d6a182196f8cdbae898f87.png

Step 1a. If the Database is an MDC Database, Connect to the System Database

To connect to the System Database, only the port number needs to be changed. In this case, the port to connect on for instance 00 would change from 30015 (default tenant port) to 30013. In the event of the system running in “singledb” mode, continue to use the original connection string.

To see all of the ports each database is listening on in an instance, execute the following in the System database:

SELECT HOST, DATABASE_NAME, SQL_PORT 
FROM SYS_DATABASES.M_SERVICES

Step 2 - Find the Block Storage Volume which Corresponds to the Data Volume Mount Point

Scale Up 

To see where the filesystem is mounted to for a scale up or single node implementation execute the following :

SELECT VALUE FROM M_INIFILE_CONTENTS 
WHERE FILE_NAME = 'global.ini' 
AND SECTION = 'persistence' 
AND KEY = 'basepath_datavolumes' 
AND VALUE NOT LIKE '$%'

 clipboard_effc23e372cf748fe7f1745e74148bb3c.png

The value returned will include the database name at the end (e.g. SH1 will correspond to /hana/data/SH1) the mount point needed to interact with is the directory above the database name (e.g. /hana/data/SH1 becomes /hana/data/).

Scale Out 

To see the hosts and filesystem mount point locations for all of the data volumes in a scale out / distributed system implementation execute the following:

SELECT HOST, STORAGE _ID, PATH, KEY, VALUE 
FROM SYS.M_ATTACHED_STORAGES WHERE KEY = 'WWID' 
AND PATH LIKE (SELECT CONCAT(VALUE,'%') 
FROM M_INIFILE_CONTENTS 
WHERE FILE_NAME = 'global.ini'AND SECTION = 'persistence' 
AND KEY = 'basepath_datavolumes' 
AND VALUE NOT LIKE '$%')

 clipboard_e7f8021a194fa37b8e59940fd6aed017b.png

The values returned inform the user of the following:

  • HOST - The SAP HANA host which the data volume is currently mounted to and being used by.
  • STORAGE_ID - A Storage ID assigned by SAP HANA to the volumes used in the scale out deployment.
  • PATH - The mount point at which the filesystem is mounted to.
  • VALUE - This is the serial number of each volume, as exposed to the filesystem. This value can be used to match the SAP HANA data volume to the block device on FlashArray. 

Step 3. (Optional) Retrieve the Serial Number of the Block Storage Volume  

This step is only required when the data volume mount point needs to be matched with a block storage device on FlashArray.

An SSH connection needs to be created to query the operating system for the device serial number for the SAP HANA data persistence volume, once the command line is available for reading and writing we run the “df -h” command to view all mounted volumes and mount points as well as retrieving the device mapper or storage device (sd) the mount point is mapped to. The output of df -h then needs to be piped and “grep” used to isolate the specific entry for the required volume.  It is possible to query the /etc/fstab for the same information but the contents of fstab may not always be what the system is working on at that point in time.

df -h | grep <SAP HANA Data Persistence mount point>

clipboard_ef1842a118c26372a89af83ef54f865e8.png

udevadm info --query=all --name<device name> | grep DM_SERIAL

clipboard_e74330b5683eac4ff86c264bfa70f5141.png

Using the serial number returned for the device, it is possible to match it up to the block storage volume on a FlashArray. Note that the block volume serial number will be all of the characters after “3624a9370”

To see all of the volumes and their serial numbers using the FlashArray CLI execute the following :

purevol list

 clipboard_e63c4b1cadff0ce6fc87c4f4d54197b5f.png

 

Step 4. Prepare the Data Snapshot and retrieve the Backup ID

To prepare the data snapshot, execute the following HDBSQL command:

BACKUP DATA FOR FULL SYSTEM CREATE SNAPSHOT COMMENT 'SNAPSHOT-<Snapshot Time>

Once the Data Snapshot has been prepared it should not be retained for a long period of time. If it is left in the "prepared" state new data will be written to a different part of the data volume and it will eventually fill and run out of space. 

To retrieve the Backup ID, execute the following HDBSQL command:

SELECT BACKUP_ID, COMMENT FROM M_BACKUP_CATALOG WHERE ENTRY_TYPE_NAME = 'data snapshot' AND STATE_NAME = 'prepared'

clipboard_e2d759620951f249483289bc4b885cd6b.png

The Backup_ID value is important, it will be needed to either confirm or abandon the data snapshot. 

Step 5. Create a Volume Snapshot for the Volume on FlashArray

To create a volume snapshot via the FlashArray CLI execute the following:

purevol snap <Volume Name> --suffix <Use the SAP HANA Backup ID as a suffix>

Step 6. Confirm or Abandon the Data Snapshot

If the volume snapshot on FlashArray has been created correctly without issue, then it can be confirmed. If any issues have occurred SAP HANA needs to be told to abandon the data snapshot so that it is not listed as a valid recovery point. 

To confirm the data snapshot execute the following: 

BACKUP DATA FOR FULL SYSTEM CLOSE SNAPSHOT BACKUP_ID <External Backup ID> SUCCESSFUL;

To abandon the data snapshot execute the following:

BACKUP DATA FOR FULL SYSTEM CLOSE SNAPSHOT BACKUP_ID <Prepared database snapshot backup ID> UNSUCCESSFUL <additional comments>;

 

More information on SAP HANA Data Snapshots can be found in the SAP HANA Administration Guide for SAP HANA Platform.