Azure SQL Managed Instance: Which Oracle Features It Actually Replicates

Why Teams Choose Azure SQL MI for Oracle Migrations

Oracle to Azure SQL Managed Instance is a legitimate migration path for organizations already committed to the Microsoft ecosystem — Active Directory, Azure DevOps, .NET application stacks, and existing SQL Server licensing through Enterprise Agreement. The pitch is that SSMA (SQL Server Migration Assistant) handles the mechanical conversion and Azure SQL MI provides the managed infrastructure that removes DBA overhead.

The pitch isn't wrong, but it's incomplete. SSMA converts roughly 60–70% of Oracle schema objects automatically. The remaining 30–40% requires understanding exactly where Azure SQL MI matches Oracle behavior, where it approximates it, and where there is no equivalent at all. This article covers the latter two categories — the parts SSMA's assessment report marks as warnings or errors.

What Azure SQL MI Actually Covers Well

Before cataloguing the gaps, it's worth being precise about what Azure SQL MI handles competently from Oracle migrations:

• Basic DML and DDL: Standard SQL, most data types with direct equivalents, primary keys, foreign keys, check constraints, unique indexes
• Stored procedures: PL/SQL without package state, without DBMS_* calls, and without Oracle-specific type system constructs migrates to T-SQL with moderate effort
• SQL Server Agent jobs: A reasonable substitute for DBMS_JOB — not DBMS_SCHEDULER — for simple time-based job scheduling
• Cross-database queries: Unlike Azure SQL Database, MI supports multi-database queries within the same instance, which is useful for schemas that relied on Oracle database links to other schemas on the same server
• CLR integration: Complex logic that cannot be expressed in T-SQL can be implemented as CLR stored procedures in .NET, which gives an escape hatch for some PL/SQL constructs
• Full-text search: Covers CONTAINS-style text search that teams were previously using Oracle Text for

Gap 1: Oracle Packages Have No Direct Equivalent

Oracle packages bundle related stored procedures, functions, types, and package-level variables into a single compiled unit. The package body can maintain state between calls within a session through package-level variables. This is a fundamental architectural pattern in Oracle-heavy systems — financial services applications in particular rely on packages for transaction state, audit trail accumulation, and complex business rule encapsulation.

T-SQL has no package concept. Azure SQL MI has no package concept. The migration options are:

1. Flatten package procedures into standalone stored procedures — works for packages with no package-level state. The package name becomes a naming prefix convention.
2. Replace package-level state with session context — SQL Server's SESSION_CONTEXT() and sp_set_session_context can hold key-value pairs scoped to a session, approximating package variables for simple scalar types.
3. Move stateful logic to the application layer — the most correct long-term choice but requires application-layer changes that extend the migration scope significantly.

-- Oracle package with session state
CREATE OR REPLACE PACKAGE audit_pkg AS
  g_current_user VARCHAR2(100);
  g_operation_id NUMBER;
  PROCEDURE set_context(p_user VARCHAR2, p_op_id NUMBER);
  PROCEDURE log_action(p_action VARCHAR2);
END audit_pkg;

-- T-SQL equivalent using SESSION_CONTEXT
-- sp_set_session_context must be called at connection time
EXEC sp_set_session_context @key = N'current_user', @value = N'jane.doe';
EXEC sp_set_session_context @key = N'operation_id', @value = 12345;

-- Retrieve in stored procedure
DECLARE @user NVARCHAR(100) = CAST(SESSION_CONTEXT(N'current_user') AS NVARCHAR(100));

The limitation of SESSION_CONTEXT() is that values are always sql_variant and limited to 256 bytes per key. Complex package state that holds cursors, collections, or large strings cannot be replicated this way.

Gap 2: Oracle DATE Stores Time; SQL Server DATE Does Not

Oracle's DATE type stores both date and time components with second precision. A column defined as DATE in Oracle containing 2024-09-17 14:32:07 carries the time. SQL Server's DATE type stores only the date — no time component. The equivalent in SQL Server is DATETIME2.

SSMA converts Oracle DATE columns to DATETIME2(0) by default. This is generally correct but creates several failure modes:

-- Oracle: This returns rows for today regardless of time
SELECT * FROM orders WHERE order_date = TRUNC(SYSDATE);

-- SQL Server equivalent after migration (if column is DATETIME2):
-- The Oracle query used TRUNC() which strips time — direct port fails
SELECT * FROM orders WHERE order_date = CAST(GETDATE() AS DATE);
-- But if the application sends literal dates without time:
SELECT * FROM orders WHERE order_date = '2024-09-17';
-- This only matches midnight exactly on DATETIME2 — misses all other times

Any application code or stored procedure that performs date equality comparisons without accounting for the time component will silently return wrong results after migration. Audit every WHERE column = :date_param pattern in the codebase.

Oracle's TIMESTAMP WITH TIME ZONE maps to SQL Server's DATETIMEOFFSET. The mapping is correct but the functions differ: SYS_EXTRACT_UTC, AT TIME ZONE, and FROM_TZ in Oracle become SWITCHOFFSET, TODATETIMEOFFSET, and AT TIME ZONE (added in SQL Server 2016) in T-SQL.

