SAP HANA database

SAP HANA is an in-memory platform that serves as the foundation for modern enterprise solutions typically from SAP. This technology enables real-time data processing. This article provides an overview of basic information about SAP HANA and detailed instructions on how to work with it. It will cover the following steps: installing the SAP HANA platform, procedures for using it, and instructions for backing up your data.

SAP HANA is an in-memory platform that combines integrates a database and an application server. The key is its ability to store and process data directly in the operating memory server (RAM), which distinguishes it from traditional databases that store data on a hard disk.

Why is SAP HANA more than just a database? 🧐

SAP HANA is designed to eliminate separate systems for transaction processing (OLTP) and analytical processing (OLAP). With its in-memory architecture (data is stored and processed in operational memory) and columnar data storage, it allows both types of operations to be performed in parallel and with exceptional speed.

Here are the main reasons why SAP HANA goes beyond the traditional database:

• Unified platform: SAP HANA integrates the database, application server and data tools integration and modeling. This simplifies IT architecture and reduces latency, as there is no need to move data between different systems.
• Application Development: It offers a complete environment for application development. Programmers can create and run applications directly on the platform, leveraging its high performance for real-time analysis and transactions.
• Advanced analytics capabilities: SAP HANA includes built-in text processing libraries, Predictive analytics, geodata, graph analytics and machine learning. This allows companies to perform complex analyses and gain deep insights into their data without having to migrate data to other specialized tools.
• Real-time processing: By storing data in memory, SAP HANA can process huge volumes of data in seconds. This is crucial for businesses that need instant visibility into the status of their operations, customer behaviour or financial results.

In essence, while a traditional database only stores and provides data, SAP HANA is a complete ecosystem that not only stores the data, but also processes it in real time, analyzes it, and allows you to build upon it complex applications on it.

Main features of SAP HANA

• Real-time processing: With in-memory technology, SAP HANA can analyze huge volumes of data in seconds. This allows companies to gain instant insights and react quickly to market changes.
• Unified platform: SAP HANA combines transactional (OLTP) and analytical (OLAP) data processing into a single system. This eliminates the need for separate databases for different types of tasks and simplifies IT architecture.
• Column-oriented architecture: Data is stored in columns, which is ideal for fast aggregation and analysis. But it also supports row storage, providing flexibility for different types of data and tasks.
• Application Development: SAP HANA is not just a database, it is also a development platform on which to build innovative and high-performance applications.

Why is SAP HANA so fast?

The speed of SAP HANA is due to several factors:

• Data in memory: Accessing data in RAM is significantly faster than accessing data on a hard drive or SSD.
• Data compression: Columnar storage allows for more efficient data compression, which reduces memory requirements and increases processing speed.
• No aggregates and indexes: Traditional databases use precomputed aggregates and indexes to speed up queries. SAP HANA can process data so quickly that these auxiliary structures are not needed, simplifying data model and reduces the data footprint.

Why was the SAP HANA database created? the official reasons

Technical reason

SAP HANA was created in response to the fundamental limitations of traditional database technologies that were no longer able to hold to keep pace with the growing demands of modern business. The main goal was to enable companies to process huge volumes of data and analytical reports in real time while turning Oracle database payments into revenue for SAP.

Main motivations and problems that SAP HANA solves:

• Growing volume of data: With the digital transformation of businesses, the amount of transactional and unstructured data. Traditional disk-based databases were too slow to process this data efficiently and analyze it.
• Delays in analysis: Historically, enterprises had to maintain two separate data structures: one for transactions (OLTP) and one for analysis (OLAP), often in a data warehouse. Data had to move between the two on a regular basis moved (ETL process), resulting in time delays and outdated reports. SAP HANA eliminates this model.
• Performance limitations: Traditional databases are designed to work with data on a hard drive, where access is significantly slower than in RAM. SAP HANA uses in-memory technology. computing, making all operations significantly faster.
• Need to simplify IT architecture: Combination of different systems (database, application server, analytical tools) was complex, costly and inefficient. SAP HANA merged these functions into a single, unified platform.

In essence, SAP HANA was created as a revolutionary solution to enable companies to respond quickly and efficiently to change in the marketplace and make strategic decisions based on current data, not the data from the day before.

The Strategic Motivation Behind SAP HANA

For a long time, even though SAP was the global leader in enterprise software (ERP), most of its systems were (such as SAP ECC) ran on third-party databases. The company had the largest market share Oracle, which meant that SAP and its customers were heavily reliant on its royalties and technology.

The development of SAP HANA enabled SAP to gain several key strategic advantages:

• Reducing dependency on third parties: By creating its own in-memory platform, SAP freed itself from from having to pay licensing fees to Oracle and other database vendors.
• Complete control over the entire software stack. optimized solution. This allowed them to design applications (such as SAP S/4HANA) to take full advantage of the power and unique features of the HANA database, which was not possible with other vendors' databases.
• New revenue stream: SAP began selling not only its applications but also its database platform, which partner became a direct competitor to Oracle in the database market.

The creation of SAP HANA was therefore not only a technological shift, but also a significant business and strategic move that changed the dynamics of the entire enterprise software market.

