Oracle to SQL Server: A DBA Migration Guide

Migrating from Oracle to SQL Server is not a lift-and-shift. It is a platform migration, an ETL project, and a data quality audit happening at the same time. The data movement itself is rarely where projects get into serious trouble — the hard part is making sure the application behaves correctly once it is on the other side.

This is a DBA-focused guide covering assessment, schema conversion, data movement, and validation. Microsoft's migration path for Oracle-to-SQL Server centers on SSMA for Oracle — used for discovery, assessment, schema conversion, and data migration — with SSIS or other ETL tooling for more complex movement scenarios. For larger environments, Microsoft also provides Oracle Estate Assessment Tools to help collect inventory and SSMA project data across the estate.

Why Oracle migrations are harder than they look

Oracle tends to stick around because the business depends on it — not because anyone enjoys maintaining it. It could be an older release that never got upgraded, or a version approaching end of support, or just a system that has been running long enough that nobody wants to touch it. Over time that means accumulated legacy code, application behavior nobody fully documented, and stored procedures that have been patched incrementally for years. Oracle's desupport timelines eventually force the decision, but by the time a team starts planning, the environment is rarely clean.

The technical scope is also bigger than syntax translation. Datatype differences, PL/SQL-to-T-SQL rewrites, sequence behavior, indexing strategy, job scheduling, performance on the new platform — all of it shifts when the workload lands on SQL Server. Teams that treat it like a database copy hit those gaps in testing, or worse, in production.

Assessment first

Before moving any data, inventory the environment. Microsoft recommends starting here: discover Oracle instances, catalog schema objects, generate conversion reports before migration work begins. That first pass usually surfaces where the real effort is hiding.

At minimum, the assessment needs to answer:

• What converts automatically?
• What needs manual rewrite?
• Which objects depend on Oracle-specific behavior?
• Which tables are large enough to need staged ETL?
• Which application processes rely on Oracle features with no SQL Server equivalent?

Rushing past this is one of the more reliable ways to end up explaining scope changes mid-project. The more clearly the source environment is understood, the easier it is to sequence work and give stakeholders honest estimates.

Schema conversion

SSMA for Oracle is the standard starting point. Microsoft's guide walks through creating a project, connecting to Oracle, converting schema objects, syncing them to SQL Server, and reviewing results. It gives you a structured path — it does not replace the need to review what came out.

Most teams end up with three piles after the tool runs: objects that converted cleanly, conversions that need a closer look, and objects that need manual redesign. Tables, primary keys, and most indexes land in the first pile without much drama. The complexity clusters in packages, procedures, functions, triggers, sequences, and any Oracle code that relies on behavior SQL Server does not replicate. That is where the tool stops being useful and the DBA has to take over.

ETL strategy

Once schema is under control, focus shifts to data movement. SSMA supports data migration directly, and Microsoft notes SSIS can be used for SQL Server data migration scenarios. For small, clean migrations, SSMA may be enough. For anything with real scale or complexity, an ETL layer is the safer design.

One tooling note worth flagging early: Microsoft announced that the SSIS Connector for Oracle ends support on July 4, 2025, and will not be supported in SQL Server 2025 or later. New migration designs should not assume that connector is available.

A repeatable pattern that works:

• Extract from Oracle into staging.
• Transform datatypes, keys, and business rules in the ETL layer.
• Load into SQL Server staging tables.
• Validate staged data.
• Merge into final tables after validation passes.

It is slower than a direct load. It is also restartable, auditable, and considerably easier to control when something breaks mid-run — and something usually does. That trade-off is worth it.

Datatype mapping

This is where migrations go quietly wrong. Microsoft specifically recommends reviewing and adjusting the default type mappings before conversion. Default mapping gets close on a lot of things — close is not always good enough.

The spots that cause the most trouble:

• Oracle DATE and TIMESTAMP semantics.
• NUMBER precision and scale.
• CLOB and BLOB handling.
• Empty string versus NULL — Oracle treats them as equivalent; SQL Server does not.
• Sequence-generated keys and how they map to identity columns.

If the application depends on specific numeric precision or particular date handling, review those mappings carefully and document every intentional decision. The application team needs to understand what changed, and so does whoever inherits the system later.

Validation

Validation is a process, not a box to check the week before go-live. Microsoft recommends running validation queries against both source and target, reviewing results in a test environment, and comparing migrated data and schema before production cutover. Layering several methods is how you actually catch things — relying on any single check is where gaps slip through.

A solid pass covers:

• Row counts per migrated table.
• Aggregate checks on key numeric columns — sums, minimums, maximums.
• Distinct counts on business keys.
• Hash or checksum comparisons for stable reference data.
• Sample record checks on high-risk tables.
• Application regression testing.

Row counts matching is not the finish line. Values can still be wrong. Checksums catch drift but they are not a substitute for functional testing — the regression pass is what tells you whether the system actually works the way users expect.

Cutover

By the time cutover happens, there should be no open questions. Schema is in place, ETL runs cleanly and repeatably, and validation results are signed off before the go-live window opens. If issues are still surfacing in rehearsal, the migration is not ready.

Before the switch:

• All schema objects deployed.
• Data loads completed successfully.
• Validation reviewed and signed off.
• Permissions and roles mapped.
• Backup and rollback tested — not just written down.
• Connection strings updated.
• Monitoring ready for the first production cycle.

After go-live, watch for blocking, deadlocks, query plan regressions, and jobs that behave differently than they did on Oracle. The migration is not finished when the data lands. It is finished when the application is stable and the business trusts it.

Wrapping up

The migrations that go well are the ones broken into phases and worked through methodically. Assess first, then convert, then move data carefully, then validate against something trustworthy before users touch it.

Keeping schema conversion, ETL design, and data validation as separate tracks — in thinking and in the project plan — cuts out most of the painful surprises. Teams that scramble at cutover almost always compressed those phases together earlier.

Questions about an Oracle migration you're working through? I'd like to hear what you ran into.