Creating, Renaming, and Deleting a Tenant Database

Objectives

After completing this lesson, you will be able to:

  • Illustrate the multitenant database architecture
  • Create, rename and delete tenants

Multitenant Architecture

An SAP HANA system is a database management system (DBMS); that is, it is a server component that manages a database model. A database management system captures and analyzes data by interacting with the user, other applications, and the database itself. A general-purpose DBMS allows the definition, creation, querying, update, and administration of databases.

A DBMS performs the following functions:

  • Manages large amounts of data in a multi-user environment so that many users can concurrently access the same data

  • Maintains relationships between data

  • Provides secure access to data using the user authorization concept

  • Recovers data automatically to the most recent consistent status after a system failure

  • Delivers high performance for processing data requests

SAP HANA has all the above explained DBMS features. The biggest difference between the general-purpose DBMS on the market and SAP HANA is that SAP HANA stores all its data in-memory. In SAP HANA the in-memory data is the primary storage location, and the data files on disk are seen as secondary storage.

Note
Don't confuse the SAP HANA in-memory technology with the general-purpose DBMS caching capabilities. These are two different things.

Single-Database-Container versus Multiple-Database-Container (MDC)

The concept of a multittenant database container system is based on a single SAP HANA system, or database management system, with a single system ID. This single system ID contains at least one tenant database, in addition to a system database. The system database keeps the system-wide landscape information, and provides system-wide configuration and monitoring. Users of one tenant databases cannot connect to other tenant databases. They also cannot access application data there, unless the system is enabled for cross database access. The tenant databases are, by default, isolated from each other in terms of application data and user management. Each tenant database can be backed up and recovered independently from one another. Because all tenant databases are part of the same SAP HANA database management system, they all run with the same SAP HANA version (revision number). In addition, the defined high availability disaster recovery scenario applies to all tenant databases.

A multiple-container system always has exactly one system database, used for central system administration, and any number of multittenant database containers (including zero), also called tenant databases. An SAP HANA system installed in multiple-container mode is identified by a single system ID (SID). Databases are identified by an SID and a database name. From the administration perspective, there is a distinction between tasks performed at system level and those performed at database level. Database clients, such as the SAP HANA cockpit, connect to specific databases.

In a multiple-container system, only the system database runs the name server. The name server contains landscape information about the system as a whole, including which tenant databases exist. It also provides index server functionality for the system database. Unlike the name server in a single-container system, the name server of the system database in a multiple-container system does not own topology information. That is, it does not own information about the location of tables and table partitions in databases. Database-related topology information is stored in the relevant tenant database catalog.

Tenant databases require only their own index server. Servers that do not persist data, such as the compile server and the preprocessor server, run on the system database and serve all databases. The server for SAP HANA extended application services runs in a tenant database. However, it can be added as a separate service, if necessary.

Note

As of SAP HANA 2.0 SPS 01, the multi-container database mode is the only database mode. By default, a single tenant database is created during installation. The upgraded system will have one tenant database that corresponds to the old single container. You can add additional tenant databases later using the SAP HANA cockpit.

A single-container system will automatically convert to a tenant database system during the update. The database of a single-container system is converted into a system database and a tenant database. The upgraded system will comprise one tenant database that corresponds to the old single container. The system database (SYSTEMDB) creates a new user (SYSTEM). During the update, a password must be given for this user. The database superuser (SYSTEM) of the single-container system becomes the SYSTEM user of the tenant database. You may have to adapt your operations concept to include the new system database.

MDC Architecture

During the installation of a multiple-container system, the SystemDB database is created. It contains information about the system as a whole and all tenant databases. It is used for central system administration.

The SystemDB is an administrative database containing landscape topology information, system-wide parameter settings, and can perform backups and restores of all tenant databases. In a SAP HANA database, running with default parameter settings, 1 to 20 additional tenant databases can be created.

Conduct system administration in the SystemDB to perform resource management for all tenant DBs on CPU and memory allocation.

Tenant databases are identified by their names, and have tenant specific parameter settings. Backup and restore operations can be performed individually per tenant.

If you use a multiple-container system, you can run multiple applications in different tenant databases. You can use this deployment option to replace existing MCOS on-premise scenarios.

MDC: User and Administration Layers

Administration tasks performed in the system database apply to the system as a whole and to all of its databases (for example, system-level configuration settings). Alternatively, it can target specific tenant databases (for example, backup of a tenant database).

The System Database

System database creation occurs during either installation of a multiple-container system, or during the conversion from a single-container system to a multiple-container system. The system database contains information about the system as a whole, as well as all its tenant databases. It is used for central system administration.

A multiple-container system has exactly one system database. It is created during system installation, or during the migration from a single-container system. It contains the data and users for system administration. System administration tools, such as the SAP HANA cockpit, can connect to this database. The system database stores overall system landscape information, including information on the tenant databases that exist in the system. However, it doesn't own database-related topology information, that is, information about the location of tables and table partitions in databases. Database-related topology information is stored in the relevant tenant database catalog.

Administration tasks performed in the system database apply to the system as a whole, and to all of its databases (for example, system-level configuration settings). Alternatively, it can target specific tenant databases (for example, backup of a tenant database).

Points to Note about the System Database

Note the following information about the system database:

  • The system database is not a database with full SQL support. Full-featured SQL support, is only available in tenant databases.

  • The system database cannot be distributed across multiple hosts: scale-out is not possible.

  • The system database can show monitoring data from tenant databases (views in the schema SYS_DATABASES), but it can never show actual content from tenant databases.

MDC: Database Isolation

The Database Isolation specifies the isolation of the tenant databases on the operating system level for multitenant database container SAP HANA systems. By default, all database processes in a multiple-container system run under the OS user <sid>adm. If you want to mitigate against cross-database attacks through OS mechanisms, configure the system for high isolation. In this way, the processes of individual tenant databases must run under dedicated OS users belonging to dedicated OS groups. Database-specific data on the file system is then protected with standard OS file and directory permissions.

Properties of MDC High Isolation Level

The properties of a system with a high isolation level are as follows:

  • Processes of individual tenant databases run under the dedicated OS users belonging to dedicated OS groups.

  • Database-specific data on the file system is protected using OS file and directory permissions. Note that <sid>adm does not have OS access to tenant data volumes, log volumes, or backups, but it can access tenant-specific trace and configuration files.

  • Operations that require OS access are restricted to users with the correct permissions. This feature adds another layer of protection between tenants: Tenant administrators with access to the OS cannot access other tenants or the system database with OS commands.

Port Assignment in the System Database

The port numbers of the system database are fixed:

  • 3<instance>01 (internal)

  • 3<instance>13 (SQL)

  • 3<instance>14 (HTTP via XS classic server)

Port Assignment in Tenant Databases

Every tenant database has its own ports and connections for internal and external communication.

Every tenant database has dedicated ports for SQL and internal communication. There is also a dedicated port for HTTP-based client communication via the SAP HANA XS classic server, which runs by default as an embedded service in the index server.

The port numbers of the tenant database that are automatically created when you install a single-tenant system are:

  • 3<instance>03 (internal communication)

  • 3<instance>15 (SQL)

  • 3<instance>08 (HTTP via SAP HANA classic server)

The ports of any subsequently added tenant database are automatically assigned according to availability at the time.

In addition to these fixed assignments, there are no standard port number assignments. Port numbers are assigned automatically from the available port number range according to availability at the time the database is created, or a service is added. Administrators can also explicitly specify which port numbers to use when they create a tenant database or add a service.

The default port number range for tenant databases is 40 to 3<instance>99. Every tenant requires 3 ports, this means that the maximum additional tenant databases that can be created per instance is 20.

However, you can increase this by reserving the port numbers of further instances. In the cockpit, a dialog will prompt you to do this, or you can configure the property [multidb] reserved_instance_numbers in the global.ini file. The default value of this property is 0. If you change the value to 1, the port numbers of one further instance are available (for example, 30040–30199 if the first instance is 00). If you change it to 2, the port numbers of two further instances are available (for example, 30040–30299 if the first instance is 00), and so on.

Note
An interactive table with all TCP/IP ports of all SAP products (including SAP HANA) can be found here:

TCP/IP Ports of All SAP Products

HTTPS Client Access

