At the minimum, implemented backup and restore strategy must satisfy the business requirements of the organization as it relates to recovery time objective (RTO) and recovery point objective (RPO). The SAP HANA database supports file-based backup and BACKINT (via the SAP HANA Backup Application Programming Interface [BAPI]) or a hybrid of both approaches. Irrespective of the approach adopted, the backup strategy should satisfy the following best practices and recommendations:

• Full database backup to a remote location performed on a daily basis
• Frequent backups (daily) of database logs to a remote location
• A retention time for database backup and log backup of at least 28 days
• Consistency check performed at least once in a backup cycle
• Periodic disaster-recovery tests performed at defined intervals

I cover the following recommendations and best practices in this article:

• Perform a full backup before and after major system operations
• Configure a system usage type for the production systems
• Refrain from using the default backup directory for data and log backups
• Encrypt database backup files
• Regularly review backup log files
• Configure and monitor alerts related to backups
• Exclude backup technical users from a SQL statement memory limit
• Restrict the use of the system user
• Enforce control in the assignment of backup- and restore-related system authorizations
• Regularly ship backup files to a remote location
• Enable automatic log backup and configure the log mode optimally
• Back up customer-specific configuration settings
• Leverage shared storage management for file-based backup
• Be mindful of the SAP HANA version when planning backup and restore operations
• Avoid interrupting a database recovery operation
• Leverage a meaningful and consistent backup naming convention
• Effectively manage delta backups
• Perform capacity planning for the backup location
• Avoid deleting obsolete backup files manually
• Perform manual integrity checks on backup files
• Perform backup file availability and accessibility checks
• Manage SAP HANA licensing effectively
• Activate auditing for the deletion of backup catalog entries
• Avoid the use of the Common Internet File System (CIFS)
• Monitor the availability of SAP HANA services

 

Perform a Full Backup Before and After Major System Operations

A backup is designed to allow you to recover a database to a particular state following a disaster or undesirable event. It is good practice to carry out backup of the database before and after specific database activities that can have direct implications on the availability and status of the database. Backing up the database before and after specific system activities ensures that you can recover (if required) the database to a specific point in time, usually before the activity. Examples of database operations that require a full database backup include:

• After the installation of the SAP HANA database
• Before and after a host is removed from the system
• Before and after a service is removed
• Before and after data encryption
• Before and after a database upgrade operation
• Before and after table distribution

Configure a System Usage Type for the Production Systems

The SAP HANA database allows you to define a usage type or role for your system such as production, development, or testing. The advantage of this is that a user is warned about the usage of the system when important changes are to be made to the database, including database backup and restore activities. This enforces some level of control, which ensures that activities that are not meant to be performed are not carried out. This control is achieved by the system displaying a message reminding the actor of the system environment (i.e., productive system). System usage can be defined during the installation of the SAP HANA database or via client applications such as the SAP HANA studio. In SAP HANA studio, this attribute is maintained by editing the usage parameter in the system information section of the configuration file, as shown in Figure 1. Following the activation of the usage type, I now attempt to perform a backup of the database by choosing the Back Up System… option shown in Figure 2. Right-click the SID node [FRD (SYSTEM)]. Follow menu path Backup and Recovery > Back Up System…. Figure 3 displays the notification to the user about the productive role of the system and the need to perform data manipulation activities with care because you are working in the production system. In the same manner, I attempt to initiate a recovery operation by choosing the Recover system… option in Figure 2. Figure 4 displays with a similar message.

Refrain From Using the Default Backup Directory for Data and Log Backups

During the installation of the SAP HANA system, the directories /usr/sap/<SID>/HDB00/backup/data and /usr/sap/<SID>/HDB00/backup/log are created. They act as the default data backup and log backup directories, respectively. SAP recommends that you not use these directories to store backup files. The backup directories should ideally point to a dedicated external storage location that will not impact the host. The default backup directory used for backup of data and log files for file-based backup can be maintained in SAP HANA studio via the Configuration tab of the backup catalog, as shown in Figure 5. You need to change the default destinations for data and log backup to the custom directories you have set up. For increased security and data protection, the data area, log area, and backups should not be on the same physical storage devices and appropriate file access rights and permission should be configured.

