This is the last part of my “SAP HANA MDC” trilogy blog, this blog will be providing the details on how to create tenant DB, post tenant DB creation activities and backup/restore of tenant DB. Without saying much of words, here you go for the first section of the blog – tenant DB creation.
Tenant DB creation:
We will be creating tenant DB count 1 with tenant DB name “HB2” as per the screen captured 2. The SYSTEM user password is required in the screen of tenant DB creation (screen captured 2).
Screen captured 1: Create Tenant Database
Screen Captured 2: Tenant Database Information
If you are required adding additional services like indexserver, scriptserver, xsengine and dpserver, you may need to click on the “Advanced Settings”. From there, you can specify the host name (for the distributed HANA environment) and port number for each of the additional services. The indexserver is the service by default when creating the tenant DB.
Screen Captured 3: Advance Settings
You may need to press F5 to refresh the page and make sure the second tenant database HB2 is listed and running.
Screen Captured 4: HB2 is running
Post Tenant DB creation activities:
After that, you can check the status of the newly created tenant DB via the HANA studio or the “/usr/sap/hostctrl/exe/sapcontrol -nr 00 -function GetProcessList” command. The following steps will be discuss about the HTTP access to the SAP HANA MDC.
The XS server is to allow the web based application to access SAP HANA via HTTP or HTTPS, hence the internal SAP Web Dispatcher of SAP HANA is to manage the incoming HTTP or HTTPS request where it allow the applications send requests to specific tenant DB in a SAP HANA MDC system. So, every tenant DB must have an alias hostname to differentiate the URL of the respective tenant DB.
1. Make sure the EX engine is working via the URL http://<host_name>.<FQDN>:8000 and you will see the following screen captured 5 with the status “up and running”
Screen captured 5: XS Engine is up and running
2. Remove the XS engine via HANA studio, you will need to check the port number of the XS engine via command “./HDB info”, the XS engine port normally is tag with port number 3<nn>07. “ALTER DATABASE <SID> remove ‘xsengine’ at ‘<hostname>:3<nn>07’
Screen captured 6: XS Engine port number
Screen captured 7: Remove XS engine
3. Modify the parameter embedded = true under httpserver in xsengine.ini in the SYSTEM DB. After that, you will need to restart your HANA instance (tenant DB and SYSTEM DB). You can stop the tenant DB via SQL command or SAP HANA cockpit.
Screen captured 8: XS engine parameter
4. Run the SQL command to change the URL for respective tenant DB and system DB
ALTER SYSTEM ALTER CONFIGURATION (‘xsengine.ini’, ‘database’, ‘HBT’) SET (‘public_urls’, ‘http_url’) = ‘http://<tenantDB_name>.<hostname>.<FQDN>:80<nn>’ WITH RECONFIGURE;
ALTER SYSTEM ALTER CONFIGURATION (‘xsengine.ini’, ‘database’, ‘HBT’) SET (‘public_urls’, ‘https_url’) = ‘https://<tenantDB_name>.<hostname>.<FQDN>:43<nn>’ WITH RECONFIGURE;
ALTER SYSTEM ALTER CONFIGURATION (‘nameserver.ini’, ‘system’) SET (‘public_urls’, ‘http_url’) = ‘http://system<sid>.<hostname>.<FQDN>:80<nn>’ WITH RECONFIGURE;
ALTER SYSTEM ALTER CONFIGURATION (‘nameserver.ini’, ‘system’) SET (‘public_urls’, ‘https_url’) = ‘https://system<sid>.<hostname>.<FQDN>:43<nn>’ WITH RECONFIGURE;
Screen captured 9: SQL command
5. Get the IT network team to maintain the DNS, hosts file in C:\Windows\System32\drivers\etc\hosts for the new alias host name created in item 4.
6. Test on the HANA cockpit for each of the tenant database and system database.
Screen captured 10: SYSTEM DB HANA cockpit
Screen captured 11: Tenant DB (HB2) HANA cockpit
7. You will need to change the XS properties to the alias host name that created in item 4 in HANA studio properties for tenant DB and SYSTEM DB
Screen captured 12: XS properties
You are now having the SAP HANA MDC system with ONE SYSTEM DB and 2 tenant DB. The next step will be configuring the memory allocation limit and CPU max concurrency parameter
The global memory allocation limit will need set to ‘0’, then the memory allocation limit for each tenant DB will need to specify the number based on the sizing results. After that, you will need to restart all the tenant DB and the SYSTEM DB. Once restarted, the subsequent changes on the memory allocation limit to the tenant DB will be on-the-fly.
The parameter max_concurrency under “execution” section in the indexserver.ini file influences the maximum number of CPU cores that can be used for each tenant database, which the number of concurrent running threads by the SAP HANA job executor. A reasonable default value is the number of cores divided by the number of tenant databases. Do not specify a value of 0. A change of this value takes effect immediately.
Backup of Tenant DB:
For the tenant DB backup, you can use the hdbsql command or HANA studio or HANA cockpit or backint or SAP Netweaver dbacockpit and OS level scripting. This blog will be showing the HANA studio, HANA cockpit and SAP netweaver dbacocpit backup steps and screen captured.
The tenant DB backup via HANA studio must be right click on the SYSTEM DB entry and select the “Backup tenant Database” option, you will able select the tenant DB in the list (only 1 tenant DB can be selected at one time), then provide the backup location and it will show up a backup summary.
Screen captured 13: Backup tenant DB with HANA studio
Screen captured 14: Tenant DB selection
For the tenant DB backup with HANA cockpit, you will need to create a DB user with the role of “sap.hana.backup.roles” and system privilege of “backup admin”, “catalog read” and “data admin” in the specific tenant DB. After that, you may login to the respective tenant DB HANA cockpit to perform the tenant DB backup (You will able to see the “Data Backup” tiles when login to the DB HANA Cockpit). After that, click on the tiles and you will be able to see the “start new backup” button on the right bottom.
Screen captured 15: DB user backup role and system priviledge
Screen captured 16: DB user backup tiles in HANA cockpit
For tenant DB where it having a SAP Netweaver ABAP stack schema able to schedule orrun the backup via SAP Netweaver DBACOCKPIT. We can change the backup destination in the “backup configuration” as in the screen captured, after that we need to create the directory “DB_<tenantDB_name>” in the respective path (for example /hana/backup/DB_HBT).
Screen captured 17: Changing the backup destination path
Screen captured 18: Completed backup with DBACOCKPIT
Screen captured 19: Backup dump files write to /hana/backup/DB_HBT
Restore of tenant DB:
We had done a couple of tenant DB restoration experiment and would like to share in this blog. We have tested three different scenario on restore tenant DB backup to single DB system, restore Single DB system to tenant DB and restore tenant DB to tenant DB.
For the scenario of restore tenant DB backup to single DB system, we have install a brand new HANA instance with SID “HBB” on the same revision 110 and we will be perform this restoration via HANA studio. Just simply add the HBB into the HANA studio and right click on the “recover system” then supply the backup files and location from the tenant DB (HB2) and you will get the summary screen as following.
Screen captured 20: Recovery of System HBB
The recovery is fail with error due to the name server, database topology is different between the SAP HANA single DB system and SAP HANA MDC system. This type of recovery will damage the SAP HANA single DB system and this is totally not supported as mentioned in SAP Note “2096000 – SAP HANA multitenant database containers – Additional Information”. It is only can recover a tenant DB backup to another tenant DB. We can only perform R3load Export/Import method if required to migrate SAP HANA tenant DB to SAP HANA single DB system as of Jan 2016.
Screen captured 21: Recovery of System HBB with Error
For the scenario of restore Single DB system to tenant DB, we are using the SAP HANA single DB backup files to restore onto the tenant DB “HB2”. To recover a tenant DB, it must be right click on the SYSTEM DB and select “Recover Tenant Database” then supply the backup files and location from the SAP HANA single DB and you will get the summary screen as following.
Screen captured 22: HANA studio recover Tenant Database
Screen captured 23: Summary recovery of tenant DB “HB2”
The recovery is fail and it will causes the volume crash on the tenant DB indexserver, this restoration scenario is not supported as well because of the name server, database topology is different. As mentioned in SAP Note “2096000 – SAP HANA multitenant database containers – Additional Information” and SAP HANA MDC operation guide, it is only can recover a tenant DB backup to another tenant DB. In order to recover the single DB system to tenant DB, it will required restore the single DB system backup to another single DB system then convert this “restored single DB system” to SAP HANA MDC system. After that, take a backup of the tenant DB (count 0) and restore it to another tenant DB.
Screen captured 24: Recovery of System HB2 with Error
For the scenario of restore tenant DB with tenant DB backup, it just simple as using the HANA studio and right click on the system Database and select recover tenant DB. Then, supply the tenant DB backup files location and you can see the tenant DB name to be restored. It will showed the message ‘1 Volumes were recovered’ without any error message which mean the recovery is successful. If you are happened to recover a tenant DB from host A to the tenant DB residing in host B, you may refer to SAP Note “2101737 – Recovery of a Multitenant Database Container fails”.
Screen captured 25: Tenant DB recovery
Screen captured 26: Tenant DB recovery successful message
To create a tenant DB is just as simple as few clicks, you will need to bear in mind that the conversion of SAP HANA single DB to SAP HANA MDC system is only one way direction. You can’t convert a SAP HANA MDC back to SAP HANA single DB system, so you will need to understand the consequences before planning the SAP HANA MDC conversion. Backup/restore in SAP HANA MDC system is the concern of many SAP HANA administration, you will need to understand the limitation of the backup/restore in SAP HANA MDC system. All the SAP HANA MDC backup/restore information can be found in SAP HANA MDC operation guide.