Saturday, May 9, 2026

Execution Phase In Migrating Oracle Database to SQL Server on Azure VM WITH VLDBs

Execution Phase In Migrating Oracle Database to SQL Server on Azure VM

1. What “During Migration” Really Means

This phase includes:

  1. Initial data load (bulk transfer)

  2. Continuous sync (change tracking)

  3. Monitoring performance and errors

  4. Final cutover (switch users to SQL Server on EC2)

2. Choose Your Migration Method (Final Decision)

For large databases:

Best Practice: Hybrid Online Migration

You will:

  1. Do initial bulk load

  2. Keep syncing changes

  3. Do final cutover with minimal downtime

3. Architecture Overview 

You now have:

  • Oracle (still running, production)

  • SQL Server on Azure VM (target)

  • Migration tool (Azure DMS or SSMA)

4. Step 1: Prepare Target Database (SQL Server)

4.1 Create Empty Database

CREATE DATABASE MigrationDB
ON 
(
    NAME = MigrationDB_Data,
    FILENAME = 'D:\SQLData\MigrationDB.mdf',
    SIZE = 500GB,
    FILEGROWTH = 10GB
)
LOG ON
(
    NAME = MigrationDB_Log,
    FILENAME = 'E:\SQLLogs\MigrationDB.ldf',
    SIZE = 200GB,
    FILEGROWTH = 5GB
);

4.2 Set Recovery Model

For large migration:

ALTER DATABASE MigrationDB SET RECOVERY BULK_LOGGED;

This improves bulk insert speed

5. Step 2: Deploy Schema (From SSMA)

5.1 Apply Converted Schema

In SSMA:

 Click “Synchronize with Database”

5.2 Validate Objects

SELECT name, type_desc
FROM sys.objects
ORDER BY type_desc;

6. Step 3: Initial Data Load (Bulk Migration)

Option A (Recommended): Azure Data Migration Service (DMS)

6.1 Create Migration Project

Steps:

  1. Go to Azure Portal

  2. Create Azure Database Migration Service

  3. Choose:

    • Source: Oracle

    • Target: SQL Server

6.2 Configure Migration

  • Mode: Online

  • Select schemas

  • Enable parallel load

6.3 Start Migration

This begins full data load while Oracle is still running

Option B: SSMA Data Migration (Smaller Segments)

6.4 Migrate Data via SSMA

  • Right-click schema

  • Select “Migrate Data”

For 10TB+, do NOT rely only on SSMA

7. Step 4: Parallel Data Transfer (VERY IMPORTANT)

For large databases, you must split work.

7.1 Example Strategy

Instead of  one huge table transfer

Do: Partition-based transfer

7.2 Example Parallel Insert Script

INSERT INTO dbo.BigTable WITH (TABLOCK)
SELECT *
FROM OPENQUERY(ORACLE_LINKED_SERVER, 
'SELECT * FROM BIG_TABLE WHERE PARTITION_ID = 1');

Run multiple jobs:

  • Partition 1

  • Partition 2

  • Partition 3

This reduces migration time from weeks → days

8. Step 5: Enable Change Tracking (Sync Phase)

You must capture ongoing changes.

Oracle Side (Log-based approach)

Use:

  • Archive logs

  • Or CDC tools (via DMS)

SQL Server Side

ALTER DATABASE MigrationDB
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 7 DAYS, AUTO_CLEANUP = ON);

9. Step 6: Continuous Sync

Azure DMS will:

  • Capture inserts

  • Capture updates

  • Capture deletes

This keeps SQL Server almost up to date

10. Step 7: Monitor Migration (CRITICAL)

10.1 Monitor SQL Server Activity

SELECT 
    session_id,
    status,
    command,
    cpu_time,
    total_elapsed_time
FROM sys.dm_exec_requests;

10.2 Check Wait Stats

SELECT wait_type, wait_time_ms
FROM sys.dm_os_wait_stats
ORDER BY wait_time_ms DESC;

10.3 Monitor Disk Performance

SELECT * FROM sys.dm_io_virtual_file_stats(NULL, NULL);

10.4 Azure Monitoring

Use:

  • Azure Monitor

  • Log Analytics

11. Step 8: Handle Common Migration Problems

Problem 1: Migration is TOO SLOW

Causes:

  • Network bottleneck

  • Disk I/O slow

Fix:

  • Increase VM size

  • Use Premium SSD

  • Increase parallel jobs

Problem 2: Data Type Errors

Example:

  • Oracle DATE vs SQL DATETIME

Fix:

Manual conversion

Problem 3: Deadlocks

SELECT * FROM sys.dm_tran_locks;

Reduce batch size

Problem 4: Transaction Log Full

Fix:

BACKUP LOG MigrationDB 
TO DISK = 'E:\Backup\log.trn';

12. Step 9: Data Validation (While Running)

12.1 Row Count Check

SELECT COUNT(*) FROM dbo.BigTable;

Compare with Oracle

12.2 Checksum Validation

SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) 
FROM dbo.BigTable;

13. Step 10: Final Cutover (THE BIG MOMENT)

This is where everything switches.

13.1 Announce Downtime

Example:

“System will be unavailable for 30 minutes”

13.2 Stop Application Writes

  • Disable app connections

  • Put system in read-only mode

13.3 Final Sync

Run last sync via DMS

13.4 Verify Data

  • Row counts match

  • No missing records

13.5 Switch Connection String

From:

OracleConnectionString

To:

SQLServerConnectionString

13.6 Bring System Online

14. Step 11: Immediate Post-Cutover Checks

Check 1: Login Success

SELECT name FROM sys.sql_logins;

Check 2: Application Queries

Run test queries

Check 3: Performance

Compare with baseline

15. Real-World Migration Timeline Example

Day 1–3:
Schema conversion

Day 4–10:
Initial data load

Day 11–14:
Continuous sync

Day 15:
Final cutover (30–60 min downtime)

16. Emergency Rollback Plan (VERY IMPORTANT)

If something fails:

Option 1: Revert to Oracle

  • Switch connection back

  • Resume operations

Option 2: Partial rollback

  • Fix issues

  • Resume sync

17. War Room Execution Script (Real-Life)

During cutover:

DBA:

“Stopping writes now”

App Team:

“Confirmed, no active transactions”

DBA:

“Running final sync”

DBA:

“Validation complete”

DBA:

“Switching connection strings”

Team:

“System live”


at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Execution Phase In Migrating Oracle Database to SQL Server on Azure VM WITH VLDBs

Execution Phase In Migrating Oracle Database to SQL Server on Azure VM 1. What “During Migration” Really Means This phase includes: Initial ...