Skip to Content

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)/wp-content/uploads/2014/12/2014_12_17_09_25_35_619282.png

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

/wp-content/uploads/2014/12/2014_12_16_13_14_35_619301.png


  • 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./wp-content/uploads/2014/12/2014_12_16_12_48_46_619304.png

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

/wp-content/uploads/2014/12/2014_12_16_12_54_53_619305.png

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

/wp-content/uploads/2014/12/2014_12_16_12_56_44_619306.png/wp-content/uploads/2014/12/2014_12_16_12_57_26_619310.png

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


/wp-content/uploads/2014/12/2014_12_16_12_59_23_619311.png

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

/wp-content/uploads/2014/12/2014_12_16_15_45_34_619312.png/wp-content/uploads/2014/12/2014_12_16_15_46_42_619316.png

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;

/wp-content/uploads/2014/12/2014_12_16_15_44_04_619317.png

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.


/wp-content/uploads/2014/12/2014_12_16_13_09_50_619318.png/wp-content/uploads/2014/12/2014_12_16_13_11_33_619319.png

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.

/wp-content/uploads/2014/12/2014_12_16_13_52_29_619321.png

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.

/wp-content/uploads/2014/12/2014_12_16_13_55_57_619325.png

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


/wp-content/uploads/2014/12/2014_12_16_14_13_15_619327.png

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


/wp-content/uploads/2014/12/2014_12_16_16_25_42_619329.png/wp-content/uploads/2014/12/2014_12_16_14_20_10_619330.png

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


/wp-content/uploads/2014/12/2014_12_16_14_21_13_619334.png

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


/wp-content/uploads/2014/12/2014_12_17_07_52_13_619335.png/wp-content/uploads/2014/12/2014_12_17_07_55_12_619336.png


 

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.


/wp-content/uploads/2014/12/2014_12_17_08_04_40_619337.png

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.

/wp-content/uploads/2014/12/2014_12_17_08_17_46_619338.png /wp-content/uploads/2014/12/2014_12_17_07_55_12_619336.png

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.


/wp-content/uploads/2014/12/2014_12_17_08_20_41_619340.png

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

To report this post you need to login first.

30 Comments

