Technology Blogs by SAP
Learn how to extend and personalize SAP applications. Follow the SAP technology blog for insights into SAP BTP, ABAP, SAP Analytics Cloud, SAP HANA, and more.
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Copy a Tenant Database using system replication

andreas_kuhn
Advisor
Advisor
‎03-12-2024 2:27 PM

In this How-To the standard HANA Admin guide was followed. Screenshots are taken from Testservers. For detailed information you MUST go through the Standard HANA Admin Guide (SAP HANA Tenant Databases > Copying and Moving Tenant Databases)

 

Copying and Moving Tenant Databases using system replication

OnPremise Machines used for the Demo:

 HostnameIPSIDHANA Version:OS
SOURCE:hanasrv83192.168.0.83H832.00.073.00SLES 15 SP5
TARGET:hanasrv84192.168.0.84H842.00.073.00SLES 15 SP5

 

  1. Make sure the hosts file contain the hostnames to be able to resolve each other.
  2. Make sure to use the below HANA Versions or higher:
    • 2.0.00.059.06
    • 2.0.00.065.00
    • 2.0.00.070.00
      Not using these Versions will lead to an error in the process later:
      SAP DBTech JDBC: [2]: general error: nameserver failed to process the request;ltt exception: exception 301107: Credential not found
  3. Further in the Guide only the Terms "Source" and "Target" will be used. The Source Server contains the Database which should be copied to the Target Server.
  4. In this Guide 2 fresh installed HANA Servers are used with default self-signed certificates created during the installation
  5. Make sure a working SQL connection (e.g. via HANA Studio) does exist.

 

As very first Step a new Tenant will be created at the SOURCE Server called "SRC"

  • Current Tenants can be checked by: select * from "SYS"."M_DATABASES";
  • Creating a test Tenant: CREATE DATABASE <DB_NAME> SYSTEM USER PASSWORD <PASSWORD>;
    • SAMPLE:
      CREATE DATABASE SRC SYSTEM USER PASSWORD <PASSWORD>;
  • See if the new Tenant got created:  select * from "SYS"."M_DATABASES";

  • If something is going wrong or you want to delete the Tenant again run:

    • ALTER SYSTEM STOP DATABASE <DB_NAME>;

    • DROP DATABASE <DB_NAME> DROP BACKUPS;

 

For a general process overview pls. read the following docs

Copy and Move Process

Security of the Copy Process

 

After the Tenant "SRC" is created the next Steps will be 

Preparing to Copy a Tenant Database

In this Tutorial security will be not disabled and the self-signed certificates will be used. If you have company signed certificates you need to do the export and import based by the official guides.

Verify the SYSTEM layer values of the following parameters in the global.ini file:
[communication] ssl must be set to systempki
[communication] sslclientpki must be set to off
[system_replication_communication] enable_ssl must be set to on

You can change these settings by executing the following SQL statements:

ALTER SYSTEM ALTER CONFIGURATION ('global.ini', 'SYSTEM') SET ( 'communication', 'ssl') = 'systempki' WITH RECONFIGURE;
ALTER SYSTEM ALTER CONFIGURATION ('global.ini', 'SYSTEM') SET ( 'system_replication_communication', 'enable_ssl') = 'on' WITH RECONFIGURE;
ALTER SYSTEM ALTER CONFIGURATION ('global.ini', 'SYSTEM') SET ( 'communication', 'sslclientpki') = 'off' WITH RECONFIGURE;
ALTER SYSTEM CLIENTPKI DROP ROOT CA;

VERIFY:
select * from M_INIFILE_CONTENTS where file_name = 'global.ini' and section='communication' and key = 'ssl';ssl.png

select * from M_INIFILE_CONTENTS where file_name = 'global.ini' and section='system_replication_communication' and key = 'enable_ssl';
enable_ssl.png
select * from M_INIFILE_CONTENTS where file_name = 'global.ini' and section='communication' and key = 'sslclientpki';
sslclientpki.png

Parameter Documentation:

During the copy process, data is replicated via a secure (TLS/SSL) network connection by default.

Procedure

1.: Connect via SQL to the SOURCE server and create a Certificate Collection and set it's purpose to SSL:
  • CREATE PSE <collection name>;
  • SET PSE <collection name> PURPOSE SSL;
    • SAMPLE:
      CREATE PSE source_db_sec;
      SET PSE source_db_sec PURPOSE SSL;

