How To Convert HANA to a Multi-Tenant HANA Database

In this blog I explain how to convert an existing Hana database into a Multi-Tenant Database.
But before we start I will provide you a short introduction to multi-tenant databases:

In the past the following choices were available when installing a SAP HANA Database

  • One SAP HANA DBMS, one database, one application and one schema.
  • One  SAP HANA DBMS, one database, several applications, several schema’s (MCOD)
  • More than one SAP HANA DBMS (one DB in each) 1-n applications, 1-n schemas (MCOS)
  • SAP HANA Virtualized

Multitenancy refers to a principle in software architecture where a single instance of the software runs on a server, serving multiple tenants. A tenant is a group of users sharing the same view on a piece of software they use. With a multitenant architecture, a software application is designed to provide every tenant a dedicated share of the instance including its data, configuration, user management, tenant individual functionality and non-functional properties. Multitenancy contrasts with multi-instance architectures where separate software instances operate on behalf of different tenants.

source: http://en.wikipedia.org/wiki/Multitenancy

How-To: Convert to a Multi-Tenant HANA Database

Single database container is called a Tenant Database, you can run multiple tenant databases on one SAP HANA System while still having only one software version for a SAP HANA system. Some advantages are:

  • Strong separation of data and users
  • Backup and restore available by tenant database
  • Resource management by tenant (CPU, memory)

When installing a multi-tenant database you have two options:

  1. Start from scratch with a new SPS09 installation, during the installation you get the option to install a single container or a multi container database node

    How-To: Convert to a Multi-Tenant HANA Database

  2. Convert an existing single server to a multitenant database, please note that the change is permanent and cannot be reversed.

Since the installation from scratch is basically not much different from the past, with the exception of the screenshot shown above, I will focus on converting an existing database in this Blog. I will add future blogs describing details regarding configuration and maintenance of a multitenant database which will be the same for a new installation and converted system.

Prerequisites

Before you can actually convert a database you have to apply to the following pre-requisites:

  • The Statistics server has been migrated or removed
  • Your HANA version is on SPS09 or newer

Migrate Statistics Server

As of SPS07 you can migrate the statistics server from a separate process to be part of the nameserver process. When you didn’t do this before please look at OSS notes “1917938 and 1925684” before executing the steps below. Since only installing a database on SPS09 with the “multiple_container” option enabled will install the database by default with the new Statistics server, you will be running the ‘old’ statistics server unless you manually migrated it already before.

First you need to check whether you are still using the old statistics server, the easiest way to do this is via the operating system using the sapcontrol command to check the processes (sapcontrol -nr <instance number> -function GetProcessList). When you see a separate process for the statistics server (as shown below), you are still working with the old statistics server and need to migrate it first.

How-To: Convert to a Multi-Tenant HANA Database

Migrating the statistics server is a simple process, just open the HANA studio and go to the configuration. From there adjust “nameserver.ini -> statisticsserver -> activate=true”.

How-To: Convert to a Multi-Tenant HANA Database

After some minutes, you will see that the statistics server is shutting down and eventually it will be removed from the list.

How-To: Convert to a Multi-Tenant HANA DatabaseHow-To: Convert to a Multi-Tenant HANA Database

Tip: when you add “watch -n 1” in front of the sapcontrol command you don’t have to repeat the command each time manually to refresh.

Finally you can run the SQL Statement “SELECT * FROM _SYS_STATISTICS.STATISTICS_PROPERTIES where key = ‘internal.installation.state’” to check if the migration went fine

How-To: Convert to a Multi-Tenant HANA Database

Check for the correct HANA version

A second prerequisite you have to check is the correct HANA version, you most probably already know on which version you run but you can also check it with the steps below (as provided by the admin guide):

First you need to stop the database with the command “HDB stop” and then you execute the command “hdbnsutil -exportTopology <file_name>”. When you run the command with the database still running you will get an error message:

checking for inactive nameserver …

nameserver phyhandb4-prd:30001 is still active. cannot open its persistence failed

How-To: Convert to a Multi-Tenant HANA DatabaseHow-To: Convert to a Multi-Tenant HANA Database

Now open the exported file in the VI editor and look for the line “topologyVersion” and check that the value is 15 or higher.

Convert Database

Now that the prerequisites are met we can convert the database to a multi-tenant database. In our test we used an empty database, to show you that the existing content is indeed still available after you convert de database we created and empty schema called “CONTENT-MIGRATION” through the SQL command: “create schema “CONTENT-MIGRATION” OWNED BY SYSTEM;”