Encrypt Database Backup Files

Data encryption provides a framework to ensure privacy and the security of data saved on disk. While SAP supports the Data Volume Encryption functionality, this is not extended to some files, including database redo logs files and database backup files. Therefore, if your business requirement involves privacy protection for backup files via encryption, SAP recommends that you implement a third-party product that integrates with BACKINT for SAP HANA.

Regularly Review Backup Log Files

Backup.log and backint.log are two important log files that provide information about database backup operations. The backup.log contains information about the data backups, log backups, backup catalog, and recovery operations. The backint.log on the other hand contains information related to the activities of the BACKINT agent. SAP recommends that you review these log files at defined intervals for backup issues (especially when a backup operation fails) and file size as part of space monitoring activities. These backup log files can be accessed via the Diagnosis Files tab of SAP HANA studio as shown in Figure 6. Choose the backup.log entry and Figure 7 displays with the content of the log. In the event that these files grow to the extent that the database performance is threatened or impacted as a result of disk space use, you can delete files you do not need again to free up disk space.

Configure and Monitor Alerts Related to Backups

Alerts are important tools for troubleshooting the SAP HANA database. Alerts keep you abreast of deviations from defined thresholds or occurrences of specific system behaviors. An alert warns you about a potentially critical event that might impact the smooth operation of the database system. The system supports the following backup-related alerts:

• Most recent savepoint operation: Determines how long ago a complete, consistent image of the database was persisted to disk.
• Log mode legacy: Determines whether the database is running in log mode legacy. Log mode legacy does not support point-in-recovery and is not recommended for productive systems. With the database running in log mode legacy, log segments are freed once the full database backup is performed and log backup is not performed.
• Log mode overwrite: Determines whether the database is running in log mode overwrite. Log mode overwrite does not support point-in-recovery (only recovery to a data backup) and is not recommended for productive systems.
• Existence of data backup: Determines whether a data backup exists. A data backup is required for database recovery.
• Status of most recent data backup: Determines whether the most recent data backup was successful.
• Age of most recent data backup: Determines the age of the most recent successful data backup.
• Status of most recent log backups: Determines whether the most recent log backups for services and volumes were successful.
• Savepoint duration: Identifies long-running savepoint operations.
• Run time of the log backups currently running: Determines whether the most recent log backup terminates in the given time.
• Storage snapshot is prepared: Determines whether the period during which the database is prepared for a storage snapshot exceeds a given threshold.
• Enablement of automatic log backup: Determines whether automatic log backup is enabled.
• Number of log segments: Determines the number of log segments in the log volume of each service.

Figure 8 You can define values for the priority of the alerts by choosing the Configure… button and (in the screen that opens, not shown) clicking the Configure Check Thresholds tab. That displays Figure 9, which contains some backup operation-related alerts’ priority threshold definitions. For example, if the ages of the most recent data backups are 5 days, 7 days, and 20 days, the priority reported in the alert is low, medium, and high respectively. Furthermore, you can also access current alerts in the Overview tab as shown in Figure 10. Click the Show Alerts link to display the details of the messages.

Exclude Backup Technical Users from a SQL Statement Memory Limit

