We help IT Professionals succeed at work.

Oracle and MS SQL to Azure.

High Priority
114 Views
Last Modified: 2020-03-30
hi,

any offical, practical guide to migrate Oracle and MS SQL to Azure MS SQL ?

Azure do not support Oracle DB yet, right?
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:

>> Azure do not support Oracle DB yet, right?


Incorrect.  They have been Supported for quite a while.


They made an announcement back in 2013 that Oracle will be supported on Hyper-V which paved the way to Support on Azure.


I can't even find the "offical" annoucment link anymore but found this one:

https://www.zdnet.com/article/oracle-databases-head-to-microsofts-hyper-v-azure/


marrowyungSenior Technical architecture (Data)

Author

Commented:
"Incorrect.  They have been Supported for quite a while.
"
good!

actually any way/method/tools to migrate Oracle directly to Azure?

MS SQL must have one ?
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:

Never done it but assume it would be just like migrating a database to any other server.


There are several ways.  Cannot say which one is "best" for you.


I would start here:

https://docs.cloud.oracle.com/en-us/iaas/Content/Database/Tasks/migrating.htm

Application Development Analyst
CERTIFIED EXPERT
Commented:
Hi,

You can bifurcate the entire migration into different steps attached:

Oracle-to-Azure-DB-Migration.PNG
You can try different tools available:

Microsoft provides multiple tools to achieve a successful Oracle database migration to Azure SQL Database or any other on-premise SQL Server platform. Some of the important tools are:

Microsoft Assessment and Planning (MAP) Toolkit is a tool to provide inventory, assessment, and reporting to simplify the Oracle database migration planning process. MAP provides detailed readiness assessment reports and recommendations with hardware and software information.
Microsoft SQL Server Migration Assistant (SSMA) for Oracleis a Microsoft provided tool to automate migration from Oracle database(s) to Azure SQL Database or on-premise SQL Server.
    SSMA for Oracle Client: A tool which connects to a source database (Oracle) and target Azure SQL Server to convert Oracle database objects to SQL Server syntax. Once database objects are ready, it loads the objects in Azure SQL Database and migrates data to Azure SQL Database or on-premise SQL Server.
      SSMA for Oracle Extension Pack: The SSMA extension pack includes functionalities to emulate Oracle features not natively supported in Azure SQL Database, an application to facilitate direct server-to-server data migration and a test database to support SSMA testing features.
      Azure Database Migration Service is a fully managed service designed to enable seamless migrations from multiple database sources to Azure Data platforms.
      marrowyungSenior Technical architecture (Data)

      Author

      Commented:
      Pratik Somaiya,

      you done this kind of project before? from oracle to MS SQL on Azure ?
      marrowyungSenior Technical architecture (Data)

      Author

      Commented:
      can oracle logic, PL/SQL, trigger, function and trigger all migrate to the cloud using tools you mentioned above?
      Pratik SomaiyaApplication Development Analyst
      CERTIFIED EXPERT

      Commented:
      Hi,

      I have worked on migrating On-Premises SQL Server to Azure SQL.

      Can you check the tool guide on the specific components you need to migrate?

      I can definitely help you if you need help in deciding the migration approach and any environment difficulties that you might face during migration.

      Thanks,
      Pratik Somaiya
      CERTIFIED EXPERT
      Most Valuable Expert 2012
      Distinguished Expert 2019

      Commented:

      >> /SQL, trigger, function and trigger all migrate to the cloud using tools you mentioned above?


      I've not seen a tool that ports code across databases.  Even if a tool exists, I wouldn't suggest one.  You write code in the database that should be efficient for the database.  Many times what works well in one product doesn't work well in another.

      marrowyungSenior Technical architecture (Data)

      Author

      Commented:
      Pratik Somaiya,

      "I have worked on migrating On-Premises SQL Server to Azure SQL"

      tks.

      from time to time I get request to migrate from oracle to Azure SQL too.

      slightwv,

      "I've not seen a tool that ports code across databases.  Even if a tool exists, I wouldn't suggest one. "

      I seen one and tried that before from Oracle to MariaDB, but it has bugs! however they good to keep improving!

      SP doesn't work usually because of the data type, if target data type doesn't exist, the SP with input of that type is not going to work ! this make sense.

      by this sense, if Oracle need to migrate to SQL Azure directly, seems need a temporary SQL server to migrate first ,then Azure SQL .

      "Many times what works well in one product doesn't work well in another."

      yeah, actually for this kind of database migration project the application tier needs to be rewrite.
      marrowyungSenior Technical architecture (Data)

      Author

      Commented:
      from my stud\ies, it seems oracle to Azure SQL DB we need to do it from Azure portal , which is different from SQL server to Azure, which use dacpack with the SSMS.
      Pratik SomaiyaApplication Development Analyst
      CERTIFIED EXPERT

      Commented:
      Before migrating from Oracle to Azure SQL, we will first have to check the database incompatibilities between Oracle and Azure as they two are hosted on different platforms and they have different underlying architectures.

      After we get those incompatibilities, we can then work towards resolving them before completely migrating the DBs.

      Any thoughts on this?

      Thanks,
      Pratik
      marrowyungSenior Technical architecture (Data)

      Author

      Commented:
      "Before migrating from Oracle to Azure SQL, we will first have to check the database incompatibilities between Oracle and Azure"

      any inompatibilities list between them ? just the same as oracle and MS SQL ?

      I don't think we need to setup a separate SQL server to migrate everything from oracle and make sure it work before migrating to Azure?
      Pratik SomaiyaApplication Development Analyst
      CERTIFIED EXPERT
      Commented:
      This link might help you define your migration and what all you need to consider for the same:

      https://datamigration.microsoft.com/scenario/oracle-to-sqldw?step=1

      Thanks,
      Pratik
      marrowyungSenior Technical architecture (Data)

      Author

      Commented:
      tks all.
      Pratik SomaiyaApplication Development Analyst
      CERTIFIED EXPERT

      Commented:
      Thanks
      marrowyungSenior Technical architecture (Data)

      Author

      Commented:
      Pratik Somaiya,

      "I have worked on migrating On-Premises SQL Server to Azure SQL"

      Assume we considre to migrate MS SQL to SQL Azure now but not oracle , what tools you use for this ? just SSMA from MS ?

      can it migrate all other SQL jobs, SP, function,UDF to SQL Azure ?

      SSMA execute from your side but Azure Database Migration Service execute on  Azure to get object and migrate MS SQL/Oracel to Azure ?

      I think migration from MS SQL to SQL Azure and oracle to SQL AZure is totally different !
      marrowyungSenior Technical architecture (Data)

      Author

      Commented:
      "SSMA for Oracle Extension Pack: The SSMA extension pack includes functionalities to emulate Oracle features not natively supported in Azure SQL Database, an application to facilitate direct server-to-server data migration and a test database to support SSMA testing features."

      include oracle SP, PL/SQL, sequence , UDT, function, trigger, etc. ?

      "Azure Database Migration Service is a fully managed service designed to enable seamless migrations from multiple database sources to Azure Data platforms."

      this one can do the same thing for MS SQL on premise and migrate all object , sp, function to SQL Azure?

      "Before migrating from Oracle to Azure SQL, we will first have to check the database incompatibilities between Oracle and Azure as they two are hosted on different platforms and they have different underlying architectures."

      by using Microsoft Assessment and Planning (MAP) Toolkit  ?
      Pratik SomaiyaApplication Development Analyst
      CERTIFIED EXPERT
      Commented:
      Hi,

      I have used DMA (Database Migration Assistant) to find out incompatibilities while migrating from on-premises to Azure SQL.
      Later on I worked on resolving those incompatibilities and manually created each and every component on Azure SQL.
      The tool can show you what Azure and on-prem differences are.

      Thanks
      marrowyungSenior Technical architecture (Data)

      Author

      Commented:
      hi,

      I have used DMA (Database Migration Assistant) to find out incompatibilities while migrating from on-premises to Azure SQL.

      so you mean you only use one tools to do the whole project ?

      I read this : https://www.sentryone.com/white-paper/cloud-migration-sql-server-to-azure

      seems we need more than DMA? do we really need sentyone to help on doing migration ?

      actually from your experience, by using DMA, what you need to fix before you can move to SQL Azure? I expect they both SQL server and why there are difference ? data type different ?
      Pratik SomaiyaApplication Development Analyst
      CERTIFIED EXPERT

      Commented:
      Though DMA can directly migrate the DB, my case was different, as there were lot of incompatibilities between the two environments (IaaS and PaaS), I had to manually create each component in Azure.
      Moreover, there were mistakes that I found in existing architecture, so I wasn't able to directly migrate.
      I fixed those manually and created on Azure.

      Thanks
      marrowyungSenior Technical architecture (Data)

      Author

      Commented:
      and do you use Azure SQL Database DTU Calculator ?

      my case was different, as there were lot of incompatibilities between the two environments (IaaS and PaaS),

      I thought SQL server and SQL Azure is the same, why incompatilities ?
      amazing !

      so from Oracle to SQL Azure, it should be even worse.

      there were mistakes that I found in existing architecture,

      what is the mistakes? you can simply tell me some of them. tks.
      Pratik SomaiyaApplication Development Analyst
      CERTIFIED EXPERT

      Commented:
      I haven't tried that calculator yet, but I do monitor the DTU consumption and scale it according to use.

      SQL Server and Azure both are different environments, and the differences is the irony, I am not sure how Oracle differs with Azure, but there will be differences for sure as both are different products.

      There were some logging and data loading logic errors that need to be corrected, we were using SSIS in SQL Server but we are now using ADF in Azure.

      You should start with your project, you can then get to learn the differences.
      marrowyungSenior Technical architecture (Data)

      Author

      Commented:
      we were using SSIS in SQL Server but we are now using ADF in Azure.

      good ! then how to migrate SSIS to ADF in your case?

      SQL Server and Azure both are different environments, and the differences is the irony, I am not sure how Oracle differs with Azure, but there will be differences for sure as both are different products.

      so you only use DMA for the whole project  ? and again no sP,coding DB logic can convert to SQL Azure from SQL server? I am wondering that.
      Pratik SomaiyaApplication Development Analyst
      CERTIFIED EXPERT
      Commented:
      We created pipelines in ADF, we didn't migrated the SSIS as-is.

      DMA can help to migrate, but we didn't used it due to issues in our case.
      marrowyungSenior Technical architecture (Data)

      Author

      Commented:
      We created pipelines in ADF, we didn't migrated the SSIS as-is.

      very less SSIS job ? I am wondering it will take you long time on rebuilding everything ?

      DMA can help to migrate, but we didn't used it due to issues in our case.

      can DMA migrate all MS SQL server logic like SP, function ?

      we are now using ADF in Azure.

      any good training material for ADF ? e.g. how to setup and run a project ?

      There were some logging and data loading logic errors that need to be corrected
      actually I do not understand this .
      Pratik SomaiyaApplication Development Analyst
      CERTIFIED EXPERT

      Commented:
      1. We didn't wanted to continue with SSIS as it's getting outdated, so we chose ADF
      2. Yes, DMA can
      3.  https://www.youtube.com/watch?v=i133n5y5DGohttps://intellipaat.com/blog/azure-data-factory-tutorial/
      4. I would say there were logical issues which prevented us from using the same SPs and functions, so we modified it
      marrowyungSenior Technical architecture (Data)

      Author

      Commented:
      Yes, DMA can

      so should you say only DMA can do the whole migration ?

      . We didn't wanted to continue with SSIS as it's getting outdated, so we chose ADF

      what good is ADF when compare with SSIS ?
      Pratik SomaiyaApplication Development Analyst
      CERTIFIED EXPERT

      Commented:
      1. Yes you can if you wish to
      2. Can you visit this link to know more details:  https://www.jamesserra.com/archive/2017/03/azure-data-factory-and-ssis-compared/

      Thanks
      marrowyungSenior Technical architecture (Data)

      Author

      Commented:
      . Yes you can if you wish to

      other than test it out by simply do a migration, what a DMA can't migrate?
      Pratik SomaiyaApplication Development Analyst
      CERTIFIED EXPERT
      Commented:
      I think you can go ahead and explore it :)
      marrowyungSenior Technical architecture (Data)

      Author

      Commented:
      good !

      from your point of view, I should ONLY go for DMA ? not even DTU ?
      Pratik SomaiyaApplication Development Analyst
      CERTIFIED EXPERT
      Commented:
      You should try DMA first I propose
      marrowyungSenior Technical architecture (Data)

      Author

      Commented:
      you mean I might need other tools to complete the migration exercise ?
      Pratik SomaiyaApplication Development Analyst
      CERTIFIED EXPERT
      Commented:
      that depends on your project, go for DMA now
      marrowyungSenior Technical architecture (Data)

      Author

      Commented:
      ok. if I have other question when I do the real one I will come back with log.
      Pratik SomaiyaApplication Development Analyst
      CERTIFIED EXPERT

      Commented:
      sure, you are welcome
      marrowyungSenior Technical architecture (Data)

      Author

      Commented:
      I am reading this:

      https://www.sentryone.com/white-paper/cloud-migration-sql-server-to-azure

      seems DMA and BACPAC can ONLY migraiton data and schema but not SQL code, but you seems telling me it is not ... I am confused.

      and how about DMS ? any idea? seems replication is one of the good tools to use if we can only use managed instance but not PaaS.
      marrowyungSenior Technical architecture (Data)

      Author

      Commented:
      and the link also say Azure Site Recovery, do you use it ? recommend that  or not ? and why  ?

      so DMA, DMS and Azure Site Recovery all free ?

      have you tried this : https://azure.microsoft.com/zh-tw/blog/introducing-microsoft-migration-accelerator/  ? is this tools stil available ? the link very old.