Data Migration Speed Through ADF

Question

Hi,

I am migration the data from oracle (on-prem) to Azure SQL database through the self hosted integration runtime.

Pre-Prod Env.

130 GB Data ,Migrated in 10 Hr

Through Speed of Migration-1.00-8 mpbs

1.Virtual Machine SHIR Bandwidth Speed- 10 gbps

(But there are firewall and F5 server which is making internet bandwidth overall slow in SHIR)

2. Reading from source takes 90% time in most of the table.

Some of tables migration details

Prod Env.

1.5 TB Data to be migrated--Which might take 115 Hr as per pre-prod data migration.

We cannot have 115 hr downtime in prod application.

Questions-1 - How I can increase the throughput speed?

Do I need good internet bandwidth in all three of them below

Oracle, Self hosted integration runtime (Virtual machine) and Azure Sql database ?

Question2- How I can improve the data migration speed significantly ?

Oracle data partioning- Not available.

Azure SQL Database- Writ batch size -10,000 unit set.

Please help with the complete details , how can the data migration speed improved significantly ?

How to identify the bottlenecks and improve the throughput speed significantly ?

Answer 1

Hello Kashish,

Thank you for posting query in Microsoft Q&A Platform.

Based on the information you provided, it seems like the reading from the source takes up most of the time during the data migration process. This could be due to several factors, such as network bandwidth, data store input/output operations per second (IOPS) and bandwidth, and the configuration of your Integration Runtime.

Here are some steps you can take to improve the data migration speed:

Increase the bandwidth speed of your Self-hosted Integration Runtime (SHIR) virtual machine. You mentioned that the SHIR bandwidth speed is 10 Gbps, but there are firewalls and F5 servers that are making the internet bandwidth overall slow in SHIR. You may need to optimize your network configuration to ensure that the SHIR has a stable and fast connection to both the source Oracle database and the target Azure SQL Database.

Increase the batch size of your Azure SQL Database. You mentioned that you have set the write batch size to 10,000 units, which is a good start. You can try increasing the batch size to see if it improves the data migration speed. However, keep in mind that increasing the batch size too much may cause performance issues on the target Azure SQL Database.

Use partitioning to split the data into smaller chunks. You mentioned that partitioning is not available on the source Oracle database, but you can still use partitioning on the target Azure SQL Database. By splitting the data into smaller chunks, you can run multiple data migration tasks in parallel, which can significantly improve the data migration speed.

Optimize your data migration pipeline. You can review your data migration pipeline to ensure that it is configured correctly and that it is using the most efficient data transfer methods. For example, you can use the binary copy option when copying data from Oracle to Azure SQL Database.

1. Monitor and optimize your data store IOPS and bandwidth. You can use monitoring tools to track the IOPS and bandwidth usage of your data stores and optimize them if necessary. For example, you can increase the IOPS of your Azure SQL Database to improve the write performance.

Hope this helps. Please let me know how it goes. Thank you.