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.

Challenges in migrating to Cloud (Microsoft Azure Data Warehouse):
  • Azure Cloud Platform doesn’t support some table functions, as compared to SQL Server on-premise DW.
  • Azure Cloud Platform doesn’t support some features of stored procedures on SQL Server on–premise DW.
  • The nature of tables is different on Azure DW, as compared to SQL Server on-premise DW.
  • Azure Cloud Platform doesn’t support Synonyms. They need to be converted to physical tables for Azure DW.
The Objective

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 Solution

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 Outcome

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