S
skhandelwal
Introduction
Migrate Mainframe Db2 data to Azure SQL Database using Fabric Data Pipelines
This blog covers the entire process of migrating data from Db2 to Azure SQL DB using Fabric pipelines. High level diagram which shows data flow from Db2 to Azure SQL using Fabric Data Pipelines is shown below:
This technical blog is divided into 4 divisions:
Sign in and register the newly installed gateway.
Configure the gateway restore key.
After successful registration status will be shown as Ready for Microsoft Fabric.
B. Create Fabric Data Pipeline
Login to fabric.microsoft.com. Select Data Factory experience from the bottom left of the screen.
Click on Data pipeline to create new pipeline.
Provide new pipeline name.
Click on Pipeline activity and then select Copy data activity.
Provide meaningful name to Copy activity.
On Source tab click on Connection drop down and then on More option to create new connection to Source Db2 database.
Search for Db2 as Source and select IBM Db2 database option listed below.
Provide parameters for connecting to source Db2 database server.
Click on Test connection to check if provided parameters and network connectivity to Db2 is working fine. For successful connection you will get message: Connection successful.
Click on Preview data to view sample data from Db2 table.
Click on Destination tab and Connection option and then More to create new connection to target Azure SQL Database.
Click on Azure SQL database.
Provide Azure SQL database server name, database name and gateway through which connection to SQL needs to be made.
Click on connect for making connection to target Azure SQL DB. Provide the table name where data should be copied. The Schema of source Db2 table and target Azure SQL DB should be same. Microsoft SQL Server Migration Assistant for Db2 (SSMA) software can help to convert Db2 Schema to SQL Schema.
Click on Run option for executing the pipeline to perform data copy from Db2 to SQL DB.
As shown below data copy pipeline execution completed successfully.
Click on Activity Name to see additional details of execution. As shown below 410 records copied from Db2 to SQL DB.
Summary
The increasing adoption of cloud technology necessitates a seamless way to migrate data to the cloud. Microsoft Fabric is an end-to-end analytics and data platform tailored for enterprises seeking a unified solution. It encompasses a broad range of services including data movement, processing, ingestion, transformation, real-time event routing, and report building. Microsoft Fabric offers a comprehensive suite of capabilities, such as Data Engineering, Data Factory, Data Science, Real-Time Analytics, Data Warehouse, and Databases.
In this blog, we have demonstrated how straightforward it is to copy data from Db2 on Mainframe to Azure SQL Database using Fabric Data Factory. This powerful tool simplifies the migration process, ensuring a smooth and efficient transition to the cloud while leveraging the robust features of Microsoft Fabric.
Feedback and suggestions
If you have feedback or suggestions for improving this data migration asset, please send an email to Database Platform Engineering Team.
Continue reading...
Mainframes are purpose-built systems with specialized hardware and software designed for high-throughput, reliability, and scalability. They excel at handling large, transaction-heavy workloads. On the other hand, Azure Cloud offers flexibility, scalability, resource sharing and operate on commodity hardware and virtualized infrastructure. Azure provides best of Compute, Storage, Networking, Security services at reasonable price.
Migrating from a Db2 z/OS database to Azure SQL Database offers numerous advantages for organizations seeking to modernize their data infrastructure and enhance operational efficiency. One of the primary drivers for this transition is cost reduction. Migration to SQL DB leads to significant reduction in expenses related to hardware, software licenses, and maintenance through automated updates and built-in management features.
Moreover, Azure SQL Database offers superior flexibility and scalability, allowing organizations to quickly adapt to changing business needs. With its cloud-native architecture, it supports seamless scaling of resources, high availability, and disaster recovery capabilities, ensuring business continuity and robust performance even during peak loads. Additionally, Azure SQL Database integrates seamlessly with other Azure services, enabling enhanced data analytics, artificial intelligence, and machine learning capabilities, which are crucial for driving innovation and gaining actionable insights from data.
Data pipeline in Fabric is better integrated with Fabric unified data platform including Lakehouse, Datawarehouse, Power BI and more. The differences between Azure Data Factory and Data Factory in Microsoft Fabric is mentioned in documentation at link.
Migrating from a Db2 z/OS database to Azure SQL Database offers numerous advantages for organizations seeking to modernize their data infrastructure and enhance operational efficiency. One of the primary drivers for this transition is cost reduction. Migration to SQL DB leads to significant reduction in expenses related to hardware, software licenses, and maintenance through automated updates and built-in management features.
Moreover, Azure SQL Database offers superior flexibility and scalability, allowing organizations to quickly adapt to changing business needs. With its cloud-native architecture, it supports seamless scaling of resources, high availability, and disaster recovery capabilities, ensuring business continuity and robust performance even during peak loads. Additionally, Azure SQL Database integrates seamlessly with other Azure services, enabling enhanced data analytics, artificial intelligence, and machine learning capabilities, which are crucial for driving innovation and gaining actionable insights from data.
Data pipeline in Fabric is better integrated with Fabric unified data platform including Lakehouse, Datawarehouse, Power BI and more. The differences between Azure Data Factory and Data Factory in Microsoft Fabric is mentioned in documentation at link.
Migrate Mainframe Db2 data to Azure SQL Database using Fabric Data Pipelines
This blog covers the entire process of migrating data from Db2 to Azure SQL DB using Fabric pipelines. High level diagram which shows data flow from Db2 to Azure SQL using Fabric Data Pipelines is shown below:
This technical blog is divided into 4 divisions:
A) Installation of on-premises data gateway
B) Creation of Fabric Data Pipeline
C) Use of Data Factory Copy Activity to perform data copy and
D) Execution of data copy process
B) Creation of Fabric Data Pipeline
C) Use of Data Factory Copy Activity to perform data copy and
D) Execution of data copy process
A. Install an on-premises data gateway
An on-premises data gateway (OPDG) is software that you install in an on-premises network which facilitates access to data in on-premises environment for copying to Azure cloud. This software works like Azure Data Factory Self Hosted Integration Runtime (SHIR) software. You can install a gateway either in personal mode, which applies to Power BI only, or in standard mode. For this data copy scenario, we recommend installing the OPDG in standard mode.
Complete steps to download and install on-premises data gateway is documented at location link. After gateway software is downloaded and installed, configure gateway by providing your Microsoft Entra ID.
Complete steps to download and install on-premises data gateway is documented at location link. After gateway software is downloaded and installed, configure gateway by providing your Microsoft Entra ID.
Sign in and register the newly installed gateway.
Configure the gateway restore key.
After successful registration status will be shown as Ready for Microsoft Fabric.
B. Create Fabric Data Pipeline
Login to fabric.microsoft.com. Select Data Factory experience from the bottom left of the screen.
Click on Data pipeline to create new pipeline.
Provide new pipeline name.
C. Use Copy data activity to copy data from Db2 to SQL DB
Click on Pipeline activity and then select Copy data activity.
Provide meaningful name to Copy activity.
On Source tab click on Connection drop down and then on More option to create new connection to Source Db2 database.
Search for Db2 as Source and select IBM Db2 database option listed below.
Provide parameters for connecting to source Db2 database server.
A) Server: Provide Db2 server DNS name or IP address.
B) Database: Db2 database name.
C) Connection name: Any name to identify source connection.
D) Data gateway: In case you want to use Data gateway to connect to source Db2 provide data gateway name here.
E) Username: Provide mainframe user id which has access to Db2 database.
F) Password: Password for mainframe user id.
B) Database: Db2 database name.
C) Connection name: Any name to identify source connection.
D) Data gateway: In case you want to use Data gateway to connect to source Db2 provide data gateway name here.
E) Username: Provide mainframe user id which has access to Db2 database.
F) Password: Password for mainframe user id.
Click on Test connection to check if provided parameters and network connectivity to Db2 is working fine. For successful connection you will get message: Connection successful.
Click on Preview data to view sample data from Db2 table.
Click on Destination tab and Connection option and then More to create new connection to target Azure SQL Database.
Click on Azure SQL database.
Provide Azure SQL database server name, database name and gateway through which connection to SQL needs to be made.
Click on connect for making connection to target Azure SQL DB. Provide the table name where data should be copied. The Schema of source Db2 table and target Azure SQL DB should be same. Microsoft SQL Server Migration Assistant for Db2 (SSMA) software can help to convert Db2 Schema to SQL Schema.
D. Run the pipeline
Click on Run option for executing the pipeline to perform data copy from Db2 to SQL DB.
As shown below data copy pipeline execution completed successfully.
Click on Activity Name to see additional details of execution. As shown below 410 records copied from Db2 to SQL DB.
Summary
The increasing adoption of cloud technology necessitates a seamless way to migrate data to the cloud. Microsoft Fabric is an end-to-end analytics and data platform tailored for enterprises seeking a unified solution. It encompasses a broad range of services including data movement, processing, ingestion, transformation, real-time event routing, and report building. Microsoft Fabric offers a comprehensive suite of capabilities, such as Data Engineering, Data Factory, Data Science, Real-Time Analytics, Data Warehouse, and Databases.
In this blog, we have demonstrated how straightforward it is to copy data from Db2 on Mainframe to Azure SQL Database using Fabric Data Factory. This powerful tool simplifies the migration process, ensuring a smooth and efficient transition to the cloud while leveraging the robust features of Microsoft Fabric.
Feedback and suggestions
If you have feedback or suggestions for improving this data migration asset, please send an email to Database Platform Engineering Team.
Continue reading...