Although migrating to Azure data warehouse cloud is always a better option, Cloud platforms have a new set of limitations and challenges, compared to the existing SQL Server on-premise data warehouse of the company.
Migrate about 800 GB of Data from the on-premise SQL Server data warehouse to Azure Cloud Platform within a span of 12 weeks by working around the limitations and challenges, to maximize the benefits of having a cloud-based data warehouse.
The Chief Architect at Data Semantics identified that, the existing on-premise data had to be re-engineered for migrating to Cloud (MS Azure Cloud Platform), considering the way the data is handled on legacy systems.
The legacy on-premise data warehouse functioned in a rather, traditional ecosystem where the lack of APIs was a major challenge. Additionally, it was difficult to spell out rules, sequential activities, and required outcomes for every data migration step due to the existing legacy systems not matching up to Cloud ecosystem and capabilities.
The Data Semantics team segregated and handled each element of the database manually, in order to facilitate error-free functioning of the data. These DB elements had their own migration challenges, which were worked on individually by the team members.
Number of Tables: 390 Tables
No of Views: 24
No of Functions: 19
Number of Stored Procedures: 115
After a 10-week activity, Data Semantics re-engineered 800 GB of data to be migrated on Cloud.
Architecture: On-Premise to Cloud Migration
The migration process of 800GB of data was completed within 12 weeks, as planned. Today, this global IT service provider accesses all their data from Cloud, to generate Business Intelligence (BI) reports in real-time.
Moving to an MPP System enabled fast querying, better data management and governance, thus increasing the velocity of generating Business Intelligence (BI) reports, especially in real-time.
Enterprise Data Warehouse Size: 800 GB Migrated
Packages Migrated: 165 SSIS Packages
Team involved: MSBI Developers, Architects, DBA Engineers
Technology Stack: SQL Server, Azure Cloud Platform, Azure Data Factory, SSIS PolyBase, TSQL, Robotic Process Automation