Search
Close this search box.

Oracle Database service for Azure – connecting Azure VM and Power App

I have connected a Database Admin Azure VM running Oracle’s SQL Developer (Windows version) and a Microsoft Power Platform application displaying Oracle’s HR demo schema (via on-premises data gateway on Azure VM connecting with Power Platform’s Oracle Premium Connector) to the same Oracle Database hosted on OCI. I used Oracle-Azure Cloud-Link by the fully automated Oracle Database service for Azure in the background. In this article I am sharing my design, learnings and key takeaways from this exercise I enjoyed a lot.

Previously, I used Oracle Application Express for Oracle Databases. Oracle APEX is really cool, it is a rapid application development tool offered by Oracle, based on the underlying database schema.

When I have seen Microsoft Power Platform, my first impression was that citizen developers will have the demand to use Oracle Databases. What is an on-premises data gateway? | Microsoft Learn solves this problem – with that, you can connect Microsoft Power Platform to Oracle Databases. However, latency is still there, so ideally you need to move the database to Azure and deploy that gateway in Azure too to eliminate latency. This how to install such Install an on-premises data gateway | Microsoft Learn

Below my updated design, on the left side you see Azure, on the right side you find Oracle Cloud. I am not using on-premises datacenter in this demo/deployment.

I wanted to test both database Admin VM (light blue arrow) and a Power App (light green arrow) to connect to Oracle Base DB via OCI-Azure Cloud Link. They all worked very well 🙂

If you have Azure experience and are new to Oracle Cloud it will take some time to understand how similar purpose objects are called in OCI. For example, Azure Resource Group is called OCI Compartment. Networking foundations, such as Virtual Networks or Subnets are the same in each cloud. Policies on subnets, such as Ingress and Egress rules are automatically generated when you provision your first database.

I was testing several VM shapes to run Base Database. For me VM.Standard2.2 worked the best, it was quite fast and still affordable in price. I used popular 19.15 database, Standard edition.

It took me some time to understand how Database System (VM shape), Database Name and Pluggable Database Name relates to each other.

I missed the idea of Pluggable Databases and Introduction to the Multitenant Architecture (oracle.com) but it is never to late to learn new things from Oracle 🙂

After selecting the Azure Virtual Network the only major (deployment stopper) mistake you can make is to use the wrong network and subnet mask

OCI CIDR is

  • a new network that is not existing in OCI and is not used in Azure
  • a new network that will be automatically created in OCI connecting Database System, Database, and Portable DB – you will connect to the portable database with your applications
  • ideally, a B class (20-bit block) network and the perfect subnet mask is /16 to utilize cloud link
  • be careful – it is not going to check if you enter non-private networks IP, for example, I was able to deploy with 172.32.0.0/16 which is obviously the wrong range (outside the private network range), and not part of the 20-bit block.

The Internet Engineering Task Force (IETF) has directed the Internet Assigned Numbers Authority (IANA) to reserve the following IPv4 address ranges for private networks:

RFC 1918 nameIP address rangeNumber of addressesLargest CIDR block (subnet mask)Host ID sizeMask bitsClassful description
24-bit block10.0.0.0 – 10.255.255.2551677721610.0.0.0/8 (255.0.0.0)24 bits8 bitssingle class A network
20-bit block172.16.0.0 – 172.31.255.2551048576172.16.0.0/12 (255.240.0.0)20 bits12 bits16 contiguous class B networks
16-bit block192.168.0.0 – 192.168.255.25565536192.168.0.0/16 (255.255.0.0)16 bits16 bits256 contiguous class C networks

Source: Private network – Wikipedia

Be careful, you can accidentally use non-private network IPs, 20-bit block range is 16-31 (172.16.0.0 – 172.31.255.255)

When you submit this wizard you can track what is going on using Tenancy Explorer and ORM Jobs.

This is just after starting deployment…

Deployment jobs are running Terraform (Terraform by HashiCorp) code in OCI. This is how Cloud Link and DBS/DB/PDB get deployed.

Jobs are running Terraform scripts.

I checked Terraform code deploys resources in OCI. Very interesting to see what’s getting deployed in the background. Remember, you can deploy this manually as well, but I think fully automated mode saves a lot of time, especially for proof of concept presales exercises.
This is an example of Security Rules in OCI – hosting Database System VM and Database Instence. Oracle DB is using TCP/1521*, and SSH 22 allowed as well.

* An Oracle client connects to the server using the port address of the listener, which is normally defined as TCP port 1521 during Oracle installation. sqlnet service is defined as using TCP port 1521.

These are the security rules automatically deployed by the TF code above part of Base DB creation process. This is very nice, everything gets configured automatically. Well done!

You can also track the deployment on the “Oracle Database Service for Azure” portal.