The server for SAP HANA extended application services allows Web-based applications to access SAP HANA via HTTPS. The internal Web Dispatcher of the SAP HANA system manages these incoming HTTPS requests. To allow applications to send requests to specific databases in a multiple-container system, every tenant database needs an alias hostname. Requests to the alias hostname are then forwarded to the server of the corresponding tenant database. Requests with the physical hostname in the HTTP host header are forwarded to the server running on the system database.

The default HTTP ports are used in all cases:

  • 80<instance> (HTTP)

  • 43<instance> (HTTPS)

Alias hostnames are mapped to internal HTTPS ports, so that incoming requests can be routed to the correct database.

To configure the internal SAP Web Dispatcher, specify the URLs by which tenant databases are publicly accessible in the xsengine.ini file of each individual tenant database. You do not need to specify the URL of the system database, because this is done automatically.

Business Case

There are a variety of business and technical use cases for the scenario when a new tenant has to be created. From an administrative perspective, it is very important to get details before executing such a task. Every created and started tenant uses system resources of the SAP HANA system, as there is DRAM, CPU and disk allocation.

In some business use cases, tenants are needed to consolidate heterogeneous data originating from multiple systems, regions, business units, and other criteria. Overall, migration aspects play an important role when further tenants have to be established. Beside those productive use cases, testing aspects and technical considerations might also be a trigger to create new tenants.

Generally speaking, new tenant databases are created in the following scenarios:

  • … at any time, if there is a business need or technical demand

  • … after installation, if no initial tenant was created

  • … converting a single-container system to a multiple-container system

  • … for testing purposes, in order to encapsulate data

  • … and in many other use cases

Tenant Database Creation

Typically the system administration creates the tenant databases, and then configures these new tenants as required. Here are some examples for configuration aspects:

The more comprehensive, time-consuming, and demanding task is the configuration. It covers a broad spectrum of different areas. Some examples are:

  • Changing the database isolation level (including further preparation and follow-up tasks on OS level)

  • Disabling certain features that are not required in tenant databases (for example backup operations)

  • Enabling and configuring cross-database access (if queries between tenant databases are requested)

  • Editing the configuration change blocklist, so that critical system properties cannot be changed by tenant database administrators

  • Configuring the SAP Web Dispatcher (if tenant databases are accessed by HTTP clients using the SAP HANA XS classic server)

    Nevertheless, these topics are not the core part of the discussion in this lesson, and it is clear to state: When creating a new tenant database, the smallest piece of work is the creation process. The administrative tasks start after this procedure is finished.

Note

In this lesson, the focus is on creating, renaming, and deleting a tenant database. These administrative tasks can be done by using the SAP HANA cockpit, or alternatively using command-line tools with SQL statements.

Tenant-related administrative tasks are handled by the system database administration, and require the system privilege DATABASE ADMIN. You can perform tenant-related tasks such as creating, starting, and stopping tenant databases using the SAP HANA cockpit or SQL statements.

Note
If the system is configured for high isolation, the operating system (OS) user and group required for the new tenant database already exist.

Creating a Tenant Database

You create tenant databases from the system database using the Database Management application in the SAP HANA cockpit as follows:

  1. In the SAP HANA cockpit, open Database Management.

  2. Choose Create TenantCreate Empty Tenant.

    The Create Tenant Database page opens.

  3. Enter the name of the new database and the password that will be used to create the user SYSTEM.

    Note

    When creating a tenant database, you must specify a unique name for the tenant database

    The password must initially comply with the password policy configured in the system database. Once the database is created, you can change the password policy for the tenant database.

  4. Optional: Specify the OS user and group of the tenant database, if the system in which you are creating the tenant database is configured for high isolation.

  5. Optional: Prevent the database from being started automatically after creation (automatic start by default).

  6. Optional: If the system is distributed across multiple hosts, specify the host on which the database should be created.

  7. Optional: Specify the number of the internal communication port of the coordinator index server.

  8. Optional: Add any other required service(s).

  9. Choose Create Tenant Database.

