April 12, 2024 at 1:46 am
Hello SQL Server Experts.
Apologies if in the wrong place, was not sure which forum to post this in.
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.
First of all, 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 SSMA on a Windows 10 VM in Azure, 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.
The oracle database has just under 19K tables. I have started running the Create Report to analyse the Oracle 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 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 all the analysis 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 on the target Azure SQL DB 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.
Thank you for reading.
Fletch.
April 12, 2024 at 2:19 pm
My opinion - it depends. The whole point of the report part is to make sure things can be converted (if I remember right). So you can skip the report if you are 100% confident you can convert things, but it isn't just tables that are being moved over - you are also moving views, schemas, stored procedures, indexes, users, logins, permissions, roles, etc. It's not JUST tables moving across. Unless you are only selecting tables, in which case you will only have to worry about tables, but I suspect tables also brings across table associated objects like indexes and triggers.
I would be a little curious if MAYBE your Win 10 VM doesn't have enough memory to hold the metadata and you are paging to disk. Paging to disk is an incredibly slow operation and will drastically slow down SSMA. I would try to put it on a machine that has a LOT of memory and see if performance improves. The tool, from everything I've read about it, is not supposed to be run on a desktop level machine. All recommendations I see are to install it on the same server as your SQL Instance and run it from there.
My best guess about why it is so slow is memory limitations of your Win 10 VM.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
April 14, 2024 at 4:24 am
Hi Brian,
Thank you for getting back, much appreciated. I did not have a hand in the installation and configuration of the tools unfortunately. It was all there when I turned up. And you are right, I'm just selecting tables, but it does indeed bring more across with the tables. The Windows 10 machine has 15GB ram, and I noticed it reached around 14.5gb at one pointed. The report finished and there is literally 1000s that did not convert properly and too 3-4 days to run when left to run continuously.
I to would have installed on a SQL Server, I'd have got it to SQL Server 2019 first, then taken it to an Azure SQL Database. The disk where not that busy, so I think I may well have been right on the cusp!
I think the customer knows it is not going to be clean, and wanted the assessment report more as an artifact to reference back on it as they will have to spend some considerable time making adjustments.
Thanks again for coming back.
Fletch.
April 15, 2024 at 6:03 pm
With desktop versions of windows, if you see 90%+ memory being used, (14.5/15 GB), that is a good sign stuff is being paged to disk. Windows tries to not let memory hit 100% utilization and can page stuff to disk early. It can hit 100% usage (I had 100% CPU, 100% memory and 100% disk earlier today which blew my mind as I'd never seen that before), but usually when memory gets too close to 100%, Windows will try to push some stuff to disk.
My opinion, seeing 1000's of objects out of the 15k (so more than 10%!!!) failing, that is a big red flag for me and I'd need to investigate further before doing the migration. But I'd try doubling the memory on the VM (at a minimum) and see if that helps the performance. If you still see the 90%+ memory utilization, I'd cancel the report and increase memory again until you see 90% or more free. Then the report generation should be a LOT faster. Paging to disk is INCREDIBLY slow because disk are slow - even SSD's. Modern RAM is faster than any modern SSD that is out there. I say "Modern" because I don't want anyone jumping in telling me that the latest and greatest SSD speeds are faster than last gen (or older) RAM which MAY be true, but even IF the SSD is faster, the OS uses your SSD (storage) differently than it uses RAM and for the intents and purposes of this post, RAM will be faster than paging to disk.
My opinion - if it is paging to disk at 15 GB and taking 3-4 days to run for the report, it'll be slower when it needs to move the data as well as that too will be paging to disk. Even if the disk doesn't appear to be getting a lot of hits, it could be that the disk is "fast", so reading and writing data to disk is quick, but the process of dumping the memory from RAM to disk and pulling it from disk back to RAM to do any processing takes away from other useful CPU cycles. I would throw some more memory at the system and see how that impacts the run time for the report portion and go from there. Installing SQL Server on the win 10 box is going to use up precious memory and it'll make the process even slower so I'd be very hesitant to go that route.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply