SAP HANA Administration - Quick Guide

---

SAP HANA Admin - Overview

SAP HANA is an in-memory database for performing real-time data analysis, and development of applications on the top of real-time data. HANA administration deals with managing SAP HANA system in a single and distributed system environment. Each HANA system can contain multi node architecture with each node containing multiple processors for fast speed data analysis and real-time data provisioning. You can use Smart data access to get the data from non-SAP systems without importing the data to HANA database, and virtual tables can be used to perform read/write data operations

SAP HANA Administration includes the following activities −

• SAP HANA multiple host system management
• SAP HANA Administration Tools
• SAP HANA System Management and Availability
• SAP HANA Lifecycle Management
• SAP HANA Security and User Management
• SAP HANA Backup and Recovery Management
• SAP HANA Data Provisioning and Integration with non-SAP systems

Each SAP system contains multiple servers and it can be checked in HANA Studio under Administration tab. SAP HANA Studio contains Administration Perspective (default) to manage all admin tasks in HANA systems.

To check the landscape of HANA system, navigate to Landscape tab in HANA studio. Services shows all the services running on HANA system.

The following screenshot shows SAP HANA system and all the key services running under system Landscape for the same system.

You can see the service name corresponding to each server in HANA system landscape.

From the dropdown list, you can select all the host names and services that you want to see and the status of each system.

Various details available for each service includes: Start time, CPU and Memory details, Used Memory, Peak Used Memory, Effective Allocation Unit, Physical Memory on host, and SQL Port.

When you click the Hosts tab, you can see the host names in the System Landscape and the role of HANA system defined under Name server.

You can also check the status of the systems, failover status, Index server role, and Failover group.

SAP HANA Admin - Architecture Overview

In SAP HANA system architecture, it contains all the server components that gets installed at the time of installation. Each server has defined set of tasks and different services are run for each server in a SAP HANA distributed environment.

The most important server system and the heart of HANA system is the Index server. This server is responsible for processing SQL/MDX statement using engines in the Index server. It also contains Session and Transaction Manager to keep track of completed and running transactions.

Persistence Layer in the Index server is used for backing up data and transaction of log files.

Following table lists all the key server components in HANA, the corresponding services, and OS process details.

Server Component Name	Service Name in HANA system	OS Process Name	Component Details
Index Server	Indexserver	hdbindexserver	Responsible for processing SQL statements using SQL/MDX Processors.
Name Server	nameserver	hdbnameserver	To maintain topology of SAP HANA system. This is used to manage all the running components and data stored on each component.
XS Classic Server	xsengine	hdbxsengine	It can be used to develop and host applications on the top of SAP HANA system.
Data Provisioning Server	dpserver	hdbdpserver	To provide SAP HANA smart data access function.
Preprocessor	preprocessor	hdbpreprocessor	To analyze the text data and perform search capabilities.
Compile Server	Compileserver	hdbcompileserver	This is used to perform compilation of SQL Procedures and doesn’t contain any data.
Web Dispatcher	Webdispatcher	hdbwebdispacher	It is used to process HTTP/HTTPS request to HANA XS Engine.

SAP HANA Admin - Multitenant Database

It is possible to set up multiple databases into a single HANA system. When you install SAP HANA system in multiple container mode, you can setup multiple isolated databases in single SAP HANA system. This is called Multitenant database containers.

You can also covert a single container HANA system to multiple container databases HANA system. In multiple container HANA system, each database runs on the same infrastructure and uses the same computing resources. However, each database contains following components −

• Database Users
• Repository
• Catalog
• Backups
• Traces and Logs
• Persistence

All the database objects - tables, schemas, database views, SQL procedures - are separate for each database. You can also perform cross-database functions in multitenant database.

In SAP HANA system, it contains multiple server - Name Server, Index Server, Preprocessor, and XS Server. With multiple container databases, different combinations of the above servers are used.

Name server maintains the landscape details of the system and the system databases run the Name server. The Name server of multitenant databases doesn’t contain topology information and this information is stored in tenant database catalog.

Preprocessor and Compile server runs on system databases as they don’t contain persist data. Each tenant database contain its own index server and SAP HANA Classic Server runs on the Index server of tenant database by default.

You can see the architecture of SAP HANA multiple container system in the following screenshot. Each instance of system database runs on multiple hosts to provide scalability and system availability. In multiple worker configuration, when a system fails, standby instance will fail over all active databases.

In the above screenshot, you can see 2 databases distributed across three hosts in multiple container database system.

In multiple container architecture, the system database is set up at the time of installation of a multiple-container system or when you convert a single container system to multiple container system. This is used to manage information about the system as a whole, and also for tenant databases. It is used for Central System Administration in multitenant environment.

System database contains data and user data for system administration and SAP HANA Administration tools such as SAP HANA Studio to connect to the system database. All the administration tasks that are performed in the system database applies to the whole system and you can also target the configuration for a specific tenant database. System database contains complete landscape information about Tenant components, however it doesn’t contain topology related information.

SAP HANA Admin - Multiple Host Systems

When your SAP HANA system is installed on a single system, it is known as Single Host System; however, when you distribute the system installation on multiple systems, it is called Multiple Host Systems. Using multiple host systems, you can use features such as Load Balancing and Scale Out feature of SAP HANA system. You can overcome the hardware limitations by distributing the load of your system on multiple servers.

You can also assign different tables to different hosts using database partition to split a single table between hosts (partitioning of tables), and to replicate tables to multiple hosts.

When SAP HANA system is installed on multiple hosts, SAP HANA Administrator can perform start and stop, backup, or run update as a single system by taking single System ID known as SID.

Each SAP HANA system contains its own server components for each host and the request from the client application can be distributed to different hosts in multiple host systems.

In the above screenshot, you can see SAP HANA multiple host system architecture, where HANA system has 3 separate instances, and each instance has its own server components.

The request from the client application can be distributed to any of the instance in this environment.

SAP HANA Admin - Tools

There are various tools that can be used to perform administration tasks in single container or multiple container system. All the tools are used to perform database level administration and the system level administration can be performed using SAP HANA Cockpit.

Following are common HANA Administration Tools −

SAP HANA Cockpit

This is an administration tool to manage your SAP HANA system and is based on SAP Fiori Launchpad based navigation to access long range of HANA applications. SAP HANA cockpit can be accessed via a web browser.

SAP HANA Studio

Using SAP HANA Studio, you can perform system administration and monitoring activities in a distributed environment. You can create users with different access privilege, backup and recovery, and data provisioning using HANA Studio.

HANA Studio is an Eclipse-based tool and is available for Window, Mac, and Unix OS.

SAP HANA Lifecycle Manager

This is an administration tool to manage SAP HANA components after installation. You can add/remove hosts or rename using the Lifecycle Manager.

SAP Solution Manager

This tool is used to integrate your SAP HANA platform with other business solutions.

SAP HANA HW Configuration Check

You can use this tool to check the interoperability of SAP HANA system with enterprise storage.

SAP HANA XS Administration Tools

This tool is used to manage and administration applications developed on the top of HANA database sing XS Classic model and XS Advanced model. You can perform security and define user authentication for applications developed and hosted in SAP HANA environment.

SAP HANA Application Lifecycle Management

This tool is used to perform the transport of delivery units, upload or download transports and to perform transport management tasks.

SAP HANA Admin - Cockpit

This is an administration tool to manage your SAP HANA system and is based on SAP Fiori Launchpad based navigation to access long range of HANA applications. SAP HANA cockpit can be accessed via a web browser.

You can also use SAP HANA Cockpit in an offline mode to perform administration tasks - starting HANA system to fix performance-related issues.

SAP HANA Cockpit with Fiori-based Launchpad shows the content in the form of tiles arranged in groups. Using these tiles, you can access individual applications and can also access app-specific data for immediate review.

You can also perform a drill on these tiles to see the detailed information about specific applications.

Following roles are required to open and access tile-based SAP HANA Cockpit −

sap.hana.admin.roles:: Monitoring or sap.hana.admin.roles:: Administrator

To open SAP HANA Cockpit via a web browser, you need to have browser support SAPUI5 library sap.m.

You can also open SAP HANA Cockpit via HANA Studio. Navigate on HANA system → Configuration and Monitoring → Open SAP HANA Cockpit.

A single-container system is accessed through the following URLs −

https://<host_FQDN>:43<instance>/sap/hana/admin/cockpit

https://<host_FQDN>:80<instance>/sap/hana/admin/cockpit

Host name is SAP HANA system name and Instance number is SAP HANA system Instance number.

SAP HANA Cockpit for Offline Administration

