Symptom
You face issues or have questions related to multitenant database containers in SAP HANA environments.
Environment
SAP HANA Database SPS 09 and higher.
Instructions in this Knowledge Base Article has been created on SAP HANA Database Revision 090 (Support Package Stack SPS09) and may differ in other revisions.
Cause
- What is SAP HANA Multitenant Database Containers?
- From which revision is multiple database containers supported?
- Will my system automatically be converted to multiple container after I upgrade to SPS9?
- How is installation different from single-container to multi-container?
- What happens after the installation of a multi-container system?
- Is there any difference in the list of services running on SYSTEM DB and Tenant database?
- Usually we connect to SAP HANA Database through indexserver which also serves as SQL port so how do we connect to system DB when there is no indexserver?
- Why do we see indexserver port other than 303 on tenant databases?
- How many tenant databases can be created per instance?
- Is it possible to overcome this limitation of 20 tenants per instance in case there is sufficent resources available?
- Can I migrate my existing single-container to multi-container?
- How can I convert SAP HANA database from single-container to multi-container?
- Which process are triggered during the conversion from single-container to multi-container?
- After I successfully converted from single-container to multi-container none of my user/password is valid, what is the reason?
- Where is statisticsserver running on SYSTEM DB when there is no indexserver?
- Is it possible to allocate more resources to one tenant database compared to others?
- While performing an installation of SAP HANA multi-container database will a tenant DB also be created as in the case of conversion?
- Is my tenant DB accessible when my SYSTEM DB is down?es?
- If Out of Memory (OOM) occurs on one tenant database will it have an impact on other tenant databases?
- Is there global_allocation_limit per tenant?
- Can we configure statement memory limit per tenant?
- Where to find information regarding sizing of multitenant database containers?
- How can I make sure tenant database administrators do not assign more resources to one tenant?
- Is cross database access enabled by default?
- Which system privilege is required for administrating tenant databases when being connected to the system DB?
- Who can start/stop a tenant database?
- Which trace files are specific to the tenant database?
- What is database isolation in multi container system?
- How can I configure the system with high isolation?
- Is there a specific process that runs in multicontainer database containers with high isolation?
- Is there any prerequistes to be considered when creating tenants on MDC system with high isolation?
- Can I start backup of tenant as tenant administrator?
- Is it possible to backup the complete database including the system db and tenant db in one step?
- How can I start/stop tenant databases?
- What possibilities exist to start/stop entire system including system database and tenant database?
- Is it possible to copy/move tenant databases between systems?
- Is it possible to rename a tenant database?
- What is the naming convention followed for volume id in multitenant database?
- In MDC environment where is the topology information stored?
- What is the order of service startup within a SAP HANA multitenant system?
- Is it possible to do housekeeping on backup catalog of tenant databases from SYSTEM DB?
- Is there any restriction in accessing the hdbcons console in SAP HANA Studio for a multitenant system?
- Is it possible to display in SAP HANA studio all services belonging to the SYSTEM DB and to every tenant database in one screen?
- Are there HANA sql scripts dedicated for multitenant systems?
- I would like to convert my single container system to MDC, is the conversion time for MDC dependant on the database size?
- Can I set different isolation levels for different tenants?
- Is there any restriction in executing SAP HANA SQL statements on a MDC system?
- Is it necessary to schedule SAP HANA Table consistency checks separately on SYSTEM DB and tenant databases?
- Is it possible to convert SAP HANA 1.0 single container system to SAP HANA 1.0 MDC system with HANA System Replication?
- How to setup connection to SAP HANA MDC system for SAP Support to access customer system?
Resolution
1. What is SAP HANA Multitenant Database Containers?
An SAP HANA system can now be installed in multiple-container mode, which means it is capable of containing multitenant database containers.
A multiple-container system always has exactly one system database and any number of multitenant database containers (including zero), also called tenant databases.
Refer SAP HANA Administration Guide for more information.
2. From which revision is multiple database containers supported?
Starting SAP HANA 1.0 SPS 09 (Revision 90), SAP HANA supports multiple isolated databases or multitenant database containers in a single SAP HANA system.
Starting SAP HANA 2.0 SPS 01 (Revision 10), multitenant database containers will become the default and the only operation mode, refer SAP Note 2423367
3. Will my system automatically be converted to multiple container after I upgrade to SPS9?
No, after you upgrade to SPS9 from any lower revision your system will still be single container.
Starting SAP HANA 2.0 SPS1, multitenant is the default option and single container system will no longer be available. So if you upgrade single container system from any lower version to HANA 2.0 SPS1 the system will automatically be converted to multitenant system.
4. How is installation different from single-container to multi-container?
Installation is performed using the same tools (hdblcm/hdblcmgui), during SAP HANA installation you have an option to choose between single-container and multi-container
Pic1.png
5. What happens after the installation of a multi-container system?
Once the installation is complete, only SYSTEMDB is created. Tenant DB's can be created from the SYSTEMDB using SQL, refer SAP HANA Administration Guide.
6. Is there any difference in the list of services running on SYSTEM DB and Tenant database?
The most notable differences are
nameserver and other non-persistent services are available only on SYSTEMDB.
Indexserver and other persistent services are available only on tenant databases
7. Usually we connect to SAP HANA Database through indexserver which also serves as SQL port so how do we connect to SYSTEM DB when there is no indexserver?
In a multi-container system the connection to System DB happens through Nameserver which serves as SQL port.
Pic2.png
Though there is no difference in tenant databases, which still uses the indexserver port for the connection.
Pic3.JPG
8. Why do we see indexserver port other than 303 on tenant databases?
By default, the first tenant database uses port 3xx03 for indexserver and 3xx07 for xsengine as in SAP HANA 1.0 single container system.
2nd tenant uses port ranges 3xx40-42, the third tenant uses ports 3xx43-45, and so on.
Internal communication port (3xx40-3xx97)
SQL (3xx41-3xx98)
HTTP (3xx42-3xx99)
9. How many tenant databases can be created per instance?
The main limitation is the resources required for each tenant database (CPU, Memory). Further there is a limitation with regards to the available ports 3XX40 until 3XX99, hence by default with each instance you could have a maximum of 20 tenant databases.
To overcome this limitation see next question.
10. Is it possible to overcome this limitation of 20 tenants per instance in case there is sufficent resources available?
Yes, we can increase this number by reserving the port numbers of further instances.
We do this by configuring the property global.ini -- [multidb] -- reserved_instance_numbers
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).
Thus we are reserving additional ports to one instance.
11. Can I migrate my existing single-container to multi-container?
Yes, you can migrate from single-container to multi-container provide you fulfill the prerequisites.
Please note this action is irrevocable.
The prerequisites are:
The SAP HANA Database is upgraded to revision 090 or higher
Embedded statistics server is enabled
12. How can I convert SAP HANA database from single-container to multi-container?
To convert a database from single container to multicontainer we need to execute the python convertMDC.py
Conversion.png
13. Which process are triggered during the conversion from single-container to multi-container?
The process triggers the following actions:
1. Shuts down the instance
2. Converts the system to support multitenant database containers by:
○ Setting the global.ini property [multidb] mode to multidb.
○ Creating the system database (SYSTEMDB@).
○ Converting the existing SAP HANA database to a tenant database. The name of this database is the system ID (@).
○ Updating the secure store in the file (SSFS) system.
3. Exports the topology
4. Re-initializes the nameserver persistence of the system database
5. Imports the topology
6. Set SYSTEM user password for system database
7. Starts the instance
14. After I successfully converted from single-container to multi-container none of my user/password is valid, what is the reason?
All the user/password on the single-container is now valid on your tenant database only and NOT on system DB.
15. Where is statisticsserver running on SYSTEM DB when there is no indexserver?
The embedded statisticsserver consists of two components.
A scheduler and workers that run in each database including system database. There is only one scheduler, but it will trigger the execution of workers in all databases.
Statistics service in System DB
Stat_SystemDB.png
Statistics service in Tenant DB
Stat_TenantDB.png
16. Is it possible to allocate more resources to one tenant database compared to others?
Yes, you can assign more resources to one tenant database if required.
Refer section Managing Resources in Multiple-Container Systems in SAP HANA Multitenant database containers operation guide.
17. While performing an installation of SAP HANA multi-container database will a tenant database also be created as in the case of conversion?
Yes, starting with SAP HANA 2.0 SPS02 Revision 21 an installation creates a tenant database is created along with System DB.
In addition, if you need additional tenants you can create them either using SAP HANA Cockpit or by using below ALTER command:
CREATE DATABASE SYSTEM USER PASSWORD
In case of scaleout landscape and you want to create a tenant DB on a specific host
CREATE DATABASE AT LOCATION 'HOST_A:30147' SYSTEM USER PASSWORD
18. Are tenant databases down when SYSTEM DB is down?
When SystemDB is down all tenant databases are also down.
19. If Out of Memory (OOM) occurs on one tenant database will it have an impact on other tenant databases?
In general we strongly recommend to set allocation limits, but if no allocation limits are set then OOM on one tenant database can also lead to OOM on other tenant databases.
20. Is there global allocation limit per tenant?
No, the global allocation limit set on system level is valid for the complete system including all tenant databases and all services.
If you would like to restrict the memory allocation for specific tenants it is recommended to restrict using the parameter
global.ini --> [memorymanager] --> allocationlimit
For more information refer section "Managing Resource Usage of Tenant Databases" of SAP HANA MDC Guide
21. Can we configure statement memory limit per tenant?
Yes, it is possible to configure statement memory limit per tenant.
22. Where to find information regarding sizing of multitenant database containers?
Please refer to SAP Note 2096000
23. How can I make sure tenant database administrators do not assign more resources to one tenant?
A configuration change blacklist exists to prevent tenant database administrators to make changes to parameters related to resource management.
24. Is cross database access enabled by default?
No, cross database access is NOT enabled by default.
For information regarding enabling cross database access refer to SAP HANA Administration Guide.
25. Which system privilege is required for administrating tenant databases when being connected to the system DB?
DATABASE ADMIN
Please note this privilege is only available on SYSTEM DB.
26. Who can start/stop a tenant database?
Only a System DB user with DATABASE ADMIN system privilege can start/stop a tenant DB using following SQL.
ALTER SYSTEM STOP DATABASE <database_name>
ALTER SYSTEM START DATABASE <database_name>
You could also use SAP HANA Cockpit to start/stop the tenants when accessed through System DB.
Please note that tenant DB administrators cannot start/stop the tenant DB.
27. Which trace files are specific to the tenant DB?
All tenant specific processes would have tenant specific trace files. Tenant specific processes are: indexserver, xsengine, scriptserver, dpserver, esserver, etsserver (this list may grow in the future).
Trace directory of System DB (This also includes folder for different Tenant DB)
Pic8.JPG
Now look into specific tenant DB trace directory (Folder --> DB_TENANATDB), depending on the tenant specific processes configured in the tenant databases we might see additional traces.
Pic9.JPG
Under the process list you would just see additional indexserver for each tenant DB in addition to the processes that are running under the System DB
Pic10.JPG
28. What is database isolation in multi container system?
In order to protect against unauthorized access at the operating system (OS) level, it is possible to increase isolation further through OS user separation and authenticated communication within databases.
By default, all database processes in a multiple-container system run under the default OS user adm. If it's important to mitigate against cross-database attacks through OS mechanisms, you can configure the system for high isolation.
29. How can I configure the system with high isolation?
The isoltaion level can be configured at the time of installation or could be increased for an already existing system.
Example:
Isolation_Installation.png
For increasing the isolation levels of an existing system, refer Multitenant Database Containers Operations Guide
30. Is there a specific process that runs in multicontainer database containers with high isolation?
Yes, when high isolation is selected there is the process hdbmdcdispatcher running under root user while all other processes of System DB runs under sidadm user.
Example
Root_user.png
This process is necessary for high isolation while creating tenant database so that indexserver process of the tenant DB runs under dedicated user.
Example:
SID: H00 --> all processes of System DB is running under h00adm
Tenant DB: MDC --> indexserver process of the tenant DB is running under user mdcadm
High_isolation_processes.png
31. Is there any prerequistes to be considered when creating tenants on MDC system with high isolation?
When using high isolation it needs to be noted that the dedicated OS user and group already exists before the tenant creation.
32. Can I start backup of tenant as tenant administrator?
It is possible to take backup directly on the tenant database via SQL but the prerequisite is the user needs to have BACKUP ADMIN and CATALOG READ privileges.
As tenant database administartor you could also use SAP HANA Cockpit to trigger data backups.
33. Is it possible to backup the complete database including the system db and tenant db in one step?
No, it is not possible to perform the backup of all tenants and system db in one step. Backups needs to be performed individually.
The same also applies for recovery.
34. How can I start / stop tenant databases?
Tenant databases can be started/stopped by the following ways:
Using SAP HANA Cockpit
Using SQL statement
35. What possibilities exist to start / stop entire system including system database and tenant database?
Two possibilities exist as of SAP HANA SPS11
Using SAP HANA Studio (While using SAP HANA Studio please note stopping System DB would automatically stop tenant databases)
Using OS command (HDB stop would stop both System DB and tenant databases)
Please note upon start up of System DB the tenant will have the same status it had before the system was stopped (For example, when the tenant db was offline even a restart of the system will not change the state similarly if the tenant db was online before the system shutdown after the system restart the tenant will continue to be online without any manual intervention)
36. Is it possible to copy / move tenant databases between systems?
Starting SAP HANA SPS 12, SAP HANA uses system replication mechanism to copy and move securely with near-zero downtime.
For more information refer SAP HANA Administration Guide
37. Is it possible to rename a tenant database?
Yes, starting SAP HANA 2.0 SPS02 there is a possibility to rename a tenant database using cockpit or by using RENAME DATABASE statement from SystemDB.
Refer SAP HANA SQL Reference
38. What is the naming convention followed for volume id in multitenant database?
In MDC multiple services can have the same volume ID but each volume ID will be assigned to database ID which makes it unique.
Look at following example:
If you look at the topology, you will see three indexservers which has the same volume id but different database id’s
indexserver=
30040=
activated_at=2016-11-19 13:06:38.580
active=no
database=3
pid=126361
start_time=2016-11-19 13:05:57.821
volume=2
30043=
activated_at=2016-12-12 22:30:53.429
active=yes
database=4
pid=124587
start_time=2016-12-12 22:30:18.074
volume=2
30046=
activated_at=2016-11-19 13:06:32.388
active=no
database=5
pid=126365
start_time=2016-11-19 13:05:57.756
volume=2
The subpath just combines both Volume id and Database id (volumeid.databaseid)
3:2= [where Database ID=3 and Volume ID=2]
active=no
catalog=yes
database=3
location=hostabc:30040
path=mnt00001/hdb00002.00003
servicetype=indexserver
4:2= [where Database ID=4 and Volume ID=2]
active=yes
catalog=yes
database=4
location=hostabc:30043
path=mnt00001/hdb00002.00004
servicetype=indexserver
tenant=-
5:2= [where Database ID=5 and Volume ID=2]
active=no
catalog=yes
database=5
location=hostabc:30046
path=mnt00001/hdb00002.00005
servicetype=indexserver
tenant=-
39. In SAP HANA MDC environment where is the topology information stored?
In SAP HANA MDC the topology information is stored in index server, you can find this while performing backup.
The backup of a tenant will contain two files for indexserver one for topology and another for the data.
Topoology.png
40. What is the order of service startup within a SAP HANA multitenant system?
The services start as per the defined runlevels, the assumption that all SYSTEM DB processes should be up and running before processes of Tenant DB is false.
Since the runlevels are for various services you might see that some services from SYSTEM DB are started initially then the services from Tenant DB are started and then again the services from SYSTEM DB are started, these entire process is just according to the runlevels configured in the system.
If for some reason a service fails to start it might prevent services of higher runlevels from starting. In order to resolve this issue the system administrator should stop the failing tenant database to get all other services running.
For e.g) If indexserver (runlevel 3) of tenant fails to start it would prevent webdispatcher (runlevel 4) from starting. Now if the tenant database of the failing indexserver is stopped it would allow the services with higher run-level to start automatically.
41. Is it possible to do housekeeping on backup catalog of tenant databases from SYSTEM DB?
Yes, backup catalog housekeeping of a tenant database can be done via SYSTEM DB using SQL.
BACKUP CATALOG DELETE FOR <TENANT_NAME> ALL BEFORE BACKUP_ID 1484146709635 COMPLETE
You could also perform this action directly on the tenant either directly in backup console or using SQL without providing TENANT NAME.
42. Is there any restriction in accessing the hdbcons console in SAP HANA Studio for a multitenant system?
Yes, usually you can access hdbcons console via SAP HANA Studio which connects to indexserver process but in a multitenant system this is only possible on System DB which automatically connects you to the Nameserver.
If you want to connect to indexserver port using the console you need to do it via the OS.
Refer to SAP Note 2222218.
43. Is it possible to display in SAP HANA studio all services belonging to the system DB and to every tenant DB in one screen?
Yes, you can display all services belonging to system database and tenant databases in diagnosis mode of SAP HANA Studio.
44. Are there HANA sql scripts dedicated for multitenant systems?
Yes, there is an overview command for MDC which needs to be executed from System database
SQL: "HANA_Multitenant_Overview"
In multitenant database container (MDC) environments several monitoring views are available in schema SYS_DATABASES for global evaluation. You can access them from the SYSTEMDB and they contain information for the system database and all tenant databases.
SAP Note 1969700 was now extended with MDC related versions of existing SQL statements whenever possible. They are marked with “MDC” in their names.
45. I would like to convert my single container system to MDC, is the conversion time for MDC dependant on the database size?
No, the conversion time is not dependant on the database size.
During conversion the content is untouched, it creates only systemdb persistence and would be the same irrespective of database size.
46. Can I set different isolation levels for different tenants?
No, isolation level is set on system and not per tenant.
47. Is there any restriction in executing SAP HANA SQL statements on a MDC system?
Yes, if would like to execute SAP HANA SQL statements from SAP Note 1969700 the following should be kept in mind
The SQL’s ending with MDC needs to be executed only on SystemDB and it is mentioned in the Restriction section of each SQL.
On the tenant databases you can execute any SQL as you would do in Single Container system.
There is no differentiation between mini checks and can be executed in both System DB and Tenant DB.
Histories aren’t MDC ready, you can only see the current state. Hence you cannot get historized data for statements ending with MDC.
48. Is it necessary to schedule SAP HANA Table consistency checks separately on SYSTEM DB and tenant databases?
Yes, SAP HANA table consistency must be scheduled separately on SYSTEM DB and tenant databases as there is total content isolation.
49. Is it possible to convert SAP HANA 1.0 single container system to SAP HANA 1.0 MDC system with HANA System Replication?
No, conversion from single container to MDC with HANA System Replication is not supported. You would need to disable replication before starting the coversion else the conversion on secondary would fail with the below error.
SAP HANA Lifecycle Management - SAP HANA 1.00.122.17.1526900527
10:03:43.088 - INFO: Start Date: 2018-10-26 - hdblcm
10:03:43.104 - INFO: Performing secondary system check
10:03:43.104 - ERR : The SAP HANA System cannot be converted to multitenant database containers, because it is a system replication site
10:03:43.105 - INFO: Summary of critical errors
10:03:43.104 - ERR : The SAP HANA System cannot be converted to multitenant database containers, because it is a system replication site
You would need to disable repliaction and need to perform the conversion on primary and all connected secondaries in case you have multitier replication.
- How to setup connection to SAP HANA MDC system for SAP Support to access customer system?
Please refer to SAP Note 2280051
Keywords
SYSTEM DB, MDC, MCOS, Multi container, Single container, tenant databases, convert, embedded statisticsserver, out of memory, OOM, global_allocation_limit, DATABASE ADMIN, system privilege, database isolation, start, stop, copy, default operation mode