Oracle to Azure SQL Managed Instance: The Pre-Go-Live Checklist

Why Pre-Go-Live Checklists Matter More for Oracle Migrations Than for SQL Server Ones

SQL Server to Azure SQL MI migrations have a relatively smooth path. The schema dialect is close, SQL Server Agent jobs translate to MI Agent jobs directly, and most SQL Server shops already understand SQL Server MI's feature boundaries from the product documentation. Oracle-to-MI migrations are harder because the source and target are architecturally different engines with different type semantics, different procedural language designs, and different operational tooling.

SSMA (SQL Server Migration Assistant for Oracle) converts a significant portion of schema objects automatically, but its conversion rate for stored procedures and packages is frequently overstated. Microsoft's own documentation acknowledges that complex PL/SQL — particularly packages with cross-procedure state, autonomous transactions, and Oracle-specific DBMS_ calls — requires manual rewrite. SSMA marks these with a warning but doesn't prevent you from deploying the unconverted output.

The checklist below covers the categories that cause go-live failures, not the ones SSMA covers adequately. Use it as a final validation layer, not a replacement for proper migration testing.

Schema and Type Verification

1. DATE Type Semantics

Oracle DATE stores date and time to the second. SQL Server DATE stores date only. When SSMA converts Oracle DATE columns, it maps them to SQL Server DATETIME2 — which is correct for precision preservation, but any application code that performs date-only comparisons (e.g., WHERE event_date = TRUNC(SYSDATE)) needs to be rewritten to use date-only functions.

-- Oracle pattern that breaks after conversion
SELECT * FROM orders WHERE order_date = TRUNC(SYSDATE);

-- SQL Server equivalent after migration
SELECT * FROM orders WHERE CAST(order_date AS DATE) = CAST(GETDATE() AS DATE);

-- Or using CONVERT
SELECT * FROM orders WHERE CONVERT(DATE, order_date) = CONVERT(DATE, GETDATE());

Run a full audit of date comparisons in stored procedures and application SQL before go-live. Silent wrong results — where the query succeeds but returns no rows — are harder to catch than errors.

2. NUMBER Type Precision

Oracle NUMBER is a variable-precision decimal with up to 38 significant digits. SSMA maps NUMBER(p,s) columns to SQL Server NUMERIC(p,s). The issue is with bare NUMBER columns (no precision specified) — SSMA maps these to FLOAT(53), which introduces floating-point representation errors on financial data.

-- Find bare NUMBER columns in SSMA output that became FLOAT
SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE = 'float'
  AND TABLE_SCHEMA = 'dbo'
ORDER BY TABLE_NAME, COLUMN_NAME;

-- These should be NUMERIC(38,10) or whatever the business precision requires
-- Change them before data load, not after

3. Collation Consistency

Azure SQL MI defaults to SQL_Latin1_General_CP1_CI_AS. Oracle typically runs AL32UTF8 character set. The mismatch causes case-sensitivity behavior differences and can cause collation conflicts on string comparisons involving joined columns from different databases.

-- Check MI instance collation
SELECT SERVERPROPERTY('Collation') AS ServerCollation;

-- Check database collation
SELECT name, collation_name
FROM sys.databases
WHERE name = DB_NAME();

-- Check column-level collations for potential conflicts
SELECT TABLE_NAME, COLUMN_NAME, COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLLATION_NAME IS NOT NULL
  AND COLLATION_NAME != (
    SELECT collation_name FROM sys.databases WHERE name = DB_NAME()
  );

Procedural Code Verification

4. Package-Level Variables

Oracle packages support package-level variables that persist for the duration of a session. This pattern is used for caching, inter-procedure state, and configuration. SQL Server has no equivalent concept — variables are procedure-scoped. SSMA cannot convert package-level variables; it converts procedures individually and drops the shared state.

Identify all package-level variables in the source:

-- Oracle: find packages with package-level variable declarations
SELECT owner, package_name, text
FROM dba_source
WHERE type = 'PACKAGE'
  AND text NOT LIKE '%PROCEDURE%'
  AND text NOT LIKE '%FUNCTION%'
  AND text LIKE '%:=%'
ORDER BY owner, package_name, line;