You can also open HANA Cockpit in an offline mode using a web browser or via standard SAP HANA Cockpit tool. This is used to perform administration tasks - starting SAP HANA system, other offline administration activities.

To login to SAP HANA Cockpit for offline administration, following perquisites should be met −

• You need to have credentials for Operating System user <sid> admuser created at the time of installation.

• Port 1129 should be open for communication.

• You need to have browser support SAPUI5 library sap.m.

There are two ways to open SAP HANA Cockpit in an offline mode −

Using the Web Browser

Enter the following URL in the web browser.

https://<host>:1129/lmsl/hdbcockpit/<sid>/index.html

You can directly enter the above URL in the web browser and it opens SAP HANA Cockpit for offline administration. You have to authenticate via Operating system user <sid> admuser.

This method is not recommended as it transfers the password in plain text via HTTP.

Using the Standard SAP HANA Cockpit

You can open SAP HANA cockpit by entering the following URL.

https://<host_FQDN>:43<instance>/sap/hana/admin/cockpit

Navigate to SAP HANA Database Administration group → Select SAP HANA Cockpit for offline administration as shown in the 2nd last box of the following screenshot.

There are various options under SAP HANA Cockpit for offline mode, when you click on the options as shown in the following screenshot −

Start, Restart and Stop

This tile shows the status of your SAP HANA system. Following are the most common status messages you can see −

• Running
• Stopping
• Stopped
• Initializing

When you click on this tile, it allows you to start, stop, and restart a server.

Diagnosis Files

This is used to open the log files, trace results and other diagnosis files. You can use this tile to get the diagnosis files in zip format and it can be attached to support messages.

It is also possible to search diagnosis files to find specific words and phrases.

Troubleshoot Unresponsive System

This tile is used to access the information required for troubleshooting performance issues.

SAP HANA Documentation Tile

Using this tile, you can see the documentation of all the administrative tasks that you can perform in SAP HANA Cockpit for offline administration.

SAP HANA Cockpit

This tile is used to access the main window where you can access all applications for performing SAP HANA administration online. In case of system replication, this option is only available with primary system.

Note − In multitenant database containers, when you use Start option under SAP HANA Cockpit offline administration → Star, Restart, Stop tile → Footer bar, Start System - this options restarts all the tenant database systems.

When you stop a system, the status of the system under Start, Restart, and Stop tile is changed to Stopped (red) as seen in the following screenshot.

SAP HANA Admin - Studio

SAP HANA Studio is an Eclipse-based tool. SAP HANA Studio is both, the central development environment and the main administration tool for HANA system. Additional features are −

• It is a client tool, which can be used to access local or remote HANA system.

• It provides an environment for HANA Administration, HANA Information Modeling, and Data Provisioning in HANA database.

There are various administration tasks that can be performed using SAP HANA Studio −

• To start and stop service
• To perform monitoring of SAP HANA system
• To perform user management and authorization
• To perform backup and recovery
• To perform Audit policy and Security configuration
• To perform license management
• To perform other configurations in HANA system
• To perform SQL development tasks - HANA Information Models, SQL Stored Procedures

In SAP HANA Studio, you will find a long list of perspectives, which can be considered as separate tools in HANA Studio. All these perspectives can be used for performing various administrative tasks, system monitoring, and data provisioning.

To see list of all Perspectives in HANA Studio, navigate to Window tab → Perspective → Open Perspective → Other.

When you click on “Other…”, it will open the list of all the perspectives that can be used to manage and perform system administration.

SAP HANA Administration Console in HANA Studio

Using this feature, you can open HANA database administration and monitoring features in HANA Studio. By default, it opens in System View.

To open SAP HANA Administration, you can select SAP HANA Administration Console Perspective default option. You can also access it via Administration button at the top.

When you click the Administration button as shown in the following screenshot, it opens SAP HANA Administration Console. The console contains all database administration and monitoring features of the SAP HANA studio.

Following tabs are available under SAP HANA Administration Console −

• Overview
• Landscape
• Alerts
• Performance
• Volumes
• Configuration
• System Information
• Diagnosis Files
• Trace Configuration

These tabs are used to perform monitoring and administration tasks in your SAP HANA system.

Adding a System in HANA Studio

Single or multiple systems can be added to HANA Studio for administration and information modeling purposes. To add new HANA system, host name, instance number and database user name and password is required.

• Port 3615 should be open to connect to Database
• Port 31015 Instance No 10
• Port 30015 Instance No 00
• SSh port should also be open

Following are the steps to add a system to HANA Studio.

Step 1 − Right-click the Navigator space and click Add System. Enter HANA system details, i.e. Host name & Instance number and click Next.

Step 2 − Enter Database user name and password to connect to SAP HANA database. Click Next and then Finish.

Once you click on Finish, HANA system will be added to System View for administration and modeling purposes. You can see the hierarchical structure of HANA system under HANA Studio.

Each HANA system has two main sub-nodes, Catalog and Content.

Catalog Tab − It contains all available Schemas, i.e. all data structures, tables and data, column views, procedures that can be used in the Content tab.

Content Tab − The Content tab contains design time repository, which holds all information of data models created with the HANA Modeler. These models are organized in Packages. The content node provides different views on the same physical data.

Executing SQL Statements in SAP HANA Studio

SQL Console can be opened by selecting the Schema name, in which, a new table has to be created using System View SQL Editor option or by a right-clicking the Schema name as shown in the following screenshot.

Once SQL Editor is opened, Schema name can be confirmed from the name written on the top of SQL Editor. You can create database objects using SQL statement.

To create a table −

Create column Table Test1 ( 
   ID INTEGER, 
   NAME VARCHAR(10), 
   PRIMARY KEY (ID) 
);

In this SQL statement, we have created a Column table “Test1”, defined data types of the table and the Primary Key.

Once you write Create table SQL query, click the Execute option on top of SQL editor on the right side of the screen. Once the statement is executed, we will get a confirmation message as follows −

Statement 'Create column Table Test1 (ID INTEGER,NAME VARCHAR(10), PRIMARY KEY (ID))' successfully executed in 13 ms 761 μs (server processing time: 12 ms 979 μs) − Rows Affected: 0

Insert statement is used to enter the data in the Table using SQL editor.

Insert into TEST1 Values (1,'ABCD')

Insert into TEST1 Values (2,'EFGH');

Click Execute.

You can right-click the Table name and use Open Data Definition to see the data type of the table. Open Data Preview/Open Content to see the table contents.

Using Log Off /Log On in SAP HANA Studio

Using SAP HANA Studio, you can also log off from a SAP HANA system and end all the connections. To login to HANA system again, click Logon.

To logoff from a system, right-click the System name in HANA Studio → Log Off.

To login again, right-click on HANA system in System Pane → Log On. You will be prompted to enter the password. Enter the password and click OK. You will be logged in to HANA system under HANA Studio. You can view all the folders and objects in HANA system.

You can also define the user logon behavior in SAP HANA Studio startup. It allows you to save the password for the users when HANA Studio or other user settings is open.

Go to Preferences → SAP HANA → Global Settings.

If you wish automatic logon takes place when the Studio is started and also to allow explicit logon when HANA studio starts, uncheck the first option.

SAP HANA Admin - System Management

When SAP HANA system is installed, there are various components that get installed with HANA system. It includes −

Mandatory Components of SAP HANA −

• SAP HANA Server
• SAP HANA Client system

Additional Components −

• SAP HANA Studio
• SAP HANA Smart Data Access SDA
• SAP HANA XS Runtime environment
• SAP Live Cache Applications

When the installation is complete, you may need to perform various tasks in SAP HANA system, such as −

• Backup and Recovery Management
• User Management
• Starting HANA System
• Stopping HANA System
• License Keys
• System INI Files
• Monitoring HANA System
• Data Replication using SMART data access
• SAP HANA integration with BusinessObjects Reporting environment

These tasks can be performed using various SAP HANA admin tools. Most common tools to perform SAP HANA system management - HANA Cockpit, SAP HANA Studio.

Multitenant DB Container Management

SAP HANA system can be configured in a single container database system or multiple container system. To set up tenant databases, your system should be configured in multiple container mode. You can also convert a single container mode to multiple container mode, before you create and configure tenant database.

When SAP HANA system is installed in multiple container mode, only the system database is created initially. Tenant databases are created by the administrator and then later it can be configured.

You can convert a SAP HANA system to support multitenant database containers using the SAP HANA Database Lifecycle Manager (HDBLCM) resident program in the graphical user interface.

When you convert a Single container system to multitenant database container using HDBLCM, it can’t be reversed.

You can open SAP HANA database lifecycle Manager using the following URL in a web browser.

https://hostname:1129/lmsl/HDBLCM/HDB/index.html