What is In-Memory Database?

In-memory database (IMDB) is a type of database that stores its data in main operating memory (RAM) of the computer instead of traditional disks (SSD/HDD).

The main reason for this approach is the extremely high speed of data access. While access to data on disk requires mechanical or electronic operations that are relatively slow, RAM allows for almost instantaneous access, resulting in significantly faster transactions and analytical operations.

How does it work and what are the benefits?

Data in IMDB is loaded into memory when the database server starts and all operations (read, write, update) take place directly in memory. In order to ensure data persistence, so that data is not lost in the event of a power failure or reboot, additional mechanisms such as:

• Transaction logging: Every change is immediately logged to a log on disk.
• Periodic snapshots: At regular intervals, the entire state of the database is saved to disk.

Main advantages include:

• Incredible speed: Ideal for applications that require processing large volumes of data in such as financial services, telecommunications or online gaming.
• Low latency: Minimal delay in responding to queries.
• Parallel processing: Many IMDBs are optimized for multi-core processors, allowing efficient parallel processing of queries.

When are in-memory databases used?

In-memory databases are great for:

• Real-time analytics: Processing data from sensors, website clicks or other real-time.
• High Frequency Trading (HFT): Lightning fast execution of financial trading transactions. markets.
• Session management: Storing user sessions and temporary data for web applications.
• Data Caching: Quickly access frequently used data without having to read it every time from from a slower disk.

Other in-memory databases besides SAP HANA

In-memory databases are a modern approach to data processing and there are a number of other major players in the market. These databases differ in their features, target markets and application focus.

• Oracle TimesTen is an in-memory database that is optimized for applications with high response speeds and large numbers of transactions, such as telecommunications or financial services. It functions as either standalone database or as a cache for larger Oracle databases.

• Microsoft SQL Server has integrated in-memory technology (called In-Memory OLTP) directly into its database core. It allows businesses to speed up transaction processing, queries, and data warehousing without without having to migrate to an entirely new platform.

• VoltDB is a database specifically designed for applications that require a huge number of transactions in real time, such as the online gaming industry or event processing.

• SingleStore (formerly MemSQL) is a distributed in-memory database that combines analytical and transactional processing. It is designed for scalability and fast processing of large volumes of data.

• A directory is, rather than a full-fledged database, a **structure of data in memory** that is often used as a fast cache, message broker, or temporary storage for web and mobile applications.

• Apache Ignite is a distributed database platform that serves as an in-memory data data grid. It is optimized for large data volumes and connectivity with other systems.

• Memcached is a freely available, distributed system for storing objects in the operating memory. Its main purpose is to speed up web applications by storing frequently requested data in RAM, such as the results of database queries or API calls. It works as a simple key-value type of storage, and unlike full-featured databases, it is not intended for persistent data storage. Memcached is extremely fast and efficient for reducing the load on databases, making it an important part of architecture of many high-performance websites and platforms.

SAP HANA Installation

SAP HANA can be run on Windows. But for any database system, it is always better to use a Unix system. Therefore, we will only cover the installation of SAP HANA on LINUX OS

SAP HANA officially supports only the Linux distributions RedHat and Suse. And it is made in such a way that it cannot even be installed on other distributions. Even manual installation is so complicated that the best approach is to download a pre-made system image where SUSE is installed plus the SAP HANA database is installed.

To download you need to register. There is then probably an app that allows you to choose what you want to download. So for initial testing purposes, the easiest way to do this is to ask a colleague to hand over a clean base virtual machine image. This will avoid a complicated download process. SAP HANA version 1.0 requires a cpu with SSE4.2 instructions. To make it work for you, be sure to choose the right CPU architecture, whether physical or virtual.

After booting, you can log in via ssh to your hxeadm account.

ssh hxeadm@IP

The first time you log in, the preconfigured system requires you to set a password for the OS and for the SAP HANA database, plus other settings.

The image PC with the SAP HANA database for Virtualbox has a preconfigured user with a password. User:hxeadm pass:HXEHana1 . After login a change is required.
Image for VirtualBOx can also be converted and imported into the Proxmox virtualization tool.

PS: there is a zypper software manager in the SUSE Linux distribution. A command to update the package list:

zypper update

First launch of VPS with SAP HANA database

Video demonstration of the first launch of a virtual server with a pre-packaged SUSE distribution and SAP HANA database:

DB version listing:

HDB version

A command to stop all services associated with the SAP HANA database system:

./HDB stop

A command to stop all services associated with the SAP HANA database system:

./HDB start

Command to display the status of the sap-hana-tmpfs service that manages the temporary in-memory file system (tmpfs) for the SAP HANA database.

systemctl status sap-hana-tmpfs

By default, the SAP HANA database communicates on port 8090.

Operating vDocker

Another option is to run the SAP HANA database as a Linux container, e.g. in Docker or even Kubernetes. Installing SAP HANA, express edition with Docker. HANA 2.0 Running inside Docker build docker image with SAP HANA.

Command to download SAP HANA as an image for docker:

docker pull saplabs/hanaexpress

Connecting to DB SAP HANA

As always, there is the option to connect from the console or use the graphical client.