For each package-level variable, the SQL Server replacement is a temporary table, a session context value, or an application-layer cache. There is no one-size-fits-all answer — the correct approach depends on what the variable is used for.

5. Autonomous Transactions

Oracle PRAGMA AUTONOMOUS_TRANSACTION allows a procedure to commit independently of its calling transaction. This is widely used for audit logging — even if the main transaction rolls back, the audit record persists. SQL Server has no equivalent pragma. The SQL Server approach is a separate database connection (via linked server or application-layer) or, in modern versions, a workaround using Service Broker.

-- Oracle: find autonomous transaction usage
SELECT owner, name, type, text
FROM dba_source
WHERE UPPER(text) LIKE '%PRAGMA AUTONOMOUS_TRANSACTION%'
ORDER BY owner, name;

Every occurrence needs manual remediation. Plan for it in the project schedule.

6. DBMS_ Package Dependencies

Oracle's DBMS_ packages (DBMS_OUTPUT, DBMS_SCHEDULER, DBMS_CRYPTO, DBMS_LOB, etc.) have no direct SQL Server equivalents. SSMA converts DBMS_OUTPUT.PUT_LINE to PRINT, which works for debugging but is not the same for application code that reads DBMS_OUTPUT programmatically. DBMS_SCHEDULER jobs need to be recreated as SQL Agent jobs.

-- Oracle: inventory DBMS_ package usage in stored code
SELECT DISTINCT owner, name, type,
  REGEXP_SUBSTR(text, 'DBMS_\w+', 1, 1) AS dbms_package
FROM dba_source
WHERE UPPER(text) LIKE '%DBMS_%'
  AND owner NOT IN ('SYS', 'SYSTEM')
ORDER BY dbms_package, owner, name;

Operational Verification

7. SQL Server Agent Job Conversion

Oracle DBMS_SCHEDULER jobs need to be recreated as SQL Server Agent jobs on the MI instance. The conversion is not automatic. Capture all scheduler jobs, their schedules, the procedure or command they execute, and any job chains (jobs that trigger other jobs on success or failure).

-- Oracle: export all scheduler jobs
SELECT job_name, job_type, job_action,
  start_date, repeat_interval, enabled, state
FROM dba_scheduler_jobs
WHERE owner NOT IN ('SYS', 'SYSTEM', 'ORACLE_OCM')
ORDER BY owner, job_name;

-- Also check job chains
SELECT chain_name, step_name, program_name, condition
FROM dba_scheduler_chain_steps
ORDER BY chain_name, step_order;

8. Database Links vs. Linked Servers

Oracle database links allow cross-database queries using the @dblink syntax. Azure SQL MI supports linked servers but the configuration, permissions model, and performance characteristics differ significantly. More importantly, MI linked servers require the target database to be reachable from the MI's VNet — which has networking implications for databases that aren't yet in Azure.

-- Oracle: find all database link dependencies in SQL
SELECT DISTINCT owner, name, type,
  REGEXP_SUBSTR(text, '@\w+', 1, 1) AS dblink_ref
FROM dba_source
WHERE text LIKE '%@%'
  AND owner NOT IN ('SYS', 'SYSTEM')
ORDER BY owner, name;

Each database link dependency needs a migration plan: linked server, application-layer data access, or schema consolidation. Do not defer this to post-go-live.

9. Authentication and User Migration

Oracle uses schema-based user accounts where the user and the schema are the same object. Azure SQL MI uses SQL logins and database users, which are separate. The migration typically maps each Oracle schema to a SQL Server schema under one or more logins — but the application connection strings and user permissions need explicit validation.

-- Verify SQL MI user permissions match Oracle source grants
-- After migration, run against MI:
SELECT
  dp.name AS principal_name,
  dp.type_desc AS principal_type,
  o.name AS object_name,
  p.permission_name,
  p.state_desc
FROM sys.database_permissions p
JOIN sys.database_principals dp ON dp.principal_id = p.grantee_principal_id
LEFT JOIN sys.objects o ON o.object_id = p.major_id
WHERE dp.name NOT IN ('public', 'dbo', 'guest')
ORDER BY dp.name, o.name, p.permission_name;