VERIFY:
select * from PSES;
step1.png

2.: Create a new self-signed certificate and extract it

 

  • On the SOURCE HANA System:
  • sapgenpse gen_pse -p <path>/<file name>.pse -x "" -noreq "CN=<hostname or FQDN of source host>"
    • SAMPLE:
      su -l h83adm
      cd $SECUDIR
      sapgenpse gen_pse -p source_db_sec.pse -x "" -noreq "CN=hanasrv83"
  • Download a copy of the extract_certificates.py python script from SAP Note 2175664  to the SOURCE Server. Since the HANA Version which has to be used is equal or higher than 2.0.00.059.6 make sure the extract_certificates_python3.zip is downloaded.
    The python script file must be accessible to the <sid>adm user and saved to the $SECUDIR path
  • Extract the generated private key and the self-signed certificate from the PSE file using the extract_certificates.py script:
    • SAMPLE:
      python extract_certificates.py -p source_db_sec.pse
  • The script will print a list of one or more SQL statements that can be transferred to an SQL console using copy and paste. 

step2.png

  • Save the output for the next steps !
3.: Add the certificate to the previous created certificate collection:

From Step 2. use the python script extract:

  • ALTER PSE <collection name> SET OWN CERTIFICATE '<private key and certificate>';
    • SAMPLE:
      ALTER PSE  source_db_sec SET OWN CERTIFICATE '<private key and certificate>';
      step3.png

VERIFY:
select * from certificates;

step3.1.png

select * from pse_certificates;

step3.2.png

4.: Connect via SQL to the TARGET server and create a Certificate Collection and set it's purpose to DATABASE REPLICATION

In the system database of the target system, create a certificate collection and set its purpose to DATABASE REPLICATION. You can choose any name for the certificate collection.

  • CREATE PSE <collection name>;
  • SET PSE <collection name> PURPOSE DATABASE REPLICATION;;
    • SAMPLE:
      CREATE PSE target_db_sec;
      SET PSE target_db_sec PURPOSE DATABASE REPLICATION;

VERIFY:
select * from PSES;

step4.png

5.: Import certificate of the SOURCE Server into the TARGET server. Use the certificate part of the export of Step 2

If not already in the certificate store, import the public-key certificate of the system database of the source system (or the root certificate of the source system) into the certificate store of the target system.
For this Demo we use the previous extracted output of the python script and only use the certificate part and import it via SQL:

  • CREATE CERTIFICATE FROM '<certificate content>';
    step5.1.png
  • VERIFY & note down the certificate ID of the new imported certificate: 
    SELECT * FROM SYS.CERTIFICATES;    step5.2.png
6.: Add the system database certificate (or root certificate) to the new collection
  • ALTER PSE <collection name> ADD CERTIFICATE <certificate id>;
    • SAMPLE:
      ALTER PSE target_db_sec ADD CERTIFICATE <certificate id>;
      step6.1.png

VERIFY:
select * from pse_certificates;

step6.2.png

Open communication from the TARGET system to the SOURCE system by enabling services in the source system to listen on all network interfaces.

Connect via SQL to the SOURCE System and execute:

  • ALTER SYSTEM ALTER CONFIGURATION ('global.ini', 'SYSTEM') SET ( 'communication', 'listeninterface') = '.global' WITH RECONFIGURE;

VERIFY:
select * from M_INIFILE_CONTENTS where file_name = 'global.ini' and section='communication' and key = 'listeninterface';

step1.png

Connect via SQL to the TARGET Server and create Credentials for Authenticated Access to SOURCE System

  • CREATE CREDENTIAL FOR COMPONENT 'DATABASE_REPLICATION' PURPOSE '<host:internal_port_of_system_DB_of_source_system>'  TYPE 'PASSWORD' USING 'user="<user_in_system_DB_of_source_system_with_DATABASE_ADMIN>";password="<password>"'
    • SAMPLE: 
      CREATE CREDENTIAL FOR COMPONENT 'DATABASE_REPLICATION' PURPOSE 'hanasrv83:30001'  TYPE 'PASSWORD' USING 'user="SYSTEM";password="<password>"'

VERIFY:
select * from credentials;

step1.png

At this Step REBOOT SOURCE and TARGET HANA Servers!

 

Back up the Tenant Database which should be copied.