How-To: Convert to a Multi-Tenant HANA Database

Before you can convert the database you have to stop the database with the command ”HDB stop”, then run the command “hdbnsutil -convertToMultiDB” to do the actual conversion.

How-To: Convert to a Multi-Tenant HANA Database

How-To: Convert to a Multi-Tenant HANA Database

This only takes a few seconds (for an empty database) after which the database is converted. This action executes the following steps:

  • Set “multidb mode” flag in the configuration
  • Create the system and tenant database
  • Updates the secure store in the file (SSFS) system.

The newly created database maintains the original data and has the original port configuration, the initial “HDB start” command only starts the system database though. Therefore it shows that you can start the tenant database by a SQL statement, after you do this it will automatically start when using the “HDB start” command in the future.

However, when you try to connect with hdbsql to this new SystemDB database it will fail with the error:
* 10: invalid username or password SQLSTATE: 28000

This is because you need to reset the password of the SYSTEM user for the SystemDB database. So make sure the database is still stopped (otherwise stop it with “HDB stop”) and then reset the system password of the user “SYSTEM” with the command “hdbnameserver -resetUserSystem”. When asked type a new password and press enter.

How-To: Convert to a Multi-Tenant HANA Database

When the System password has been reset you have to start the database with “HDB start” which will only start the System Database. You can see this with the sapcontrol command, this does not show an additional index server for the tenant database.

How-To: Convert to a Multi-Tenant HANA Database

Now connect to the database with the hdbsql command “hdbsql -i <instance number> -n <hostname>:3xx13 -d SystemDB -u SYSTEM” after which the system will ask you for a password. You can see that you are connected when the prompt shows “hdbsql SystemDB=>”.

How-To: Convert to a Multi-Tenant HANA Database

Now you have to start the tenant database with the SQL command “ALTER SYSTEM START DATABASE <SID>”. But when you do this, the system will complain that the user is forced to change its password:

* 414: user is forced to change password: alter password required for user SYSTEM SQLSTATE: HY000

Solve this by running the SQL command “ALTER USER SYSTEM PASSWORD <new_password>”. Now run the SQL command ““ALTER SYSTEM START DATABASE <SID>” again and it should work (takes a few seconds before you get your prompt back).

How-To: Convert to a Multi-Tenant HANA DatabaseHow-To: Convert to a Multi-Tenant HANA Database

After doing this you can exit the hdbsql command and check if the tenant database is running with the “sapcontrol -nr <instance> -function GetProcessList” command, it should now show an additional XS engine and index server instance “e.g. indexserver-TST and xsengine-TST, where our SID is TST”.

How-To: Convert to a Multi-Tenant HANA Database

Add a Multi-Tenant Database to the SAP HANA Studio

In order to add a multi-tenant database to the HANA studio, you first have to install HANA studio version “Version: 2.0.7”. After that add a system as usual (right click and choose the option “Add System”).

How-To: Convert to a Multi-Tenant HANA DatabaseHow-To: Convert to a Multi-Tenant HANA Database

When adding a “normal” system with the new studio you choose the option “Single Container” but when adding a multi-tenant database you choose the option “Multiple Containers”. Each multi-tenant database has one System Database and one or more Tenant Databases. First we will add the System Database to the studio.

The system user password of the SystemDB database is the one that you have reset in the previous steps.

How-To: Convert to a Multi-Tenant HANA Database

As you can see, the SystemDB database looks as a normal database but it is only used to maintain the tenant database and not to hold corporate data. As shown in the database above the SystemDB does not contain the schema called “CONTENT-MIGRATION” which we created earlier.

When you choose to install a new SPS09 Database from scratch with the option “Multiple_Containers” then you will have only a SystemDB after the installation and you have to add the tenant databases manually after the installation.

How-To: Convert to a Multi-Tenant HANA DatabaseHow-To: Convert to a Multi-Tenant HANA Database

After the SystemDB is added to the studio you can also add the tenant database. The process is the same, but now you choose the option “Tenant database container” and you provide the tenant database name. The System user password of this database is the same as the original database before converting it to a multi-tenant database.

As you can see this tenant database does contains the schema called “CONTENT-MIGRATION” which we created before we converted to a multi-tenant database.

How-To: Convert to a Multi-Tenant HANA Database

Now you have converted a single database to a multi-tenant database, in future blogs I will write more details about adding and dropping a tenant database and also provide more information regarding the configuration of a multi-tenant database (e.g. XS engine access).

 

 

Leave a Reply