Enable multicloud integrations from Oracle Cloud ERP to Microsoft Azure SQL Database

Oracle Integration Cloud Service is a fully managed service that enables multicloud integration architectures.

This reference architecture provides the necessary considerations and recommendations to enable a multicloud, event-driven solution to feed a private Microsoft Azure SQL Database with real-time business events data coming from Oracle Fusion Cloud Enterprise Resource Planning (Oracle Cloud ERP), using:

• A no-code approach with Oracle Integration Cloud Service (Oracle Integration) native adapters for Oracle Cloud ERP and Microsoft Azure SQL Database
• An event-driven pattern to decouple Oracle Cloud ERP from Microsoft Azure SQL Database using Oracle Cloud Infrastructure Streaming (OCI Streaming) Service (Oracle managed Kafka Service) and the native adapter we have for this Oracle Cloud Infrastructure (OCI) Service in Oracle Integration.

• A private connection to Microsoft Azure SQL Database using the Oracle Integration Connectivity Agent, which is the key enabler for multicloud integration architectures.

Architecture

This reference architecture shows how to enable a multicloud, event-driven and no-code integration solution to receive real-time feeds from Oracle Cloud ERP and send those to a private Microsoft Azure SQL Database, leveraging a component Oracle Integration provides called the connectivity agent, to facilitate on-premises/multicloud integrations.

You can use the Oracle Integration Connectivity Agent in any of the following patterns detailed in the following sections to set up a connection between Oracle Integration and Microsoft Azure SQL Database on a private Azure network.

Multicloud, event-driven feeds from Oracle Cloud ERP to Microsoft Azure SQL Database - public internet and Oracle Integration Connectivity Agent deployed in Azure private network pattern

This scenario uses an Oracle Integration connectivity agent to communicate with a private Azure SQL Database.

• The Oracle Integration Connectivity agent is installed on an Azure VM in a private subnet within a VNet. An Azure Private Link connects to the private Azure SQL Database. Private Link allows you to connect to the Azure SQL Database via a private endpoint.
• In this case, the Azure VM which hosts the Oracle Integration Connectivity Agent can connect to the private Azure SQL Database (public access to Azure SQL Server is disabled) via the private endpoint, which is also set up on the same Azure virtual network (VNet).
• The Oracle Integration Connectivity Agent communicates with Oracle Integration over public internet through an Azure NAT Gateway (associated to the private subnet within the VNet) on one side, and with the private Azure SQL Database on the other side through an Azure Private Endpoint (Private Link).
• When an integration flow within Oracle Integration makes use of the Microsoft Azure SQL Database adapter to perform an action on it (example, load Oracle Cloud ERP business events data into an Azure SQL table), the connectivity agent initiates a secure connection to Oracle Integration, retrieves the request, and then performs the action on the private Azure SQL Database.
• When you use the connectivity agent, you don't have to open firewalls to access the Azure SQL Database on your Azure private network. In addition, all messages between the Azure private network and Oracle Integration are encrypted (SSL).

The following diagram illustrates this reference architecture.

Description of the illustration oci-multicloud-int-public-internet.png

oci-multicloud-int-public-internet-oracle.zip

Multicloud, event-driven feeds from Oracle Cloud ERP to Microsoft Azure SQL Database - Oracle Interconnect for Azure and Oracle Integration Connectivity Agent deployed in an Azure private network pattern

This scenario uses an Oracle Integration connectivity agent to communicate with a private Azure SQL Database.

• The Oracle Integration Connectivity agent is installed on an Azure VM on a private subnet within a VNet. An Azure Private Link connects to the private Azure SQL Database via a private endpoint.
• In this case the Azure VM which hosts the Oracle Integration Connectivity Agent can connect to the private Azure SQL Database (public access to the Azure SQL Server is disabled) via the private endpoint, which is also set up on the same Azure virtual network (VNet).
• The Oracle Integration Connectivity Agent communicates with Oracle Integration over a secure, private intercloud connection between Azure network and OCI VCN that bypasses the public internet (Oracle Interconnect for Azure).
• In this case, the connectivity agent can access Oracle Integration through an Azure Virtual Network Gateway (associated with the private subnet within the VNet), which in turn connects to an OCI VCN via Oracle Interconnect for Azure (ExpressRoute on the Azure side, and FastConnect on the Oracle Cloud side), and with the private Azure SQL Database through an Azure Private Endpoint (Private Link).
• When an integration flow within Oracle Integration makes use of the Microsoft Azure SQL Database adapter to perform an action on it (example, load Oracle Cloud ERP business events data into an Azure SQL table), the connectivity agent initiates a secure connection to Oracle Integration, retrieves the request, and then performs the action on the private Azure SQL Database.
• In addition, an OCI Service Gateway must be configured to route the traffic from the VCN to Oracle Integration. Also, Transit Routing directly through gateways must be enabled on the OCI VCN (Route tables on the OCI VCN must be set up for the OCI DRG attachment, as well as for the OCI Service Gateway). This is so traffic transits through the VCN to its destination beyond the VCN such as Oracle Integration (private access to Oracle Cloud Services like Oracle Integration is ensured when using OCI Service Gateway).