You can also open this browser from SAP HANA Studio. Right-click HANA system → Lifecycle Management → Platform Lifecycle Management → SAP HANA Platform Lifecycle Management.

You can also perform conversion of single container to multitenant database system using SAP HANA Cockpit. Navigate to SAP HANA Platform Lifecycle Manager.

Click Convert to Multitenant Database Containers as shown in the following screenshot.

Creating a Tenant Database

You can create a tenant database in SAP HANA multiple container system using SAP HANA cockpit. A tenant database can be created from the system database as and when it is required. A tenant database contains all the data - including users, configuration, and connection properties of the original system.

Step 1 − To create a tenant database, you need to navigate to Manage Database app of SAP HANA Cockpit.

Step 2 − To access this tile in SAP HANA Cockpit, you must have the following role assigned: sap.hana.admin.cockpit.sysdb.roles::SysDBAdmin

Step 3 − In the footer toolbar, you have to navigate to Overflow menu → Create Tenant Database.

Step 4 − Enter the name of tenant database and the system user password. You can also specify OS user and group of tenant database.

You can select various optional fields while creating tenant database, such as creating OS user or to add tenant database to a group and many more.

Step 5 − Once you complete the wizard, click Create Tenant Database and it may take some time to complete the creation process.

New database that has been created has been added to manage database app in SAP HANA cockpit. You can also check newly created tenant database using database view command −

SELECT * FROM "PUBLIC"."M_DATABASES 

A HANA database administrator can start or stop the tenant databases either individually or all in one go, by starting/stopping the whole system. A tenant database which is individually stopped can’t be started with the whole system and you need to start it individually.

Select the tenant database that you want to start and stop under manage database app in HANA Cockpit. Click Start Tenant database/Stop tenant database to perform a start and stop.

SAP HANA Admin - Starting a HANA System

In a distributed environment, it is required to perform the start of HANA systems. It could be after a maintenance task, backup, and recovery, or some other reason. A start of single system or multiple systems can be performed using SAP HANA cockpit Offline Administration or using SAP HANA Studio.

Start a System Using HANA Cockpit

Navigate to SAP HANA Database Administration → SAP HANA Cockpit for Offline Administration.

Navigate to Open the System Operations app by clicking the Start, Stop, Restart tile on the homepage of the SAP HANA cockpit for offline administration.

At the bottom, you have an option to Start the System. When you click on Start System, database services start one by one. In case your environment contains multitenant database containers, it starts the services of all tenant databases.

The status of the system shows “Running” when all the services are successfully started.

SAP HANA Admin - Stopping a HANA System

You can stop all SAP HANA systems using SAP HANA Cockpit for offline administration option in HANA Cockpit. In multitenant database container system, all tenant databases will be stopped using this option.

When all the services are stopped one by one for all the databases, it shows the status of system as “Stopped” as shown in the following screenshot.

You can also use the SAP HANA Cockpit for offline administration to restart SAP HANA system. In the multitenant database environment, when you select Restart from the footer bar, it also restarts all the tenant databases.

Start and Stop a System in HANA Studio

To perform a start/stop of a single HANA system or in multiple container system, you can also use SAP HANA Studio. All the tenant databases which are stopped individually has to start separately in HANA Studio.

Stopping a system is required in certain scenarios, when you have to make certain configuration changes, maintenance, patch upgrade, or other scenarios.

To start a system, navigate to System view and right-click HANA system that you want to start and stop → Configuration and Monitoring → Start System.

SAP HANA Admin - License Keys

SAP HANA License management and keys are required to use HANA database. You can install or delete HANA License keys using HANA studio.

Types of License keys

SAP HANA system supports two types of License keys −

Temporary License Key

Temporary License keys are automatically installed when you install the HANA database. These keys are valid only for 90 days and you should request permanent license keys from SAP marketplace before the expiry of 90 days period after installation.

Permanent License Key

Permanent License keys are valid only till the predefined expiration date. License keys specify the amount of memory licensed to target HANA installation. They can be installed from SAP Marketplace under Keys and Requests tab. When a permanent License key expires, a temporary license key is issued, which is valid for only 28 days. During this period, you have to install a permanent License key again.

There are two types of permanent License keys for HANA system −

• Unenforced − If unenforced license key is installed and consumption of HANA system exceeds the license amount of memory, the operation of SAP HANA is not affected in this case.

• Enforced − If Enforced license key is installed and consumption of HANA system exceeds the license amount of memory, HANA system gets locked. If this situation occurs, HANA system has to be restarted or a new license key should be requested and installed.

There are different License scenarios that can be used in HANA system depending on the landscape of the system (Standalone, HANA Cloud, BW on HANA, etc.) and not all of these models are based on the memory of HANA system installation.

SAP HANA System Lockdown

There are various scenarios when your SAP HANA system locks down. This happens when a License key expires or license keys are deleted.

When a system is locked, it is not possible to run any transactions by connecting to the database. To renew license keys/unlock the system, only HANA user with License Admin system privilege can connect to HANA database.

Following are the scenarios which can cause the lockdown of SAP HANA system −

• When hardware license key changes and Temporary license key is used in SAP HANA system.

• Temporary License key expires in HANA system.

• All license keys installed in HANA database are deleted.

• SAP HANA system permanent license key expires and has not been renewed in the grace time period of 28 days.

• You are using Enforced License key and your memory consumption exceeds the licensed amount and tolerance limit assigned.

Note − It is not possible to take a backup of HANA system when the system is locked down.

How to Check and Install License Keys of HANA?

The role required to install/delete an existing License Key: LICENSE ADMIN

To check and install a new License key, navigate and right-click HANA system → Properties → License.

It tells about License type, Start Date and Expiration Date, Memory Allocation and the information (Hardware Key, System Id) that is required to request a new license through SAP Marketplace.

Now to renew/install a new License key, navigate to Install License key → Browse → Enter Path, is used to install a new License key. The Delete option is used to delete any old expiration key.

All Licenses tab under License tells about the Product name, description, Hardware key, First installation time, etc.

To delete the license key, you can use SAP HANA Studio or it can also be performed using SQL console. Deleting a license key is required in various scenarios such as you have installed permanent license keys with an incorrect installation number or incorrect system number on the database.

To delete a license key, right-click HANA system → Properties → License → Delete License Key.

You can also execute the following SQL command to delete all license keys in HANA database that results in a system lockdown.

UNSET SYSTEM LICENSE ALL

Monitoring the HANA System

System Monitor in HANA Studio provides an overview of all your HANA system at a glance. From System Monitor, you can drill down into the details of an individual system in the Administration Editor. It tells about Data disk, Log disk, Trace disk, Alerts on resource usage with priority.

The following information is available in the System Monitor −

SAP HANA alert monitoring is used to monitor the status of system resources and services that are running in the HANA system. Alert monitoring is used to handle critical alerts like CPU usage, disk full, FS reaching threshold, etc. The monitoring component of HANA system continuously collects information about health, usage, and performance of all the components of HANA database. It raises an alert when any of the component breaches the set threshold value.

The priority of alert raised in HANA system tells the criticality of the problem and it depends on the check that is performed on the component. Example: If CPU usage is 80%, a low priority alert will be raised; however, if it reaches 96%, the system will raise a high priority alert.

The System Monitor is the most common way to monitor HANA system and to verify the availability of all your SAP HANA system components. The System monitor is used to check all key components and services of a HANA system.

You can also drill down the details of an individual system in Administration Editor. It tells about Data disk, Log disk, Trace disk, alerts on resource usage with priority.

Alert tab in Administrator editor is used to check the current and all other alerts in HANA system.

It also shows the time when an alert is raised, the description of the alert, the priority of the alert, etc.

SAP HANA monitoring dashboard shows the key aspects of system health and configuration.

You can also use SAP HANA Cockpit for system monitoring and alerts. SAP HANA Cockpit can be opened directly from HANA Studio or you can also open it via web browser. You should have sap.hana.admin.roles::Monitoring or sap.hana.admin.roles::Administrator role assigned to access SAP HANA Database Administrator catalog access.

SAP HANA Admin - Table Management

SAP HANA supports all the database functions that can be performed in a conventional database. You can create database tables, views, triggers, synonyms, stored procedures, and other database functions. In SAP HANA, you can create two types of tables −

• Row Store
• Column Store

SAP HANA column store tables are suitable to perform performance optimized read operations and also to perform write operations. You can achieve a data compression up to 11 times, and searches and calculations can be performed much faster as compared to column store tables. Data Partitioning feature of SAP HANA is only available to column store tables and SAP HANA Data modeling can be used only on column store tables.

Row store tables are more suitable for performing INSERT and UPDATE SQL statements on small size tables.

