Create Tenant Database for SAP HANA Express – by the SAP HANA Academy
Recently, we have published a number of new tutorial videos to the SAP HANA Express edition playlist on our SAP HANA Academy YouTube Channel.
In this playlist you will find video tutorials on miscellaneous topics; some included in the Getting Started with SAP HANA express edition guide (PDF) that comes with the download, others not (yet).
How to create a tenant database is such a topic.
SAP HANA express edition comes as a binary installer or as a preconfigured virtual machine image (ova file). There are two versions for the VM: server only, and server+apps. On both versions, SAP HANA is running as a multitenant database container (MDC) system.
If you are not familiar yet with the topic of MDC in general, please see my blog or check out the MDC playlist on the Academy: http://bit.ly/2dP53hw
SAP HANA Multitenant (MDC) videos now available on the SAP HANA Academy
Two of the most important components of a SAP HANA system are:
- name server (hdbnameserver) – holds information about the topology of the system (hosts, services, data)
- index server (hdbindexserver) – the actual database engine (row store, column store, object store)
There are many other components. Some serve an auxiliary role, like the web dispatcher, compiler, preprocessor, and script server. Other components are added to the system when you install, for example, Dynamic Tiering (extended store server) or Smart Data Integration (data provisioning server).
One of the main differences between a regular SAP HANA system and a multitenant database container system is that with MDC, the database engine for the system database is embedded in the nameserver process. Only tenant databases run in an indexserver process.
At first sight, the system database might look like a regular database and seem like a fine place to store your development artifacts. However, the system database is not a regular database and should only be used to manage the overall system.
The main role of the nameserver is to take care of system and landscape operations. Blowing up the system database with user data interferes with this essential task and might – eventually, potentially – introduce system stability and performance issues.
There are also a few technical restrictions on the system database: you simply cannot add services that process user data. For example, you cannot add the script server required for the Predictive Analysis Library (PAL) or add the data provisioning server (dpserver) required for Smart Data Integration (SDI).
When you try, SAP HANA will respond:
Could not execute ' <command> '
Feature not supported: only applicable to user databases
The same message will be returned when you try to use the system database for any of the SAP HANA options (Dynamic Tiering, Smart Data Streaming, RDSync).
Besides the limitations, you would also miss out on a great feature when using the system database for development, namely database copy (and move). It is very easy to copy and move tenant databases between systems, for example, between a development VM and production system. However, if your development artifacts are stored in the system database, the only way to get them out is by exporting the content as a delivery unit DU).
In other words, before you start to do any serious development with SAP HANA express edition, first create a tenant database.
So how can you do this? It is simple.
1. Watch the video
2. Use the code
-- create a tenant
CREATE DATABASE TESTDB SYSTEM USER PASSWORD Initial1;
-- add the script server service
ALTER DATABASE TESTDB ADD 'scriptserver'
-- or hit two birds with one stone
CREATE DATABASE TESTDB ADD 'scriptserver' SYSTEM USER PASSWORD Initial1;
-- Tenants by default start automatically, but can be started and stopped manually as well
ALTER SYSTEM START DATABASE TESTDB;
-- Stop before you drop
ALTER SYSTEM STOP DATABASE TESTDB;
DROP DATABASE TESTDB;
Blog Series
This blog is part of a series:
- https://blogs.sap.com/2016/11/03/running-sap-hana-express-in-cal-by-the-sap-hana-academy/
- https://blogs.sap.com/2016/10/31/backup-databases-sap-hana-express-sap-hana-academy/
- https://blogs.sap.com/2016/10/27/create-tenant-database-sap-hana-express-sap-hana-academy/
- https://blogs.sap.com/2016/10/21/managing-encryption-keys-sap-hana-express-sap-hana-academy/
Help Portal
Overview of SAP HANA Architecture
Multitenant Database Containers
Copying and Moving Tenant Databases Between Systems
SAP Notes
2104291 – FAQ – SAP HANA multitenant database containers
2101244 – FAQ: SAP HANA Multitenant Database Containers
2096000 – SAP HANA multitenant database containers – Additional Information
Thank you for watching
The SAP HANA Academy provides free online video tutorials for the developers, consultants, partners and customers of SAP HANA.
Topics range from practical how-to instructions on administration, data loading and modeling, and integration with other SAP solutions, to more conceptual projects to help build out new solutions using mobile applications or predictive analysis.
For the full library, see SAP HANA Academy Library – by the SAP HANA Academy.
For the full list of blogs, see Blog Posts – by the SAP HANA Academy.
- Subscribe to our YouTube channel for updates
- Join us on LinkedIn: linkedin.com/in/saphanaacademy
- Follow us on Twitter: @saphanaacademy
- Google+: plus.google.com/+saphanaacademy
- Facebook: facebook.com/saphanaacademy
Thanks for sharing! Ill be sure to go through in detail!
I tried both methods, however, Cockpit and LM apps will not be installed automatically in the new tenant although these are marked as auto-install DUs. Is that correct, or maybe an issue with my setup?
Hi Andreas,
With HANA 2.0, cockpit runs on a separate system.
Lifecycle Management (hdblcm) is a software component, that is an independently installable unit. You can download if from the Software Download Center (support.sap.com/swdc) - it is also included with the full installation bundle ("DVD").
For HANA 1.0, cockpit comes as a delivery unit (DU). You can add this to a tenant database just like you would do for a regular system.
However, SAP HANA cockpit 2.0 also support SPS 12 systems, so you could also consider adding a cockpit node for the administration of your SPS 12 tenant.
Hi, I have a similar question. I created my tenant database using the SQL commands and I can access the database through Eclipse. I would like to understand how I can list all tenant databases (and administer them) through the cockpit. I am running HANA Express through a VM. Thanks
Hi Michael,
In HANA 1.0, cockpit will lists all tenants for the current system but you will have to grant the SYSTEM user of the SystemDB, the sap.hana.admin.cockpit.roles::SysDBAdmin role.
In HANA 2.0, you register each tenant separately.
Regards,
Denys
hello Deys
I implemented Hana express edition 2.0 and in hana studio, i see a function Open SAP Cockpit.
But when i click then is not working. i get
Die Seite kann nicht angezeigt werden.
what can i do that i can call the SAP Cockpit?
Many thanks for your fast help.
Nick Scherer
Hi Nick,
Just noticed your question; somehow missed the notification or maybe the notification missed me.
As others might encounter a similar issue, here is my not-so-fast-and-even-belated response.
Development has stopped for SAP HANA studio as of SAP HANA 2.0 SPS 00 (July 2016) so you can expect this tool gradually to behave incorrectly. This is one such example. The link in studio points to cockpit hosted by XS classic (https port 4390). This version of cockpit is no longer supported (or even present) in SAP HANA 2.0. In SPS 02, XS classic is deprecated entirely even.
So even if die Seite could have been angezeigt it would not have worked.
The issue here is actually is most likely a hostname resolution issue. Your computer does not know how to resolve hxehost correctly. The easiest workaround to make this stimmt is to put the IP of the HANA server in the hosts file of your computer.
There are videos on this topic in the HANA Academy.
Best,
D
Hi,
When I try to create manually my tenant database in my VM HXE 2.0, it works fine:
CREATE DATABASE TESTDB ADD 'scriptserver' SYSTEM USER PASSWORD Initial1;
ALTER SYSTEM START DATABASE TESTDB;
However, when I try to login via HANA Studio on by tenant TESTDB with SYSTEM user, I always get the typical error "The system cannot be reached"...
However, if I try to connect via HDBSQL, I can connect fine and enter the initial password for SYSTEM.
Is there any missing command/permission/other I need to execute/add in order to access my TESTEDB via HANA Studio?
Thanks,
Manuel Dias
Hi Manuel,
Most likely the port is blocked.
Port 22 for SSH is open, so you can connect with a telnet client and run the hdbsql command.
The default HANA ports for the indexserver (30015) and host agent are open (HTTP/S on 50013/4), so you can connect to systemdb.
However, as the ports are blocked, the tenant cannot be reached.
See Connections for Multitenant Database Containers for port allocation.
You could try to disable the firewall and see if that solves the issue. If that's the case, you need to configure the firewall to either allow all traffic from VMware, or configure it to allow traffic from the ports of the tenant.
D
Hi Denys,
I'm using the latest version of SAP HANA Express VM (2.0) with Virtual Box, and the firewall is disabled.
Anyway, I created a new HANA Express VM today and I was able to connect successfully to my tenant TESTDB with HANA Studio. I believe I was doing something wrong in my previous tests...
Thanks
Manuel Dias
Hi,
I have the follow landscape: BW 7.5 and HANA 2.0 and I need now use AFL functions inside BW via HANA Analysis Process.
I have installed library AFL but in multitenant mode (I had to create a specific tenant for AFL library), but SAP BW does not recognize it
Any help will be welcome !
Rodrigo S.
Hi Rodrigo,
You did the right thing. Features are enabled in a tenant. For AFL, that would be te script server.
Could it be that BW is listening to the wrong port? See Port Assignment in Tenant Databases (SAP HANA Administration Guide).
D
I´ll check it!
regards
Rodrigo.
Hi Denys,
I noticed SAP HANA 2.0 SP02 system has default tenant DB with name, DBSID other than SYSTEMDB with default install. Does this normal behaviour with SPS02 ? Also this new tenant DB has standard SQL port of SAP HANA 1.0 system, 3xx15.
Could you please advise whether this default tenant meant to use by customer or does SAP HANA uses it internally similar to SYSTEMDB? Of none of these the case, could we stop and drop this tenant DB as customer may wanted to create own tenant with specific naming convention.
Thanks
HI Denya,
As discussed internally, I would like to clarify follwoing on SAP HANA 2.0 SPS02 Scale-out setup for MDC.
I noticed SAP HANA Studio [ or even SAP HANA Cockpit], allowed to add only the Master node to the system, and any worker node can not added to SAP HANA Studio/Cockpit even though those having Indexserver process running. Checkig the landscape from SystemDB shows it correctly setup, and I don't find any issues.
If I remeber correctly, SAP HANA 1.0 we were able to logon to worker nodes [except stand-by node]. Could you please check and advise whether access to worker node cause by any issues/limitation or is this exected behaviour now.
PS: we can manage the workder nodee within SystemDB, so access not cause manor issues. This question, more to understand and align with SAP HANA 1.0 behaviour.
Hi Dinesh,
SAP HANA studio is an SAP HANA 1.0 tool. There has been no more development on studio as of SAP HANA 2.0 SPS 00. It could very well be that this concerns new functionality introduced with 2.0 that is no longer supported / functioning in HANA studio. Just like HANA studio, for example, is no longer supported / functioning in the latest Eclipse Oxygen release.
For the screen captures, I can see that you have a 2-node system with instance number 00 and SID UHD, and two hosts: host-a and host-b.
The studio connection to the systemdb on host-a is successful but the connection to systemdb on host-b fails.
Is there a systemdb running on host-b? Does it listen to same port?
Hi
I just need to thank you for all education videos in SAP channel at youtube , I learned a lot from you.
Thanks
Good to hear, Mohamed!