The following diagram illustrates this reference architecture.

Description of the illustration oci-multicloud-interconnect-azure.png

oci-multicloud-interconnect-azure-oracle.zip

Multicloud, event-driven feeds from Oracle Cloud ERP to Microsoft Azure SQL Database - Oracle Interconnect for Azure and Oracle Integration Connectivity Agent deployed in an OCI VCN pattern

This scenario uses an Oracle Integration connectivity agent to communicate with a private Azure SQL Database.

• The Oracle Integration connectivity agent is installed on an OCI VM on a private subnet within an OCI VCN. In addition, an OCI Service Gateway must be configured to route the traffic from the VCN to Oracle Integration. On the Azure side, an Azure Private Link connects to the private Azure SQL DB. Private Link allows you to connect to the Azure SQL DB via a private endpoint.
• In this case, the OCI VM which hosts the Oracle Integration connectivity agent within an OCI VCN can connect to the private Azure SQL Database (public access to the Azure SQL Server is disabled) via the private endpoint, which is set up on an Azure virtual network (VNet).
• The Oracle Integration Connectivity Agent (deployed in an OCI VCN) communicates privately with Oracle Integration via an OCI Service Gateway (private access to Oracle Cloud Services like Oracle Integration is ensured when using OCI Service Gateway).
• On the other side, Oracle Integration Connectivity Agent communicates with the private Azure SQL Database over a secure, private intercloud connection between Azure network and OCI VCN that bypasses the public internet (Oracle Interconnect for Azure). In this case, the connectivity agent can access the private Azure SQL Database through an OCI Dynamic Routing Gateway (DRG), which in turn connects to an Azure VNet via Oracle Interconnect for Azure (ExpressRoute on the Azure Side, and FastConnect on the Oracle Cloud Side), it reaches an Azure Virtual Network Gateway (associated with the Azure VNet), and finally connects to the private Azure SQL Database through an Azure Private Endpoint (Private Link), configured within the Azure VNet.
• When an integration flow within Oracle Integration makes use of the Microsoft Azure SQL Database adapter to perform an action on it (example, load Oracle Cloud ERP business events data into an Azure SQL table), the connectivity agent initiates a secure connection to Oracle Integration, retrieves the request, and then performs the action on the private Azure SQL Database.

The following diagram illustrates this reference architecture.

Description of the illustration oci-multicloud-interconnect-azure-agent.png

oci-multicloud-interconnect-agent-oracle.zip

For all three patterns described in the above sections:

Oracle Integration is used for:

Producer Integration Flow

• Receives real-time data from Oracle Cloud ERP through the Oracle ERP Cloud native adapter (example, Purchase Order Event, Supplier Created Event, Payables Invoice Approved, Journal Batch Posting Completed, and so on).
• Enriches the Oracle Cloud ERP Event Payload, retrieving additional Event details using the Oracle ERP Cloud native adapter such as – Get Additional Supplier Details on top of the Supplier Created Event – (optional, only applies for a few business events).
• Publishes the Oracle Cloud ERP Business Events Data to OCI Streaming Private Kafka Streams/Topics (example, PurchaseOrderTopic, SupplierTopic, AccountPayablesTopic, and so on) through the OCI Streaming native adapter (Oracle Integration connection uses a Connectivity Agent deployed in an OCI VM in a private subnet, which has access to the private subnet the OCI Streaming Private Endpoint belongs to. This Connectivity Agent makes requests to Oracle Integration via an OCI Service Gateway, ensuring access to Oracle Integration is routed over the internal network).

Consumer Integration Flow

