How to Migrate from Oracle to SQL Server: 2 Methods (CDC and Manual)

What this guide achieves

By the end of this guide you will be able to choose between two migration paths and run whichever one fits your situation: a real-time pipeline using Change Data Capture (CDC) that keeps the Oracle source live, or a one-time export and load using native Oracle and SQL Server tools. You will know the exact prerequisites each path needs, the data type traps that cause silent corruption mid-migration, and the failure modes to plan for before you start.

Time and effort. The CDC path needs roughly half a day to enable Oracle supplemental logging, create the capture user, and configure both connectors; the initial backfill then runs on its own and its duration scales with data volume. The manual path needs a maintenance window sized to your largest table, plus time upfront to map data types and rewrite any PL/SQL.

A reader who already knows the basics can skip straight to Set up the Oracle source capture or Export Oracle data to CSV using the headings.

Why Migrate from Oracle to SQL Server?

Teams move from Oracle to SQL Server for a few concrete reasons:

• Microsoft stack consolidation: If your reporting runs on Power BI, your apps on .NET, and your cloud on Azure, putting the database on SQL Server removes integration friction with SSIS, Azure SQL, and Synapse.
• Licensing model: SQL Server's per-core licensing and included features such as in-memory OLTP and columnstore indexes can lower total cost for mid-range workloads compared with Oracle Enterprise Edition and its separately licensed options. Whether this holds for your workload depends on core counts and which options you use, so price both vendors against your actual configuration rather than assuming a saving.
• Operational familiarity: Teams already running SQL Server reduce the number of platforms they have to staff and monitor.