In the Configuration tab, it is possible to set a SQL statement memory limit for a single SQL statement in order to improve the performance of the SAP HANA system. Memory limitation for a single SQL statement is configured via the global.ini configuration file using the parameter statement_memory_limit in the memorymanager section (Figure 11). When a limit is configured for single SQL statements, it is important to test the settings properly before deploying the settings in the production system. This is because statement executions that would require more memory than the configured limit are aborted when they reach the limit. Consequently, a dump file is created with compositelimit_oom in the name. This can adversely impact database operations, including backup and restore activities. More importantly, the technical users used to perform backup activities should be exempted from this SQL statement memory limitation setting to forestall possible issues associated with important system operations such as backup and restore. This can be configured via the SQL editor by executing the statement: ALTER USER <user_name> SET PARAMETER STATEMENT MEMORY LIMIT = <gb> as shown in Figure 12 for user AIDENX. Setting the statement memory limit to 0 disables any statement memory limit for the user. The STATEMENT MEMORY LIMIT parameter is displayed in the User Parameters tab (like all other user parameters) as shown in Figure 13. To go to Figure 13 double-click the user ID and follow menu path Security > Users in SAP HANA studio. The system automatically populates the user parameters. The setting in the Configuration tab is at the system level, while the setting made via the SQL statement is at the user level.

Restrict the Use of the System User

The system user is a very powerful user in the SAP HANA database as it is assigned excessive authorizations by default and can be used to perform critical database operations, including database backup and restore and deletion of backups. More so, in an environment with multi-tenant databases, the system user can be used for database operations across all the database tenants. Therefore, the use of the system user should be controlled as much as possible. It should not be used for day-to-day system administration functions. Instead, dedicated technical users should be created and assigned applicable system privileges needed to perform backup and restore operations. The system user should be deactivated when not needed for critical administrative activities. My article “A Guide to Passing an SAP HANA System Security Audit” provides more detail about how to secure the system user.  

Enforce Control in the Assignment of Backup- and Restore-Related System Authorizations

The SAP HANA system offers different tools to manage backup operations. Depending on the backup tool used, specific authorizations in the form of roles or privileges need to be assigned to the responsible user. When using SAP HANA studio or SQL commands for backup operations, the applicable system privileges include CATALOG READ, BACKUP ADMIN, and BACKUP OPERATOR. To use the web-based interface SAP HANA backup component of the SAP HANA Extended Services (XS) application for backup operations, roles sap.hana.backup.roles::Operator or sap.hana.backup.roles::Administrator are required. The assignment of these authorizations needs to be restricted and only assigned on a need-to-have basis to avoid possible abuse and perpetration of malicious activities. Consider segregating the functions within the backup and restore operations. For example, if you have a script used to schedule the regular execution of backups using cron (at the Linux operating system level), a user with the BACKUP OPERATOR authorization only should be used because this enforces control in the backup operation. It prevents backups from being deleted inadvertently.

Regularly Ship Backup Files to a Remote Location

Backup files should be shipped to a remote location at defined intervals using agreed-upon shipping methods. The shipping method can be via courier or over the network. This is to ensure that the business can react appropriately to disasters, especially when the physical location of the system is destroyed. With the backup in a remote location, it can be retrieved and restored accordingly. The frequency of the shipping and the method adopted go a long way to determining the amount of data that will be lost in the event of data disaster. Hence, due diligence and business consultation should be performed in making a decision.

Enable Automatic Log Backup and Configure the Log Mode Optimally

To ensure that the log backup file area is not filled up uncontrollably, ensure that automatic log backup is activated. This can be done via the backup administration console window (in the Configuration tab) as shown in Figure 14. Double-click the Backup subnode under the SID node and in the screen that displays, choose the Configuration tab. Place a checkmark in the Enable Automatic Log Backup check box. After you make your settings, click the highlighted save icon. You can also maintain the configuration parameter enable_auto_log_backup as yes in the persistence section of the global.ini file as shown in Figure 15. Double-click the SID node. In the screen that displays, navigate to the Configuration tab, filter the parameter enable_auto_log_backup. The default is yes. In the event that you have the system or host set to no, double-click the entry (Figure 16). Change no to yes (Figure 17). Click the save icon. Furthermore, the setting of the log_mode to normal can be done in the persistence section of the global.ini configuration file in the Configuration tab of SAP HANA studio as shown in Figure 18. Setting the log mode to normal ensures that log segments are backed up and not freed following a savepoint event. The parameter log_backup_timeout_s also controls when an automated log backup occurs. Therefore, the value for this parameter should be set appropriately based on the related business requirement. The setting of the log backup timeout parameter can be done in the persistence section of the global.ini configuration file in the Configuration tab of SAP HANA studio as shown in Figure 19.