High Availability and Disaster Recovery Verification

10. Business Continuity Policy Validation

Azure SQL MI has built-in HA using a local quorum of replicas. The default service tier (General Purpose) uses remote storage with 5-10 second RTO on node failure. Business Critical tier uses local NVMe SSD with in-memory replicas and sub-second RTO. Verify the tier matches the RTO/RPO requirement from the business — many migrations default to General Purpose because it's cheaper, without validating whether the HA behavior meets the SLA.

-- Check current service tier
SELECT @@SERVERNAME,
  SERVERPROPERTY('Edition') AS Edition,
  SERVERPROPERTY('EngineEdition') AS EngineEdition;

-- Check backup retention period
SELECT name, backup_storage_redundancy, retention_days
FROM sys.databases
WHERE name = DB_NAME();

11. Read Scale-Out Validation

If the Oracle source used Active Data Guard read replicas for reporting workloads, Azure SQL MI Business Critical tier provides a built-in read replica accessible via the ApplicationIntent=ReadOnly connection string parameter. Validate that reporting application connection strings are configured to use it, and that query workloads balance as expected.

Performance Validation

12. Statistics and Index Validation

SSMA migrates table structure and data but does not migrate Oracle optimizer statistics. After data load, run UPDATE STATISTICS before any performance validation. Also verify that indexes migrated correctly — Oracle function-based indexes require careful translation to SQL Server computed columns with indexes, and SSMA does not always get this right.

-- Update all statistics after data load
EXEC sp_updatestats;

-- Or for specific tables with large row counts
UPDATE STATISTICS dbo.orders WITH FULLSCAN;
UPDATE STATISTICS dbo.order_items WITH FULLSCAN;

-- Check for missing indexes that would benefit top workloads
SELECT TOP 20
  migs.avg_total_user_cost * (migs.avg_user_impact / 100.0)
    * (migs.user_seeks + migs.user_scans) AS improvement_measure,
  mid.statement AS table_name,
  mid.equality_columns,
  mid.inequality_columns,
  mid.included_columns
FROM sys.dm_db_missing_index_group_stats migs
JOIN sys.dm_db_missing_index_groups mig ON migs.group_handle = mig.index_group_handle
JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
ORDER BY improvement_measure DESC;

13. Top-SQL Benchmark Comparison

Capture the top 50 queries from Oracle AWR or Statspack before migration. Run each one against the MI instance and compare execution plans and elapsed times. Pay attention to queries that used Oracle's CBO hints — those hints are stripped during conversion and the MI optimizer may choose different plans.

Application Connectivity

14. Connection String Validation

Oracle applications typically connect via OCI, JDBC thin driver, or ODBC with Oracle client. The migrated application needs ODBC or JDBC SQL Server drivers. Validate that connection strings point to the MI endpoint (not the Oracle listener), that the port (1433) is reachable from application servers, and that TLS certificate validation is configured correctly for MI's mandatory TLS connections.

-- Test connectivity from application server (run in application server context)
-- Use sqlcmd or Test-NetConnection to validate before application testing
-- sqlcmd -S your-mi.public.xxxx.database.windows.net,3342 -U sa -P pass
-- The public endpoint uses port 3342; private endpoint uses 1433

15. Error Code and Exception Handling

Oracle error codes (ORA-xxxxx) are different from SQL Server error codes. Application code that catches specific Oracle error numbers and takes different paths will not catch the corresponding SQL Server errors after migration. Find all exception handlers that reference Oracle error codes and update them to SQL Server equivalents.

-- Oracle error codes to SQL Server equivalents (common ones)
-- ORA-00001 (unique constraint) → SQL Server 2627
-- ORA-02291 (FK constraint)     → SQL Server 547
-- ORA-01400 (NOT NULL)          → SQL Server 515
-- ORA-00904 (invalid column)    → SQL Server 207
-- ORA-00942 (table not found)   → SQL Server 208

-- Search application code for ORA- error references
-- grep -r "ORA-" ./src/

Missing this check leads to silent exception handling failures — the database throws the SQL Server error, the application handler doesn't match, and exceptions propagate to the top-level handler instead of being handled gracefully at the source.