but it will not give you detail 🙂
If you make a mistake with network configuration deployment will fail at ~19 minutes. If you do everything right, it will take 1 hour and ~33 minutes to complete the deployment. That’s quite good considering/comparing Oracle Enterprise Linux and Oracle DB Instance semi-automated on-premises deployment task.
Again, the best way to see what’s going on is to use the Tenancy Explorer in OCI and observe objects in the mapped resource group.
It is very important to understand how Azure objects are mapped/mirrored in OCI. Compartments are nested in OCI, unlike Azure Resource Groups. Under the root, you have the CloudLink_Azure. Below the CloudLink you have Subscription ID mapped and Resource Group mapped.
When the deployment is completed, you can get the connection string to DB and PDB using both OCI and Azure-like portal.
This is the Database.
And this is the connection string…
I connected to the database instance. Please do not mix this with the Pluggable Database, that’s different. This is quite handy, you can get connection string from both Oracle and Azure-like portal.

Let’s connect from Power Platform. In order to do that, I had to deploy On-premises data gateway on the Azure hosted Admin VM.

Installing OnPrem Data GW

This error message means that I have no license for Power Platform 🙂 OMG. I enabled E5 trial in my Azure Active Directory and assigned license to the user configured onprem data GW.
Fun fact. It was the licensing issue and not the misconfiguration above. Without proper Power Platfrom licenses on-prem data GW will not work. And remember, Oracle Connector is a Premium Connector needs to be covered by proper licensing.

After allocating the proper licenses it started to work

Hurray it works

Now I was able to add Oracle DB to Power Platform App
Remember, we are using the Pluggable Database connection string
You can get the same directly from OCI
What I did is quite simple, I updated a salary in HR.EMPLOYEES table for Steven King. COMMIT is needed to close the transaction 🙂
And after refreshing the data source the Power App grid immediately shows the updated data. That’s it. Simple and easy. I am using Azure Admin VM and Microsoft Cloud Power App with OCI database backend – and everything works nice and fast via free and low latency Oracle-Azure Cloud-Link. Nice!
I was planning more sophisiteced experiments such as testing database latency and comparing with same cloud (Oracle DB in Azure), other clouds (Oracle DB in AWS) or on-premises based Oracle Databases, etc. but this is quite time consuming, so I decided to drop this idea, for now 🙂
This is a nice screenshot of Tenancy Explorer – VM/DB and Dynamic Routing Gateway – both automatically provisioned.

When you are done with the demo, make sure to delete (terminate) the same way as you created. So use the Azure-like portal and never delete the managed objects from OCI directly.

You can check and confirm the deletion in OCI, but again, delete must be invoked from Azure-like portal not OCI directly.

When it comes to price, I was mainly paying for Oracle Standard Database License for 2 days

Yellow is Database cost, compute is green almost nothing.
You can check the billing on the Azure-like portal. Before you get scared on the 2 days consumption (7224 USD = 2 970 924 HUF), it shows probably every currency in USD without converting. So this is 7224 HUF and not USD. There is a quite big difference. I am not yet an enterprise customer of Oracle Hungary 🙂
The OCI portal shows consumption in a proper currency.
And here is the real invoice – main usage is the Database, just like the cost analysis showed before.

Key takeaway, there is no charge on Azure side. The fantastic time saver full automation is free, the cloud-link is free and provides low latency, it is easy to use. Well done, I can’t wait to see how this evolves over time.

Related posts

Comparison of VMware relocation options in public cloud

I keep researching this topic from several perspectives: regional availability, provided architecture, most popular use cases, VMware software versions, provided hardware configuration, and finally the price of a 3-node vSphere cluster in the Cloud.

AWS MiGratioN, GCP Migrate4Cloud, and Azure Migrate pros and cons

It’s been more than 5 years since I am testing and comparing 1st party migration tools. I have seen these tools getting better over the years, with major improvements by acquisitions, end-of-life products, continuous changes, and improvements not just the tools but the methodology around, well-architected, CaF, the concept of the landing zone, 5Rs become 7Rs. In this article, I am sharing my experiences with the most commonly used cloud migration tools.

Oracle Database service for Azure – connecting Azure VM and Power App

I have connected a Database Admin Azure VM running Oracle’s SQL Developer (Windows version) and a Microsoft Power Platform application displaying Oracle’s HR demo schema (via on-premises data gateway on Azure VM connecting with Power Platform’s Oracle Premium Connector) to the same Oracle Database hosted on OCI.

Oracle Database service for Azure – linking subscriptions

As part of my multi-cloud research, I wanted to test Oracle Database Service for Azure. In this article, you will see how to sign up for the new service and how to link Oracle and Azure accounts. I used Frankfurt datacenters, Azure MSDN, and OCI paid account (Free Tier does not work) using my private Azure Active Directory.

Why multi-cloud is the way to go? VMware and Oracle perspective.

While cloud migration is still a popular topic during customer discussions, I have noticed that more and more customers are considering an exit plan from one cloud (vendor lock-in) to another cloud meaning there is an increase in multi-cloud migration demand. VMware, Oracle, and SAP are the major workloads in on-premises data centers today. Based on my research both VMware and Oracle are very vocal about the importance of having a multi-cloud strategy.

AWS Site-to-Site VPN using MikroTik RouterOS

There are two ways of approaching this challenge. (#1) running MikroTik virtual appliance (CHR) in AWS (#2) using Virtual Private Gateway, a “cloud-native” networking solution provided by AWS. Each solution has its own benefits.