In SAP HANA database, it is possible to join different store table types - Row store table can be joined to a column store table but it is recommended to join similar table types together to keep the performance high.

Creating a Table in HANA Database Using GUI Option in HANA Studio

Right-click on Table tab under Schema → Select ‘New Table’ option as shown in the following screenshot.

Once you click on New Table, it will open a window to enter the Table name. Choose Schema name from the dropdown → Define Table type from the dropdown list: Column Store or Row Store.

Define data type as shown in the following screenshot, columns can be added by clicking the (+) sign. Primary Key can be chosen by clicking the cell under Primary key in front of Column name. Not Null will be active by default. Once columns are added, click Execute.

In the following screenshot, you can see the Table Type as Column Store. You have an option to create a Row store table by selecting Row Store from the dropdown list.

While using SQL statement to create a table, you need to mention “Column” keyword in Create Table command. By default, it creates a Row store table using SQL Editor.

Once you Execute (F8), right-click Table Tab → Refresh. The new table will be reflected in the list of tables under the chosen Schema.

Note − You can also change the table type in SAP HANA database using the following Alter command in SQL Editor.

Alter table_name Column; 

Benefits of Using Column Store Tables

Following are the advantages of using Column store tables as compared to Row store −

Performance Optimized Column Operations

You can perform complex calculations and aggregations much faster as compared to Row Store tables. This removes the need to store aggregated tables in HANA database and hence also saves the memory space.

Built-In Indexes for Column Tables

Using columnar data structure for tables removes the need of Indexes as when you store data in columns it works like a built-in index for each column. This saves the memory space and also improves the performance during write operations.

Data Compression

When you store column based tables in HANA database, similar data types are stored continuous in memory. It allows you to apply various data compression techniques such as - Run Length Compressed, Dictionary Compressed, hence reducing the space required to store the tables. You can achieve a data compression as high as 11 times as compared to conventional database.

Parallel Processing

With the use of multi core processors, you can perform parallel processing on column store tables. As data is stored vertically, column operations can easily be processed on column based tables.

SAP HANA Admin - Table Partition

Using Partitioning, you can split column store tables into smaller tables in a multiple host system. You can even divide tables into smaller more manageable parts using partitioning. Partitioning can be done using DML SQL statements.

When a table is partitioned it contains different set of rows for each part and Partitioning can be done based on different algorithms - Hash Partitioning, Single Level or multilevel Partitioning.

Following are the advantages of using Table Partitioning −

Partitioning Pruning

You can determine if a query can be analyzed in a particular partition. Using this method, you can reduce the load on the system and hence improve the response time.

Example − When you partition a table based on the year, a query is executed to analyze the data for a particular year. You can use specific partition and hence query performance is improved.

Load Balancing

You can divide individual partitions on multiple hosts and hence a query is not processed by a single server, results in better load balancing on all the servers hosting table partitions.

Size Limitation

In a column store table, maximum number of rows that can be entered is around 2 billion. You can overcome this by distributing the rows of a column table on multiple partitions and hence the size limit is increased to 2 billion for each partition.

Improved Delta Merge Operation

During delta merge, if data is only modified for some partitions, you need to merge less partitions in delta merge.

Parallel Processing of Queries

Using partitions, you can run multiple queries in parallel and hence improve the response time.

Let us now discuss the types of partionining.

Single Level Partitioning

There are different types of single level partitioning available in SAP HANA system −

• Hash Partitioning
• Round Robin Partitioning
• Range Partitioning

In Hash partitioning, rows are distributed equally to perform load balancing. You don’t require the detailed information about table content in this Partitioning type.

As compared to Hash Partitioning, in Round Robin Partitioning rows are equally distributed to each partition and new rows are equally assigned to each partition.

To create 4 partitions of a table using Round Robin, you can use the following SQL queries. No primary keys should be defined.

CREATE COLUMN TABLE TABLENAME (a INT, b INT, c INT) 
PARTITION BY ROUNDROBIN PARTITIONS 4 

This command will create 4 partitions of a table.

CREATE COLUMN TABLE Table_Name (a INT, b INT, c INT, PRIMARY KEY (a,b)) 
PARTITION BY HASH (a, b) PARTITIONS 4 

This will create 4 partitions on column a and b, and you need to specify at least one column.

Range Partitioning

In Range partitioning, you create dedicated partitions for certain values and you should have in-depth knowledge of table contents for partitioning columns.

Example − Creating one partition for each calendar month.

CREATE COLUMN TABLE TABLE_NAME (a INT, b INT, c INT, PRIMARY KEY (a,b)) 
PARTITION BY RANGE (a) 
(PARTITION 1 <= VALUES < 10, PARTITION 10 <= VALUES < 20, 
PARTITION VALUE = 50, PARTITION  OTHERS) 

This will create 4 partitions, one for partition range 1 to 10, 2^nd from 10 to 20, 3^rd for value 50, and 4^th for all other values.

Multilevel Partitioning

In multilevel partitioning, you can overcome the limitation of HASH and RANGE single level partitioning to use key columns as partitioning columns. Using multilevel partitioning, you can partition a column that is not a primary key. The most common type of multilevel partitioning is HASH-Range Partitioning.

In Hash-Range multilevel partitioning, you implement Hash partitioning at the first level to implement load balancing and Range partitioning at the second level to implement timebased partitioning.

CREATE COLUMN TABLE Table_name (a INT, b INT, c INT, PRIMARY KEY (a,b))
PARTITION BY HASH (a, b) PARTITIONS 4,
RANGE (c) (PARTITION 1 <= VALUES < 10, PARTITION 10 <= VALUES < 20) 

Instead of using Hash partitioning at the first level, you can also use Round Robin partitioning and it is known as Round Robin-Range multilevel partitioning.

CREATE COLUMN TABLE Table_name (a INT, b INT, c INT) 
PARTITION BY ROUNDROBIN PARTITIONS 4,
RANGE (c) (PARTITION 1 <= VALUES < 10, PARTITION 10 <= VALUES < 20) 

SAP HANA Admin - Table Replication

In SAP HANA system, it is also possible to replicate tables on multiple hosts. When you need to join the tables or partition tables on multiple hosts, table replication is useful to improve the performance, to reduce the load on the network in a distributed environment.

SAP HANA table replication has certain limitations −

• You can’t replicate Partitioned Tables.

• When you are using SAP BW on HANA, it doesn’t support Table replication.

• When you perform table replication, it consumes the main memory and disk space to store persistence of each replica.

• Column store tables with history tables and text columns without a primary key can’t be replicated.

Create Column Store Tables with Replica on All Hosts in Multiple Container System

CREATE COLUMN TABLE Table_Name (I INT PRIMARY KEY) REPLICA AT ALL LOCATIONS 

This command will create a column store table with a replica on each host. You can also replicate an existing column base table on each available host using ALTER table command as follows −

ALTER TABLE Table_Name ADD REPLICA AT ALL LOCATIONS 

It is also possible to drop replica of an existing table using ALTER table drop replica command as follows.

ALTER TABLE Table_name DROP REPLICA AT ALL LOCATIONS 

Note −

• You can perform Table Replication on row store tables.

• In a distributed environment, you can perform table replications on row store tables stored in master node.

In SAP HANA system, you can also perform consistency check on replicated tables using the following SQL command −

CALL CHECK_TABLE_CONSISTENCY('CHECK_REPLICATION', '<schema>', '<table'>) 

SAP HANA Admin - Data Compression

Using SAP Column store tables, you can perform data compression up to 11 times, which results in a cost-saving solution to store more data in HANA database. Column store tables also provide faster data access, search, and complex calculations.

The ratio of uncompressed data size to compressed data size is known as Compression Factor. The compressed table size is the size occupied by the table in the main memory of SAP HANA database.

Check Compression of a Column Table

Using SAP HANA Studio, you can find out the compression status of a column store table and also the compression factor. To find compression details, you need to first load the table into memory.

To load a table into SAP HANA memory, you should have −

• System privilege − TABLE ADMIN

• Object privilege − UPDATE for the table or the schema in which the table is located

You can also load a table using SQL command. Open the SQL console and execute the following statement −

LOAD <table_name>  
UNLOAD <table_name>

Note that when you load a table, it loads the complete data and also delta storage into the main memory of SAP HANA system.

To perform data compression, run the following SQL command to check data compression properties.

SELECT SCHEMA_NAME, TABLE_NAME, COLUMN_NAME, COMPRESSION_TYPE, LOADED from  
PUBLIC.M_CS_COLUMNS where SCHEMA_NAME = '<your_schema>' and TABLE_NAME = '<your_table>' 

You can check the output in Result tab.

