Connecting an on-prem SQL Database to Azure Logic Apps (feat. Azure Arc for Data Services)
Recently, we built a web app example for one of our favorite clients in Azure that involved demonstrating connectivity to an on-premise Microsoft SQL Server environment in the datacenter. This is a fairly common requirement we see out there… although its easy and to build and launch web apps in the cloud, most enterprises and organizations still need to tie into databases, apps, data lakes, storage platforms, devices, and other key services that are located in their datacenters for a multitude of reasons.
In this particular example, we needed to build an example web app to handle a general user registration use case, tie into Azure Bot Services for logic, and leverage some kind of easy connectivity to a backend database on-premise—in this case and like in many others, Azure Logic Apps was chosen as the glue, just due to its ease of use and the fact that we were building a fairly straight-forward app.
Here’s how the architecture in Azure and on-premise looked:
We used Azure Data Gateway for the connectivity between Azure and our lab and Azure Arc for Data Services, which is currently in preview, to orchestrate and run the deployment of database services, in this case Azure SQL MI (SQL Managed Instance).
Azure Arc for Data Services deploys to most mainstream Kubernetes clusters, including OpenShift, Rancher, Azure Kubernetes Service (AKS), etc.
In the real-world, for an Azure end-to-end strategy for an organization committed to Azure (and all its managed distributions), we might recommend running AKS on Azure Stack HCI to run Azure Arc for Data Services, but for the purposes of this example in our lab, we deployed Azure Arc for Data Services on a 5-node Rancher RKE cluster in our lab (1 master node and 4 worker nodes):
The 5-node Kubernetes cluster running Azure Arc and all its associated pods looked something like the following. One of the worker nodes was assigned to run the actual Azure SQL MI instance (one pod – 3 containers), while the pods that ran the core services for Azure Data Controller were spread across all the worker nodes.
Once Azure Arc for Data Services was spun up and operational, we then connected it to Azure via the Azure Data Gateway. There are numerous ways to connect to Azure from the datacenter and the edge, and this was the simplest and quickest way for us in our setup.
After Azure Arc and its various services were connected, we were then able to utilize Azure Logic Apps and its connectors to initiate the connection to on-premise services fairly easily, starting with this Azure SQL MI instance on-premise.
Connectivity from Azure and Azure Logic Apps to Azure SQL MI from on-premise Azure Arc:
After we connected to Azure SQL MI in Azure Arc, we also connected to some traditional SQL Server 2019 databases housed in VMware to demonstrate the idea that whether the foundation is Azure Arc or if its traditional, we have the ability to easy integrate with any SQL instance housed in the datacenter.
This exercise in “hybrid cloud” was actually pretty straight-forward, considering Azure Arc for Data Services is in preview--- we look forward to seeing what other services make their way into the Azure Arc foundation as we move forward!