We have a very big Oracle db and want to archive whole of the oracle db to SQL Server. We are trying to weigh between SSIS v/s SSMA. Can the complete migration be achieved using SSMA? We do not have any schema/tables setup in sql. We need to migrate whole of the oracle db to sql.
Is there any cons using SSMA in this kind of migration?
Best Answer
Conceptually, yes, SSMA is well equipped to handle large Oracle DB migrations. However, every environment is different and depending on what idiosyncrasies your Oracle DB holds, this may or may not be a simple process. Don't expect to see a success screen after a few clicks with any approach.
First, I suggest you give a quick read of Michelle Li's answer to a similar question here. She outlines some key items you need to be aware of before you start. I will also draw your attention to the Migration Engine option. You'll want to ensure this is set to Server Side Data Migration Engine otherwise your data will go through whatever client SSMA is being executed on, and this is just a waste of bandwidth.
Most importantly, plan for failure. It will likely take a few iterations to work out all the bugs, and you may even have to roll in some custom processes to accommodate things SSMA just can't handle, such as External C libraries/functions, SQL Loader Tasks, complex PL/SQL statements, any external scripts being called via cron, etc. I think SSMA will take a stab at conversion of many of these things, but that doesn't mean it will work 100%. Testing is going to be critical, so plan accordingly. I will also suggest you try migrating a copy of your Prod environment (e.g. Dev or some other pre-prod area) first to work out the bugs. This will ensure you're not killing your production Oracle environment while you're fine tuning things.
In closing, the complexity of your end solution will likely reflect the complexity of the Oracle database. If it's just data, this could go swimmingly well, but if you've got a lot of logic tied into PL/SQL routines, etc. this can be time consuming. Use SSMA if possible as it'll hopefully cover 80% or more of the work, and then manage the remaining components how best you see fit, be it SSIS or otherwise.