To check the Compression ratio for a table, you can navigate to Table Definition. Go to Runtime Information.

To see Compression Ratio, go to Columns tab. You can see the compression ratio in the Main Size Compression Ratio [%] column.

Compress a Table Manually in SAP HANA

It is also possible to compress a table in SAP HANA system manually by executing the following SQL statement.

UPDATE "table_name" WITH PARAMETERS ('OPTIMIZE_COMPRESSION' = 'YES') 

This results in deciding whether a compression is required or an existing compression can be optimized. In this scenario, HANA system uses most suitable compression algorithm.

When you run the above SQL command, compression status remains the same. You can also force the database to reevaluate compression using the following SQL status −

UPDATE "AA_HANA11"."SHOP_FACTS" WITH PARAMETERS ('OPTIMIZE_COMPRESSION' = 'FORCE')

SAP HANA Admin - Solman Integration

You can also support your SAP HANA system by using SAP Solution Manager. To make two systems communicate with each other, you need to register your SAP HANA system to SAP solution manager’s System Landscape Directory (SLD). This directory contains the information about the landscape and software component versions. A SAP system can be configured to register under SLD. SLD manages the information about all installable and installed elements of your system landscape.

Important Checks

To register your SAP HANA system under SLD, you should meet the following prerequisites −

• Your SAP HANA system should be installed with SAP HANA Database Lifecycle Manager (HDBLCM).

• You should be logged in with Administrator account of SID credentials.

• SAP HANA system is running.

To perform the integration of SAP HANA system under SLD, open SAP HANA Database Lifecycle Manager GUI. You can open SAP HANA Database Lifecycle Manager via HANA cockpit or via HANA Studio → Platform Lifecycle Manager.

Navigate to Configure System Landscape Registry Configuration under SAP HANA Platform Lifecycle Management.

Enter the following information under System Landscape Directory −

• SLD Host Name − Name of the host where the SLD system is installed.

• SLD Port − Enter the standard HTTP access port of the SLD.

• SLD User Name − Enter the user of the SLD system. It must be a user that already exists on the host where the SLD system is running.

• SLD Password − Enter the password for the SLD system.

• Use HTTPS − Here you can mention whether to use HTTPS or not.

Click the Run button to finish the configuration under System Landscape Directory.

You can also perform the above steps from the command line, by executing the following command −

./hdblcm --action=configure_sld 

Enter the above specified parameters using command line. Select ’y’ to finalize the configuration under SLD.

SAP HANA Admin - Lifecycle Management

SAP HANA Lifecycle Management includes two features in HANA system: Platform Lifecycle Management for performing updates and customizing SAP HANA platform, and Application Lifecycle Management to manage applications based on SAP HANA system and transports.

SAP HANA Admin ─ Platform Lifecycle Management

SAP HANA Platform Lifecycle Manager can be used to perform installation and update of SAP HANA system, to configure additional components, post installation configuration, to add or remove hosts, to view system information, and to configure inter-service communication.

Following are the key features under SAP HANA Platform Lifecycle Manager −

• You can integrate your SAP HANA system with business solutions such as - SAP Solution Manager by registering under System Landscape Directory SLD.

• You can also add/remove hosts to your SAP HANA landscape.

• You can add and configure additional components, rename your HANA system or you can convert a single container system to multiple container system and vice versa as shown in the following screenshot.

In the above screenshot, you can see different options under SAP HANA Platform Lifecycle Management.

SAP HANA Platform Lifecycle Management can be accessed via three ways −

• Web User Interface
• Graphical User Interface
• Command Line

SAP HANA Admin ─ Application Lifecycle Management

You can use SAP HANA Application Lifecycle Management to support all the phases of lifecycle of SAP HANA application development, defining the application structure to perform transport and update management for Applications.

SAP HANA Application Lifecycle Manager supports various phases of application development −

• Model − Define Package structure, define package hierarchy, and assign packages to Delivery Units for application development.

• Develop − Perform application development in packages and to track changes.

• Transport − Transport your developed application and it can be done using transporting products or delivery units.

• Assemble − Assemble your developed product and translation delivery units for assembling your add-on product.

• Install − Install products and software components from SAP Support Portal.

Access SAP HANA Application Lifecycle Management (ALM)

To access SAP HANA ALM, you can open SAP HANA Cockpit and navigate to SAP HANA Application Lifecycle Management.

You can click HANA Application Lifecycle Management from here or can be opened directly using the web browser.

http://<WebServerHost>:80<SAPHANAinstance>/sap/hana/xs/lm

Example − In our system, HANA ALM is accessed using the link − https://hanaerp:4312/sap/hana/xs/lm/?page=HomeTab

In SAP HANA Cockpit, you can use tiles available in the SAP HANA Application Lifecycle Management and in the SAP HANA Application Installation and Update groups. These tiles can be customized as per your requirement.

SAP HANA Application Lifecycle Management Roles

In order to perform SAP HANA Application Lifecycle Management tasks, you should assign application lifecycle management roles to the users. There are various roles that are available in SAP HANA system to perform HALM tasks. Few of the key roles include −

sap.hana.xs.lm.roles::Administrator

This role is required to perform all read and write operations in the SAP HANA Application Lifecycle Management and to grant access privilege to other uses in SAP HALM environment.

sap.hana.xs.lm.roles::Developer

This role is required to perform changes and to work on the change list and add objects to the change list from the development perspective.

sap.hana.xs.lm.roles::ExecuteTransport

This role is required to perform specific transport operations but they can’t perform system register or maintain systems, define new transport routes, delivery units, or new packages in the repository.

These roles can be assigned to the users using SAP HANA Cockpit or under SAP HANA Studio → Security tab.

In the above screenshot, you can see different SAP HANA Application Lifecycle Management roles that can be assigned to the users for performing development and transport tasks.

SAP HANA Admin - Securing HANA System

It is necessary to implement security in SAP HANA environment to protect critical information and access the database system. You should properly manage authentication and authorization methods, and security policies should be reviewed regularly.

You should also manage the users and roles, auditing activities in SAP HANA, encryption of data in HANA database, and client certificates in the system.

SAP HANA system has many security settings that should be implemented carefully otherwise any misconfiguration can result in a risk of unauthorized access.

SAP HANA Cockpit and HANA Studio provides you with different options to monitor critical security settings.

Following is a list of security related features provided by SAP HANA −

• User and Role Management
• Authentication and SSO
• Authorization
• Encryption of data communication in Network
• Encryption of data in Persistence Layer

Additional features in multitenant HANA database −

• Database Isolation − It involves preventing cross-tenant attacks through the operating system mechanism.

• Configuration Change blacklist − It involves preventing certain system properties from being changed by tenant database administrators.

• Restricted Features − It involves disabling certain database features that provides direct access to the file system, the network, or other resources.

View Security Settings in SAP HANA Cockpit

To view security settings in SAP HANA system, open SAP HANA Cockpit and navigate to SAP HANA Security Overview group.

You should have the role sap.hana.security.cockpit.roles::DisplaySecurityDashboard assigned to view the security settings in HANA Cockpit.

You can check the status of security on each tile under SAP HANA Security Overview. You can further drill down by clicking on any tile to see more details.

SAP HANA Admin - User Provisioning

SAP HANA user and role management configuration depends on the architecture of your HANA system. If SAP HANA is integrated with BI platform tools and acts as a reporting database, then the end user and the role are managed in the application server.

If the end user directly connects to SAP HANA database, then the user and the role in the database layer of HANA system is required for both end users and administrators.

Every user who wants to work with HANA database must have a database user with necessary privileges. User accessing HANA system can either be a technical user or an end user depending on the access requirement. After successful logon to the system, the user’s authorization to perform the required operation is verified. Executing that operation depends on the privileges that the user has been granted. These privileges can be granted using roles in HANA Security. HANA Studio is one of the powerful tools to manage the user and the roles for HANA database system.

User Types

User types vary according to the security policies and different privileges assigned to the user profile. User type can be a technical database user or an end user. The user needs access to HANA system for reporting the purpose or for data manipulation.

Standard Users

Standard users are the users who can create objects in their own Schemas and have Read access in the system Information models. Read access is provided by PUBLIC role, which is assigned to every standard user.

Restricted Users

Restricted users are those users who access HANA system with some applications and they don’t have SQL privileges on HANA system. When these users are created they don’t have any access initially.

If we compare restricted users with Standard users −

• Restricted users can’t create objects in HANA database or their own Schemas.

• They don’t have access to view any data in the database as they don’t have generic Public role added to profile like standard users.

• They can connect to HANA database only using HTTP/HTTPS.

HANA Users Administration and Role Management

