Microsoft Azure: Data Factory, SQL Database, Logic Apps: Transitioning On-Premises Workflows to the Cloud
Client based in – USA
Challenges:
Space Constraints and Reliability: On-premises systems require physical space for servers and infrastructure. Hardware failures or crashes can disrupt workflows, causing downtime and impacting critical processes. This reliance on physical hardware poses risks to continuity and system reliability.
Dependency on Remote Connectivity: Users relying on the flow system need remote access to view or make changes, especially when away from their desktops or outside the office. This reliance on remote connections introduces logistical challenges and potential disruptions to workflow accessibility.
System Maintenance: Regular system maintenance, including updating software versions and configuring settings, presents challenges. When software versions are updated, configurations need to be adjusted, and additional toolkits may be required. Moreover, deploying updates to production environments may involve transferring software to different systems, potentially causing compatibility issues or disruptions in workflow.
Additionally, system downtime during software updates poses a significant challenge. After updating software versions, it is often necessary to restart the system, leading to downtime. During this period, workflows cannot be executed, causing delays in processes and impacting overall productivity.
Components Used:
- ADF
- Azure SQL Database
- Logic Apps
The Solution:
To address our client's requirements, we implemented a solution leveraging Microsoft Azure Cloud Services in a three-step process.
- Utilizing Azure Data Factory: We employed Azure Data Factory as the initial step to extract data from both the On-Premises platform and API sources. This involved configuring pipelines to efficiently fetch data from disparate sources and prepare it for further processing.
- Leveraging Azure SQL Database: In the second step, we utilized Azure SQL Database as the central repository to store and manage the data obtained from the API and On-Premises sources. Additionally, we developed stored procedures to perform various data manipulation tasks such as formatting, JSON parsing, data insertion, updating, deletion, and table data comparison. These stored procedures ensured efficient data processing and maintained data integrity throughout the workflow.
- After completing the validation process using SQL Server stored procedures, we proceeded to generate Excel files using Azure Data Factory. This involved creating two files: one indicating successful validation and the other indicating validation failure. These files were then transferred back to the On-Premises platform. Based on the generation of the success or failure file, the Azure Logic App was triggered to update the status accordingly.
- Implementing Azure Logic App: Finally, we deployed Azure Logic App to automate the notification process. Upon completion of the data processing tasks or encountering any errors, Azure Logic App was configured to send confirmation emails to relevant stakeholders. This proactive notification system ensured timely communication of process completion status and facilitated prompt resolution of any issues encountered during the workflow execution.
The image below depicts the same process we described earlier for converting the on-premises flows to the cloud:
.png)
