Copy Data to Azure SQL Database from Mainframe Db2 using Microsoft Fabric Data pipelines

introduction

Mainframes are purpose-built systems with specialized hardware and software designed for high throughput, reliability, and scalability. They excel at handling large transaction-intensive workloads. On the other hand, azure The cloud offers flexibility, scalability, and resource sharing, and operates on commodity hardware and virtualized infrastructure. Azure offers the best compute, storage, networking, and security services at reasonable prices.

Migrating from a Db2 z/OS database to Azure SQL Database offers numerous benefits to organizations looking to modernize their data infrastructure and improve operational efficiency. Some of the key drivers for this transition include: Cost savingsMigrating to SQL DB significantly reduces costs related to hardware, software licensing, and maintenance through automated updates and built-in management features.

Azure SQL Database also provides outstanding flexibility and scalability, enabling organizations to quickly adapt to changing business needs. Its cloud-native architecture supports seamless resource expansion, high availability, and disaster recovery capabilities, ensuring business continuity and robust performance even during peak loads. Azure SQL Database also seamlessly integrates with other Azure services to deliver advanced data analytics, artificial intelligence, and machine learning capabilities that are critical to driving innovation and gaining actionable insights from your data.

Fabric’s data pipelines are better integrated with Fabric’s unified data platforms, including Lakehouse, Datawarehouse, Power BI, etc. The differences between Azure Data Factory and Microsoft Fabric’s Data Factory are mentioned in the documentation. link.

Migrate Mainframe Db2 Data to Azure SQL Database Using Fabric Data Pipelines

This blog covers the complete process of migrating data from Db2 to Azure SQL DB using Fabric pipelines. A high-level diagram showing the data flow from Db2 to Azure SQL using Fabric Data Pipelines is shown below.

This tech blog is divided into four sections:

A) Installing an on-premise data gateway
B) Creating a Fabric Data Pipeline
C) Use Data Factory Copy Activity to perform data copy
D) Execute the data copy process

A. Installing the On-Premises Data Gateway

On-Premises Data Gateway (OPDG) is a software that is installed on your on-premises network to access and copy data from your on-premises environment to the Azure cloud. It works like the Azure Data Factory Self Hosted Integration Runtime (SHIR) software. The gateway can be installed in either Personal mode, which is specific to Power BI, or Standard mode. For this data copy scenario, we recommend installing OPDG in Standard mode.

The complete steps to download and install the On-Premises Data Gateway are documented in the location. link. After downloading and installing the gateway software, configure the gateway by providing your Microsoft Entra ID.

Log in and register your newly installed gateway.

Configure the gateway restore key.

After successful registration, the status will be displayed as follows: Ready For Microsoft Fabric.

rain. Creating a Fabric Data Pipeline

log in fabric.microsoft.com. Select Data Factory Get experience at the bottom left of the screen.

Click here Data Pipeline Create a new pipeline.

Providing a new pipeline name.

aspirate. Copy data from Db2 to SQL DB using Copy data activity.

Click here Pipeline activities And choose Copy data activity.

Provide something meaningful name Copy the activity.

~ in source Click the tab connection Go down and then back again more Option to create a new connection to the source Db2 database.

search db2 Select by source IBM Db2 Database The options are listed below.

Provide parameters to connect to the source db2 Database server.

A) Server: Provide the Db2 server DNS name or IP address.
B) Database: Db2 database name.
C) Connection Name: A name that identifies the source connection.
D) Data Gateway: If you want to connect to the source Db2 using a data gateway, provide the data gateway name here.
E) Username: Enter the mainframe user ID that has access to the Db2 database.
F) Password: Password for the mainframe user ID.

Click here Test connection Verify that the provided parameters and network connection to Db2 are working properly. If the connection is successful, you will see the following message: Connection successful.

Click here Preview data To view sample data from a Db2 table.

Click here destination tag and connection options Then Additional information about how to create a new connection to the target Azure SQL Database.

Click here Azure SQL Database.

Provides Azure SQL Database Server Name, Database name and Gateway What connection should I use to connect to SQL?

Click here Connect Provide the following to connect to the target Azure SQL DB: Table name This is where the data should be copied. outline The source Db2 tables and the target Azure SQL DB must be identical. Microsoft SQL Server Migration Assistant for Db2 (SSMA) software can help you convert Db2 schema to SQL schema.

d. Run the pipeline

Click here run Options for running a pipeline to copy data from Db2 to SQL DB.

The data copy pipeline execution has completed as shown below. Successfully.

Click here Activity name For more details about the execution, see below. 410 Records were copied from Db2 to SQL DB.

summation

As adoption of cloud technologies increases, a seamless way to migrate data to the cloud is needed. Microsoft Fabric is an end-to-end analytics and data platform tailored for enterprises looking for integrated solutions. It includes a wide range of services including data movement, processing, collection, transformation, real-time event routing, and reporting. Microsoft Fabric provides a comprehensive set of capabilities such as data engineering, data factory, data science, real-time analytics, data warehouse, and database.

In this blog, we have shown you how easy it is to copy data from Db2 on your 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 power of Microsoft Fabric.

Feedback and Suggestions

If it’s you If you have any feedback or suggestions to improve this data migration asset, please email us. Database Platform Engineering Team.