SSMA for Oracle to MS SQL - Need cons and pros

SSMA is a free, Microsoft-supported tool that accelerates Oracle-to-SQL Server migrations by automating schema conversion, SQL translation, and data movement—but it cannot handle every Oracle feature automatically and may require manual intervention, custom mappings, and additional tooling for complex scenarios.

Pros:

Free, Microsoft-supported tool with no licensing fees and compatibility with the latest SQL Server and Azure targets.

Automates schema conversion (tables, indexes, views, stored procedures) from Oracle to T-SQL, saving weeks of manual work.

Generates detailed assessment reports highlighting unsupported constructs and data-type mismatches before migration.

Side-by-side SQL comparison lets you review original Oracle code versus converted T-SQL for fine-tuning.

Integrated data migration supports bulk loads to SQL Server or Azure SQL, eliminating separate ETL scripting.

Extensible with custom type mappings and conversion scripts to meet site-specific requirements.

Clear, step-by-step migration workflow for auditing progress and maintaining control.

Cons:

Complex PL/SQL constructs (packages with global variables, advanced analytics functions) often require manual rewriting.

Does not support some Oracle features (database links, fine-grained access control, certain XML/JSON functions) out of the box.

Converted T-SQL may be non-optimal (cursor loops instead of set-based operations), requiring post-migration performance tuning.

GUI can feel dated and unresponsive for very large schemas; console mode or scripting may be needed for high-scale migrations.

Built-in data migration is single-threaded—large tables may need SSIS, Azure Data Factory, or bcp for parallel loading.

Designed for one-time migrations; no built-in change-data-capture or ongoing synchronization.

Requires correct Oracle Client installation and configuration (32-bit vs. 64-bit), which can cause connection issues if mismatched.

Best Practices to Mitigate Cons

1. Run Conversion Assessments Early Generate SSMA reports on schemas to identify unsupported objects and plan manual rewrites before data migration begins.
2. Use Custom Type Mappings Adjust default Oracle→SQL Server data-type mappings in SSMA’s Project Settings to match your performance and precision needs.
3. Leverage Supplemental Tools Offload large data loads to SSIS or Azure Data Factory for parallel throughput; use SQL Server Profiler and Query Store for post-migration tuning.
4. Plan for Manual Refactoring Allocate time for rewriting unsupported PL/SQL packages, triggers, and advanced SQL expressions into T-SQL or .NET routines.
5. Validate Functional Equivalence Use SSMA’s SQL comparison and unit-test frameworks to verify converted code produces identical results under representative test cases.
6. Automate Scripting For very large environments, consider SSMA console mode or PowerShell scripting to orchestrate batch conversions and deployments without GUI limitations.