POST MIGRATION GUIDE From Oracle To SQL Server on Azure VM
1. First 24 Hours: CRITICAL STABILIZATION
Immediately after go-live, do NOT relax.
1.1 Run Smoke Tests
Check:
Can users log in?
Can core queries run?
Are transactions committing?
Test Query
SELECT TOP 10 * FROM dbo.YourCriticalTable;
1.2 Check Errors
EXEC xp_readerrorlog;
1.3 Monitor Active Sessions
SELECT
session_id,
login_name,
status,
cpu_time
FROM sys.dm_exec_sessions;
If something looks wrong, fix NOW — not later.
2. Configure High Availability (HADR)
For mission-critical 10TB–100TB systems, this is NOT optional.
2.1 Choose Best Option
Always On Availability Groups (Best Practice)
Provides:
Automatic failover
Data redundancy
Read replicas
2.2 Prerequisites
SQL Server Enterprise Edition
Windows Server Failover Cluster (WSFC)
Multiple Azure VMs
2.3 Enable Always On
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'hadr enabled', 1;
RECONFIGURE;
2.4 Create Availability Group
CREATE AVAILABILITY GROUP AG_MigrationDB
FOR DATABASE MigrationDB
REPLICA ON
'SQLVM01' WITH (
ENDPOINT_URL = 'TCP://sqlvm01:5022',
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
FAILOVER_MODE = AUTOMATIC
),
'SQLVM02' WITH (
ENDPOINT_URL = 'TCP://sqlvm02:5022',
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
FAILOVER_MODE = AUTOMATIC
);
2.5 Join Secondary Replica
ALTER DATABASE MigrationDB
SET HADR AVAILABILITY GROUP = AG_MigrationDB;
2.6 Verify HADR Status
SELECT * FROM sys.dm_hadr_database_replica_states;
Now your database can survive VM failure.
3. Backup Strategy (LIFE INSURANCE)
3.1 Types of Backups
You MUST implement:
Full backup
Differential backup
Transaction log backup
3.2 Full Backup Script
BACKUP DATABASE MigrationDB
TO DISK = 'E:\Backup\MigrationDB_full.bak'
WITH COMPRESSION, STATS = 10;
3.3 Differential Backup
BACKUP DATABASE MigrationDB
TO DISK = 'E:\Backup\MigrationDB_diff.bak'
WITH DIFFERENTIAL;
3.4 Transaction Log Backup
BACKUP LOG MigrationDB
TO DISK = 'E:\Backup\MigrationDB_log.trn';
3.5 Restore Test (VERY IMPORTANT)
RESTORE VERIFYONLY
FROM DISK = 'E:\Backup\MigrationDB_full.bak';
If you don’t test restore, your backup is useless.
4. Automate Backups (SQL Agent Jobs)
4.1 Create Job
EXEC msdb.dbo.sp_add_job
@job_name = 'FullBackupJob';
Schedule:
Full: daily
Differential: every 6 hours
Log: every 15 minutes
5. Performance Tuning (CRITICAL FOR LARGE DBs)
5.1 Update Statistics
EXEC sp_updatestats;
5.2 Rebuild Indexes
ALTER INDEX ALL ON dbo.BigTable
REBUILD WITH (ONLINE = ON);
5.3 Identify Slow Queries
SELECT TOP 10
total_elapsed_time,
execution_count,
total_elapsed_time / execution_count AS avg_time,
text
FROM sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
ORDER BY avg_time DESC;
5.4 Add Missing Indexes
SELECT * FROM sys.dm_db_missing_index_details;
6. Storage Optimization
6.1 Check File Usage
EXEC sp_spaceused;
6.2 Enable Data Compression
ALTER TABLE dbo.BigTable
REBUILD WITH (DATA_COMPRESSION = PAGE);
Saves huge space for 100TB databases
7. Security Hardening
7.1 Remove Unused Logins
SELECT name FROM sys.sql_logins;
7.2 Enable Encryption (TDE)
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'StrongPassword123';
CREATE CERTIFICATE TDECert
WITH SUBJECT = 'TDE Certificate';
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDECert;
ALTER DATABASE MigrationDB
SET ENCRYPTION ON;
7.3 Audit Access
SELECT * FROM sys.fn_get_audit_file('auditfile', DEFAULT, DEFAULT);
8. Monitoring System (YOUR EYES)
8.1 Key Metrics to Watch
CPU usage
Memory usage
Disk latency
Query performance
8.2 Monitor CPU
SELECT TOP 10 *
FROM sys.dm_exec_query_stats
ORDER BY total_worker_time DESC;
8.3 Monitor Blocking
SELECT * FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0;
8.4 Azure Monitoring
Use:
Azure Monitor
Log Analytics
Alerts
9. Data Validation (FINAL CONFIRMATION)
9.1 Row Count
SELECT COUNT(*) FROM dbo.BigTable;
9.2 Checksum
SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*))
FROM dbo.BigTable;
10. Common Post-Migration Problems
Problem 1: Slow Performance
Causes:
Missing indexes
Bad execution plans
Fix:
Rebuild indexes
Update stats
Problem 2: Application Errors
Cause:
SQL syntax differences
Fix:
Rewrite queries
Problem 3: Storage Running Out
Fix:
Enable compression
Add disks
Problem 4: Blocking & Deadlocks
SELECT * FROM sys.dm_tran_locks;
Fix:
Optimize queries
Reduce transaction size
11. Disaster Recovery Plan
11.1 Restore Database
RESTORE DATABASE MigrationDB
FROM DISK = 'E:\Backup\MigrationDB_full.bak'
WITH RECOVERY;
11.2 Failover Availability Group
ALTER AVAILABILITY GROUP AG_MigrationDB FAILOVER;
12. Long-Term Maintenance Plan
Daily
Check alerts
Review logs
Weekly
Rebuild indexes
Validate backups
Monthly
Performance tuning
Capacity planning
13. Final Production Checklist
Before you say “migration is complete”:
✔ Backups working
✔ HADR configured
✔ Monitoring active
✔ Performance stable
✔ Security hardened
✔ Documentation ready
No comments:
Post a Comment