Overview
The objective of this blog series is to share the experience of migration test from SAP HANA On-Premise to SAP HANA Cloud. In Part 1, I have converted classical Calculation Views to XSA based HDI Container model. In Part 2, l have migrated the model from SAP HANA On-Premise to SAP HANA Cloud. In this blog, I will transfer data from SAP HANA On-Premise to SAP HANA Cloud.
Step1. Migrate classical Calculation Views to XSA HDI Container objects
Step2. Import the migrated objects to SAP HANA Cloud
Please note that SAP has plan to release migration tool (the planned first scope is from SAP Cloud Platform, HANA Service (HANA as a Service / HaaS) to SAP HANA Cloud), this means probably the steps described in this blog will be obsolete in the future.
Let’s get started. The steps are as follows.
1. Configure Data Provisioning Agent (DP Agent)
2. Create Virtual Table
3. Transfer data
I will transfer data using SQL statement (Insert – Select) in this blog. Of course you can use Smart Data Integration (SDI) flowgraph to transfer data, I would like to explain it in another blog.
1. Setup DP Agent
1.1 Install DP Agent
Install DP Agent on On-Premise side so that SAP HANA Cloud can connect to On-Premise SAP HANA. In this blog, I will install DP Agent on a Windows server which is in the same network as On-Premise SAP HANA.
1.2 Configure DP Agent
1.2.1 Create a DB user for Agent Admin
From SAP HANA Cockpit, create a HANA DB user who has following authorizations.
Application Privilege : sap.hana.im.dp.admin::Administrator
System Privilege : AGENT_ADMIN, ADAPTER_ADMIN
1.2.2 Run DP Agent Configuration tool
Run command prompt as administrator and configure environment variables as followings, then execute DP Agent Configuration tool.
setx DPA_INSTANCE "C:\usr\sap\dataprovagent"
cd %DPA_INSTANCE"
cd bin
agentcli.bat --configAgent
1.2.3 Start Agent
Choose option 1 (Start of Stop Agent) and then choose option 1 (Start Agent). DP Agent will start by this.
1.2.4 Configure SAP HANA Connection
Configure to connect from DP Agent to SAP HANA Cloud. First, choose option 6 (SAP HANA Connection). Next, input as followings.
Use encrypted JDBC connection : true
Use WebSocket to connect : false
Host Name (for Direct SQL) : SAP HANA Cloud InstanceのEndpoint(*)
Port Number (for Direct SQL) : SAP HANA Cloud InstanceのEndpoint(*)
Agent Admin HANA User & Password : Information about the user created in Step1.2.1
Use HTTP Proxy Server : false (for this blog, select based on your environment)
HANA User for Agent Messaging : Any user name & password, true (create new user)
Configuration of connection between DP Agent and SAP HANA Cloud has been completed.
* You can confirm the hostname and port number of SAP HANA Cloud in SAP Cloud Platform Cockpit. Please see Endpoint of SAP HANA Instances.
1.2.5 Register DP Agent
Go back to the top of the Configuration tool and choose option 7 (Agent Registration) to register DP Agent.
Agent Name: any name
Agent Host: IP address of the server where DP Agent is installed
1.2.6 Register HANA Adapter
Register “HANAAdapter” which allows SDI to connect to SAP HANA. Go back to the top of the tool and choose option 8 (Adapter Registration). Then choose option 2 (Register Adapter) and input as follows.
Adapter Name: HANAAdapter
1.2.7 Confirm the configuration
Go to Database Explorer and confirm Agent and Adapter are successfully registered.
2. Create Virtual Table
Configuration of DP Agent to connect between SAP HANA Cloud and SAP HANA On-Premise has been completed. Next, I will create virtual tables on SAP HANA Cloud which access to On-Premise SAP HANA tables.
2.1 Create a Remote Source
At Database Explorer, right-click on “Remote Sources” and choose “Add Remote Source”.
Input Remote Source Name and choose “HANAAdapter” as Adapter Name. Input other necessary information like hostname and port number of On-Premise SAP HANA, user name and password. Save it when completed.
Open the remote source by clicking the name, schema is displayed. And when you open the schema, you see the tables in the schema. The source schema managed by HDI Container of On-Premise SAP HANA is “SALES_1”, so you can see the tables created in Part 2 of this blog (SALES::HDI_MD_PRODUCTS, SALES::HDI_SO_HEADER, SALES::HDI_SO_ITEM).
2.2 Grant privileges to create virtual table
Remote source has been created in the previous step. Next step is creating virtual tables. Before this I need to grant privileges to create virtual table to administrator of the HDI container.
In this blog I will use “.hdbgrants” file to grant privileges. You can directly grant privileges to the user using SAP HANA Cockpit.
2.2.1 Create User-Provided Service
First, create a service for grant privileges. At SAP Cloud Platform Cockpit, create a new service (grant-service) by clicking “New Instance”.
{
"driver": "com.sap.db.jdbc.Driver",
"tags": [
"hana"
],
"user": "SYSTEM",
"password": "XXXXXX"
}
2.2.2 Edit mta.yaml
Register the service to mta.yaml. Update it as followings. (I need to configure “TARGET_CONTAINER” because there are several HDI Containers in my environment. You can find the id from “Service Instances” on SAP Cloud Platform Cockpit)
2.2.3 Create and Build “.hdbgrants”
Create and build “.hdbgrants” file to grant privileges. In this blog, I will grant “CREATE VIRTUAL TABLE” privilege to the remote source which was created before.
{
"grant-service": {
"object_owner": {
"global_object_privileges": [
{
"name": "OPHANA",
"type": "REMOTE SOURCE",
"privileges":[
"CREATE VIRTUAL TABLE"
]
}
]
}
}
}
2.3 Create Virtual Table
At this point, I have created remote source and granted privileged to create virtual table. Next, create virtual tables.
Open SAP Web IDE for full-stack and right-click to create a new virtual table.
Input virtual table name, remote source name, schema name and table name. For Database Name, input “<NULL>”. Save and build it, then virtual table is created. Create all necessary virtual tables.
Go to Database Explorer to check the virtual table. Click “Table” and you can confirm that virtual tables are successfully created (VT_ tables here).
Execute SQL Select statement against the virtual tables and you can confirm data can be retrieved. This means every time SQL is executed on SAP HANA Cloud, access to On-Premise SAP HANA to get data.)
3. Transfer Data
Virtual tables which access On-Premise SAP HANA table has been created. Next, I will transfer data from On-Premise SAP HANA to SAP HANA Cloud. The following 2 migration methods are mainly available.
1. Migrate data with SQL Insert – Select statement
2. Migrate data with SDI flowgraph
In this blog, I will use #1 above.
3.1 Transfer Data
Access to HDI Container using Database Explorer. The execute “Insert – Select” SQL like below.
insert into "SALESDB_1"."SALES::HDI_MD_PRODUCTS" select * from "SALESDB_1"."SALES::VT_MD_PRODUCTS";
The data transfer is complete when the SQL completes successfully.
3.2 Check the data
Access to the Calculation View and confirm the data and view are successfully migrated. To do this, go to “Column Views” in Database Explorer. You can see runtime objects of Calculation Views here. Open the CUBE type view “CV_SALES” and click data preview, you can see data is successfully displayed.
The migration of Calculation View and table data from On-Premise SAP HANA to SAP HANA Cloud has been completed. There may be other more efficient ways of migration, but I hope this helps you.
No comments:
Post a Comment