You must be Logged on to comment or reply to a post.

  1. Michael Pytel

    Thank you for the blog!  Question – do you have any links handy for official support statements related to the Business Suite and multi-tenant HANA instances?

    (0) 
    1. Nathan Oyler

      Support for SAP HANA multitenant database containers: see SAP note 2096000.


      It refers to several other notes that contain information on Business Suite and multi-tenant instances. 1826100 and 1661202 seem particularly relevant as well.

      (0) 
  2. Ashok Babu Kumili

    Hello Van der Linden.
    Thanks for the Blog. It is very helpful in articulating the possibilities and building solutions….Very nicely written article I’ve learnt the key message . . . 🙂

    High level of technical details.. Rating as 5 Start content.

    -Kind Regards Ashok

    (0) 
  3. Harald Kelber

    Hello Mr van der Linden.

    thank  you for your blog.

    One question left .. is it also possible to migrate to a tenant DB by ..

    – backup single HANA

    – install a new HANA Rev 9x

    – create a tenant DB with SQL

    – recover “tenant DB” from the single DB backup

    or I would have to make a system copy (export/import) ?

    Best Regards

    Harald

    (0) 
    1. N. van der Linden Post author

      Hi Harald,

      that is a very good question 🙂 .

      When I have time this week I will try it out but I think it won’t be possible. The reason for that is that the tenant database have a mismatch of services which has to be the same for a recovery. But If I had some time to try it out I will let it know.

      Regards,

      Nico

      (0) 
      1. Harald Kelber

        Hello Nico.

        thx for the quit response.

        I tried the whole weekend to restore a backup. Always errors like this:

        RECOVER DATA finished with error:

          recovery could not be completed,

          Recovery failed in nameserver startup:

          Error while  topology recovery for user database

        Can not start the service ‘nameserver’ at  ‘q4de3gsy402:32001’ responsible for the volume ‘1’ because an error during  recovery before ….

        I guess you are right. It’s not possible because the handling of the topology information is not compatible between single DB and multi container DB.

        http://help.sap.com/saphelp_hanaplatform/helpdata/en/a8/b7145633dd48399480597a4977d7cc/content.htm

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

        Best Regards

        Harald

        (0) 
          1. Harald Kelber

            Hi Nicholas,

            sounds good , thx. I will try it and inform you about the result.

            It’s hard to consolidate two Rev8x into one Rev9x with two tenant Container + systemDB

            One possible way would be to convert one Rev8x  into Rev9x (as described) and consolidate the second Rev8x with SWPM (export/import).

            Regards

            Harald

            (0) 
              1. N. van der Linden Post author

                Hi nicholas,

                thanks for trying it out. Good to hear that it worked!

                But I believe that Harold originally needed to backup a Single container database and restore that on a tenant database on a multicontainer database (correct me if I am wrong). Although it is good to know this works, I still doubt that the same approach also works for a restore from a single to a multi container tenant database. For that I think you have to do a migration.

                After the migration you could of course take a backup from the new migrated tenant and then restore it with the above process to another tenant if you still need to do that.

                I am glad that the OSS note mentions that it is only a workaround though and that SAP will come with a real solution because restoring a tenant to a different database should be much easier of course 🙂

                Regards,

                Nico

                (0) 
        1. Henrique Pinto

          Your error seems more related to the nameserver/staticserver thing than the DB structure itself. I’ve heard from PM it should be possible to do that (recover a regular DB backup on a tenant DB).

          (0) 
  4. Srikar Vankadaru

    Hi Nico

    Nice blog. When trying to change the SYSTEM user password, I am getting below error:

    assign failed with: _SYS_REPO. stopping service…


    And the service is shutting down. Any thoughts ?

    Thanks

    Srikar

    (0) 
  5. Somayya Nandikolmath

    Hi Nico

    Thanks for these above steps, but once I convert the database to a Multi Database tenant, we are facing issues in starting the HANA Database please find the below error details.

    # su – <SID>adm

    host:/usr/sap/<SID>/HDB03> HDB start

    StartService

    Impromptu CCC initialization by ‘rscpCInit’.

      See SAP note 1266393.

    OK

    OK

    Starting instance using: /usr/sap/<SID>/SYS/exe/hdb/sapcontrol -prot NI_HTTP -nr 03 -function StartWait 2700 2

    17.03.2015 04:59:28

    Start

    OK

    17.03.2015 04:59:44

    StartWait

    FAIL: process hdbdaemon HDB Daemon not running

    HOST:/usr/sap/<SID>/HDB03> hdbnameserver -resetUserSystem

    starting interactive mode for resetting user SYSTEM…

    unclean shutdown of service instance with pid 74228.

    service startup…

    accepting requests at 127.0.0.1:30301; 127.0.0.2:30301

    searching for master nameserver opsvedadev:30301 …

    assign as master nameserver. assign to volume 1 started

    service startup…

    Can not start the service ‘nameserver’ at ‘HOST:30301’ responsible for the volume ‘1’ because an error during recovery before -> stopping instance …

    cannot send signal. (2, No such file or directory)

    prepare for shutting service down…

    triggering timezone checker shutdown…

    preparing to shutdown…

    waiting for assign thread …

    Could you please assist

    Regards

    Somayya M N

    (0) 
    1. Denys Kempen

      What happens during the conversion is that a new SYSTEMDB is created to hold the master data while the original database is converted in a tenant. You can then create additional tenants. To return to a single tenant database you would somehow have to merge all the tenant databases into a single database again including systemdb.

      (0) 
  6. Ning Tong

    Hello Nico,

    A nice blog.

    I have few question about password changed in the second time (* 414: user is forced to change password: alter password required for user SYSTEM SQLSTATE: HY000)

    It changes the password of system db user in SystemDB or tenant DB after executing command ALTER USER SYSTEM PASSWORD <new_password>?

    Regards,

    Ning

    (0) 
    1. N. van der Linden Post author

      Hi Ning,

      If I am not mistaken it is the password of the system user on the systemDB which you just changed in the step before. The tenant database is not started at this stage.

      Kind Regards,

      Nico van der Linden

      (0) 
      1. Ning Tong

        Hi Nico,

        Yes, I agree with you. In my test, the system password of first tenant DB (DB existing before convert) is not changed at all after conversion. 🙂

        Regards,

        Ning

        (0) 
  7. Praveen Perala

    Hi Van der Linden,

    After converting single to multi tenant I was able to start the database but I was not able to add it as a multi tenant system in Hana Studio, select * from “SYS”.”M_DATABASES” showing it as a SingleDB. The database size is 40GB.

    I see few DB_<SID> directory got created under data, log, trace directories.

    Any thoughts??

    Thanks

    Praveen

    (0) 
  8. Mauricio RamĂ­rez Cuevas

    Hello Nico,

    Excellent blog!

    I performed a convertion to MDC using your method however somehing was wrong because role  sap.hana.admin.cockpit.sysdb.roles::SysDBAdmin does not exist in SystemDB.

    Could you check if that role exists in your system? do you have an idea if I failed in some step?

    Thank you

    (0) 
    1. Tilman Giese

      Actually, converting with HDBLCM is the only supported procedure by now. Using hdbnsutil directly as described in the initial blog post is and never was supported and can lead to a variety of different problems, especially with recent service pack stacks.

      The HDBLCM conversion procedure was added with SPS 11. On earlier revisions, you should run
      python python_support/convertMDC.py –change=mode

      Kind regards,
      Tilman

      (1) 
  9. N. van der Linden Post author

    Hi Anand,

    since you are using multiple tenants in an MDC system you also need to be able to connect to a seperate XS engine on which the tenant “runs”. Please check chapter 8.1.8.3 (Configure HTTP(S) Access to Multitenant Database Containers) of the Hana administration guide.

    Kind Regards,
    Nico van der Linden

    (0) 
  10. Jorge Pinaud

    Very good article.
    Can you tell me how long does it takes to convert an existing HANA Database to Multitenant Database Container? Does it depend on the database size? For example, if I have a SAP BW system with a 1 TB database how long does it takes to convert to MDC?
    Regards,

    Jorge.

    (0) 
  11. Aldi H

    Hi, i’m trying to install HANA using method MCOS, HANA BW and HANA ERP in one system (server). The question is :

    1. Do i have to assign two IP addr in that one server ; one for HANA BW and one for HANA ERP and then each IP have their own Hostname so that when install hana, each HANA using each local hostname and “Certificates Hosts Properties” ?
    2. Is the HDBDaemon (nameserver,indexserver, etc) that belong to HANA 1 will have conflict port number with HDBDaemon that belong to HANA 2 ? or each HANA will generate unique port number when installation process ?

    Thank you.

    Regards,

    Aldi

     

    (0) 
  12. Anjali Juneja

    Hi,

    I am looking for configurations that would be required to change on the application side (ERP) to adapt to the conversion of Single DB to MDC?

     

    Can we have the steps that you have followed on the SAP Application side (ERP)?

     

    Thanks

    Anjali

    (0) 

Leave a Reply