Replicate data from on-premise to the cloud with the Cloud Connector
In this article, you will learn how to use the SAP Connectivity Service to connect an SAP HANA on-premise database to an SAP HANA Cloud database to replicate data from SAP HANA on-premise to the cloud database. This includes setting up the cloud connector in your on-premise database, which allows you to set up a connection to a remote source that does not require the installation and configuration of the Data Provisioning Agent.
This connection is a convenient choice if you want to quickly replicate data from an on-premise database to SAP HANA Cloud database.
You have an SAP HANA on-premise database but no production instance of SAP HANA Cloud and would like to try out the SAP Connectivity Service? This service is available in the free trial of SAP HANA Cloud! Sign up now.
How does Replication of data in SAP HANA Cloud, HANA database work?
Generally, there are different technologies that support different scenarios of replicating data from and to an SAP HANA Cloud, HANA database.
In this diagram provided by the technical documentation on Replicating Tables from Remote Sources, you can see different methods of replicating data to SAP HANA Cloud, HANA database:
You can replicate data from many different sources to an SAP HANA Cloud, HANA database by using the Data Provisioning Agent and Smart Data Integration (SDI).
This includes replicating data from SAP HANA on-premise. With this method, you can replicate data both ways and using versions of SAP HANA on-premise of SPS04 and earlier. You can read more about how to use SDI in this learning article.
Another option that does not require the Data Provisioning Agent is Smart Data Access (SDA):
When replicating data from one SAP HANA Cloud, HANA database to another, the best way is to use Remote Table Replication with the ODBC adapter and SDA. To learn more about Remote Table Replication between SAP HANA Cloud, HANA databases, you can read this blog post by Seungjoon Lee.
SDA can also be used to replicate data from SAP HANA Cloud, HANA database to SAP HANA on-premise. Learn more about how to replicate data this way in this learning article.
Using SDA and the ODBC adapter, you can also replicate data from an SAP HANA on-premise database to an SAP HANA Cloud, HANA database. This requires an additional step to create a secure connection between the two systems: The cloud connector.
What is the Cloud Connector?
The cloud connector is a component for on-premise databases that serves as a link between SAP HANA Cloud and other SAP Business Technology Platform applications. It lets you easily connect and expose specific parts of an on-premise database without having to expose the entire on-premise landscape. It gives you fine-grained control and helpful features for business-critical scenarios, like recovering broken connections automatically and running in a high-availability setup.
The cloud connector offers you several advantages. For example, installation and configuration are easy as, for example, you do not have to configure an on-premise firewall for allowing access to an on-premise system.
Now let us look at the steps you need to take to install and set up cloud connector.
The steps described in this article require…
- …an SAP HANA on-premise database with SPS05 or higher if you want to use Remote Table Replication. If you have HANA express edition SPS04 Rev45, however, you may still use the cloud connector for SDA virtual access.
- …a running instance of an SAP HANA Cloud, HANA database (trial or production).
Now let us look at the steps you need to take to install and configurate the Cloud Connector, which you will learn how to do throughout this article:
- Enabling the Connectivity Service for SAP HANA Cloud
- Installing and configuring the cloud connector
- Establishing a remote source to replicate data from SAP HANA on-premise to SAP HANA Cloud
To get started, go to the next section and learn hands-on how to enable the Connectivity Service.
How to enable the Connectivity Service for SAP HANA Cloud
First, you need to enable the Connectivity Service in the SAP HANA Cloud, HANA database you want to connect to an on-premise database.
You can do so either when provisioning a new instance or by editing an already existing instance.
Option 1: Enable the Connectivity Service during the provisioning of a database
- Open SAP BTP Cockpit and enter your (trial) account.
- Enter your Global Account, Subaccount and then space.
- There, click on SAP HANA Cloud on the left side menu.
- Click on Create Database.
- The wizard will navigate you through the steps necessary to create your instance. If you are unsure how to provision the instance, you can read our learning article here.
- In step 3 of the process (SAP HANA Advanced Settings), you can specify what IP addresses you allow or deny. Below, you can see the option to enable or disable the SAP Cloud Connector.
- Click on Enable and select what IP addresses you would like to allow.
- Finish the rest of the settings and click on Create Instance.
- Your instance will be created and it is ready to be connected with the cloud connector.
Option 2: Enable the Connectivity Service in an existing instance
- Open SAP BTP Cockpit and enter your (trial) account.
- Enter your Global Account, Subaccount and then space.
- Navigate to the tile of the instance you want to connect to an SAP HANA on-premise database.
- Click on Actions on the bottom right corner of the tile.
- Select Monitor database. SAP HANA Cloud Central will open in a new tab.
- There, click on the three dots in the Actions column on the right side of the screen.
- Select Edit.
- In the wizard that opens, scroll down to the section Connections.
- Click on Enable.
- Click on Save.
- Your instance is now enabled and ready to be connected with the cloud connector.
Now that you have enabled the Connectivity Service for SAP HANA Cloud, it is time to install the cloud connector and configure it. Learn in the next section how to do that.
How to install and configure the Cloud Connector
- Install the cloud connector. You can download it from here. Check the system requirements for different operating systems as well as additional setup recommendations in the technical documentation here.
- Once the download and installation are finished, open a web browser to administer the Cloud Connector. You can find the supported browsers here.
- In a web browser, enter:
- 8443 is the default port specified during installation. If you have installed the cloud connector to a different machine than your local one, enter its hostname instead of the default port.
- On the logon screen that opens, enter this statement and include your credentials (case sensitive):
Administrator / manage for <User Name> / <Password>
- When you first log in, you need to change the password.
Define the subaccount
- In the Cloud Connector Administration, you can see at the top of the screen that it will not work before you define a subaccount. You need to define the subaccount to map the cloud connector to the subaccount that your SAP HANA Cloud instance you want to connect is located in.
- Select the region of the subaccount.
- In the field Subaccount, you need to enter the subaccount ID.
- To obtain it, go to SAP BTP Cockpit and navigate to your subaccount. If you still have SAP BTP Cockpit open from the steps of enabling the Connectivity Service, you can get there by clicking on the name of your subaccount in the top panel of the screen.
- Copy the subaccount ID from the top of the screen and enter it in the field Subaccount in the cloud connector.
- Optionally, you can enter a display name
- Login in with the credentials of the subaccount on SAP BTP Cockpit.
- If your subaccount is not on Cloud Foundry these steps vary slightly: You need to fill in the technical Subaccount name, subaccount user name and password.
- Enter a location ID and note it for later as you will need this information to set up a remote source.
- Click on Save.
Connect the Cloud Connector to your SAP HANA on-premise system
- After the configuration has finished, click on Cloud To On-Premise on the left panel.
- There, click on the plus icon in the top right corner to add a new mapping that will map your SAP HANA on-premise system to the cloud connector.
- A wizard opens. There, from the drop-down menu Back-end Type, select SAP HANA.
- Click on Next.
- For the Protocol, select TCP from the drop-down menu.
- In the next step, enter the internal host information and internal port of your on-premise system.
- To expose your internal host to the public network, you need to next specify the virtual host and virtual port. Here, it is recommended to use a host name that is different from your internal host name. The port can stay the same.
- Click on Next. You can optionally add a description to this mapping or skip that step.
- Check the summary shown and click on Finish.
- Your internal system name is now mapped to your virtual host name.
- In the column Check Result, it still says Unchecked.
- To check it, click on the looking glass icon on the right.
- The status will change to Reachable. Now the cloud connector can reach your SAP HANA on-premise system.
- Congratulations, you have now configured the cloud connector and you are ready to use this connection.
- For further details on configurating the cloud connector, please refer to the technical documentation.
Now you have successfully installed and configured the cloud connector. Only one more step is needed to replicate data from the on-premise database to the SAP HANA Cloud, HANA database. Learn in the last section how to establish a remote source using the connection you have just created.
How to establish a remote source from SAP HANA Cloud to SAP HANA on-premise
In your SAP HANA Cloud, HANA database instance, you can now create a remote source between this instance and the on-premise database.
- Open the SAP HANA Database Explorer connected to your SAP HANA Cloud instance.
- Open a SQL console of your instance.
- With this statement, you can create a remote source from SAP HANA Cloud to SAP HANA on-premise to then replicate data from the on-premise system to the cloud.
CREATE REMOTE SOURCE <NAME> ADAPTER "hanaodbc" CONFIGURATION 'ServerNode=<virtual_host_name:portnumber>;use_haas_socks_proxy=true;scc_location_id=<locationid>' WITH CREDENTIAL TYPE 'PASSWORD' USING 'user=<username>;password=<password>';
- In that statement, you need to add the host name and port number of the virtual host that you have manually created in the cloud connector. The reason for this is to not reveal the actual hostname and port which might be a breach in security.
- For on-premise connections, the socks proxy needs to be specified, which is already included in this statement.
- Next, in that statement, add the cloud connector location id, which you specified in the beginning.
- Running this statement will create a remote source from SAP HANA Cloud to your on-premise system.
- The remote source will be established and you can create virtual tables and use Remote Table Replication just like when connecting different SAP HANA Cloud instances. Please note that you are only able to use Remote Table Replication if you are using SAP HANA on-premise SPS05 or higher, otherwise, only SDA virtual access is available.
For more technical details on creating the remote source, read more in the technical documentation.
And now you know how to install and configure the cloud connector to connect an SAP HANA on-premise database to SAP HANA Cloud. You can now use Remote Table Replication to replicate data from an SAP HANA on-premise database to an SAP HANA Cloud, HANA database. Learn in the next section how to use it.
How to use Remote Table Replication
Once you established a remote source following the instructions from the previous step, you can start by creating a virtual table:
- In your catalog, click on Remote Sources.
- Then, double-click on your remote source. A wizard will open.
- In the drop-down menu to select a schema, you can now see the schema information from the remote source system.
- Select the schema you would like to replicate a table from and click on Search.
- You will now see the tables in the schema in your on-premise database.
- Next, select a table from the list and create a virtual object that can be included in federated queries.
- You can also adjust and execute this SQL statement to create a virtual table:
CREATE VIRTUAL TABLE "VT_TABLENAME" AT "REMOTESOURCE"."DATABASE"."SCHEMA"."TABLE";
- Once you have executed this statement successfully, you can find the virtual table in the catalog in your tables.
- You can now query the data in this remote table.
In some cases, a virtual table may not be the best solution. For example, if you need to improve query performance. With Remote Table Replication, you can copy real-time data to your SAP HANA Cloud, HANA database, which makes querying this data faster.
Additionally, if changes in the definitions of the source table of a virtual table occur, for example dropping or adding of columns, querying the virtual table pointing to the source table will result in an error. This can be avoided by setting up a remote subscription, which will not only replicate the data in real-time from the source system, but also the table definitions.
After the virtual table has been created, to set up Remote Table Replication, you need create a Replica Table and a Remote Source Subscription:
- The replica table will be the target table, that resides in the SAP HANA Cloud, HANA database. This target table should be just like the virtual table you have just created.
- So, first, create the replica table by executing this statement:
CREATE COLUMN TABLE <TARGETTABLENAME> LIKE <VIRTUALTABLENAME>;
- Once this statement is executed, you can find the metadata information from the remote source in this target table.
- Double click on the new target table and you can see the same columns as in the virtual table. However, you will see no data because no replication has been set up for this table yet.
- To start real-time replication, you need to create a remote subscription and activate that subscription. This will start the replication to your target table.
- When creating a remote subscription, you need to specify a name for this remote subscription and specify from which virtual table to which target table the remote subscription should be established. You can adjust and execute this statement:
CREATE REMOTE SUBSCRIPTION <REMOTESUBSCRIPTIONNAME> ON <VIRTUALTABLENAME> TARGET TABLE <TARGETTABLENAME>;
- Once this statement has been executed successfully, you can click on Remote Subscriptions in your catalog and you should see it in the list there.
- Next, to activate this remote subscription and start replicating data from your source system, you need to ALTER it and set it to DISTRIBUTE using this statement:
ALTER REMOTE SUBSCRIPTION <REMOTESUBSCRIPTIONNAME> DISTRIBUTE;
- While this statement is being executed, in the background all data from the source table will be replicated to the target table.
- After the execution is complete, you can use a SELECT * FROM statement on your target table and you will see the data from your source table in it.
- If you now add new rows to your source table in your on-premise database, you will instantly see the added data in your target table due to the remote subscription.
- This applies for adding columns to the source table as well. Data definition changes like adding or dropping columns will also be instantly applied to your remote subscription.
- The virtual table, however, will stay untouched by these changes and querying it would result in an error.
- To refresh the virtual table to also include changes in data definitions, you need to execute this statement:
create virtual table <VIRTUALTABLENAME> refresh definition;
- If you now query the virtual table, you will see the updated definitions in this virtual table as well.
If you want to disable a remote subscription, you can use this statement:
alter remote subscription <REMOTESUBSCRIPTIONNAME> reset;
This will deactivate the DISTRIBUTION mode of the remote subscription. The definition of the target table will remain, but no data or definition changes will be updated from the source system anymore.
Make sure to check out more of our learning tracks on related topics, for example:
- Learning Track “Extending your SAP HANA on-premise to SAP HANA Cloud”
- Learning Track “Connection Guides for SAP HANA Cloud”
- Learning Track “How to set up a multi-source data landscape and improve query performance”
More related content: