Data Migration using ADF

Description

This project involved a complex data migration from an on-premises SQL Server database to a cloud-based system hosted on Azure, aimed at modernizing the data infrastructure to achieve better scalability, reliability, and performance. The project required leveraging Azure Data Factory (ADF) and other Azure services to streamline the transition process while ensuring minimal downtime and data accuracy.

Azure Services and Components Used

• Azure Data Factory (ADF): Used to design data pipelines for Extract, Transform, and Load (ETL) processes.

• Linked Services: Configured to establish connections between on-premises and cloud databases, and customized for handling dynamic changes such as database names across environments.

• Integration Runtime (IR): Set up to allow secure data movement and transformation across on-premises and Azure environments.

• Copy Activity & Script Activity: Copy Activity was used to extract data from the on-premises SQL database and move it to the staging area. Script Activity was used for inserting data into the production database.

• Automated Pipelines: Created to schedule daily data synchronization between the on-premises system and the Azure cloud, ensuring seamless data updates without manual intervention.

Phased Migration Approach

• Phase 1: Migrated data from some selected stores to the cloud and thoroughly tested the integrity and performance of the migrated data.

• Phase 2: Expanded the migration to all stores after successful validation of the initial phase, ensuring that the process was fully scalable.

Challenges Faced

• Dynamic Pipeline Configuration: One of the key challenges was building pipelines that could handle dynamic parameters, such as store numbers, start and end dates. This flexibility was necessary to ensure that data for different stores could be migrated at different times without manual reconfiguration.

• Daily Synchronization: Scheduling the daily sync of data using ADF pipelines was another challenge, as the pipelines needed to run at regular intervals without failure and handle large volumes of data without losing consistency.

• Phased Testing: Managing the migration in phases added complexity, especially during the initial phase of testing the migration for only a subset of stores. Ensuring the data accuracy during both the migration and post-migration was crucial.

• Database Schema Compatibility: Ensuring compatibility between the on-premises SQL schema and the Azure-based environment required careful planning and handling of any potential conflicts during data transformation.

• Minimizing Downtime: Reducing the downtime during migration was a critical challenge to ensure the business could continue operations while the data was being transferred and updated in Azure.