Graphical Client

The graphical client that can run on Windows is: squirrel-sql.

Logging in via DBeaver works, you just need to set a DB type other than the default DB HANA type. DBeaver is a universal graphical database client I useDBeaver for example to connect to MS SQL databases. The advantage of DBeaver is that it is multiplatform, so it will work on Windows, Linux and macOS on both x86 and x86_64 or ARM64.

Console Client

DB Connection:

/usr/sap/HXE/HDB90/exe/hdbsql -i 90 -d SystemDB -u SYSTEM -p 2021#Test

• -i is an option to specify the instance number.
• -d is an option to specify the name of the database you want to connect to.
• -u is an option to specify a username for logging in.
• -p is an option to specify a password.

SQL queries in SAP HANA

SQL (Structured Query Language), is a query language, used to manage and manipulate data in relational databases. It is a standard way to communicate even with SAP HANA database.

SAP HANA is not case sensitive by default for identifiers such as table, column, or schema names.

SQL to create a table and insert records:

CREATE TABLE status (id INT, text NVARCHAR(30) );
INSERT INTO status (id,text) VALUES (1, 'text1');
INSERT INTO status (id,text) VALUES (3, 'Three');

Note that writing with double quotes does not work:

INSERT INTO status (id,text) VALUES (1, "text1");

SQL SELECT:

select * from status;

Video tutorial on how to connect to SAP HANA database and perform basic SQL query:

Tenants in SAP HANA

What is a tenant in databases?

The term "tenant" in databases refers to an isolated set of data that belongs to a single client or user in a multi-tenant architecture environment.

Multi-tenant Architecture

Multi-tenant architecture is a software model where a single instance of an application serves multiple customers. Each customer (tenant) shares the same database instance, but its data is strictly separated and isolated from that of other customers. This architecture is very common in cloud solutions (SaaS - Software as a Service) because allows providers to manage and scale the service efficiently.

Key Features and Benefits

• Data isolation: Each tenant has its own virtual data store, even though it is physically located in a shared database. This ensures that one tenant's data is not visible to the others.
• Efficiency and cost savings: Running a single database instance for many tenants is much cost-effective than managing and maintaining a separate database for each customer. Resources such as memory and processor are shared and used more efficiently.
• Easier to manage and update. one location, simplifying maintenance and deployment of new features for all clients at once.

Examples

An example of a multi-tenant architecture might be Salesforce or Office 365. In Salesforce, all customers (tenants) share one giant database, but each company's data (like yours) is logically separate and only its authorized users can access it.

Example of working with Tenants in SAP HANA

CREATE DATABASE TENANTDB SYSTEM USER PASSWORD Password1

Simply put, this command does the following:

1. Creates a new tenant database named TENANTDB.
2. For this new database, it creates a user with administrative rights (superuser) named SYSTEM.
3. This user SYSTEM will be given the password Password1.

This newly created TENANTDB database is isolated from other tenant databases within the same SAP HANA instance. Each such database has its own users, schema and data, which allows for efficient manage and separate different projects or departments .

In a multi-database SAP HANA architecture, there is one master database. (SystemDB), that manages the entire instance and all tenant databases. This command is run in the context of SystemDB and is used to create a new one, independent database for the application.

Reasons why it uses tenate databases in practice:

• Data and application isolation: Each application or department can have its own database.
• Simpler administration: Administrators can manage each database independently (e.g. restart, backup, update).
• Resource optimization: Resources such as memory and CPU are shared among all databases, but can be efficiently managed for each of them.

Stopping the database:

ALTER SYSTEM STOP DATABASE TENANTDB;

Stop system database:

ALTER SYSTEM STOP DATABASE HXE;

The command to start the database:

ALTER SYSTEM START DATABASE HXE;

You should not create any additional tables in the system database and store user data there. But create a new database=new tenant!

CREATE SCHEMA schema1

Use the SCHEMA command to create a new schema named schema1 in the SAP HANA database. In a database context, a schema is a logical container that groups and organizes database objects such as tables, views, functions, and procedures.

Creating a schema and using SQL to insert data:

CREATE TABLE schema1.table (id INT, text NVARCHAR(30) );
INSERT INTO schema1.table (id,text) VALUES (1, 'text1');

Video tutorial on how to use and work with tenants in SAP HANA database:

Backup

Backup and data recovery is done in a complicated way with the SAP HANA database, so I'll have to write it up in a separate article.

If you need to design and implement an automated backup process, contact me.

More commands

Determine how much space the database is taking up:

select sum((disk_size)/1024/1024/1024) from m_table_persistence_statistics;

Interesting links

• Youtube: How to - HANA DB Manual Backup on Suse Linux
• Supported OS versions for SAP HANA
• SAP HANA database interactive terminal (hdbsql) - by the SAP HANA Academy
• How to find HANA disk size,memory size,record count,memory usage of COLUMN TABLES in HANA
• Backup and restore SAP HANA2 using scripts
• Tigon:SAP HANA Backup
• How to Backup SAP HANA Database using Command Line

Experience

Working with the SAP HANA database is complex and complicated. If you have chosen it and need help managing and backup feel free to contact me via contact.