Back Up Customer-Specific Configuration Settings

Custom configuration settings can be made to the SAP HANA database via the maintenance of the *.ini configuration files, such as global.ini and indeexserver.ini. The custom settings made to the configuration file are written to the specific operating system directory depending on whether the setting is global or host-specific, as detailed below:

• /usr/sap/<SID>/SYS/global/hdb/custom/config – global custom settings
• /usr/sap/<SID>/HDB<instance number>/<hostname> - host specific custom settings.

Figure 20 shows an example of the directory in which the global custom setting is stored at the operating system level, with an example of the content of the indexserver.ini file (inset). Figure 21 shows the corresponding values in the front end (configuration editor) under the system column for the indexserver.ini parameters. Support is not provided for the backup of customer-specific configuration settings with the standard backup of the SAP HANA database. This information is displayed when you initiate a backup via SAP HANA studio as shown in Figure 22. As custom changes made to the configuration settings can impact the behavior of the database system and, consequently the applications running on it, it is important to have a strategy to back up and restore such custom settings independent of the backup of the database. Since these are physical files, I expect organizations will have a tool in place to back them up.

Leverage Shared Storage Management for File-Based Backup

If you use file-based backup, it is important to ensure that the backup destination is accessible across the entire system landscape. Shared storage provides access to the backup area for all nodes in a database. More importantly, the shared-storage architecture facilitates the master node to perform an availability check when a recovery process is initiated. This availability check for recovery-centric metadata is important so that any potential issues are identified and fixed before the actual recovery process is started. The check is performed automatically by the system.

Be Mindful of the SAP HANA Version When Planning Backup and Restore Operations

More times than not, it is possible to operate the SAP HANA database in an environment with multiple versions. Aside from having a clear-cut strategy to manage the associated complexities such an environment presents, knowledge of the preconditions for backup and restore is imperative. The rule of thumb when performing backup and recovery operations with an SAP HANA database is that the version used for the recovery must always be the same version or higher than the database version used to create the backup. Suffice to say that an SAP HANA database cannot be recovered to an SAP HANA database with a lower software version. The business must be abreast of the implications of performing backup and restore operations in a multi-version SAP HANA database environment. For example, if the backup of the source database was created using SAP HANA lower than revision 45, the backup catalog for the source database must be rebuilt as detailed in SAP Note 1812980 (Changes to Backup Catalog with Revision 45).

Avoid Interrupting a Database Recovery Operation

An SAP HANA database does not support suspending a database recovery operation and resuming it later. On the other hand, a database recovery can be cancelled. However, a recovery still needs to be performed to bring the system into a useable state. This is because following the interruption of the recovery operation the system cannot be restarted successfully. Refrain as much as possible from cancelling a recovery operation as it is likely to cause database corruption and inconsistencies.

Leverage a Meaningful and Consistent Backup Naming Convention

A consistent and meaningful naming convention must be adopted for data-backup file names. This goes a long way to help with ease of identification and version management. Unlike data backup, log backup names are defined automatically by the system. More importantly, you need to have a unique name prefix for backups, especially when you use a file-based backup to prevent the system from overwriting the previously backed-up files. You can use the time stamp as the prefix for backup names as that allows the uniqueness attribute to be satisfied easily. Moreover, overwriting backups can have adverse space implications on the backup directory. This is because when a backup file is to be overwritten, it only is overwritten after the new backup completes successfully. That implies that twice as much space is required for the old backup and the new backup simultaneously, at least for the particular period of time of the backup session. While the issue of overwriting is not prevalent when backing up using the SAP HANA API as it is able to identify multiple versions of backup, SAP highly recommends the adoption of unique backup names. The system is able to identify it internally, but the administrator will also want to identify the version physically, the same as for the file-based backup. Furthermore, refrain from changing backup file names. This is because when a backup is created, the backup name is registered in the backup catalog and the details are retrieved when a recovery operation is initiated from the backup catalog. Therefore, if the backup name is changed, it becomes impossible to find the details of the backup via the backup catalog. That consequently makes the backup unusable.

