Connect your on-premise environment to Oracle Autonomous Data Warehouse

The Cloud has never been closer to you

When looking for cloud services such as Oracle Analytics Cloud (OAC), you most probably come up first with tons of questions:

  1. Where the data will be stored and if it complies with the rules in your organization?
  2. What’s the level of security applied and the risks involved due to regulations (e.g. GDPR) ?
  3. How to populate the data from your local environment into the Cloud?
  4. What will be the TCO and the ROI of the move to the cloud?
  5. …

As you go through your evaluation process, in general you realize that Cloud infrastructure answer in fact to your requirements, and that it is finally very similar to your on-premise environment.

Based on our experience, we can help you in making the best decisions and in implementing the solution that matches your needs.

In this specific case, from a technical point of view, Synchrotech has developed a guideline with 3 steps that help you to establish a seamless connection of your on-premise environment with the Oracle Autonomous Data Warehouse.

 

3 steps to successfully configure the gateway

The purpose of our guideline is to help you configure the gateway that populates data from your local environment into the Cloud infrastructure. The use case typically relates to Analytics projects, where most operational applications (data sources systems) still reside in the local infrastructure. New modern Analytics solutions are available as a Service (SaaS) in the Cloud and therefore, it will be required to push or stream data to the Cloud. The image below gives you an overview of the architecture required.

 

Following our approach, we successfully used the ETL Microsoft SQL Server Integration Service (SSIS) to connect to Oracle Autonomous Data Warehouse (ADW).

 

1-Install components

Oracle Instant Client basic and SQL*Plus, which provide Oracle Database tools, libraries, and SDKs, have to be installed on the local environment. It enables to connect to the remote Oracle database. While installing these components, Oracle client credentials (wallet files) need to be downloaded from the Oracle Cloud Infrastructure console and installed locally accordingly. Wallet files, along with the Database user ID and password, provide access to data in your Autonomous Data Warehouse. Therefore, wallet files have to be stored in a secure location.

In order to use SSIS and to configure the connection, Visual Studio and SQL Server Data Tools (SSDT) needs to be installed on the local environment.

 

2-Configure ETL packages

In our tests, we configured the connection in the SSIS package using the .Net\SqlClient Data Provider (e.g. ODP.NET, Managed Driver). Once the connection is set, the dataflow task can be defined based on your need. First, the source and transformation operations have to be created as usual. Then, the destination operation will use the newly created connection to Oracle Cloud ADW. Depending on the data you want to load, you will most probably need to check dataflow’s and connection’s custom parameters (e.g. BatchSize, CommandTimeout, Maxpoolsize).

 

3-Run and tests connections

The final step consists in executing the ETL package. Once the extraction, transformation, and load of the data into Oracle Cloud ADW runs successfully in Visual Studio, you can plan the deployment of the package in Microsoft SQL Server.

 

Need more information?

Don’t hesitate to contact us if you still have questions regarding this topic. Our team is pleased to help you in your integration journey to the Cloud.

Catégories