In this blog post I would like to talk about the possibility to copy/move a tenant database from one multitenant container (MDC) system to another remote one. This feature is available with SPS12.
In my landscape I have two SAP HANA MDC systems M01 and M02. M02 is my source system, from where I would like to copy/move a tenant DB1 to my target system M01.
Scenario
M02 is our test system. The application CAR_DEALER is running on the DB1 tenant database. The application has its own schema CAR_DEALER with tables. It also has some application administrators like APPADMIN and USERADMIN. For DB1 we have two administrators like SYSTEMADMIN with CATALOG READ, INIFILE ADMIN, TRACE ADMIN, TENANT ADMIN privileges and BACKUPADMIN. The system administrators monitor system health e.g. some traces are configured.
After several rounds of successful application testing we have decided to move DB1 to our production system M01. Our production system already has some running applications on different tenants. We would like to have full database isolation between applications. So that means the CAR_DEALER application should also run in a separate tenant DB on our production system M02. So, we have decided to move the DB1 tenant, where the CAR_DEALER application is already running, to M01.
Preparations
TLS/SSL communication channel
First, I have to verify that TLS/SSL is enabled on internal communication channels on the basis of the system public key infrastructure (system PKI). Using e.g. SAP HANA studio Configuration tab of Administration editor, I can set the global.ini->communication->ssl parameter to systemPKI. It should be done for both systems, target and source.
Configure Inter-Service Communication
After this I have to open the communication between my source (M02) and target (M01) systems. You need to perform the communication configuration only in the source system. For this purpose I would like to use HDBLCM Web UI. But you can also use HDBLCM command-line and GUI.
In a browser I enter the following URL https://<host>:1129/lmsl/HDBLCM/<sid>/index.html
On the next page I set the Inter-Service Communication properties to global. And you can also enter an internal network address.
On the last page you can review your settings and press the Run button. Be aware your system will be restarted.
The Execution Progress page shows you the configuration progress and also the operations executed during the configuration.
So, the configuration finished successfully.
Open the external communication between source and target systems
Now I have to set up a trust relationship between my systems. First, I assign the necessary system privileges CERTIFICATE ADMIN, TRUST ADMIN, and DATABASE ADMIN to the system administrator of the target system.
Now I can open the external communication channel between source and target systems. For this purpose I need RSA public- and private-key certificates. If these certificates do not exist you have to create them. To find out how to do this, read in SAP Note 2175664.
I already have the RSA certificates so I can proceed. First I have to store the public-key certificate in my target system. I will do it using the Certificate Store app in SAP HANA cockpit. For this, my system administrator needs some additional roles: sap.hana.security.cockpit.roles::DisplayCertificateStore and sap.hana.security.cockpit.roles::EditCertificateStore for using this app. After assigning these roles I can see the Certificate Store app, drill-down into it and import my publicKey.pem file in SAP HANA cockpit of my target system.
Alternatively, you can just paste the content of the publicKey.pem file into the text area.
In the Configure Certificate Collections app I have to create a new certificate collection (e.g. CollectionTarget).
You can change the purpose of the CollectionTarget to DATABASE REPLICATION PURPOSE by pressing the Edit button.
Next I add the certificate that I just imported to the CollectionTarget collection.
I also have to meet some prerequisites in the source system. Here, I need to create a new certificate collection e.g. CollectionSource and set the source system's own certificate. This certificate must contain both keys: public and private.
After this I have to change the purpose of my certificate collection to SSL.
As the next step we have to create credentials. This enables authenticated access to the source system for the purpose of copying or moving a tenant database.
Create credentials
I create credentials in the system database of the target system by executing this SQL statement:
CREATE CREDENTIAL FOR COMPONENT 'DATABASE_REPLICATION' PURPOSE
'<host_name>:3<instance_number>01' TYPE 'PASSWORD' USING 'user="<user_in system_DB_of_source_system_with_DATABASE_ADMIN>";password="<password>"';
Back up a tenant database
The next important step is to back up the tenant database that will be copied. The new tenant database has the backup history of the original tenant database and can be recovered. I can back up my tenant database using SAP HANA Studio.
Copy a tenant database
After successful backup I can copy my tenant DB to the target system. I will do it by executing this SQL statement in the system database of the target system:
CREATE DATABASE <new_tenant_database_name> AS REPLICA OF <copied_tenant_database_name> AT '<source_system_hostname>:3<source_system_instance_number>01' OS USER '<os_user_name>' OS GROUP '<os_group_name>';
Note: If high isolation is configured on your systems, you have to provide OS USER or OS GROUP in CREATE DATABASE… statement. If you don’t have any OS USER or OS GROUP, create them first.
groupadd <group_name> && useradd <user_name> -c "<put something>" -d /var/lib/hdb/<SID> -g sapsys -G <group_name>,<shm_name> -s /bin/foo
You can see the status of the data replication in the system view SYS_DATABASES.M_DATABASE_REPLICAS e.g. in the system database of the target system. The REPLICATION_STATUS is ACTIVE if the replication is done successful.
Finalize replication
Finally, I have to finalize the replication by executing this SQL statement:
ALTER DATABASE <new_database_name> FINALIZE REPLICA
If everything ran successfully you can now add the copied tenant database into your workspace in SAP HANA studio and work with it like you did before.
Maybe it is interesting to explain here what happens during the execution of the last two SQL statements:
If the CREATE DATABASE … statement was executed with [NO START], the new tenant database will be started during the finalize replication step. The root key for data volume encryption will be changed in the new database and data re-encrypted if data volume encryption is enabled. If cross-database access was configured in the source tenant database, the remote identities of database users will be deleted.
By the way, if you want to move the tenant database, the finalize replication step should be executed by using this SQL statement:
ALTER DATABASE <new_database_name> FINALIZE REPLICA DROP SOURCE DATABASE
This will drop the tenant database in the source system after the finalize replication step. If you forget to do this, you can just drop the old tenant afterward using this SQL statement in system database of source system:
DROP DATABASE <database_name>
In my next blog I will tell you how you can copy/move a tenant database from one host to another in the same multiple-container system.
In my landscape I have two SAP HANA MDC systems M01 and M02. M02 is my source system, from where I would like to copy/move a tenant DB1 to my target system M01.
Scenario
M02 is our test system. The application CAR_DEALER is running on the DB1 tenant database. The application has its own schema CAR_DEALER with tables. It also has some application administrators like APPADMIN and USERADMIN. For DB1 we have two administrators like SYSTEMADMIN with CATALOG READ, INIFILE ADMIN, TRACE ADMIN, TENANT ADMIN privileges and BACKUPADMIN. The system administrators monitor system health e.g. some traces are configured.
After several rounds of successful application testing we have decided to move DB1 to our production system M01. Our production system already has some running applications on different tenants. We would like to have full database isolation between applications. So that means the CAR_DEALER application should also run in a separate tenant DB on our production system M02. So, we have decided to move the DB1 tenant, where the CAR_DEALER application is already running, to M01.
Preparations
TLS/SSL communication channel
First, I have to verify that TLS/SSL is enabled on internal communication channels on the basis of the system public key infrastructure (system PKI). Using e.g. SAP HANA studio Configuration tab of Administration editor, I can set the global.ini->communication->ssl parameter to systemPKI. It should be done for both systems, target and source.
Configure Inter-Service Communication
After this I have to open the communication between my source (M02) and target (M01) systems. You need to perform the communication configuration only in the source system. For this purpose I would like to use HDBLCM Web UI. But you can also use HDBLCM command-line and GUI.
In a browser I enter the following URL https://<host>:1129/lmsl/HDBLCM/<sid>/index.html
and open Configure Inter-Service Communication app. On the first page I enter the system administrator (<sid>adm) credentials.
On the next page I set the Inter-Service Communication properties to global. And you can also enter an internal network address.
On the last page you can review your settings and press the Run button. Be aware your system will be restarted.
The Execution Progress page shows you the configuration progress and also the operations executed during the configuration.
So, the configuration finished successfully.
Open the external communication between source and target systems
Now I have to set up a trust relationship between my systems. First, I assign the necessary system privileges CERTIFICATE ADMIN, TRUST ADMIN, and DATABASE ADMIN to the system administrator of the target system.
Now I can open the external communication channel between source and target systems. For this purpose I need RSA public- and private-key certificates. If these certificates do not exist you have to create them. To find out how to do this, read in SAP Note 2175664.
I already have the RSA certificates so I can proceed. First I have to store the public-key certificate in my target system. I will do it using the Certificate Store app in SAP HANA cockpit. For this, my system administrator needs some additional roles: sap.hana.security.cockpit.roles::DisplayCertificateStore and sap.hana.security.cockpit.roles::EditCertificateStore for using this app. After assigning these roles I can see the Certificate Store app, drill-down into it and import my publicKey.pem file in SAP HANA cockpit of my target system.
Alternatively, you can just paste the content of the publicKey.pem file into the text area.
In the Configure Certificate Collections app I have to create a new certificate collection (e.g. CollectionTarget).
You can change the purpose of the CollectionTarget to DATABASE REPLICATION PURPOSE by pressing the Edit button.
Next I add the certificate that I just imported to the CollectionTarget collection.
I also have to meet some prerequisites in the source system. Here, I need to create a new certificate collection e.g. CollectionSource and set the source system's own certificate. This certificate must contain both keys: public and private.
After this I have to change the purpose of my certificate collection to SSL.
As the next step we have to create credentials. This enables authenticated access to the source system for the purpose of copying or moving a tenant database.
Create credentials
I create credentials in the system database of the target system by executing this SQL statement:
CREATE CREDENTIAL FOR COMPONENT 'DATABASE_REPLICATION' PURPOSE
'<host_name>:3<instance_number>01' TYPE 'PASSWORD' USING 'user="<user_in system_DB_of_source_system_with_DATABASE_ADMIN>";password="<password>"';
Back up a tenant database
The next important step is to back up the tenant database that will be copied. The new tenant database has the backup history of the original tenant database and can be recovered. I can back up my tenant database using SAP HANA Studio.
Copy a tenant database
After successful backup I can copy my tenant DB to the target system. I will do it by executing this SQL statement in the system database of the target system:
CREATE DATABASE <new_tenant_database_name> AS REPLICA OF <copied_tenant_database_name> AT '<source_system_hostname>:3<source_system_instance_number>01' OS USER '<os_user_name>' OS GROUP '<os_group_name>';
Note: If high isolation is configured on your systems, you have to provide OS USER or OS GROUP in CREATE DATABASE… statement. If you don’t have any OS USER or OS GROUP, create them first.
groupadd <group_name> && useradd <user_name> -c "<put something>" -d /var/lib/hdb/<SID> -g sapsys -G <group_name>,<shm_name> -s /bin/foo
You can see the status of the data replication in the system view SYS_DATABASES.M_DATABASE_REPLICAS e.g. in the system database of the target system. The REPLICATION_STATUS is ACTIVE if the replication is done successful.
Finalize replication
Finally, I have to finalize the replication by executing this SQL statement:
ALTER DATABASE <new_database_name> FINALIZE REPLICA
If everything ran successfully you can now add the copied tenant database into your workspace in SAP HANA studio and work with it like you did before.
Maybe it is interesting to explain here what happens during the execution of the last two SQL statements:
- A secure connection to the system database of the source system will be established.
- After this a new tenant database will be created. It has the same topology as the tenant database in the source system.
- Data will be replicated between the services in the source tenant DB and services in the target tenant DB.
If the CREATE DATABASE … statement was executed with [NO START], the new tenant database will be started during the finalize replication step. The root key for data volume encryption will be changed in the new database and data re-encrypted if data volume encryption is enabled. If cross-database access was configured in the source tenant database, the remote identities of database users will be deleted.
By the way, if you want to move the tenant database, the finalize replication step should be executed by using this SQL statement:
ALTER DATABASE <new_database_name> FINALIZE REPLICA DROP SOURCE DATABASE
This will drop the tenant database in the source system after the finalize replication step. If you forget to do this, you can just drop the old tenant afterward using this SQL statement in system database of source system:
DROP DATABASE <database_name>
In my next blog I will tell you how you can copy/move a tenant database from one host to another in the same multiple-container system.
- SAP Managed Tags:
