HANA: hdbuserstore, hdbsql and storage snapshot
Store HANA credentials, connect with hdbsql and prepare a snapshot for an external backup tool.
Notes on storing HANA credentials with hdbuserstore, connecting with hdbsql, and taking a storage snapshot for an external backup tool.
Store credentials with hdbuserstore
hdbuserstore keeps connection credentials in the secure store of the OS user, so scripts do not need a clear-text password.
/hana/shared/<SID>/hdbclient/hdbuserstore set <KEY> <host>:<sql-port> <USER> <password>
/hana/shared/<SID>/hdbclient/hdbuserstore LIST
To keep the password out of the shell history, delete the last history entry right after the command:
/hana/shared/<SID>/hdbclient/hdbuserstore set BACKUP <host>:30013 <USER> '<password>' ;history -d $(history 1)
In the examples the SQL port of the system database of instance 00 is 30013; a second instance, for example 96, uses 39613:
/hana/shared/<SID>/hdbclient/hdbuserstore set BACKUP <host>:39613 <USER> '<password>'
Connect with hdbsql
With the stored key (-a no column headers, -x quiet, -j no page break, -i instance number):
/hana/shared/<SID>/hdbclient/scripts/hdbsql -a -x -j -U BACKUP -i 00
Without the secure store, directly against the system database:
/hana/shared/<SID>/hdbclient/scripts/hdbsql -n localhost -i 00 -u <USER> -p '<password>' -d SYSTEMDB
Storage snapshot for an external backup tool
A backup tool that snapshots the storage first prepares a HANA data snapshot, then closes it once the storage snapshot is taken:
BACKUP DATA FOR FULL SYSTEM CREATE SNAPSHOT COMMENT 'Pre-freeze snapshot';
SELECT BACKUP_ID FROM SYS.M_BACKUP_CATALOG WHERE ENTRY_TYPE_NAME='data snapshot' AND STATE_NAME='prepared';
BACKUP DATA FOR FULL SYSTEM CLOSE SNAPSHOT BACKUP_ID <backup_id> SUCCESSFUL 'data_backup_first';
Remote administration through a bastion with Ansible
To reach HANA hosts through a bastion, create a dedicated OS user on the target and install the public key:
sudo adduser <ansible-user>
sudo su - <ansible-user>
mkdir .ssh
chmod 700 .ssh/
vim .ssh/authorized_keys
Then connect through the bastion with ProxyCommand:
ssh <ansible-user>@<target> -oProxyCommand="ssh -i ~/.ssh/id_rsa <bastion> -W %h:%p" -i ~/.ssh/id_ed25519
Notes
- Prefer a dedicated HANA user for backups over
SYSTEM. - Cross-tenant queries are possible; see the SAP blog on cross-tenant database queries.