EXECUTION PHASE OF MIGRATING OnPrem SQL Server to AWS EC2 With VLDBs

EXECUTION PHASE OF MIGRATING OnPrem SQL Server to AWS EC2 With VLDBs

Think of this phase as:

“Move data safely, keep systems consistent, and switch with minimal downtime”

1. Choose the Execution Flow 

For large, mission-critical databases, the safest pattern is:

Hybrid Migration Flow

1. Full Backup → Restore on EC2

2. Continuous Sync → Log Shipping or AWS DMS

3. Final Cutover → Apply last logs + switch application

This avoids long downtime.

2. Step 1: Take Initial Full Backup (SOURCE SERVER)

This is your starting point.

Script: Full Backup (Multi-file for Speed)

BACKUP DATABASE YourDB

TO DISK = 'G:\Backup\YourDB_1.bak',

   DISK = 'G:\Backup\YourDB_2.bak',

   DISK = 'G:\Backup\YourDB_3.bak',

   DISK = 'G:\Backup\YourDB_4.bak'

WITH 

    COMPRESSION,

    STATS = 10;

```

Why this matters:

* Parallel writes → faster backup

* Compression → smaller transfer size

* Essential for 10–100TB DBs

3. Step 2: Transfer Backup to AWS EC2

Now you must move huge data safely.

Option A: Fast Network Transfer

Use:

* SMB copy

* Robocopy

Example:

```

robocopy G:\Backup \\EC2-IP\Backup /E /Z /MT:16

```

Option B: AWS DataSync (Recommended for Large Data)

* Automates transfer

* Handles retries

* Optimized for large datasets

Option C: AWS Snowball (Offline Transfer)

If bandwidth is slow:

* Copy data to device

* Ship to AWS

4. Step 3: Restore Database on EC2 (TARGET SERVER)

Now restore the database.

Script: Restore with NORECOVERY

RESTORE DATABASE YourDB

FROM DISK = 'G:\Backup\YourDB_1.bak',

     DISK = 'G:\Backup\YourDB_2.bak',

     DISK = 'G:\Backup\YourDB_3.bak',

     DISK = 'G:\Backup\YourDB_4.bak'

WITH 

    MOVE 'YourDB_Data' TO 'D:\Data\YourDB.mdf',

    MOVE 'YourDB_Log' TO 'E:\Logs\YourDB.ldf',

    NORECOVERY,

    STATS = 10;

```

Important:

* `NORECOVERY` keeps DB in restoring mode

* Required for applying logs later

5. Step 4: Start Continuous Synchronization

Now we keep EC2 updated while production is still running.

OPTION 1: LOG SHIPPING (Most Reliable)

5.1 Take Transaction Log Backups (Source)

BACKUP LOG YourDB

TO DISK = 'G:\Backup\YourDB_log_1.trn'

WITH COMPRESSION;

```

5.2 Copy Logs to EC2

Use:

robocopy G:\Backup \\EC2-IP\Backup *.trn /MT:16

```

5.3 Restore Logs on EC2

RESTORE LOG YourDB

FROM DISK = 'G:\Backup\YourDB_log_1.trn'

WITH NORECOVERY;

```

Automate This Loop

Repeat every:

* 5 minutes (high critical)

* 15 minutes (normal)

OPTION 2: AWS Database Migration Service (DMS)

When to Use DMS

* Near-zero downtime needed

* Mixed workloads

* Continuous replication

Steps:

1. Create DMS replication instance

2. Define source (on-prem SQL Server)

3. Define target (EC2 SQL Server)

4. Enable CDC (Change Data Capture)

5. Start replication

Key Setting:

* Full load + ongoing replication

6. Monitor Synchronization

You must confirm:

* No missing logs

* No delays

* No failures

Check Restore Status

SELECT 

    database_id,

    state_desc

FROM sys.databases

WHERE name = 'YourDB';

```

7. Performance Considerations During Sync

7.1 Avoid Network Bottlenecks

* Use enhanced networking

* Monitor bandwidth

7.2 Monitor Disk Throughput

* Use CloudWatch (AWS)

* Ensure IOPS is sufficient

7.3 Monitor SQL Server Load

SELECT 

    cpu_count, 

    scheduler_count 

FROM sys.dm_os_sys_info;

```

8. Pre-Cutover Validation (VERY IMPORTANT)

Before switching, verify:

8.1 Row Counts

SELECT COUNT(*) FROM YourTable;

```

Compare source vs target.

8.2 DBCC CHECKDB

DBCC CHECKDB('YourDB');

```

8.3 Check Users

Fix orphan users:

EXEC sp_change_users_login 'Auto_Fix', 'username';

```

9. FINAL CUTOVER (Critical Moment)

This is where downtime happens—but we minimize it.

Step-by-Step Cutover

9.1 Announce Downtime

Inform users:

* Application will be offline

* Freeze transactions

9.2 Stop Application Writes

* Disable app connections

* Or set DB to read-only

9.3 Take Final Log Backup

BACKUP LOG YourDB

TO DISK = 'G:\Backup\YourDB_final.trn'

WITH NORECOVERY;

```

9.4 Transfer Final Log

Copy to EC2.

9.5 Apply Final Log

RESTORE LOG YourDB

FROM DISK = 'G:\Backup\YourDB_final.trn'

WITH RECOVERY;

```

Important:

* `WITH RECOVERY` brings DB online

10. Switch Application to EC2

Update:

* Connection strings

* DNS

* Application configs

11. Post-Cutover Smoke Test

Immediately test:

* Login works

* Queries run

* Data is correct

12. Common Problems (And Fixes)

Problem 1: Slow Restore

Fix:

* Use multiple files

* Increase IOPS

Problem 2: Missing Log Files

Fix:

* Re-copy logs

* Maintain sequence

Problem 3: Orphan Users

Fix:

ALTER USER username WITH LOGIN = loginname;

```

Problem 4: Performance Drop

Fix:

* Update statistics

EXEC sp_updatestats;

```

13. Real Downtime Optimization Tips

Reduce Downtime by:

* Frequent log backups

* Smaller final log

* Pre-tested scripts

* Automation

14. During Migration Checklist (Mental Model)

Before moving to next stage, confirm:

* Full backup restored successfully

* Logs continuously applied

* No data loss

* Final log applied successfully

* Application switched

* Users validated system