SAP Hana Multi-Tenant DB with Replication
Starting release SP91 of Hana the option to deploy a multi-tenant database is now available, for my test/deployment I will use this option to run the following use case;
Deploy a multi-tenant database and create 2 tenant database, one will be connected to the SLT server to receive the replicated table from ERP 6.0 Ehp4, and the other tenant database will use SDA functionality to virtualize table from an Microsoft SQL Database.
Once both tenant databases will be feed by the necessary data, cross database access will be used to consolidate data and create views.
In order execution
- Install Hana database multi-tenant (type system)
- Install tenant database “A” and “B”
- Configure HTTP access for mutli-tenant container
- Setup SLT to work with tenant database “A” In my previous documentation I have already explained how to configure and install the SLT server, so I’ll use the architecture already in place, but apply the note 2101084 and create a new SLT configuration for this scenario but reuse the existing RFC destination
- Setup SDA for tenant database “B” to access table of SQL server
- Enable cross-database access
SAP Hana Administration Guide SP9
2101084 - SLT Replication Server (2011 SP07/08): Support HANA Multitenant Database Containers
2104291 - FAQ - SAP HANA multi-tenant database containers
2096000 - SAP HANA multitenant database container - Additional
Install Multi-tenant database (SYSTEM)
A multi-tenant database is split in 1 system database which will contain the entire tenant DB and n tenant database.
For my test purpose i'll install a new db
Install tenant database HA2 “A” and HA3 “B”
Now that the SYSTEM database is installed I’m going to install the two tenants DB by using sql command over the system database.Few point to take in consideration or specify like:
- Specify the SYSTEM user password for the tenant database
- Specify the hostname of the server where the tenant database will install (in case of multi node)
Statement to create a tenant databaseBy using the statement above:
- I create the tenant DB with SID: CREATE DATABASE HA2
- Define the SYSTEM user’s password: SYSTEM USER PASSWORD xxxxx
Now that the tenant database I create I add to the studio
I did create the second tenant database "HA3" the same way and add it to my studio too
Now that both tenant databases are installed I check out the port used by each of them, we normally have 3 ports:
- Internal communication with the range (3<XX>40 - 3<XX>97)
- SQL (3<XX>41 - 3<XX>98)
- HTTP (3<XX>42 - 3<XX>99)
Run the following SQL statement in each tenant dbto check:
SELECT PORT, SQL_PORT, (PORT + 2) HTTP_PORT FROM SYS.M_SERVICES
Tenant HA2 Tenant HA3
Enable HTTP access for multitenant database container
In order to access a multitenant database container by HTTP some parameters needs to be change according the embedded webdispatcher at the webdispatcher.ini file under profile on the SYSTEM database
Double click on the parameter “wdisp/system_0”
I change the default value:
SID=$(SAPSYSTEMNAME), EXTSRV=http://localhost:3$(SAPSYSTEM)08, SRCURL=/
SID=$(SAPSYSTEMNAME), EXTSRV=http://localhost:3$(SAPSYSTEM)14, SRCVHOST=usphlhana07b. phl.sap.corp
For the SYSTEM and add this new one for each tenant database
Tenant HA2: wdisp/system_1 = SID=HA2, EXTSRV=http://localhost:3$(SAPSYSTEM)42, SRCVHOST=usphlhana07b-ha2. phl.sap.corp
Tenant HA3: wdisp/system_2 = SID=HA3, EXTSRV=http://localhost:3$(SAPSYSTEM)45, SRCVHOST=usphlhana07b-ha3. phl.sap.corp
Note: the value I have used “SRCVHOST=usphlhana07b-ha2 & ha3” should be create as an alias in the DNS since I working on the host
Once the values added restart the webdispatcher and check the log
Now i test the cockpit url by: http://usphlhana07b:8004/sap/hana/admin/cockpit/
SLT server adjustment to work with Multitenant database
In order to use the new Hana feature with the SLT server, specific action needs to be taking care of and the note 2101084 needs to be applied according the SP7 of the DMIS. Before taking any action stop all configurations.
Create the following table in SE11
Once the manual step done apply the automatic correction by snote
To be perform
Now done I set the connection with hana and here is the result
Setup SDA for tenant database HA3
In order the virtualize table and read it rom tenant database HA2, I will connect this tenant db to an Microsoft SQL database though
I will first going to create a database instance in the SQL server with few tables and the sql user for the connection with Hana
Note: before doing the SDA config make sure you did install odbc driver, create the .odbc.ini and set the required variable, once done do a quick check
Now go to SDA and create the required connection
Connection done check out the monitoring
Enable cross database access
In a multi-tenant database context each database is independent; however it’s possible to allow a read-only query access to another tenant database.
From the “SYSTEM” database, go to configuration in global.ini, look for the parameter “cross_database_access” and change the value to “TRUE”
Once done add the new parameter “targets_for_ha2” with the value “ha3”
Now that the parameter is set and the cross database enable, we need to create a remote identity user which needs to perform remote query.
I’ll use the following statement to map the new user in HA2 to match the user RQUERY created previously in HA3.
“CREATE USER FROMHA2 WITH REMOTE IDENTITY RQUERY AT DATABASE HA3”
The configuration is completed and the user can be used to run query on the remote tenant database.