Gap 3: CONNECT BY Hierarchical Queries

Oracle's CONNECT BY syntax for hierarchical tree traversal is heavily used in financial systems (chart of accounts), HR systems (org charts), and any schema with parent-child relationships. SQL Server uses recursive CTEs instead.

-- Oracle CONNECT BY
SELECT
  employee_id,
  manager_id,
  name,
  LEVEL,
  SYS_CONNECT_BY_PATH(name, '/') AS path
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id
ORDER SIBLINGS BY name;

-- SQL Server recursive CTE equivalent
WITH org_tree AS (
  -- Anchor: root nodes
  SELECT
    employee_id,
    manager_id,
    name,
    1 AS lvl,
    CAST(name AS NVARCHAR(MAX)) AS path
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- Recursive: children
  SELECT
    e.employee_id,
    e.manager_id,
    e.name,
    ot.lvl + 1,
    CAST(ot.path + '/' + e.name AS NVARCHAR(MAX))
  FROM employees e
  INNER JOIN org_tree ot ON e.manager_id = ot.employee_id
)
SELECT * FROM org_tree
ORDER BY path;

The conversion is mechanical for straightforward hierarchies. It breaks for Oracle-specific CONNECT BY features: CONNECT_BY_ISLEAF, CONNECT_BY_ISCYCLE (cycle detection), and NOCYCLE option. SQL Server's recursive CTE detects cycles with MAXRECURSION but doesn't expose cycle information as cleanly as Oracle does. Graphs with known cycles require additional cycle-tracking columns in the recursive CTE.

Gap 4: DBMS_SCHEDULER vs SQL Server Agent

Oracle's DBMS_SCHEDULER is substantially more capable than SQL Server Agent. Specific features with no SQL Server Agent equivalent:

• Event-based scheduling: DBMS_SCHEDULER can trigger jobs on database events (file arrival, queue message, another job's completion). SQL Server Agent is strictly time-based.
• Job chains: DBMS_SCHEDULER job chains define complex DAG-style dependencies between jobs. SQL Server Agent job steps are linear within a single job; cross-job dependencies require workarounds.
• Window-based scheduling: DBMS_SCHEDULER windows define time periods with resource plans. SQL Server Agent has no resource-aware scheduling.
• External jobs: Running OS-level executables with credential management. SQL Server Agent supports this but with different permission models.

For complex Oracle job chains migrating to Azure, the correct solution is usually not SQL Server Agent — it's Azure Data Factory pipelines or Azure Logic Apps, which support event-based triggers and dependency chains natively.

Gap 5: Oracle-Specific Functions with No T-SQL Equivalent

SSMA flags these but doesn't always provide workable alternatives:

-- Oracle DECODE -- equivalent: CASE WHEN (SSMA converts automatically)
DECODE(status, 'A', 'Active', 'I', 'Inactive', 'Unknown')
-- T-SQL: CASE WHEN status = 'A' THEN 'Active' WHEN status = 'I' THEN 'Inactive' ELSE 'Unknown' END

-- Oracle NVL -- equivalent: ISNULL or COALESCE (SSMA converts automatically)
NVL(commission, 0)
-- T-SQL: ISNULL(commission, 0)

-- Oracle ROWNUM -- no direct equivalent; use ROW_NUMBER() OVER (ORDER BY ...)
SELECT * FROM employees WHERE ROWNUM <= 10;
-- T-SQL: SELECT TOP 10 * FROM employees ORDER BY (SELECT NULL);
-- Note: Oracle ROWNUM is pre-sort; TOP without ORDER BY is non-deterministic

-- Oracle LISTAGG -- equivalent: STRING_AGG (SQL Server 2017+)
LISTAGG(product_name, ', ') WITHIN GROUP (ORDER BY product_name)
-- T-SQL: STRING_AGG(product_name, ', ') WITHIN GROUP (ORDER BY product_name)

-- Oracle PIVOT/UNPIVOT -- equivalent exists in T-SQL but syntax differs significantly
-- Oracle MODEL clause -- no equivalent; requires complete rewrite as application logic

The MODEL clause used in financial projection queries and certain ETL patterns has no T-SQL equivalent. It requires rewriting as iterative cursor logic or moving the computation to the application layer. In SSMA assessments, MODEL clause usage is one of the hardest conversion challenges and is frequently underestimated in migration project scoping.

Sizing the Migration Effort Accurately

SSMA's assessment report categorizes objects by conversion complexity. The categories are useful but the effort estimates are optimistic. A more realistic multiplier from field experience:

• Automatic conversion (green): 0.25 hours per object — review and test only
• Manual conversion required (yellow): 2–8 hours per object — rewrite and test
• Not convertible (red): 8–40 hours per object — architectural redesign, application changes, or acceptance that the feature won't migrate

A 200-object Oracle schema that SSMA assesses as 70% green / 25% yellow / 5% red translates to approximately 140 objects at 0.25h (35h), 50 objects at 5h average (250h), and 10 objects at 20h average (200h) — roughly 485 hours of migration work, not counting testing, performance validation, and cutover rehearsal. SSMA assessments that report high automatic conversion rates frequently lead to underestimated project timelines because the manual and red-category objects are where the calendar time lives.