Migrating SQL Server to Oracle with SQL Developer's Migration Workbench: Automation and Limits

The problem: moving a SQL Server estate to Oracle without manual rewrite

Teams adopting Oracle often inherit databases built on Microsoft SQL Server. The schema alone—tables, views, indexes, triggers, and thousands of lines of T‑SQL in stored procedures—represents months of engineering. Rewriting by hand is error‑prone and slow. SQL Developer’s Migration Workbench promises to automate the heavy lifting, but the documentation glosses over where the tool stops and manual work begins.

This post walks through a realistic migration path: capturing a SQL Server schema, converting it to Oracle DDL, automating the run in a CI pipeline, and handling the gaps the tool cannot close.

How the Migration Workbench works

The Workbench operates in two modes. The GUI wizard (Tools → Migration → Migration Repository) connects to the source via JDBC, reverse‑engineers metadata into an internal repository, runs a rule‑based converter, and emits Oracle DDL plus a conversion report. The same logic is exposed through sdcli migrate, a headless command‑line utility that accepts a migration project XML file. That XML file is the artifact you check into version control and feed to a build server.

Supported sources include SQL Server 2008 through 2022, MySQL 5.6–8.0, PostgreSQL 9.5–15, Sybase ASE, and IBM DB2. Target Oracle versions span 11g through 23c. The converter handles tables, views, indexes, triggers, sequences, and stored procedures. It does not migrate security principals, roles, or permissions—those must be scripted separately using DBMS_METADATA or custom grants.

Worked example: a SQL Server schema with T‑SQL quirks

Consider a SQL Server database Orders containing a table dbo.OrderLines, a view dbo.v_OpenOrders, and a stored procedure usp_ProcessOrder that uses TRY/CATCH, a temporary table #tmp, and dynamic SQL via sp_executesql. The migration project XML (exported from the GUI after a successful test run) looks like this:

<?xml version=\"1.0\" encoding=\"UTF-8\"?>
<migration-project>
  <source-database type=\"SQLSERVER\" version=\"2019\">
    <jdbc-url>jdbc:sqlserver://src-db:1433;databaseName=Orders</jdbc-url>
    <username>migration_user</username>
    <password-encrypted>...</password-encrypted>
  </source-database>
  <target-database type=\"ORACLE\" version=\"19c\">
    <jdbc-url>jdbc:oracle:thin:@//tgt-db:1521/ORCL</jdbc-url>
    <username>migration_user</username>
    <password-encrypted>...</password-encrypted>
  </target-database>
  <options>
    <generate-ddl>true</generate-ddl>
    <generate-data-move-scripts>true</generate-data-move-scripts>
    <parallel-degree>4</parallel-degree>
  </options>
</migration-project>

Run the conversion headless on a Linux build agent with an enlarged JVM heap:

export _JAVA_OPTIONS=\"-Xms2g -Xmx4g\"
sdcli migrate \\
  -project /workspace/migration/orders.xml \\
  -output /workspace/migration/output \\
  -log /workspace/migration/migrate.log

The command writes Oracle DDL scripts under output/ddl/, data‑move control files under output/data/, and a conversion report output/report.html. The report flags the temporary table #tmp as converted to a global temporary table (GTT) with ON COMMIT PRESERVE ROWS, and the TRY/CATCH block as translated to a PL/SQL exception handler. Dynamic SQL via sp_executesql becomes EXECUTE IMMEDIATE with bind variables—if the parameter signatures match. The report lists any unsupported constructs (e.g., FILESTREAM columns, CLR assemblies) with line numbers.

Where the tool stops: trade‑offs and manual work

• Advanced T‑SQL features. CLR assemblies, Service Broker queues, FILESTREAM/FileTable, and columnstore indexes have no direct Oracle equivalent. The report marks them as Manual rewrite required. Plan sprint capacity for these.
• Security model. Logins, users, roles, schema ownership, and object‑level permissions are not migrated. Export SQL Server principals with sp_help_revlogin (community script) and recreate in Oracle using CREATE USER, GRANT, and DBMS_METADATA.GET_GRANTED_DDL.
• Large object counts. The GUI loads the entire repository into JVM memory. Beyond ~10,000 objects you’ll hit OutOfMemoryError. The CLI with -Xmx4g (or higher) and batch processing (split projects by schema) is the supported path.
• Data movement. The tool generates SQL*Loader control files and external‑table DDL for CSV staging. For terabyte‑scale loads, prefer Oracle Data Pump (impdp) with a network link or parallel direct‑path insert; the generated scripts are a starting point, not a performance‑tuned solution.

Verification checklist before cutover

1. Run sdcli migrate -help on the build agent to confirm the CLI version matches the SQL Developer release notes.
2. Create a test migration project against a restored backup of production (small subset if needed). Compare generated DDL with source schema using a diff tool (e.g., sqldiff or Schema Compare in SQL Developer).
3. Execute the generated DDL in a clean Oracle schema. Run the application’s integration test suite against it.
4. Validate row counts and checksums for every table after the data‑load phase.
5. Script and apply security principals separately; verify with a least‑privilege application account.

Closing: automate the repeatable, budget for the rest

SQL Developer’s Migration Workbench eliminates weeks of mechanical DDL translation and gives you a repeatable, version‑controlled artifact (migration-project.xml) that fits in CI/CD. The conversion report is your punch list: everything it flags as unsupported or partially converted is explicit manual work. Treat the tool as a force multiplier for the 80% of objects that map cleanly, and allocate engineering time for the remaining 20%—security, CLR, Service Broker, and performance tuning of the data load. Run the CLI in your pipeline nightly against a refreshed backup; when the conversion report shows zero surprises, you have a reliable migration cadence.