Effectively Manage Delta Backups

With SAP HANA Support Package Stack 10, support is now provided for delta backups—incremental and differential. An incremental backup saves the data changed since the last data backup, while differential backups save all the data changed since the last full data backup. Delta backups enable you to optimize your backup strategy—for example, by less frequently executing the more time-consuming and resource-intensive full data backups. When compared with log backups, a delta backup reduces the time needed for a recovery because the number of logs that need to be replayed is reduced. When a delta backup is implemented, delta backups must be in the same location as the data backups on which they are based to ensure the use of the delta backup for successful recovery.

Perform Capacity Planning for the Backup Location

If the disk space where the backup files are stored is filled up, this can cause the database to hang and become inoperable. Therefore, it is important to monitor disk use of the data and log backup volumes. This can be done via SAP HANA studio in the Overview tab as shown in Figure 23. Double-click the SID node and in the screen that displays, navigate to the Overview tab. It is only showing the total disk size and disk usage for data volume and log volume for the purpose of monitoring space availability. You do not need to make any entries in the screen. It is only showing the total disk size and disk usage for data volume and log volume for the purpose of monitoring space availability. The system information (Overview tab) provides an interface to monitor the used disk space for data and log volumes. This is an important activity for preventing log full situations. Ideally, the color of the screen should be green, indicating the log is not close to being full. If you see yellow, which is a warning, or red, which is critical, then consider reorganizing the unnecessary log files and, more importantly, ascertaining whether the last backup run was successful, as it might have impacted the backup operation. If it is red, the database will hang and might not start anymore. Follow the instruction in SAP Note 1679938 to get the database back online. Performing appropriate capacity planning of the backup directory is essential because a backup can fail if disk space is not sufficient. Therefore, it is a good practice to estimate the size of the data backup file before a backup operation to avoid unpleasant surprises. This can be done by querying the total size (bytes) of currently allocated pages using the M_CONVERTER_STATISTICS view, as shown in Figure 24. It returns a single value as the estimated data backup file size. Choose the SQL editor icon and enter the SQL statement and choose execute. The output displays in the Result tab. It is helpful to have an idea of the average database size as part of capacity planning.

Avoid Deleting Obsolete Backup Files Manually

As part of the backup-management process and housekeeping activities, it is possible to delete old backup files that are no longer needed for a recovery operation. This frees up the space used to store obsolete backup files. The deletion of backup files should not be done manually as this can lead to issues associated with database inconsistency. Instead, the backup console (Backup Catalog tab) should be used to delete unwanted database backup. Follow the procedure shown in Figure 25 to delete obsolete database backups. Access the backup console and navigate to the Backup Catalog tab, which shows the deletion options after you right-click in the window. Double-click the Backup subnode under the SID node. In the screen that displays, choose the Backup Catalog tab. Once the window opens, the first entry is highlighted by default. You can highlight a backup for the purpose of deleting that backup (Delete Data Backup… option) or deleting older backups based on the highlighted backup (Delete Older Backups…. option). Click the Delete Older Backups… option, for example, and Figure 26 displays with the backup deletion settings. Choose the Catalog and Backup Location radio button and then click the Finish button to confirm the deletion. I just chose anyone for the purpose of the article.  The difference is detailed below:

• Catalog only: The backups are deleted from the backup catalog only.
• Catalog and backup location: The backups are deleted from the backup catalog and physically from the backup location.