Technically, the creation process runs in the background as follows:

  1. The database is assigned a unique system local ID.

  2. If you did not specify host information, load-balancing algorithms determine optimal host placement.

  3. If you did not specify the number of the internal communication port, it is assigned automatically based on the port number availability.

  4. The necessary data and log volumes are created on the affected hosts.

  5. The new database is entered in the M_DATABASES system view of the system database.

  6. The daemon.ini file is updated and the daemon process is triggered to start the indexserver service, and any other added services on each configured host.

  7. The specified password is set for the SYSTEM user in the new tenant database.

  8. The new tenant database is created and possibly started, and appears in Database Management application. It is now also accessible in the M_DATABASES view.

SQL Command to Create a Tenant

Note

This statement is available only for the SYSTEMDB in a multitenant database system.

You create a tenant database using the CREATE DATABASE SQL statement as follows:

CREATE DATABASE <database_name> [ AT [ LOCATION ] '<hostname>[:<port_number> ] ' ] SYSTEM USER PASSWORD <password>

Example to create a tenant database via SQL (with automatic host placement and automatic port assignment):

CREATE DATABASE HA200NEWDB SYSTEM USER PASSWORD Welcome1

Note

In multihost scenarios, you can specify further options like the name of the host to create the tenant database on. For the complete SQL syntax, see SAP HANA SQL Reference Guide for SAP HANA Platform.

Tenant Database Rename

You can rename tenant databases from the system database using the Database Management app in the SAP HANA cockpit, or using an SQL statement with a command-line tool like hdbsql.

Prerequisites

When a tenant rename takes place, the tenant will be stopped. Concerning the authorizations, the same applies as for the creation of a tenant. The action can only be done as system database administrator with the system privilege DATABASE ADMIN. Other concurrently executed operations like copy or move tenant, as well as an active system replication, or if the option dynamic tiering is in use, would lead to an erroneous situation.

Within the procedure, the database is renamed, and the on-disk directories that contain the tenant name are also renamed. Existing backups are not renamed, but backup history remains continuous. From a business content point of view, the data and log volumes are not affected.

By default, the renamed tenant database will not start automatically after renaming, but there is an option to start the tenant when, for example, the SAP HANA cockpit is used for the renaming action (see graphic below).

SQL Command to Rename a Tenant

Note
This command is available only for the SYSTEMDB in a multitenant database system.

You create a tenant database using the RENAME DATABASE SQL statement as follows:

RENAME DATABASE <current_database_name> TO <new_database_name>

Example to rename a tenant database via SQL:

RENAME DATABASE HA200NEWDB TO HA200RENAME

Note
Backup directories that were previously in use, and backups that are written to third-party backup tools, are not deleted when a tenant database is renamed. So, take care about the backups and if possible, perform a complete backup directly after the procedure when the renamed tenant was started.

Tenant Database Deletion

You can delete tenant databases that are no longer required. You delete tenant databases from the system database using the Database Management app in the SAP HANA cockpit.

As with other tenant handling tasks, only the system database administrator with assigned system privilege DATABASE ADMIN is allowed to drop a tenant database. In addition, the tenant database is stopped. Either the administrator stops the database manually, or it will be stopped automatically by the SAP HANA system before the delete operation occurs.

If you delete a tenant database that is running SAP HANA 2.0 SPS01 or later, you have the option to keep the backup directories of the deleted tenant (see figure above). Backups can then only be removed by deleting them from the file system. If you delete a tenant database that is running an earlier version of SAP HANA, the backup directories will be deleted automatically. It is therefore recommended that if you want to preserve these backup directories, you should relocate them before deleting the database.

Note

Once you have deleted the tenant, you can still access and consume any undeleted database backups by creating a new tenant with the same name. This will only work if the system was not configured for high isolation.

Once deleted, the tenant database disappears from the list. Volumes and trace files are removed.

SQL Command to Delete a Tenant

Note

This command is available only for the SYSTEMDB in a multitenant database system.

You delete a tenant database using the DROP DATABASE SQL statement as follows:

DROP DATABASE <database_name> [ DROP BACKUPS ]

Example to delete a tenant database via SQL:

DROP DATABASE HA200NEWDB

The option DROP BACKUPS drops all current backup directories for the tenant database.

Backup directories that were previously in use, and backups that are written to third-party backup tools, are not deleted.

Further Information

Note
In the context of administrative tasks based on tenants, you might find it helpful to use the following resources:

Log in to track your progress & complete quizzes