On-Premise to Azure Database Sync Mechanism for an Insurance Company in USA

About Company

A USA based agency which implements Central Repository of Integrated data for one or more disparate sources for state government administrative agency. They are the trusted insurance provider among US. They help design and implement insurance program which can help there customers to choose among various insurance programs.

Project Summary

Client had multiple production databases on-premise which they require to move to Azure Datawarehouse, in that way they can build there daily reports on the live data using the Alteryx designer tool, since they had vast number of transactions happening on the on-premise they needed a source that can handle these transactions on daily basis, and can be performant in order to build various production related reports.

Challenge

The challenge was to schedule a daily movement of data from on-premise to Azure SQL Datawarehouse so that all transactions remains at the destination and completely sync. They have 5 databases of size of at least 400gb each. They also need an automation process to start and pause Azure SQL Datawarehouse. Analysis of 5 database with 300 tables each and provide them migration plan was set to be completed in 10 working days.

Solution

We assigned a group of Azure Experts and did deep analysis, understand the environment and requirements. We used Azure Data Factory v1 to connect to on premise database, we used another windows server at the same network to install data gateway in order to solve connection issues. Building incremental load pipelines to move data daily according to dates. Building json scripts to move the data daily as an incremental copy.

Automated Azure Datawarehouse using automation account to start/stop at certain. Our team completed the challenge in 5 working days, which consisted out designing a flow using .JSON scripts and schedule the pipelines that needed to be run daily.