It is a best practice to ensure that you have performed a recent and accessible backup before deleting old backups so that you have a usable backup at any point in time. If you delete all your backups without having a useable backup, it means you will not have a backup to go back to in case you have issues backing up the database after the last successful backup.

Perform Manual Integrity Checks on Backup Files

The system automatically performs a verification check on backup files. However, backup files might be susceptible to damage when they are moved from their original location to another location. With SAP HANA studio, the integrity of the backup is automatically checked when a recovery operation is started. If an error is detected, the recovery is stopped and needs to be repeated. A backup that cannot be used for a recovery operation is as good as useless. The metadata and composition of a backup file can be altered, especially when moved from the database to a backup storage media. Therefore, it is a best practice to carry out an integrity check on each part of the SAP HANA instance backup files. This integrity verification check can be performed using the hdbbackupcheck tool. This tool is used to check for possible corruption in data or log backups. For detailed information on the use of the hdbabackupcheck tool, consult SAP Note 1869119.

Perform Backup File Availability and Accessibility Checks

Performing a backup of the SAP HANA database is not sufficient to guarantee the recoverability of the database. It is important that the database be available and accessible when the need arises for recovery. The hdbbackupdiag tool can be used to check if the required data and log backups are available and undamaged. Note that the hdbbackupdiag tool does not perform an integrity check on the backup files, but only checks the metadata. For detailed information on the use of the hdbbackupdialog tool, consult SAP Note 1873247.

Manage SAP HANA Licensing Effectively

A valid license is required in order to use the SAP HANA system productively. An SAP HANA database is licensed based on the hardware key and the SID as shown in Figure 27. If the SID of the SAP HANA system changes following a system recovery operation, then a new license needs to be installed to avoid possible disruption to the business. It is possible to use the SAP HANA system for 90 days without a productive license if the backup used for the restore operation has a permanent license. However, the system is locked immediately after recovery if the backup used for the recovery has a temporary license.

Activate Auditing for the Deletion of Backup Catalog Entries

Auditing provides a framework to review who did what and when. This is important in holding individuals responsible for their activities. The information can also help in troubleshooting and providing guidance for corrective actions. Critical activities that can impact database operation should be audited. An example of such activity is the deletion of database backups. Auditing can be activated via the security editor (Auditing Status) in SAP HANA studio as shown in Figure 28. To do so, select Enabled in the Auditing Status field. In the Audited Actions column in the Audit Policies section, select the value BACKUP CATALOG DELETE to track who deletes backups. Click the highlighted save icon.

Avoid the Use of the Common Internet File System (CIFS)

SAP HANA supports different network file systems. The Network File System (NFS) is a client/server application that allows a computer user to display, store, and update files on a remote computer as though they were on the user’s own computer. This concept allows you to create backup files directly in a remote area that is available within the network by using network file systems. When backing up the SAP HANA database to an external file shared server, SAP recommends that you use the NFS. SAP Note 1820529 contains the list of NFSs that have posed access problems when used to save SAP HANA data and log backups, and it specifically mentions the CIFS. Additionally, this SAP SCN post (https://scn.sap.com/thread/3463075) also identifies the New Technology File System as a problematic file system as it relates to saving SAP HANA databases on external shared file servers.

Monitor the Availability of SAP HANA Services

It is important to have proper processes and procedures in place to monitor the availability of the services of the SAP HANA system at any point in time. This is important in respect to database backup because the system must not be down for a backup operation to be initiated (unlike a recovery operation). You cannot back up a database that is down. Therefore, all configured services must be active before a backup session can be started. The status of the services can be reviewed via the Landscape tab of system administration as shown in Figure 29. Double-click the SID node and in the screen that displays, navigate to the Landscape tab. The configured services typically include daemon, indexserver, nameserver, and statisticsserver. You can also execute the SQL statement SELECT HOST, SERVICE_NAME, ACTIVE_STATUS FROM M_SERVICES S to obtain the status of the services of the SAP HANA database, as shown in Figure 30.