• Consumes the Oracle Cloud ERP Business Events Data from OCI Streaming Private Kafka Streams/Topics (example, PurchaseOrderTopic, SupplierTopic, AccountPayablesTopic, and so on) through the OCI Streaming native adapter.
• Loads the Oracle Cloud ERP Business Events Data into a private Azure SQL Database through the Microsoft Azure SQL Database native adapter.

OCI Streaming in conjunction with Oracle Integration is used to:

• Decouple Oracle Cloud ERP from Azure SQL Database, minimizing the impact on the solution in case the downstream application/system goes down (Azure SQL Database in this case).
• Ensure the delivery of the messages to the Azure SQL Database with effective error handling (if the Azure SQL Database is down, you can use Retry Kafka Topics and Dead Letter Kafka Topics in OCI Streaming to retry failed messages, using retry scheduled integrations in Oracle Integration which consume failed messages and try to load them into Azure SQL Database).
• Parallelize Oracle Cloud ERP business events data processing using the OCI Streaming adapter capability to consume messages from a specific partition associated with the same Topic.
• Meter the requests sent to Azure SQL Database, since the OCI Streaming adapter allows to consume one or multiple records from a specific Partition/Topic, as well as control the frequency you consume messages from the Partition/Topic.

The architecture has the following components:

Oracle Cloud Infrastructure components

• Oracle Cloud ERP

  Oracle Cloud ERP application exposes web services, APIs, business objects, and publish events. The Oracle ERP Cloud adapter included with Oracle Integration provides connectivity to different modules within Oracle Cloud ERP like Financials, Procurement, Account Payables, General Ledger, and so on, without knowing about specific details involved in the integration. Oracle Cloud ERP business events can only be consumed via the Oracle ERP Cloud adapter.

• Oracle Integration

  Oracle Integration is a fully managed service that allows you to integrate your applications, automate processes, gain insight into your business processes, and create visual applications.

• Connectivity Agent

  You can create hybrid integrations and exchange messages between applications in private or on-premises networks and Oracle Integration using the connectivity agent.

• Streaming

  Oracle Cloud Infrastructure Streaming provides a fully managed, scalable, and durable storage solution for ingesting continuous, high-volume streams of data that you can consume and process in real time. You can use Streaming for ingesting high-volume data, such as application logs, operational telemetry, web click-stream data; or for other use cases where data is produced and processed continually and sequentially in a publish-subscribe messaging model.

• Region

  An Oracle Cloud Infrastructure region is a localized geographic area that contains one or more data centers, called availability domains. Regions are independent of other regions, and vast distances can separate them (across countries or even continents).

• Availability domains

  Availability domains are standalone, independent data centers within a region. The physical resources in each availability domain are isolated from the resources in the other availability domains, which provides fault tolerance. Availability domains don’t share infrastructure such as power or cooling, or the internal availability domain network. So, a failure at one availability domain is unlikely to affect the other availability domains in the region.

• Virtual cloud network (VCN) and subnets

  A VCN is a customizable, software-defined network that you set up in an Oracle Cloud Infrastructure region. Like traditional data center networks, VCNs give you complete control over your network environment. A VCN can have multiple non-overlapping CIDR blocks that you can change after you create the VCN. You can segment a VCN into subnets, which can be scoped to a region or to an availability domain. Each subnet consists of a contiguous range of addresses that don't overlap with the other subnets in the VCN. You can change the size of a subnet after creation. A subnet can be public or private.

• Network security group (NSG)

  Network security group (NSG) acts as a virtual firewall for your cloud resources. With the zero-trust security model of Oracle Cloud Infrastructure, all traffic is denied, and you can control the network traffic inside a VCN. An NSG consists of a set of ingress and egress security rules that apply to only a specified set of VNICs in a single VCN.

• Service gateway

  The service gateway provides access from a VCN to other services, such as Oracle Cloud Infrastructure Object Storage. The traffic from the VCN to the Oracle service travels over the Oracle network fabric and never traverses the internet.

• Dynamic routing gateway (DRG)

  The DRG is a virtual router that provides a path for private network traffic between VCNs in the same region, between a VCN and a network outside the region, such as a VCN in another Oracle Cloud Infrastructure region, an on-premises network, or a network in another cloud provider.

• Route table

  Virtual route tables contain rules to route traffic from subnets to destinations outside a VCN, typically through gateways.

