SQL Server to Oracle 23ai Migration: A Technical Case Study

We migrated a 12TB SQL Server 2019 OLTP system to Oracle 23ai over 11 weeks. Not a greenfield project — a real production system with 380 tables, 2,100 stored procedures, six reporting teams, and a data engineering group that had opinions about every decision. This is the story of how it went.

If you are evaluating Oracle 23ai as a migration target, or if you are deep in a migration project and running into friction, this is written for you. We are not going to pretend it was painless. But the outcome was worth it, and the pieces that went well point directly to specific Oracle 23ai capabilities that made the difference.

Why We Migrated: The Drivers That Made It a Business Decision

The migration was not driven by a love of Oracle — it was driven by three specific problems that SQL Server could not solve without architectural rework we were not prepared to do:

1. The JSON document problem. About 30% of the application's data access patterns had shifted over five years toward semi-structured payloads. The team was storing JSON in NVARCHAR(MAX) columns, parsing it on read, and fighting query plan instability when those columns appeared in WHERE clauses. The application was doing document work in a relational engine, and both sides were suffering.

2. The read-scale gap. Reporting queries were landing on the primary to avoid the replication lag from Always On Availability Groups readable secondaries. AG replica lag was averaging 4-8 seconds under load, which broke two reporting pipelines. The team was looking at adding a separate read replica cluster — at that point, the conversation shifted to "why are we paying for two SQL Server licenses plus Windows Server plus Always On?"

3. The AI feature roadmap. Three product teams were planning to add semantic search and similarity-matching features. The internal architecture review concluded that building this on SQL Server would require a separate vector database (Pinecone or Weaviate), an ETL pipeline from SQL Server to the vector store, and a sync job to keep them coherent. Oracle 23ai's native VECTOR datatype removed all of that from the equation.

Two of these three drivers were addressed specifically by Oracle 23ai features: JSON Relational Duality (driver 1) and AI Vector Search (driver 3). True MVCC addressed the read-scale problem without requiring a separate replica cluster. The migration became the right call once those capabilities existed in a single release.

Oracle 23ai Features We Actually Used

Not every feature in 23ai was relevant to our migration. Here is what we actually used, and why.

JSON Relational Duality — The Reason the Migration Made Architectural Sense

The migration would have been justified on schema parity alone, but JSON Relational Duality changed how we thought about the application design post-migration. The source system had 47 NVARCHAR(MAX) columns holding JSON payloads — application state, event streams, loosely-typed configuration objects. In SQL Server these were query plan landmines. In Oracle 23ai we converted them to a JSON Relational Duality view over structured tables.

The dual-access view meant the reporting team could read data as structured rows (no code changes), and the new API service could read the same data as JSON documents — same underlying storage, no duplication, no sync jobs. This eliminated one of the biggest architectural tensions in the original SQL Server design.

True MVCC — Eliminating Read/Write Blocking Without a Replica Cluster

SQL Server's default isolation level (read committed) blocks readers when a writer holds an exclusive lock. In high-concurrency OLTP workloads, this manifests as blocking chains — the classic symptom where a long-running report blocks an incoming transaction, which blocks a user-facing query, which triggers an alert.

Oracle 23ai's true MVCC means readers never block writers and writers never block readers. Every query sees a consistent point-in-time snapshot of the database. This is not new in Oracle — Oracle has had read-consistent snapshots for decades. But 23ai's implementation removes the last remaining edge cases around isolation level configuration that required careful tuning in earlier Oracle releases.

The practical impact on the migrated system: peak-hour blockingwaits dropped by 94% compared to the equivalent workload on SQL Server. The reporting queries that had previously required read-only AG replicas now run directly against the primary without generating blocking events.

AI Vector Search — Why We Removed the Vector Store From the Architecture

Three application services needed vector similarity search: a product recommendation engine, a support ticket routing system, and an internal knowledge base search. In the original SQL Server architecture, these were scoped for a separate Pinecone deployment. Oracle 23ai's native VECTOR datatype let us keep the data in the same database.

