Convert to a multi tenant hana database

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

In the past you had the following choices when installing an 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 then one SAP HANA DBMS (one DB in each) 1-n applications, 1-n schemas (MCOS)
• SAP Hana Virtualised

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 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. (http://en.wikipedia.org/wiki/Multitenancy)

A 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:

• 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

• Convert an existing single server to a multi tenant database, please not 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 multi tenant database which will be the same for an 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 did not do this before you have to 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 so is from the operating system with 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) then you are still working with the old statistics server and need to migrate it first.

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”.

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

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

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

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 an 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;”

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.

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 an 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.

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.

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=>”.

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

to solve this run 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).

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”.

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”).

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.

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.

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.

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).