When SQL Server is not the right target: If you depend on Oracle-specific capabilities (advanced PL/SQL packages, Oracle Spatial, partitioning strategies tuned to Oracle's optimizer, or RAC for scale-out), the rewrite cost can erase the licensing saving. At very high core counts, SQL Server Enterprise licensing is not always cheaper than Oracle. Inventory these dependencies before committing to a target.

The two methods compared

Quick decision: choose the manual method for a one-time copy of a small database when you can tolerate a maintenance window or when policy forbids third-party tools. Choose CDC when the source cannot go offline, the dataset is too large for a single export window, or you need Oracle and SQL Server in sync through a phased switchover. A fuller version of this decision is in Which method should you choose? at the end.

Method 1:  Real-Time Oracle to SQL Server Migration Using Estuary

The operational problem this solves

A production Oracle database that backs live applications cannot usually be frozen for the hours a large export takes. You also often want both databases running in parallel so you can cut applications over one at a time and roll back to Oracle if validation fails. A point-in-time export cannot do either: anything written to Oracle after the export is lost until you run it again, and there is no ongoing sync.

How Estuary's CDC handles it

Estuary moves data from Oracle to SQL Server continuously using Change Data Capture, so the Oracle source stays online for the entire migration and SQL Server stays current after cutover. The Oracle capture connector reads Oracle's change stream using Oracle LogMiner and writes those changes to durable intermediate storage; the SQL Server materialization connector reads from there and applies merge-based writes to keep the destination in sync.

That decoupling is the reason CDC handles a multi-hour load more safely than a single long-running export. If SQL Server goes offline during the load, the capture keeps advancing against Oracle rather than stalling, so a downstream outage does not force a restart from the source. Estuary discovers tables and maps types automatically and surfaces them for review; you confirm the mapping rather than building it by hand.

Estuary publishes production migration case studies, including pipelines off Oracle, on its success stories page.

Who it is for: teams migrating a production Oracle database they cannot take offline, or anyone who needs Oracle and SQL Server to run in parallel during a phased switchover.

When not to use it: if you only need a one-time snapshot of a small database and you have a maintenance window anyway, CDC is more setup than the job requires. Use Method 2.

Prerequisites

The Oracle connector reads change data through LogMiner, which needs more than a single logging flag. Set all of this up before you build anything else, because the most common failure is configuring the connector first and only then discovering the capture returns no changes. The full, current requirements are in the OracleDB connector docs; the essentials are below.

Oracle 11g or above.

Network access from Estuary to your Oracle database, either by allowlisting Estuary's IP addresses or via an SSH tunnel.

A dedicated read-only capture user with the grants LogMiner needs. For a non-container database:

sql
CREATE USER estuary_flow_user IDENTIFIED BY <your_password_here>;
GRANT CREATE SESSION TO estuary_flow_user;
GRANT SELECT ANY TABLE TO estuary_user;
GRANT SELECT_CATALOG_ROLE TO estuary_user;
GRANT EXECUTE_CATALOG_ROLE TO estuary_user;
GRANT SELECT ON V$DATABASE TO estuary_user;
GRANT SELECT ON V$LOG TO estuary_user;
GRANT LOGMINING TO estuary_user;

(Container databases use a c##-prefixed common user created in the root container. See the docs for the CDB variant.)

A watermarks table the connector writes to during backfills:

sql
CREATE TABLE estuary_user.FLOW_WATERMARKS(SLOT varchar(1000) PRIMARY KEY, WATERMARK varchar(4000));
GRANT INSERT, UPDATE ON estuary_user.FLOW_WATERMARKS TO estuary_user;
ALTER USER estuary_user QUOTA UNLIMITED ON USERS;

Supplemental logging enabled. Without it, the redo logs do not contain enough information to reconstruct row changes. The Estuary Oracle connector expects all-column supplemental logging:

sql
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

On Amazon RDS, use rdsadmin.rdsadmin_util.alter_supplemental_logging instead, as shown in the docs.

An Estuary account. Register at dashboard.estuary.dev.

Verification before you scale up: confirm your Oracle version is supported and that supplemental logging is on, then run a single-table capture first to confirm the change stream is flowing before you add the full schema.

Step 1: Connect to Oracle as a Source Connector

1. Log in to your Estuary account.
2. Go to the Sources section and click + New Capture.
3. Select the OracleDB connector from the list.
4. Enter the connection details: address (the host or host:port where Oracle is reachable), user, password, and database (the logical database name, which defaults to ORCL; in a multi-container environment use the PDB name).
5. If your schemas could change during the migration, leave the advanced dictionary_mode at its default of extract so LogMiner handles schema changes; switch it to online only for tables whose schema is fixed, to save resources.
6. Click Save and Publish.

Expected result: the capture status moves to Active and Estuary discovers the tables in the schemas your user can read. Open the resulting collection and confirm rows are present before continuing. If the capture publishes but returns no data, recheck supplemental logging first.

Step 2: Configure the SQL Server materialization

On the SQL Server side, create a login the connector will use. The connector creates destination tables itself, so the user needs control of the target database:

sql
USE <database>;
CREATE LOGIN flow_materialize WITH PASSWORD = 'secret';
CREATE USER flow_materialize FOR LOGIN flow_materialize;
GRANT CONTROL ON DATABASE::<database> TO flow_materialize;

The SQL Server port is always 1433; together with the server host you use host:1433 as the address. Full options, including Azure Entra authentication, are in the SQL Server materialization docs.

1. Go to the Destinations section and click + New Materialization.
2. Select the SQL Server connector.
3. Enter the destination details: address (host:1433), user, password, and database.
4. Select the collections produced by your Oracle capture as the source for this materialization.
5. Click Save and Publish.

Expected result: the materialization status moves to Active and the connector creates one table per bound collection in SQL Server. Query a target table and confirm the row count is approaching the Oracle source count as the backfill completes.

Plan for the archive log retention failure mode

The risk specific to Oracle CDC is the archive log retention window. The capture reads change data from Oracle's redo and archived logs. If the capture falls behind, or is paused longer than Oracle retains its archive logs, the changes it needs can age out before it is read. Recovery at that point means a fresh backfill of the affected tables, which on a large table can mean re-reading the entire table.

Set Oracle's archive log retention generously relative to your largest expected pause, and monitor capture lag rather than assuming it will catch up on its own. If you tune the load and hit a PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT error, lower the advanced incremental_scn_range so fewer changes are processed per iteration.

Estuary can also connect Oracle to other destinations, including Snowflake, BigQuery, and Databricks; see the full connector reference if a future architecture change points somewhere other than SQL Server.

Method 2: Manual Oracle to SQL Server Migration

Export the Oracle data to CSV, map the data types, then load the CSV into SQL Server with BULK INSERT. This produces a point-in-time copy, not an ongoing sync.

Who it is for: small or one-time migrations, or environments where compliance rules prohibit third-party pipeline tools.

Prerequisites

• Admin access to both databases. You need an Oracle account that can read every schema you are moving and a SQL Server account in the bulkadmin or sysadmin role, because only those roles can run BULK INSERT.
• A target SQL Server version that supports the types you map to.DATETIME2, used below for Oracle DATE values, is available in SQL Server 2008 and later; this guide assumes SQL Server 2016 or later.
• A file path SQL Server can read.BULK INSERT reads the data file from a path the SQL Server service account can reach (a local path, a UNC share, or Azure Blob Storage), not from your workstation.

Export Oracle data to CSV

BULK INSERT loads from a delimited text file, so the export has to produce CSV. Note that Oracle Data Pump (expdp) is not the tool for this path: its dump files are written in a proprietary binary format that only the Data Pump Import utility (impdp) can read, so a .dmp file cannot be fed to BULK INSERT. Use a CSV-producing tool instead. Oracle's SQLcl makes this one command per table:

Connect to the source database with SQLcl as a user that can read the table.

Switch the output format to CSV:

sql
SET SQLFORMAT csv

Spool the table to a file and run the query:

sql
SPOOL customers.csv
SELECT * FROM schema_name.customers;
SPOOL OFF

Expected result: a customers.csv file containing a header row plus one row per source row. Open it and confirm the row count and that delimiters inside text values are quoted. Repeat per table, in dependency order if you plan to load with constraints enabled.

Map Oracle data types to SQL Server

Map types before loading. The mappings that cause the most trouble:

• NUMBER with no precision or scale. Oracle allows this; SQL Server DECIMAL has no unbounded equivalent. Decide per column whether it becomes DECIMAL(p,s), BIGINT, or FLOAT, and confirm no value overflows or loses scale.
• DATE. Oracle DATE includes a time component; SQL Server DATE does not. Map to DATETIME2 if you need the time.
• CLOB / BLOB. Map to VARCHAR(MAX) / VARBINARY(MAX) and test large values.

Create the target tables in SQL Server with these mapped types before the load.

Load the CSV into SQL Server

sql
BULK INSERT schema_name.customers
FROM 'C:\\data\\customers.csv'
WITH (
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\\n'
);

FIRSTROW = 2 skips the header row the spool produced. See the BULK INSERT reference for batch size, error file, and code page options.

Expected result: SQL Server reports the number of rows affected. Compare that against the source count from the export step.

Validate the loaded data

1. Compare row counts per table between Oracle and SQL Server.
2. Spot-check the transformed columns, especially the NUMBER mappings, against known edge-case rows.
3. Confirm primary keys, unique constraints, and foreign keys are intact before pointing any application at the new database.

Limitations of the manual method

• It is a snapshot. Anything written to Oracle after the export is not reflected until you run it again.
• It needs a freeze or maintenance window to avoid missing in-flight writes.
• PL/SQL stored procedures and triggers do not transfer; they must be rewritten in T-SQL.
• It demands strong admin skills on both platforms, and verification is manual.

Which method should you choose?

• Choose manual for a one-time copy of a small database, or when policy forbids third-party tools, and you can tolerate a maintenance window.
• Choose CDC when the source cannot go offline, the dataset is large enough that a single export window is impractical, or you need Oracle and SQL Server in sync during a phased cutover.

Common challenges in Oracle to SQL Server migration

• Data type precision loss. The NUMBER to DECIMAL mapping is where silent corruption hides. A column that looks fine in a sample can overflow on an edge-case row, so validate against the full range, not a sample.
• Procedural code does not port. PL/SQL and T-SQL differ enough that packages, triggers, and stored procedures need manual rewriting. Inventory this before estimating the project; it is often the largest hidden cost.
• Schema and constraint differences. Identity columns, default expressions, and constraint behavior differ. Validate primary keys, unique constraints, and foreign keys after load.
• Referential integrity during load. Loading tables out of dependency order trips foreign key constraints. Either load in dependency order or disable and re-enable constraints around the load, then revalidate.
• Long object names (CDC). Oracle LogMiner does not support table or column names longer than 30 characters, so the Estuary Oracle connector cannot capture them. Identify any such objects before you start.

Troubleshooting

The three failure points that most often stop a migration:

• Estuary capture publishes but returns no changes. Supplemental logging is almost always the cause. Confirm ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS; ran successfully, then republish.
• Capture falls behind and changes age out. Oracle's archive log retention is shorter than your capture's pause. Increase retention and monitor lag; recovery otherwise requires a fresh backfill of the affected tables.
• BULK INSERT fails or returns a permissions error. The executing login is not in bulkadmin or sysadmin, or the SQL Server service account cannot read the file path. Grant the role and place the file where the service can reach it.

For the Oracle connector specifically, a ORA-01950: no privileges on tablespace 'USERS' error is resolved with ALTER USER estuary_user QUOTA UNLIMITED ON USERS;.

Best practices for Oracle to SQL Server migration

• Assess and inventory first. List schemas, data volumes, PL/SQL objects, and Oracle-specific features in use. The PL/SQL inventory drives the real timeline.
• Test in a staging environment. Run a full migration against staging to surface type mapping and constraint issues before they hit production.
• For CDC, enable supplemental logging and confirm capture lag before you trust the pipeline. Run one table end to end first.
• Validate against the full data range, not a sample, with attention to the NUMBER columns.
• Monitor SQL Server after cutover. Indexing and query plans tuned for Oracle's optimizer will not be optimal on SQL Server. Re-tune the hot queries.
• Plan rollback. Keep the Oracle source authoritative until validation passes. With CDC the source stays live, which makes rollback during a phased cutover lower risk.

Conclusion: your next step

If you are doing a one-time copy of a small Oracle database and can take a maintenance window, the manual CSV-and-BULK INSERT route is enough. If the database is large, must stay online, or needs to stay in sync with SQL Server through a phased switchover, set up a real-time pipeline instead.

To build the Oracle to SQL Server pipeline described in Method 1, start free in the Estuary dashboard, or read the OracleDB connector and SQL Server materialization docs to confirm the configuration against your own setup.

Related guides

• Oracle to Snowflake
• Oracle to PostgreSQL
• Oracle to MySQL
• Oracle to BigQuery
• Oracle to Databricks
• Oracle vs SQL Server: Key Differences