We need to migrate the 1.5 TB data (prod env) from Oracle on premises, what's the best target database to migrate?

Question

Hi,

I want to migrate the 1.5 TB data from the oracle on premises to the below target database.

We want low latency, high performance.

1. On premises sql server.
2. Azure sql database.
3. Managed sql database.

Tool for Migration- ADF

Does ADF support migration from on prem oracle to on prem sql server?

Please share with data points , which database in above list will be the best.

Note- Please, I would be using only using tool ADF for the data migration.

In Lower env with 25 GB data we tested oracle on prem to azure sql database migration with ADF tool, but there was latency observed in application after connecting to the migrated target azure sql database.

Please suggest us the strategic solution ,comparing all the 3 database and what would be the best solution.?

Answer 1

My suggestion for a database that size is to move to a SQL Server VM on Azure (Iaas) with SSD disks P30 and you will get good performance. My suggestion is not only limited to the migration project but also life after migration of large databases (backup/restore times, P30 Disks perform better than most PaaS options) although SQL Server licenses are expensive.

ADF does support from on-premises Oracle to On-premises SQL Server but in this scenario the SSMA tool should show better performance.

If you would like to go to PaaS to avoid SQL Server licensing, my suggestion is to scale up to premium tiers (the maximum tier you can use) during the migration only, to speed up things as premium tiers use local attached SSD storage.

Answer 2

What is the best choice for you is impossible to say, it is very much "it depends".

You say:

we tested oracle on prem to azure sql database migration with ADF tool, but there was latency observed in application after connecting to the migrated target azure sql database.

This could be due to that you have a so-called chatty application that sends a lot of requests to the database engine for every key press. Such applications will never work well with a cloud database. Typically, you will have a distance between application and the Azure data centre, and the speed of light will set the limit of how fast the turnaround time can be. There are probably a lot overhead in the cloud service as well.

Then again, the slowness could also be due to poor indexing or poorly written queries. Keep in mind that what may be good practice on Oracle is necessarily not best practice on SQL Server - and vice versa.

But if your main focus is performance, and cost is of second nature, then on-prem is likely to be the best solution. In any case, I think you should make your initial test of performance etc on-prem, so that you can track down slow queries etc.

Answer 3

Hello

this is a migration from Oracle to SQL Server or Azure SQL. For the latency part , there are multiple factors involved so you have to test

I will suggest you to see Azure Data Studio and the Database Migration Assessment for Oracle extension (Preview) which could be a better option than ADF

this is what I have been using recently to perform migrations (it is the replacement of SQL Server Migration Assistanct)