Foundation & Planning Phase For Migrating Oracle Database to SQL Server on Azure VM

Foundation & Planning Phase  For Migrating Oracle Database to SQL Server on Azure VM

1. First, Understand What You Are Really Doing

Let’s be honest: this is not just “moving data.”

You are doing:

• Platform change (Oracle → SQL Server)

• Infrastructure change (on-prem → Azure VM)

• Possibly architecture change (monolith → distributed)

• Risk-heavy migration (because data is mission critical)

So your job is:

Move everything safely, with minimal downtime, and without breaking the business.

---

2. Key Concepts You Must Know First

Before touching anything, understand these:

2.1 Source vs Target

• Source = Oracle Database

• Target = SQL Server on Azure VM

---

2.2 Types of Migration

You will choose one:

1. Offline Migration (Simple but downtime heavy)

   • Stop Oracle

   • Move data

   • Start SQL Server

2. Online Migration (Complex but minimal downtime)

   • Data moves while system is running

   • Final sync at cutover

For 10TB–100TB, you almost always need online or hybrid migration

---

2.3 Migration Layers

You are migrating:

1. Schema (tables, indexes)

2. Data

3. Code (PL/SQL → T-SQL)

4. Jobs

5. Security

6. Applications

---

3. Tools You May Use (Free Azure Tools)

These are your best friends:

3.1 SQL Server Migration Assistant (SSMA for Oracle)

• Converts schema

• Converts PL/SQL → T-SQL

• Migrates data

---

3.2 Azure Data Studio

• Lightweight management tool

• Works with extensions

---

3.3 Azure Database Migration Service (DMS)

• Online migration

• Minimal downtime

• Handles large databases

---

3.4 Oracle SQL Developer (Free)

• Export schema/data

• Analyze Oracle structures

---

3.5 Azure CLI / PowerShell

• Automate deployment

---

4. Step-by-Step BEFORE Migration Plan

---

Step 1: Inventory Everything (CRITICAL)

Do not skip this. You need to know exactly what exists.

---

4.1 Run Oracle Inventory Queries

List all schemas

SELECT username FROM dba_users;

---

List tables and sizes

SELECT 
    owner,
    segment_name,
    ROUND(bytes/1024/1024/1024,2) AS size_gb
FROM dba_segments
WHERE segment_type='TABLE'
ORDER BY size_gb DESC;

---

List stored procedures

SELECT owner, object_name, object_type
FROM dba_objects
WHERE object_type IN ('PROCEDURE','FUNCTION','PACKAGE');

---

Check database size

SELECT 
    ROUND(SUM(bytes)/1024/1024/1024,2) AS total_size_gb
FROM dba_data_files;

---

 Save all these outputs. This becomes your migration blueprint.

---

Step 2: Identify Migration Complexity

You must classify objects:

---

5 Levels of Complexity

Level 1 (Easy)

• Tables

• Simple views

Level 2 (Medium)

• Indexes

• Constraints

Level 3 (Hard)

• PL/SQL procedures

• Triggers

Level 4 (Very Hard)

• Packages

• Oracle-specific functions

Level 5 (Extreme)

• Advanced features:

  • Partitioning

  • RAC

  • Materialized views

---

Your biggest risk is PL/SQL conversion

---

Step 3: Prepare Azure Environment

---

3.1 Create Azure VM for SQL Server

Recommended for large DB:

• OS: Windows Server

• SQL Server: Enterprise Edition

• Storage: Premium SSD or Ultra Disk

---

3.2 Disk Layout Best Practice

Separate disks:

• Data files

• Log files

• TempDB

---

3.3 PowerShell Script to Create VM

New-AzVM `
  -ResourceGroupName "DBMigrationRG" `
  -Name "SQLVM01" `
  -Location "EastUS" `
  -VirtualNetworkName "VNet1" `
  -SubnetName "Subnet1" `
  -SecurityGroupName "NSG1" `
  -PublicIpAddressName "PublicIP1" `
  -OpenPorts 3389

---

Step 4: Install SQL Server Properly

---

4.1 Enable Required Features

EXEC sp_configure 'max server memory', 50000;
RECONFIGURE;

EXEC sp_configure 'cost threshold for parallelism', 50;
RECONFIGURE;

---

4.2 Configure TempDB

ALTER DATABASE tempdb 
ADD FILE (NAME = tempdev2, FILENAME = 'D:\tempdb2.ndf', SIZE = 10GB);

---

Step 5: Install Migration Tools

---

5.1 Install SSMA for Oracle

Steps:

1. Download SSMA

2. Install Oracle client

3. Connect to Oracle

4. Connect to SQL Server

---

Step 6: Schema Conversion (First Dry Run)

---

6.1 Create SSMA Project

• Select Oracle

• Connect source

• Connect target

---

6.2 Convert Schema

Click: Convert Schema

---

6.3 Review Conversion Report

You will see:

• Success

• Warnings

• Errors

---

Common Errors

• Unsupported functions

• Data type mismatch

• PL/SQL conversion issues

---

Step 7: Fix Data Type Mapping

---

Common Mapping Issues

Oracle	SQL Server
NUMBER	DECIMAL
DATE	DATETIME
CLOB	VARCHAR(MAX)

---

Example Fix

-- Oracle
NUMBER(10,2)

-- SQL Server
DECIMAL(10,2)

---

Step 8: Plan Data Migration Strategy

---

For 10TB–100TB databases:

You must use:

Hybrid Approach

1. Initial bulk load

2. Incremental sync

3. Final cutover

---

Options

Option 1: SSMA (Small DBs)

Not ideal for large data

---

Option 2: Azure Data Migration Service (BEST)

✔ Online migration
✔ Minimal downtime

---

Step 9: Network Preparation

---

9.1 Ensure Connectivity

• Open ports:

  • Oracle: 1521

  • SQL Server: 1433

---

9.2 Test Connection

tnsping ORCL

Test-NetConnection -ComputerName SQLVM01 -Port 1433

---

Step 10: Backup Strategy BEFORE Migration

Never start without backup.

---

10.1 Oracle Full Backup

BACKUP DATABASE PLUS ARCHIVELOG;

---

10.2 Export Backup (Optional)

expdp system/password FULL=Y DIRECTORY=backup_dir DUMPFILE=full.dmp

---

Step 11: Performance Baseline (VERY IMPORTANT)

You must know current performance.

---

Capture Oracle Metrics

SELECT * FROM v$sysstat;

---

Capture Top Queries

SELECT sql_text, elapsed_time
FROM v$sql
ORDER BY elapsed_time DESC;

---

After migration, you will compare this.

---

Step 12: Risk Planning

---

Most Common Problems 

1. Data loss

Solution: multiple backups

---

2. Downtime too long

Solution: online migration

---

3. Slow migration

Solution:

• Parallel processing

• Partitioning

---

4. PL/SQL not working

Solution:

• Manual rewrite

---

5. Storage bottleneck

Solution:

• Use Premium SSD

---

Step 13: Create Migration Runbook

You must write your own script like:

1. Stop writes

2. Sync data

3. Validate

4. Switch app

---

Example:

Day 1:
- Schema conversion

Day 5:
- Initial load

Day 10:
- Sync

Day 15:
- Cutover

---

Step 14: Testing Strategy

---

Types of Testing

1. Unit testing

2. Data validation

3. Performance testing

4. User acceptance testing

---

Step 15: Validation Queries

---

Row count comparison

-- Oracle
SELECT COUNT(*) FROM employees;

-- SQL Server
SELECT COUNT(*) FROM employees;