Migrating from Oracle to SQL Server: A Practical Checklist

Oracle-to-SQL-Server migrations look straightforward on paper — both are enterprise relational databases with decades of battle-tested features. In practice, the gap between PL/SQL and T-SQL, the divergent type systems, and Oracle-specific idioms buried deep in application code turn what looks like a lift-and-shift into a multi-month engineering project. Teams that skip the assessment phase almost always discover breaking incompatibilities days before cutover. This checklist distills the patterns that separate migrations that land cleanly from the ones that drag on for years.

Pre-Migration Assessment

The single biggest predictor of migration success is how thoroughly you understand what you are migrating before you start. Skipping this phase is how teams end up discovering Oracle-specific package dependencies at 2 AM the night before go-live.

Start by cataloging every schema object in the source database: tables, views, stored procedures, functions, packages, triggers, sequences, synonyms, database links, and scheduler jobs. Oracle's ALL_OBJECTS view gives you the full inventory.

-- Inventory all schema objects by type
SELECT
    owner,
    object_type,
    COUNT(*) AS object_count,
    SUM(CASE WHEN status = 'INVALID' THEN 1 ELSE 0 END) AS invalid_count
FROM all_objects
WHERE owner NOT IN ('SYS','SYSTEM','DBSNMP','OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC')
GROUP BY owner, object_type
ORDER BY owner, object_count DESC;

Next, score each object for migration complexity. A simple lookup table with no foreign keys and standard scalar columns is low risk. A PL/SQL package that wraps dynamic SQL, calls database links, uses DBMS_SCHEDULER, and references Oracle spatial types is a project of its own. Assign a weight to each object and sum the scores to get a migration complexity index for each schema. This number becomes your planning input for sprint sizing.

Document external dependencies explicitly: JDBC/OCI connection strings embedded in application configuration files, ETL pipelines that rely on Oracle change data capture, reporting tools with Oracle-dialect SQL baked into their metadata layer, and any application code that calls ORA_ROWSCN or other Oracle-proprietary pseudocolumns. Each dependency is a separate work item with its own acceptance criteria.

Data Type Mapping (Oracle → SQL Server)

Data type mismatches are the most common source of silent data corruption in database migrations. The table below covers the mappings you will encounter on the majority of OLTP schemas. Edge cases — Oracle INTERVAL types, XMLTYPE, spatial types, and RAW — require individual treatment.

PL/SQL to T-SQL Conversion

Syntactic differences between PL/SQL and T-SQL are well-documented; semantic differences are where migrations break. The constructs below account for the majority of manual conversion work on typical OLTP schemas.

ROWNUM vs. TOP / ROW_NUMBER() — Oracle's ROWNUM pseudocolumn is assigned before ORDER BY is evaluated, which surprises developers who expect it to behave like SQL Server's TOP. For simple top-N queries, TOP is the correct replacement. For pagination, use ROW_NUMBER() OVER (ORDER BY ...).

-- Oracle: first 10 rows by salary (unreliable ordering with ROWNUM)
SELECT * FROM employees WHERE ROWNUM <= 10 ORDER BY salary DESC;

-- SQL Server: correct equivalent using TOP with ORDER BY
SELECT TOP 10 * FROM employees ORDER BY salary DESC;

-- Oracle: pagination with ROWNUM (verbose and fragile)
SELECT * FROM (
  SELECT e.*, ROWNUM rn FROM (
    SELECT * FROM employees ORDER BY salary DESC
  ) e WHERE ROWNUM <= 20
) WHERE rn > 10;

-- SQL Server: clean pagination with ROW_NUMBER()
SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
  FROM employees
) ranked
WHERE rn BETWEEN 11 AND 20;

DECODE vs. CASE — Oracle's DECODE is a legacy equality-only switch expression. SQL Server's CASE is the ANSI standard and handles range predicates as well. The conversion is mechanical but must be done consistently across all stored procedures, views, and ad hoc query libraries.

-- Oracle DECODE
SELECT DECODE(status, 'A', 'Active', 'I', 'Inactive', 'Unknown') FROM accounts;

-- SQL Server CASE equivalent
SELECT
  CASE status
    WHEN 'A' THEN 'Active'
    WHEN 'I' THEN 'Inactive'
    ELSE 'Unknown'
  END
FROM accounts;

Sequences and IDENTITY — Oracle sequences are standalone schema objects referenced via sequence_name.NEXTVAL. In SQL Server, use IDENTITY for simple auto-increment primary keys, or create a SEQUENCE object (available since SQL Server 2012) when you need cross-table sequence sharing or the ability to cache and pre-allocate values.

-- Oracle sequence usage in INSERT
INSERT INTO orders (order_id, customer_id, order_date)
VALUES (order_seq.NEXTVAL, 101, SYSDATE);

