Blog: Expand capacity with an IBM DB2 SQL Azure SQL Database migration

One of the world’s largest distributors of non-food consumable products wanted to rationalize their data platform and business intelligence strategy. Their current IBM DB2 database system lacked repository to host historical audit related data, so they could hardly retrieve data from past years. Then we came up with an Azure cloud database solution that could store all the historical data and created Power BI reports on top of it. We performed the IBM DB2 Azure SQL Database migration as a part of the proof of concept, showing the benefits of the cloud solution to the customer.

As a result, we helped our customer craft a new cloud database system that meets their requirements and the latest industry standards. Learn more about our technical approach from the following video.

Customer’s original system

Our customer utilized an Enterprise Resource Planning system, which was running on an IBM DB2 database. It was installed on rather outdated IBM iSeries on-premises hardware. This original system could keep the data for the last couple of years. Such system may be good for transactional processing, but its capabilities were not enough for audit purposes.

So, the main customer’s problem was related to the lack of space to host historical data. That’s why they wanted to build completely new database system. They were considering a move to the cloud to create a reliable solution for a long period of time.

Possible solutions

Database application upgrade should be the first thing that comes to mind when you think about the possible solutions for this case. However, even after a basic system overview, it goes without saying that you can never build a modern database system on the legacy iSeries hardware. So, not just the database application, but the physical servers also require a major upgrade.

Wait, why do you need to upgrade servers when you can simply get rid of them, moving the entire system to the cloud? Actually, since the upgrade is inevitable, you can consider switching to another platform. Together with the customer, we decided to build the new system in the Azure cloud.

Move to the cloud

As a part of a proof of concept, the customer provided us with access to the limited set of data. We leveraged SQL Server Migration Assistant (SSMA) for DB2 to migrate 4 database schemas to the Azure SQL database. They fully reflected the initial set of data. On the next step, we needed to extract the data from the source database and move it into the Azure cloud. At this point, we discovered a number of issues related to the data extraction from the source database. So, we had to overcome these issues before setting up the Power Pivot Extract to move the data to the target database. Basically, we performed the ETL process via flat CSV files.

Then we migrated the data to the Azure SQL Database and created 3 users with various security roles. Now everything was set for the live demonstration of the proof of concept.

Improving IBM DB2 Azure SQL Database migration

The customer can use this scalable cloud solution on a regular basis for transactional processing. However, we can build an ad-hoc solution to keep the historical data using Microsoft Azure SQL Data Warehouse. Below you can find the key differences between Azure SQL Database and Azure SQL Data Warehouse.

Apparently, Azure SQL Data Warehouse looks by far a better solution for keeping historical data of unlimited size. At the same time, Azure SQL Database looks like an ideal platform for fast and reliable cloud transactional databases. However, we remain platform agnostic, and the choice of the platform and the architecture for the future system lies on customer’s shoulders.

Mastering the customer’s system to perfection, we should consider creating automated ETL processes. We suggested leveraging Data Management Gateway after solving the initial issues with data extraction. Moreover, we can use the Data Warehouse Migration Utility to simplify the migration of already created database schemas from Azure SQL Database to Azure SQL Data Warehouse.

Putting the icing on the cake, we delivered a Power BI reporting service. It allows for extraction of audit-related historical data from the cloud database at any point of time.

In the end, we delivered a cloud proof of concept to our customer. The newly created system keeps all the required data, and with the help of the modern online reporting services, the specific historical audit-related data became easily accessible.