Execution Phase In Migrating Oracle Database to SQL Server on Azure VM
1. What “During Migration” Really Means
This phase includes:
Initial data load (bulk transfer)
Continuous sync (change tracking)
Monitoring performance and errors
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:
Do initial bulk load
Keep syncing changes
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:
Go to Azure Portal
Create Azure Database Migration Service
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”