Technical database users are used only for administrative purpose such as creating new objects in the database, assigning privileges to other users, on packages, applications, etc.

SAP HANA User Administration Activities

Depending on the business needs and the configuration of HANA system, there are different user activities that can be performed using user administration tools such as HANA studio.

Most common activities include −

• Create users
• Grant roles to users
• Define and create roles
• Delete users
• Reset user passwords
• Reactivate users after too many failed logon attempts
• Deactivate users when it is required

Create Users in HANA Studio

Only database users with the system privilege ROLE ADMIN are allowed to create users and roles in HANA Studio. To create users and roles in HANA Studio, go to HANA Administrator Console. You will see the security tab in System view.

When you expand the security tab, it gives an option of User and Roles. To create a new user, right-click on the User and go to New User. A new window will open where you define User and User parameters.

Enter the user name (mandate) and in the Authentication field enter the password. Password is applied while saving password for a new user. You can also choose to create a restricted user.

The specified role name must not be identical to the name of an existing user or role. The password rules include a minimal password length and a definition of which character types (lower, upper, digit, special characters) have to be part of the password.

Different authorization methods can be configured such as SAML, X509 certificates, SAP Logon ticket, etc. Users in the database can be authenticated by varying mechanisms −

• Internal authentication mechanism using a password.

• External mechanisms such as Kerberos, SAML, SAP Logon Ticket, SAP Assertion Ticket or X.509.

• A user can be authenticated by more than one mechanism at a time. However only one password and one principal name for Kerberos can be valid at any one time. One authentication mechanism has to be specified to allow the user to connect and work with the database instance.

It also gives an option to define the validity of the user. You can mention the validity interval by selecting the dates. Validity specification is an optional user parameter.

There are some users that are by default delivered with the SAP HANA database: SYS, SYSTEM, _SYS_REPO, _SYS_STATISTICS.

Once this is done, next is to define the privileges for the user profile.

Types of Privileges to User Profile

There are different types of privileges that can be added to the user profile.

Granted Role

This is used to add inbuilt sap.hana roles to the user profile or to add custom roles created under Roles tab. Custom roles allow you to define roles as per access requirement and you can add these roles directly to the user profile. This removes the need to remember and add objects to a user profile every time for different access types.

Public Role

This is a generic role and is assigned to all database users by default. This role contains read-only access to system views and execute privileges for some procedures. These roles cannot be revoked.

Modeling

It contains all privileges required for using the information modeler in the SAP HANA studio.

System Privileges

There are different types of System privileges that can be added to a user profile. To add system privileges to a user profile, click on the (+) sign.

System privileges are used for Backup/Restore, User Administration, Instance start and stop, etc.

Content Admin

It contains the similar privileges as that in MODELING role, but with the addition that this role is allowed to grant these privileges to other users. It also contains the repository privileges to work with imported objects.

Data Admin

This is another type of privilege that is required for adding Data from the objects to the user profile.

Following are some common supported System Privileges −

ATTACH DEBUGGER − Authorizes the debugging of a procedure call, called by a different user. Additionally, the DEBUG privilege for the corresponding procedure is needed.

AUDIT ADMIN − Controls the execution of the following auditing-related commands: CREATE AUDIT POLICY, DROP AUDIT POLICY and ALTER AUDIT POLICY and the changes of the auditing configuration. Also allows access to AUDIT_LOG system view.

AUDIT OPERATOR − Authorizes the execution of the following command: ALTER SYSTEM CLEAR AUDIT LOG. Also allows access to AUDIT_LOG system view.

BACKUP ADMIN − Authorizes BACKUP and RECOVERY commands for defining and initiating backup and recovery procedures.

BACKUP OPERATOR − Authorizes the BACKUP command to initiate a backup process.

CATALOG READ − Authorizes the users to have unfiltered read-only access to all system views. Normally, the content of these views is filtered based on the privileges of the accessing user.

CREATE SCHEMA − Authorizes the creation of database schemas using the CREATE SCHEMA command. By default, each user owns one schema. With this privilege, the user is allowed to create additional schemas.

CREATE STRUCTURED PRIVILEGE − Authorizes the creation of Structured Privileges (Analytical Privileges). Only the owner of an Analytical Privilege can further grant or revoke that privilege to other users or roles.

CREDENTIAL ADMIN − Authorizes the credential commands: CREATE/ALTER/DROP CREDENTIAL.

DATA ADMIN − Authorizes reading all data in the system views. It also enables the execution of any Data Definition Language (DDL) commands in the SAP HANA database. A user having this privilege cannot select or change data stored tables for which they do not have access privileges, but they can drop tables or modify table definitions.

DATABASE ADMIN − Authorizes all commands related to databases in a multi-database, such as CREATE, DROP, ALTER, RENAME, BACKUP, RECOVERY.

EXPORT − Authorizes export activity in the database via the EXPORT TABLE command. Note that besides this privilege, the user requires the SELECT privilege on the source tables to be exported.

IMPORT − Authorizes the import activity in the database using the IMPORT commands. Note that besides this privilege, the user requires the INSERT privilege on the target tables to be imported.

INIFILE ADMIN − Authorizes changing of system settings.

LICENSE ADMIN − Authorizes the SET SYSTEM LICENSE command to install a new license.

LOG ADMIN − Authorizes the ALTER SYSTEM LOGGING [ON|OFF] commands to enable or disable the log flush mechanism.

MONITOR ADMIN − Authorizes the ALTER SYSTEM commands for EVENTs.

OPTIMIZER ADMIN − Authorizes the ALTER SYSTEM commands concerning SQL PLAN CACHE and ALTER SYSTEM UPDATE STATISTICS commands, which influence the behavior of the query optimizer.

RESOURCE ADMIN − Authorizes commands concerning system resources. For example, ALTER SYSTEM RECLAIM DATAVOLUME and ALTER SYSTEM RESET MONITORING VIEW. It also authorizes many of the commands available in the Management Console.

ROLE ADMIN − Authorizes the creation and deletion of roles using the CREATE ROLE and DROP ROLE commands. It also authorizes the granting and revocation of roles using the GRANT and REVOKE commands.

Activated roles, meaning roles whose creator is the pre-defined user _SYS_REPO, can neither be granted to other roles or users nor dropped directly. Users having ROLE ADMIN privilege are also not able to do so. Please check documentation concerning activated objects.

SAVEPOINT ADMIN − Authorizes the execution of a savepoint process using the ALTER SYSTEM SAVEPOINT command.

Components of the SAP HANA database can create new system privileges. These privileges use the component-name as the first identifier of the system privilege and the componentprivilege-name as the second identifier.

Object/SQL Privileges

Object privileges are also known as SQL privileges. These privileges are used to allow access to objects like Select, Insert, Update and Delete of tables, Views, or Schemas.

Following are the types of Object Privileges −

• Object privilege on database objects that exist only in runtime.

• Object privilege on activated objects created in the repository, such as calculation views.

• Object privilege on schema containing activated objects created in the repository.

• Object/SQL Privileges are collection of all DDL and DML privileges on database objects.

Following are some commonly supported Object Privileges −

There are multiple database objects in HANA database, so not all the privileges are applicable to all kinds of database objects.

Object Privileges and their applicability on database objects.

Analytic Privileges in User Profile

Sometime it is required that data in the same view shouldn’t be accessible to other users who don’t have any relevant requirement for that data.

Analytic privileges are used to limit the access on HANA Information Views at the object level. We can apply row and column level security in Analytic Privileges.

Analytic Privileges are used for −

• Allocation of row and column level security for specific value range
• Allocation of row and column level security for modeling views

Package Privileges

In the SAP HANA repository, you can set package authorizations for a specific user or for a role. Package privileges are used to allow access to data models - Analytic or Calculation views or on to Repository objects. All privileges that are assigned to a repository package are assigned to all sub packages too. You can also mention if the assigned user authorizations can be passed to other users.

Steps to add package privileges to User profile −

• Step 1 − Click Package privilege tab in HANA studio under User creation → Choose (+) sign to add one or more packages. Use Ctrl key to select multiple packages.

• Step 2 − In the Select Repository Package dialog, use all or part of the package name to locate the repository package that you want to authorize access to.

• Step 3 − Select one or more repository packages that you want to authorize access to, the selected packages appear in the Package Privileges tab.

Following grant privileges are used on repository packages to authorize the user to modify the objects −

• REPO.READ − Read access to the selected package and design-time objects (both native and imported)

• REPO.EDIT_NATIVE_OBJECTS − Authorization to modify objects in packages

• Grantable to Others

If you choose ‘Yes’ for this, this allows assigned user authorization to pass to the other users.

Application Privileges