Storing embeddings in the same database as transactional data meant join operations between vectors and relational data run in a single query, under the same ACID guarantees, without crossing a network boundary to a separate vector store. The recommendation query that previously required an ETL pipeline to Pinecone plus an application-level join is now a single SQL statement.

Native JSON Datatype — Replacing NVARCHAR(MAX) Columns

Beyond the duality views, Oracle 23ai's native JSON datatype replaced the NVARCHAR(MAX) storage. JSON columns store data in an optimized binary format (ORACLE JSON) with predicate pushdown support and efficient in-place updates.

Migration Challenges and How We Solved Them

Nothing migrates cleanly. Here is what went wrong and what we did about it.

Challenge 1: T-SQL to PL/SQL Rewrite — Scope Was Larger Than Expected

We estimated 8-10 days for the stored procedure rewrite. It took 23 days. The gap was not the syntax difference — it was the behavioral differences between T-SQL and PL/SQL in edge cases: dynamic SQL execution context, transaction scope in nested procedures, table variable scoping, and error handling with TRY/CATCH vs. exception blocks.

Lesson: If you are migrating a codebase with more than 500 stored procedures, budget 3x your initial estimate for the rewrite phase. T-SQL and PL/SQL share SQL syntax but diverge significantly in procedural behavior.

Challenge 2: IDENTITY Columns — Oracle Uses Sequences Differently

SQL Server's IDENTITY column generates sequential integers at insert time with a single statement. In Oracle, sequences are shared across sessions — RETURNING INTO is the equivalent pattern. We found 184 INSERT patterns in application code that used the SCOPE_IDENTITY() pattern. We automated the rewrite with a Python script using regex patterns. Total rewrite time: 4 days.

Challenge 3: TempDB Contention Became PGA Memory Tuning

The SQL Server system had documented tempdb contention problems. Oracle's PGA and temp space allocation works differently — we used Oracle's SQL Monitoring to identify the three queries generating 80% of the temp space usage. Rewriting those queries eliminated the contention without any Oracle-level configuration change.

Lesson: Oracle's architecture made the underlying problem disappear. But the migration forced us to fix the queries rather than tune around them, which was the right outcome.

Challenge 4: Linked Server References in Application Code

The SQL Server system had 14 linked server references to external systems. Oracle has heterogeneous connectivity (DBMS_HS) but it is not a drop-in replacement for SQL Server linked servers. We migrated to Oracle External Tables for file-based sources and DBMS_HS for database connections. Three of the linked servers were deprecated systems we could remove entirely. Budget 3-4 days per live linked server reference.

Migration Timeline and Phases

Total elapsed time: 11 weeks. The stored procedure rewrite was the critical path — everything else ran in parallel where possible.

Measurable Outcomes

Six months post-migration, here is what changed:

What We Would Do Differently

Start the stored procedure inventory earlier. We underestimated the rewrite scope until week 3. If we had catalogued all 2,100 procedures in phase 1 and classified them by complexity, we would have started the rewrite earlier and finished a week ahead of schedule.

Test the JSON Relational Duality views with the application team before schema migration. We designed the duality views based on our understanding of the application's JSON access patterns. Two views needed revision after the application team tested against them — the rework took three days and was not in the plan.

Get the Oracle 23ai environment sized correctly from day one. We under-provisioned PGA initially and spent two weeks tuning memory configuration that would have been avoided with a larger PGA allocation upfront. Oracle 23ai's memory advisors are good — use them before you go live.

Who Should Consider This Migration

Oracle 23ai is the right target if you have: a SQL Server workload with JSON-heavy access patterns, a read-scale problem you are solving with Always On AG readable secondaries, or an AI/ML roadmap that currently requires a separate vector database. The migration is not free — the stored procedure rewrite is the dominant cost — but for workloads that hit those specific problems, the architectural outcomes justify the investment.

If you are running a simple OLTP system with mostly relational data and no AI roadmap, SQL Server is still the right choice. The case for Oracle 23ai is specific to the capabilities it adds, not to Oracle as a default.

Evaluating your own migration path? We run database migration assessments covering schema analysis, stored procedure complexity scoring, and Oracle 23ai suitability review. If you want a concrete answer on whether this migration makes sense for your system, we can give you one.