Moving an on-premise data warehouse into the cloud

Approach

Delivering a Proof of Concept and documenting validated solutions to develop a well-researched action plan

We kicked off an intense collaboration process with our client's stakeholders to develop a solid cloud migration proposal. Several questions guided this process, including questions such as

• What amount of data is currently processed by the on-premises data warehouse appliance?
• How heavily is the on-premises PDA appliance loaded today?
• What precisely is SSIS used for today, and how are the SSIS packages built?

Step by step, we identified the building blocks of the new cloud architecture. To start with, Azure was selected as the cloud platform. Two considerations prompted that choice: our client was already using Azure for other applications and was extensively using Microsoft SSIS with their on-premises data warehouse appliance. A key element in making this choice was the possibility to easily lift and shift existing SSIS packages to Azure-based virtual machines in the cloud, re-hosting those applications without extensive modifications.

Then, a decision needed to be made about the database services to be deployed in the cloud. While PDA is a performant appliance designed to process vast amounts of data in parallel, we proposed to replace it with a standard Azure SQL database. And yes, we were challenged by our client – throughout the entire proposal building process, and particularly on our proposal to use a standard Azure SQL database rather than the more powerful Azure Synapse Analytics service.

That proposal was not just backed up by an extensive analysis of the amount of data processed by the on-premises PDA appliance. It was also supported by the outcome of various performance tests conducted on a Proof of Concept. To deliver this Proof of Concept, we migrated a small part of the on-premises data warehouse into the Azure cloud environment. Throughout, we worked closely with a dedicated Microsoft Cloud Solution Architect to optimize the cloud architecture and provide the best match for our client’s needs in terms of performance and cost.

Building a solid cloud migration proposal took about three months, including the time needed to deliver and validate the Proof of Concept. While we migrated a small part of the on-premises data warehouse into the Azure cloud environment during this initial phase, we assembled best practices and lessons learned in a migration manual to document the steps that recur with each migration phase.

Part of the work included verifications to ensure that functionalities of the on-premises data warehouse remain available in the Azure cloud environment, and identify workarounds when needed. We captured the outcome of those verifications in a functionality matrix. The combined migration manual and functionality matrix enabled us to develop a well-researched action plan for the entire cloud migration process.

Impact

A more cost-effective and future-proof Azure cloud environment

In hindsight, PDA's End of Support announcement gave our client an excellent incentive to move their on-premises data warehouse into the cloud. The first tangible benefit of such a move was that it became straightforward for our client to integrate with additional Azure cloud services and modernize their workloads. For example, think about Azure Machine Learning to build and train models for predictive analyses. Or consider incorporating Azure Maps to develop solutions with geospatial APIs, opening new horizons for a humanitarian organization like our client.

And then, of course, there’s the scalability that comes with any cloud solution. Resources can be sized to your actual needs, while you only pay for the resources you use. Needless to say that the reduced total cost of ownership proved to be a precious outcome by itself for our client.

The entire cloud migration process, which spans 15 months, was facilitated by a wide range of Microsoft efficiency tools. These included tools to move on-premises SSIS workloads to Azure Data Factory and samples to address various database technologies from the cloud. However, an on-premises data warehouse always comes with several custom components that cannot simply migrate into the cloud utilizing off-the-shelf Microsoft efficiency tools only. That was no different for our client, and it's precisely where our knowledge and experience were crucial to resolving any pitfalls along the road and delivering a smooth cloud migration tailored to our client's needs.