Application privileges in a user profile used to define authorization for access to HANA XS application. This can be assigned to an individual user or to a group of users. Application privileges can also be used to provide different level of access to the same application such as to provide advanced functions for database administrators and read-only access to normal users.

To define Application specific privileges in a user profile or to add a group of users, following privileges should be used −

• Application-privileges file (.xsprivileges)
• Application-access file (.xsaccess)
• Role-definition file (<RoleName>.hdbrole)

SAP HANA Admin - Authentication Methods

All SAP HANA users who have access to HANA database are verified with different Authentication methods. SAP HANA system supports various types of authentication methods and all these login methods are configured at the time of profile creation.

Following is the list of authentication methods supported by SAP HANA −

• User name/Password
• Kerberos
• SAML 2.0
• SAP Logon tickets
• X.509

User Name/Password

This method requires HANA user to enter the user name and password to login to database. This user profile is created under User management in HANA Studio → Security Tab.

Password should be as per password policy. For example - Password length, complexity, lower and upper case letters, etc. You can change the password policy as per your organization’s security standards.

Note − The password policy cannot be deactivated.

Kerberos

All users who connect to HANA database system using an external authentication method should also have a database user. It is required to map the external login to the internal database user.

This method enables the users to authenticate HANA system directly, using JDBC/ODBC drivers through the network or by using front-end applications in SAP Business Objects.

It also allows HTTP access in HANA Extended Service using HANA XS engine. It uses SPENGO mechanism for Kerberos authentication.

SAML

SAML stands for Security Assertion Markup Language and can be used to authenticate the users accessing HANA system directly from ODBC/JDBC clients. It can also be used to authenticate the users in HANA system, coming via HTTP through HANA XS engine.

SAML is used only for authentication purposes and not for authorization.

SAP Logon and Assertion Tickets

SAP Logon/assertion tickets can be used to authenticate the users in HANA system. These tickets are issued to the users when they login into SAP system, which is configured to issue tickets such as SAP Portal, etc. User specified in SAP logon tickets should be created in HANA system as it doesn’t provide support for the mapping users.

X.509 Client Certificates

X.509 certificates can also be used to login to HANA system via HTTP access request from HANA XS engine. Users are authenticated by certificates that are signed from trusted Certificate Authority, which is stored in HANA XS system.

The user in trusted certificate should exist in HANA system as there is no support for user mapping.

Single Sign On in HANA System

Single sign on can be configured in HANA system, which allows the users to login to HANA system from an initial authentication on the client. User logins at client applications using different authentication methods and SSO allows the user to access HANA system directly.

SSO can be configured using the following configuration methods −

• SAML
• Kerberos
• X.509 client certificates for HTTP access from HANA XS engine
• SAP Logon/Assertion tickets

You can also use SAP HANA Cockpit for performing user and role management tasks.

SAP HANA Admin - Auditing Activities

SAP HANA audit policy specifies the actions to be audited and also the condition under which the action must be performed to be relevant for auditing. Audit Policy defines what activities have been performed in HANA system and who has performed those activities at what time.

SAP HANA database auditing feature allows to monitor the action performed in HANA system. SAP HANA audit policy must be activated on HANA system to use it. When an action is performed, the policy triggers an audit event to write an audit trail. You can also delete audit entries in Audit trail.

In a distributed environment, where you have multiple database, Audit policy can be enabled on each individual system. For the system database, audit policy is defined in nameserver.ini file and for tenant database it is defined in global.ini file.

Audit in the SAP HANA Cockpit

You can configure and activate Auditing policy in SAP HANA system using SAP HANA Cockpit. There is an Auditing app in SAP HANA Cockpit that can be used for auditing activities.

The role required to perform auditing in HANA system − sap.hana.security.cockpit.roles::MaintainAuditPolicy

In SAP HANA Cockpit, Auditing tile is available under SAP HANA Security Overview as in the following screenshot.

When you open Auditing App, navigate to Configuration tab and select Edit button from the bottom.

Next, select Auditing status to Enabled. You have to configure multiple audit trail targets: one for the system (Overall Audit Trail Target), and optionally one or more for the severity of audited actions that is the audit level of the corresponding audit entries.

If you do not configure a specific target for an audit level, audit entries are written to the audit trail target configured for the system.

By default, database table is default audit trail target. You can also select - Syslog, CSV text file for audit trail target.

Create an Audit Policy

You can define an audit policy to monitor the actions for audit. When an action is performed, the policy is triggered and an audit event is written to audit trail. You can create an audit policy using Auditing app of HANA system.

Step 1 − To create an Audit Policy, navigate to Audit Policies tab.

Step 2 − On the right side, create Audit Policy button. Click the Create Audit Policy button and enter the Policy name.

Step 3 − Select the status of Audit Policy. You can select Enabled/Disabled option.

Step 4 − Select the Action status.

• SUCCESSFUL − The action is audited only when the SQL statement is successfully executed.

• UNSUCCESSFUL − The action is audited only when the SQL statement is unsuccessfully executed.

• ALL − The action is audited when the SQL statement is both successfully and unsuccessfully executed.

Step 5 − Select the audit level. The audit level specifies the severity of the audit entry written to the audit trail when the actions in the policy occur.

Step 6 − Select Audit Trail Target. Audit entries triggered by this policy will be written to the specified audit trail target(s).

Step 7 − Enter the actions to be audited by clicking the add button and selecting the relevant actions.

There are different Actions that can be selected using Add Action button as shown in the following screenshot.

In Add action, you need to enter the target object(s) to be audited by clicking the add button and selecting the relevant objects. You can also select actions to be audited. For example: SELECT, INSERT, UPDATE, DELETE, and EXECUTE. You can click the Save button to SAVE the policy.

Check Audit Details in HANA Cockpit

In SAP HANA Cockpit, you can check the audit details of all Audit Policies. When you open the Audit app of SAP HANA Cockpit, navigate to Audit Policies tab. You will find the following details.

Note − You can manage Auditing Policy in SAP HANA Studio as well. Please check our SAP HANA tutorial −

https://www.howcodex.com/sap_hana/sap_hana_auditing.htm

Backing Up HANA System

SAP HANA backup and recovery is used to perform HANA system backups and recovery of system in case of any database failure.

To view backup details, you can navigate to SAP HANA Backup app in HANA Cockpit. You can see the last backup status on the main screen and it can be customized.

When you open Data Backup app, you can see different options related to data backup. You can create a new backup or you can add schedules.

You can also see the last backup status, time, duration, size and destination type details under Backup Catalog details.

You can also perform SAP HANA Backup using HANA Studio. Navigate to Backup Folder in System view.

Overview Tab

This tab specifies the status of the currently running data backup and last successful data backup.

Backup now option can be used to run data backup wizard.

Configuration Tab

This tab specifies the Backup interval settings, file-based data backup settings, and logbased data backup setting.

Backup Interval Settings

Backint settings provides an option to use third party tool for data and log backup with configuration of a backing agent.

Configure the connection to a third-party backup tool by specifying a parameter file for the Backint agent.

File and Log Based Data Backup Settings

File-based data backup setting specifies the folder where you want to save the data backup on HANA system. You can change your backup folder.

You can also limit the size of data backup files. If the system data backup exceeds this set file size, it will split across the multiple files.

Log backup settings specify the destination folder where you want to save log backup on the external server. You can choose a destination type for log backup.

File − Ensures sufficient space in the system to store backups.

Backint − Special named pipe exists on the file system, however, require no disk space.

You can choose backup interval from the dropdown. It tells the longest amount of time that can pass before a new log backup is written.

Backup Interval − It can be in seconds, minutes, or hours.

Enable Automatic log backup option − Checking this option, helps to keep the log area vacant. If you disable this option, the log area will continue to fill and cause the database to hang.

Open Backup Wizard − To run the backup of system.

Backup wizard is used to specify backup settings. It specifies the Backup type, Destination type, Backup Destination folder, Backup prefix, the size of backup, etc.

Click Next → Review Backup settings → Finish. It runs the system backups and shows the time of complete backup for each server.

SAP HANA Admin - Recovery HANA System

To recover SAP HANA database, the database needs to shut down. Hence, during recovery, the end users or SAP applications cannot access the database.

Recovery of SAP HANA database is required in the following situations −

• A disk in the data area is unusable or a disk in the log area is unusable.

• As a consequence of a logical error, the database needs to be reset to its state at a particular point in time.

• You want to create a copy of the database.

How to Recover a HANA System?

Choose HANA system → Right-click → Back and Recovery → Recover System.

Types of Recovery in HANA System

Most Recent State − Used for recovering the database to the time as close as possible to the current time. For this recovery, the data backup and log backup have to be available since the last data backup and the log area is required to perform the above type recovery.