-- SQL Server with IDENTITY column (DDL)
CREATE TABLE orders (
  order_id    INT IDENTITY(1,1) PRIMARY KEY,
  customer_id INT NOT NULL,
  order_date  DATETIME2(0) NOT NULL DEFAULT SYSDATETIME()
);

-- SQL Server with SEQUENCE object (equivalent to Oracle standalone sequence)
CREATE SEQUENCE dbo.order_seq
  START WITH 1
  INCREMENT BY 1
  CACHE 50;

INSERT INTO orders (order_id, customer_id, order_date)
VALUES (NEXT VALUE FOR dbo.order_seq, 101, SYSDATETIME());

Additional constructs requiring conversion include: NVL → ISNULL or COALESCE, SYSDATE → SYSDATETIME(), CONNECT BY hierarchical queries → recursive CTEs (WITH ... AS (... UNION ALL ...)), Oracle packages → SQL Server schemas with grouped stored procedures, and BULK COLLECT/FORALL patterns → set-based T-SQL with table variables or temporary tables.

Using SSMA (SQL Server Migration Assistant)

SSMA for Oracle is a free Microsoft tool that automates the mechanical portions of schema and code conversion. It connects directly to the Oracle source and SQL Server target, generates a conversion assessment report with a complexity score for each object, and produces converted T-SQL DDL and stored procedure code. SSMA does not replace human review — it generates a starting point, not a finished migration — but it eliminates several weeks of manual DDL rewriting on large schemas.

The SSMA workflow follows five phases. First, connect to the Oracle source and load the schema metadata. Second, run the assessment report to identify which objects will convert automatically, which will convert with warnings, and which SSMA cannot convert. Third, run the automatic conversion and review the output — SSMA inserts inline comments flagging every construct it could not convert cleanly. Fourth, manually fix the flagged objects. Fifth, synchronize the converted schema to the SQL Server target and migrate the data.

SSMA also handles data migration for moderate dataset sizes using its built-in data migration engine. For large tables (hundreds of millions of rows), use SQL Server Integration Services (SSIS) or a bulk copy approach with bcp or BULK INSERT instead — SSMA's data migration engine does not parallelize at the table-partition level and will bottleneck on very large volumes.

Validation and Cutover Strategy

Validation is not a phase that happens at the end — it runs in parallel with conversion from the moment the first table lands on the SQL Server target. Deferred validation amplifies the cost of every error.

Row count verification is the baseline check. Every table in the source must match the target row count exactly after the initial load and after any incremental sync passes.

-- Row count comparison query (run on SQL Server side after migration)
-- Assumes a linked server or manual collection of Oracle counts into a staging table
SELECT
    src.table_name,
    src.oracle_row_count,
    tgt.mssql_row_count,
    src.oracle_row_count - tgt.mssql_row_count AS delta
FROM oracle_row_counts src
JOIN (
    SELECT t.name AS table_name, p.rows AS mssql_row_count
    FROM sys.tables t
    JOIN sys.partitions p ON t.object_id = p.object_id AND p.index_id IN (0,1)
) tgt ON UPPER(src.table_name) = UPPER(tgt.table_name)
WHERE src.oracle_row_count <> tgt.mssql_row_count
ORDER BY ABS(src.oracle_row_count - tgt.mssql_row_count) DESC;

Checksum comparison goes one level deeper than row counts by detecting corrupted or mismatched column values. Compute an aggregate checksum over key business columns on both sides and compare the results. Oracle's ORA_HASH and SQL Server's CHECKSUM_AGG use different algorithms, so the comparison must be done by extracting matching column values and hashing them on a common platform, or by using a purpose-built data comparison tool such as Red Gate SQL Data Compare or Attunity Replicate's validation module.

Functional regression testing is the highest-fidelity validation layer. Capture a representative set of application queries against the Oracle source — including stored procedure calls with realistic parameter sets — record their result sets, then replay the same calls against the SQL Server target and diff the outputs. Automate this with a test harness that runs nightly against the migrated environment throughout the conversion project, not just at cutover.

For the cutover itself, plan for a brief maintenance window even in migrations that use continuous replication for data sync. The window covers: stopping writes to Oracle, applying the final incremental data sync, validating the delta, updating application connection strings, and flipping DNS or load-balancer targets. Practice the cutover procedure at least twice in a staging environment to get an accurate window estimate before scheduling production.

Working with JusDB on SQL Server Migration

JusDB's database engineering team has executed Oracle-to-SQL-Server migrations across OLTP systems, data warehouses, and mixed-workload environments. Our migration engagements begin with a structured assessment that produces an object inventory, complexity index, and phased project plan before any conversion work starts. We use SSMA as the baseline automation layer, pair it with manual T-SQL conversion for complex PL/SQL packages, and run continuous validation throughout the engagement — not as a gate at the end. If you are evaluating a migration or have already started one that has stalled, we can step in at any phase.

Explore JusDB SQL Server Migration Services →  |  Talk to a DBA