SSMA SQL Server Migration Assistant for Oracle

Question

I have no experience with SSMA other that what I have gained over the last couple of days, and I am hoping that someone might be able to answer some questions I have from their own experiences with the tool.

This is an Oracle (not sure of the Oracle version) to Azure SQL Database migration, the Oracle database was restored from a backup file from a third party, so I was not involved in that. I have not been involved in the availability of Oracle nor the creation of the Azure SQL Database. I am using a Windows 10 VM, which has had all the required tools I need to do the migration. I have already connected to the Oracle database, and the Azure SQL Database in the SSMA, so no issues there. Here is the problem/query.

The oracle database has just under 19K tables. I have started running the Create Report to analyse the schema, which I now see is/was an optional step.

It has been running for over 24 hours so far, analysing the metadata of the Oracle database. I'm not sure how long I can sit back and let this keep going if it is an optional step. What I would like to know is, if and when this report finishes, will I be sitting around for the same amount of time for the schemas to convert/get created on to the SQL Database?

If yes, should I just cancel the report and get on with converting the schemas, or if No, with the report run, will the schemas be converted very quickly, and I will not have to wait at all as its already gone through it all when it created the report?

I do not have the time allocated to sit through the time it takes to create the report, and then again if the conversion is going to take the same time as the report took to be created, or is it done when the report has finished, and the creation of the schema is quick?

I hope my question is clear enough, any follow up questions, then please ask and I'll do my best to answer. I guess it is more around how the SSMA works more than anything else.

In the meantime, I'll letting the create report continue.

Thank you for reading.

Fletch.

I was expecting it to be much quicker. I am hoping once the report is run, I can recreate the schema on the Azure SQL Database quickly. Or despite the report, will it take the same amount of time to create the schema on the SQL Database as it did to run the report?

Answer 1

Hello Fletch and welcome to this community.

I've been using SSMA with Oracle, DB2 and MySQL and being SSMA a quite exotic tool you can consider myself a sort of expert.

• How much is going to take? No one is going to tell you how much this is going to take because no one knows.
• About the Create Report: If the Create Report is optional step try not to use it.
• will I be sitting around for the same amount of time for the schemas to convert/get created on to the SQL Database? Probably YES. But because you have >19K tables it can also freeze and you can never tell if it's working or not. How much RAM your computer has? Try to put it on a more powerful machine

This is what you are going to do, I'm going to give you a script and is not a script made of code but made of English words. Go to your manager and say:

"...after a long investigation I'm here to tell you that I have to press the red button alert: we cannot tell how much the conversion can take or even if is ever gonna be successful. It may, it may not.

The Oracle database has >19k tables which means is probably 2-3 decades old. Probalby all sort of developers, from Senior to Junior, have worked on it and we cannot tell how many version have been upgraded to. We cannot tell the current version. We cannot tell if it contains parts that can or cannot be converted to SQL Server or Azure SQL Database.

This conversion is gonna take more time than expected and in order to complete it I'm going to move this way..."

This are the next action you ae going to take:

1. Install Oracle Server on your machine and import the database. There investigate on what version is that. You need this information
2. Ask to your manager a more performant machine, you need a lot of CPU and RAM for the conversion. Otherwise this is going to fail. (how big is the database BTW?)
3. Try to convert first to SQL Server: Azure SQL Database it way to modern. The first miracle would be to convert that dinosaur to SQL Server 2019. Then the second miracle would be to convert it to Azure SQL Database
4. Import the Oracle database directly into Azure or Oracle Cloud and do the conversion there. That would be furiously expensive.

Expect all sort of stored procedure to fail. The conversion of the code from Oracle to SQL Server might not translate all commands. And the same will go from SQL Server to Azure SQL Database.

**

UPDATE after your comment:

**

You need to know the version of the database. Run SELECT * FROM v$version;

Once you know the version (which will be probably low) try to create a report to a lower version, something like 2012 or 2014. Luckily it will be more compatible and you will have less error:

But try to migrate first to SQL Server.

Because we are trying everything let's give a shot to Azure DMA for Oracle on Azure Data Studio: there are 4 extensions and they are all provided by Microsoft. Might work, might not work.