Point in Time − Used for recovering the database to the specific point in time. For this recovery, the data backup and log backup have to be available, since last data backup and the log area are required to perform the above type of recovery.

Specific Data Backup − Used for recovering the database to a specified data backup. Specific data backup is required for the above type of recovery option.

Specific Log Position − This recovery type is an advanced option that can be used in exceptional cases where a previous recovery failed.

HANA XS Application Service

In SAP HANA system, there are many tools that can be used to manage application development in HANA XS Service Classic model and Advance model. You can provide administering and support services to XS service model or you can also perform development under SAP HANA system.

HANA XS Application service includes tools available under SAP HANA XS Administration Cockpit or you can also use SAP HANA Studio to maintain application development under HANA XS service.

Following are the various roles that exist under SAP HANA system to manage XS Application Service.

HTTPDestAdministrator

Full access to the details of HTTP destination configurations (display and edit).

HTTPDestViewer

Read-only access to HTTP destination configurations, which are used to specify connection details for outbound connections. For example, using the server-side JavaScript Connectivity API that is included with SAP HANA XS.

RuntimeConfAdministrator

Full access to the configuration settings for SAP HANA XS application security and related user-authentication providers.

RuntimeConfViewer

Read-only access to the configuration settings for SAP HANA XS application security and related user-authentication providers. For example, SAML or X509.

There are various roles that exist in SAP HANA system to manage HANA XS Application Service as shown in the above screenshot.

XS Service Tools Under SAP HANA Cockpit

In SAP HANA Cockpit, there are various tools that you can use to maintain HANA XS Service. They are web-based tool that allows you to configure and maintain the basic administration-related elements of the application-development process and environment.

XS Artifact Administration

This is used to maintain runtime configurations for individual applications or a complete application hierarchy.

XS Job Dashboard

This is used to monitor and maintain SAP HANA XS job schedules defined using the XS job syntax.

Trust Manager

This is used to maintain certificates which are used to establish trust relationships between servers used by SAP HANA XS applications.

SMTP Configuration

This tile is used to define the details of SMTP server that is available for use by all applications running on an SAP HANA XS server.

User Self-service Tool

This tile provides the set of tools that allows to maintain self-service requests and administrate the self-service tools by the users.

How to View Configuration Parameters for SAP HANA XS Engine in HANA Studio?

To view the configuration details of HANA XS Engine in HANA Studio, double-click HANA system in the system view pane. Navigate to Configuration tab.

Navigate to xsengine.ini file under the configuration tab and expand the folder. You can view the various configuration parameters as shown in the following screenshot.

Application_container

This contains list of all the applications trusted by XS Engine.

Communication

This contains application-related connection requests and configuration.

Customer_usage

This defines customer specific usage details for HANA application service.

Debugger

This is used to manage debugger settings.

Httpserver

This provides SAP HANA XS Web Server details. Other options under xsengine.ini is used to define SAP HANA XS application service configuration.

SAP HANA Admin - Data Provisioning

SAP HANA Replication allows the migration of data from source systems to SAP HANA database. A simple way to move data from the existing SAP system to HANA is by using various data replication techniques.

System replication can be set up on the console via command line or by using HANA studio. The primary ECC or transaction systems can stay online during this process. There are three types of data replication methods in HANA system −

• SAP LT Replication method
• ETL tool SAP Business Object Data Service (BODS) method
• Direct Extractor Connection method (DXC)

SAP LT Replication Method

SAP Landscape Transformation (SLT) Replication is a trigger-based data replication method in HANA system. It is a perfect solution for replicating real-time data or schedulebased replication from SAP and non-SAP sources. It has SAP LT Replication server, which takes care of all trigger requests. Replication server can be installed as a standalone server or can run on any SAP system with SAP NW 7.02 or above.

There is a trusted RFC connection between HANA DB and ECC transaction system, which enables trigger-based data replication in HANA system environment.

ETL SAP Data Services Replication

SAP HANA ETL based replication uses SAP Data Services to migrate data from SAP or nonSAP source system to target HANA database. BODS system is an ETL tool used to extract, transform, and load data from the source system to the target system.

It enables to read the business data at the Application layer. You need to define data flows in Data Services, scheduling a replication job, and defining the source and the target system in data store in Data Services designer.

Direct Extractor Connection Method (DXC)

Direct Extractor Connection data replication reuses the existing extraction, transformation, and load mechanism built into SAP Business Suite systems via a simple HTTP(S) connection to SAP HANA. It is a batch-driven data replication technique. It is considered as a method for extraction, transformation, and load with limited capabilities for data extraction.

DXC is a batch-driven process and data extraction using DXC at certain interval is enough in many cases. You can set an interval when batch job executes, for example, every 20 minutes. In most of the cases it is sufficient to extract data using these batch jobs at certain time intervals.

You can check complete details about Data Provisioning in our SAP HANA tutorial −

https://www.howcodex.com/sap_hana/data_replication_overview.htm

SAP HANA Admin - Smart Data Access

In SAP HANA system, you can use Smart Data Access (SDA) option to replicate data to HANA database from other data sources where you create virtual tables, which are linked to tables in the remote system. You can perform read/write operations on these virtual tables - SELECT, Insert, and Update.

Different types of remote systems can be used under Smart Data Access −

• SAP HANA
• Sybase
• Teradata
• Apache Hadoop
• Oracle

All the above remote data sources are supported under HANA SPS07 or higher. Older version of SAP HANA supports only - Sybase, Apache Hadoop, and Teradata.

New Remote System Connection

To create a new remote connection, navigate to Data Provisioning tab → New Remote Sources.

Enter Source name and select Adapter from the dropdown list. On the right hand side, you have option to Test the Connection. You can also run to create the data source.

Once you set up the connection, it can be used to perform read/write functions in remote system and to join the tables for data provisioning.

SAP HANA Admin - Integration with Hadoop

In SAP HANA system, you can also integrate SAP HANA computing power with Hadoop to process huge amount of data at faster speed. Hadoop system is used for storing huge amount of unstructured data and HANA provides high speed data analysis.

Following scenarios can be used to connect SAP HANA system to Hadoop −

• Hive ODBC Driver
• Smart Data Integration
• HANA Spark Controller

To use Smart Data Access to connect to Hadoop system in HANA Studio, select Adapter Name: Hadoop

In Connection Properties, enter the URL and Credentials.

You can also use the following SQL statement to create a remote connection to connect to Hadoop system. We have selected Authentication type as Password here −

CREATE REMOTE SOURCE RC_Hadoop 
ADAPTER "hadoop" 
CONFIGURATION 'webhdfs_url = http:// 
<full_qualified_domain_name> 
: 
50070;webhcat_url = http:// 
<full_qualified_domain_name> 
:50111' 
WITH CREDENTIAL TYPE 'PASSWORD' 
USING 'user = username;password = pwd'; 

Enter the above SQL statement in HANA Studio SQL editor and click the Execute button.

This way you can integrate your SAP HANA system with Hadoop using Smart data integration in HANA Studio.

SAP HANA Admin - Key Commands

Display the general information about HANA database and has to be executed in the command line

hdbsql -n localhost -i 1 -u username -p Password \s 

This command will display the details of HANA database such as the host name, database, user, Kernel version, SQLDBC version, etc.

View the concurrent database login to HANA database

hdbsql -n localhost -i 1 -u username -p Password 
"SELECT CNO,TITLE,FIRSTNAME,NAME,ZIP FROM Database_Name" 

The above command displays the following result about concurrent sessions −

CNO | TITLE | FIRSTNAME | NAME | ZIP 

Start and stop HANA database from HDB

To start and stop HANA database using HDB, you have to login to HANA system host as <sid>adm and run the following command −

/usr/sap/<SID>/HDB<instance number>/HDB start 
/usr/sap/<SID>/HDB<instance number>/HDB stop 

SAP HANA Admin - Job Responsibilities

Following are certain experiences and capabilities required to handle the job responsibility of HANA Admin.

• Integration of SAP HANA into different remote systems. Exposure to different remote access options in HANA system.

• Experience in monitoring, tuning, and troubleshooting of SAP HANA systems.

• HANA system administration using SAP HANA Cockpit and HANA Studio.

• Experience in managing users and roles in SAP HANA system.

• Implementing SAP HANA Table management techniques - data compression, table partition, compression techniques, and load/unload table into memory.

• HANA Installation, Data Provisioning, Admin, Modelling.

• SAP BusinessObjects and HANA integration, Design document, RFPs, and estimation plans.

• Experience with high availability and backup mechanism.

• Implementing SAP HANA Security mechanism, HANA XS Application service, and monitoring of HANA system.