Microsoft Business Intelligence

Azure Data Factory, Azure SQL Database, Azure 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:

Benefits:

• Accessibility: Azure Cloud can be accessed from anywhere with an internet connection, eliminating the need for logging into remote desktops. Users can securely access Azure services and resources from any device, enhancing productivity and flexibility.
• Role-Based Access Control (RBAC): Azure offers robust security features, including RBAC, which allows administrators to assign roles and permissions to users based on their job responsibilities. This ensures that users have access only to the resources and data necessary for their roles, reducing the risk of unauthorized access and data breaches.
• Cost Efficiency: Azure's pay-as-you-go pricing model allows businesses to only pay for the resources and services they use.