• FastConnect

  Oracle Cloud Infrastructure FastConnect provides an easy way to create a dedicated, private connection between your data center and Oracle Cloud Infrastructure. FastConnect provides higher-bandwidth options and a more reliable networking experience when compared with internet-based connections.

• OCI and Azure Interconnect

  Oracle Cloud and Microsoft Azure Interconnect is Oracle’s first multicloud offering. It delivers a direct network connection between specific Azure and Oracle Cloud Infrastructure (OCI) data centers around the world. It enables Azure administrators and developers to connect their applications to applications and services running in OCI without creating dedicated links or sending their application traffic across the public internet.

Microsoft Azure components

• Azure SQL Database

  Azure SQL Database is a fully managed database engine (platform as a service) that handles most of the database management functions such as upgrading, patching, backups, and monitoring without user involvement. The Microsoft SQL Server Adapter included with Oracle Integration enables you to integrate the Microsoft Azure SQL Server database residing behind the firewall of your Azure network with Oracle Integration through use of the Oracle Integration connectivity agent. Use the Microsoft SQL Server Adapter to poll for new and updated records for processing in Oracle Integration.

• Virtual network (VNet) and subnet

  A VNet is a virtual network that you define in Azure. A VNet can have multiple non-overlapping CIDR blocks subnets that you can add after your create the VNet. You can segment a VNet into subnets, which can be scoped to a region or to an availability zones. Each subnet consists of a contiguous range of addresses that don't overlap with the other subnets in the VNet. Use VNet to isolate your Azure resources logically at the network level.

• Private Link (Private Endpoint)

  Private Link allows you to connect to various PaaS services in Azure (like Azure SQL Database) via a private endpoint. A private endpoint is a private IP address within a specific VNet and subnet.

• Virtual network gateway

  A virtual network gateway allows traffic between an Azure VNet and a network outside Azure, either over the public internet or using ExpressRoute, depending on the gateway type that you specify.

• Route tables

  Route tables direct traffic between Azure subnets, VNets, and networks outside Azure.

• ExpressRoute

  Azure ExpressRoute lets you set up a private connection between a VNet and another network, such as your on-premises network or a network in another cloud provider. ExpressRoute is a more reliable and faster alternative to typical internet connections, because the traffic over ExpressRoute doesn't traverse the public internet.

Recommendations

Use the following recommendations as a starting point when enabling a multicloud, event-driven integration solution from Oracle Cloud ERP to Microsoft Azure SQL Database through Oracle Integration and OCI Streaming. Your requirements might differ from the architecture described here.

• Connectivity

  All connections within OCI and Azure should be established through a private network, that is the:

  • Connectivity Agent(s) which connect to OCI Streaming should be installed in an OCI VM within a private subnet.
  • OCI Streaming Kafka Streams/Topics that you create should be associated with a Stream Pool deployed with a private endpoint (associated with a private subnet in an OCI VCN).
  • Connectivity Agent(s) which connect to the Azure SQL Database should be installed in an Azure VM within a private subnet in a VNet.
  • Connection between the Azure VM which hosts the Connectivity Agent (deployed in a private subnet in the VNet) and Azure SQL Database should be via an Azure Private Link (Private Endpoint).

  You can start with setting up the Public Internet and Connectivity Agent deployed in an Azure Private Network pattern, which only requires you to install the Connectivity Agent in an Azure VM, and configure the appropriate security setup in Azure to guarantee access from the Connectivity Agent to the Oracle Integration instance (this is a SSL communication over Public Internet).

  To guarantee end-to-end private connectivity for this integration architecture, you might consider setting up either the Oracle Interconnect for Azure and Connectivity Agent deployed in an Azure Private Network pattern or the Oracle Interconnect for Azure and Connectivity Agent deployed in an OCI VCN pattern , to use Oracle Interconnect for Azure so all integration transactions between Oracle Integration and the Connectivity Agent which has private access to Azure SQL Database are routed through it.

• Restrict access to an Oracle Integration instance

  Restrict the networks that have access to your Oracle Integration instance by configuring the Oracle Integration Allowlist (formerly a whitelist). Only users/systems from the specific IP addresses, Classless Inter-Domain Routing (CIDR) blocks, and virtual cloud networks that you specify can access the Oracle Integration instance.

  In this integration architecture, Oracle Integration Allowlist could restrict access to the Oracle Integration instance, only allowing requests initiated by Oracle Cloud ERP instance(s) and VCN OCID(s) associated with the VMs hosting the Connectivity Agents.

