Migrating Data Warehouse from On-Premise to Cloud (Best Practices)


Today most of the companies don’t have a need for On-Premise systems, because Cloud based services can give the same service usually cheaper and with lower maintenance costs. Cloud services are also much more scalable than On-Premise systems and Cloud has build-in services for Analytics and AI applications, which you can implement whenever your business needs it.

If you are thinking to migrate your Data Warehouse(s) from On-Premise systems to a Cloud, this post will try to help you with a strategy that will give you some freedom to operate. If your current Data Warehouse is at least partly working well for you purposes then it is worth saving, no need to start from a scratch. It is relatively easy to move physical objects (tables, views, schemas, etc.) between databases, even when moving them to a cloud based database. What is usually very difficult, is to move business logic from one system to another – especially when in most cases the documentation and data modelling has not been done properly. Business logic in Data Warehouse can reside in database views, in database stored procedures, in ETL tools SQL, in ETL tools transformations or then all of the above at the same time. There can also be business logic in the reporting tools, but that doesn’t apply to this solution. This solution will give you time to do the enhancements and business logic migration while your system will be fully operational all the time.

Phase 1

First you have to make an exact copy of the On-Premise Data Warehouse to your Cloud database. You should copy all the schemas (if possible), database tables and views. If there is some business logic in the user access then copy that too. At this point it is also a good point to start documenting the database and doing the data modelling (if it hasn’t been done beforehand). And you should make some qualification for the entities also, useless and outdated objects should be cut out at this point.

Picture ‘Phase 1’ shows you the overall picture what should be done.

Cloud Migration, Phase 1

When all the entities/objects needed have been copied to a Cloud Data Warehouse, the next thing to do is a full replication from the On-Premise tables to Cloud tables. Replication is after the initial load a fast job, because there is going to be just a merging loads (only updated, deleted and new rows will be replicated). Or if the amount of rows is small enough this replication can be with truncate/insert job every time. Replication can follow the cycle you are loading you On-Premise Data Warehouse, it can be once an hour or then once a night, depending on your need.

This approach gives you time to do your analytics and reporting migration without service breaks. Your old system is fully operational all the time you are doing your migration. And at the same time you are able to do enhancements on you system, even on the reporting and analytics side.

Phase 2

On phase 2 of the migration you have to start to move the integration jobs one-by-one to the Cloud. You should cut the cords to the On-Premise system only when the Cloud part of the integration is fully operational and working.

At the same time you can cut the cords from On-Premise Analytics and Reports, when the Cloud part is working.

Picture ‘Phase 2’ shows you an overall picture on how to start integration migration.

Cloud Migration, Phase 2

Phase 3

On phase 3 of the migration all the integration migrations have been done. Also all the Analytics and Reports have been migrated to use the Cloud-based Data Warehouse. You might have been making enhancements on the way of the migration, but this is the point when you can really start thinking about the possibilities that the Cloud system gives you.

Picture ‘Phase 3’ shows you how the migration is done, and you can shutdown the expensive On-Premise systems.

Cloud Migration, Phase 3

Finally you can stop paying for the On-Premise administration costs and start thinking about the possibilities Cloud gives you regarding Internet of Things, Big Data, Machine Learning and Artificial Intelligence – in addition to the data you already have in your Cloud Data Warehouse.