Connect to the SOURCE Tenant via SQL and check the current backup config:

  • select * from M_BACKUP_CONFIGURATION;
  • Perform Full Backup of the Source Tenant. In this sample FILE based Backup is used:
    • SAMPLE:
      BACKUP DATA USING FILE ('COMPLETE_DATA_BACKUP_2024_03_11-10_50') COMMENT 'data backup of 2024_03_11-10_50';
      step1.png

VERIFY: 
select * from M_BACKUP_CATALOG where ENTRY_TYPE_NAME = 'complete data backup';

step2.png

Start the Replication of Database "SRC" to "TRG"

Connect to the TARGET Server via SQL and run:

  • CREATE DATABASE <target_database_name> [ AT [ LOCATION ] '<target_hostname>[:<port_number_master_indexserver> ] ' ]
    { ADD '<servicetype>' [ AT [ LOCATION ] '<target_hostname>[:<port_number_service> ]@<source_hostname>:<port_number_service>' ] }...
    { AS REPLICA OF [ <source_database_name> ] AT [ LOCATION ] '<source_hostname>[:<port_number_systemdb> ]' KEY MANAGEMENT CONFIGURATION <id> }
    [ OS USER '<username>' OS GROUP '<groupname>' ]
    [ NO START ]
    [ <restart_mode> RESTART ]
    • SAMPLE:
      CREATE DATABASE TRG AS REPLICA OF SRC AT 'hanasrv83:30001';
      step1.png

The Replication can be checked via:

  • select * from SYS_DATABASES.M_DATABASE_REPLICA_STATISTICS;
    step2.png
    step2.1.png
    step2.2.png
  • select * from SYS_DATABASES.M_DATABASE_REPLICAS;
    step3.png

Possible Errors:

1. 

Could not execute 'CREATE DATABASE TRG AS REPLICA OF SRC AT 'hanasrv83:30001'' in 80 ms 508 µs .
SAP DBTech JDBC: [2]: general error: nameserver failed to process the request;ltt exception: exception 7100011: Database connection to the source database error: Connection to source database at 'hanasrv83:30013' with credential FOR PURPOSE 'hanasrv83:30001' failed, exception 1: no.7100011 (MultiDB/impl/DatabaseConnectionSQLDBC.cpp:121) TID: 31931
Database connection to the source database error: SQLDBC connection failed with SQLDBC error -10709: Connection failed (RTE:[89001] Cannot resolve host name 'hanasrv83' rc=-2: Name or service not known (hanasrv83:30013))
.
Solution: See if both servers can resolve each others name. Either via the hosts file or via system_replication_hostname_resolution

-------

2.

Could not execute 'CREATE DATABASE TRG AS REPLICA OF SRC AT 'hanasrv83:30001'' in 83 ms 189 µs .
SAP DBTech JDBC: [2]: general error: nameserver failed to process the request;ltt exception: exception 7100011: Database connection to the source database error: Connection to source database at '192.168.0.83:30013' with credential FOR PURPOSE 'hanasrv83:30001' failed, exception 1: no.7100011 (MultiDB/impl/DatabaseConnectionSQLDBC.cpp:121) TID: 31931
Database connection to the source database error: SQLDBC connection failed with SQLDBC error -10709: Connection failed (RTE:[300015] SSL certificate validation failed: host name '192.168.0.83' does not match name in certificate 'hanasrv83' (192.168.0.84:12790 -> 192.168.0.83:30013))
.
Solution: If system_replication_hostname_resolution is used for name resolving make sure it is set the correct way (hostname -> ip). SAMPLE for the Testservers above. 

ALTER SYSTEM ALTER CONFIGURATION ('global.ini', 'SYSTEM') SET ( 'system_replication_hostname_resolution', 'hanasrv83') = '192.168.0.83' WITH RECONFIGURE;
ALTER SYSTEM ALTER CONFIGURATION ('global.ini', 'SYSTEM') SET ( 'system_replication_hostname_resolution', 'hanasrv84') = '192.168.0.84' WITH RECONFIGURE;

  • Finalize the Tenant Copy at the TARGET Server

Connect to the TARGET Server via SQL and run:

  • ALTER DATABASE <new_database_name> FINALIZE REPLICA
    • SAMPLE:
      ALTER DATABASE TRG FINALIZE REPLICA
      step1.png

VERIFY:
select * from SYS_DATABASES.M_DATABASE_REPLICA_STATISTICS;
select * from SYS_DATABASES.M_DATABASE_REPLICAS;

 

1 Comment
Labels in this area
Popular Blog Posts