• Use the Connectivity Agent in High Availability environments

  You can use the Connectivity Agent in high availability environments with Oracle Integration. You install the connectivity agent twice on different hosts. The connectivity agents can scale horizontally, thereby providing all the benefits of running multiple agents for an agent group. This results in increased performance and extends failover benefits.

  In this integration architecture, you could have:

  • Two Connectivity Agents installed on two different OCI VMs (for the agents which access OCI Services), each OCI VM deployed on different Fault Domains (within an Availability Domain), or different Availability Domains (within a Cloud Region).
  • Two Connectivity Agents installed on two different Azure VMs (for the agents which access Azure Services), each Azure VM deployed on different Azure Availability zones (within an Azure Region).
• VCN (also applies to Azure VNet)

  When you create a VCN, determine the number of CIDR blocks required and the size of each block based on the number of resources that you plan to attach to subnets in the VCN. Use CIDR blocks that are within the standard private IP address space.

  Select CIDR blocks that don't overlap with any other network (in Oracle Cloud Infrastructure, your on-premises data center, or another cloud provider) to which you intend to set up private connections.

  After you create a VCN, you can change, add, and remove its CIDR blocks.

  When you design the subnets, consider your traffic flow and security requirements. Attach all the resources within a specific tier or role to the same subnet, which can serve as a security boundary.

  Use regional subnets.

• Network security groups (NSGs)

  You can use NSGs to define a set of ingress and egress rules that apply to specific VNICs. We recommend using NSGs rather than security lists, because NSGs enable you to separate the VCN's subnet architecture from the security requirements of your application.

  In this integration architecture, NSG is used to define ingress and egress rules for OCI Streaming with private endpoint, OCI VM(s) hosting Connectivity Agents, and so on.

Considerations

Consider the following points when deploying this reference architecture.

• Scalability

  When creating OCI Streaming Streams/Topics, administrators specify the number of Streams they plan to use. Streams could be created per Business Domain (example, InvoiceStream, PurchaseOrderStream, and so on). Administrators also specify partitions they plan to use per Stream/Topic. Partitions allow you to distribute a stream/topic by splitting messages across multiple nodes, allowing to have multiple consumers reading from a stream/topic in parallel (in this case, you could have multiple clones of the same consumer integration flow in Oracle Integration, each one reading from a different partition of a Stream/Topic using OCI Streaming adapter as the trigger).

  When creating Oracle Integration instances, administrators specify the number of message packs they plan to use for per instance.

• Resource limits

  Consider the best practices, limits by service, and compartment quotas for your tenancy.

• Security

  Use Oracle Cloud Infrastructure Identity and Access Management ( OCI IAM) policies to control who can access your cloud resources (example, Oracle Integration, OCI Streaming instances, OCI Compute Instances, and so on) and what operations can be performed. To protect the database passwords or any other secrets, consider using the OCI Vault service.

• Availability

  Consider using a high availability option based on your deployment requirements and your region. The options include distributing resources across multiple availability domains in a region and distributing resources across the fault domains within an availability domain.

  Fault domains provide the best resilience for workloads deployed within a single availability domain. For high availability in the application tier, deploy the application servers in different fault domains, and use a load balancer to distribute client traffic across the application servers.

• Performance and cost

  OCI offers Compute shapes that cater to a wide range of applications and use cases. Choose the shapes for your compute instances carefully. Select shapes that provide optimal performance for your load at the lowest cost. If you need more performance, memory, or network bandwidth, you can change to a larger shape.

Explore More

Learn more about establishing multicloud private network connectivity through Oracle Integration.

Review these additional resources:

• Video: Building a multicloud, event-driven PO feed solution with low-code integration
• Oracle ERP Cloud Adapter capabilities
• Using the Oracle Cloud Infrastructure Streaming Service Adapter with Oracle Integration
• Microsoft SQL Server Adapter Capabilities
• Creating Hybrid Integrations Using Oracle Integration
• Learn About Interconnecting Oracle Cloud with Microsoft Azure
• Connectivity Agent
• Restrict Access to Oracle Integration
• Securing a Stream
• Best practices framework for Oracle Cloud Infrastructure
• Oracle Cloud Infrastructure documentation
• Oracle Cloud Cost Estimator

Acknowledgments

• Author: Juan Carlos González Carrero