FOUNDATION PHASE FOR MIGRATING OnPrem SQL Server to AWS EC2 With VLDBs

FOUNDATION PHASE FOR MIGRATING OnPrem SQL Server to AWS EC2 With VLDBs

This is the most important part.

If you rush here, everything later becomes painful.

Think of this phase as: “Measure twice, cut once”

1. Understand What You Are Migrating

Before touching AWS, you must deeply understand your current SQL Server environment.

1.1 Inventory Everything

You need a full inventory of:

* SQL Server versions (2012, 2016, 2019, etc.)

* Edition (Standard, Enterprise)

* Database sizes (10TB, 50TB, 100TB)

* Number of databases

* Dependencies (apps, jobs, ETL, reports)

* Linked servers

* SQL Agent jobs

* SSIS / SSRS / SSAS usage

Script: Get Database Sizes

SELECT 

    name AS DatabaseName,

    size * 8 / 1024 AS SizeMB

FROM sys.master_files

WHERE type = 0;

```

1.2 Identify Critical Databases

Not all databases are equal.

Classify them:

* Tier 1 → Mission-critical (financial, production)

* Tier 2 → Important

* Tier 3 → Non-critical

Focus most effort on Tier 1 (your 10–100TB databases)

1.3 Understand Workload

Ask:

* Peak hours?

* Transactions per second?

* Read vs Write ratio?

* Heavy queries?

Script: Top Queries

SELECT TOP 10

    total_worker_time/execution_count AS AvgCPU,

    execution_count,

    query_hash

FROM sys.dm_exec_query_stats

ORDER BY AvgCPU DESC;

```

2. Choose Migration Strategy

There is no single method. You must choose wisely.

2.1 Migration Types

Option 1: Backup and Restore (Most Common)

* Simple

* Reliable

* Best for large databases

* Requires downtime

Option 2: Log Shipping (Minimal Downtime)

* Continuous sync

* Good for large DBs

* Manual failover

Option 3: AWS DMS (Database Migration Service)

* Continuous replication

* Good for near-zero downtime

* Free tier available (limited)

Option 4: Always On Availability Groups (Advanced)

* Near zero downtime

* Complex

* Best for mission-critical

2.2 Recommendation for 10–100TB

Use Hybrid Approach:

* Initial load → Backup/Restore

* Sync → Log Shipping or DMS

* Cutover → Final log restore

3. Prepare AWS Environment

Now we move into AWS.

3.1 Create EC2 Instance for SQL Server

Key Decisions:

* Instance Type:

  * Memory optimized → r5, r6i

* Storage:

  * Use EBS io2 or gp3

* Network:

  * Place in VPC

3.2 Storage Design (VERY IMPORTANT)

For large databases:

* Data files → separate volume

* Log files → separate volume

* TempDB → separate volume

Best Practice Layout:

* C:\ → OS

* D:\ → Data

* E:\ → Logs

* F:\ → TempDB

* G:\ → Backups

3.3 Enable Enhanced Networking

Improves throughput for large data transfers.

4. Install SQL Server on EC2

Steps:

1. Launch Windows Server EC2

2. Install SQL Server (same or higher version)

3. Configure:

   * Max memory

   * TempDB

   * Parallelism

Script: Set Max Memory

EXEC sp_configure 'max server memory', 50000;

RECONFIGURE;

```

5. Use Free AWS Migration Tools

Here are the most important ones:

5.1 AWS Schema Conversion Tool (SCT)

Use this to:

* Analyze compatibility

* Detect issues before migration

5.2 AWS Database Migration Service (DMS)

Use this for:

* Continuous replication

* Minimal downtime migration

5.3 AWS DataSync

Useful for:

* Moving large backup files (10TB+)

5.4 AWS Snowball (Offline Transfer)

If internet is slow:

* AWS sends a device

* You copy data

* Ship it back

6. Network Planning

Migration fails without good networking.

6.1 Connectivity Options

* VPN 

* Direct Connect (best for large data)

6.2 Open Required Ports

* SQL Server → 1433

* RDP → 3389

7. Backup Strategy Before Migration

You must take full backups before touching anything

7.1 Full Backup

BACKUP DATABASE YourDB

TO DISK = 'G:\Backup\YourDB_full.bak'

WITH COMPRESSION;

```

7.2 Transaction Log Backup

BACKUP LOG YourDB

TO DISK = 'G:\Backup\YourDB_log.trn';

```

7.3 Verify Backup

RESTORE VERIFYONLY

FROM DISK = 'G:\Backup\YourDB_full.bak';

```

8. Security Preparation

8.1 Script Logins

SELECT 

    name, 

    sid 

FROM sys.sql_logins;

```

Use Microsoft script to transfer logins with passwords.

8.2 Permissions

Script:

* Users

* Roles

* Permissions

9. Prepare for Large Database Migration (10–100TB)

This is where things get serious.

9.1 Split Backup Files

BACKUP DATABASE YourDB

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

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

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

WITH COMPRESSION;

```

This improves speed.

9.2 Use Compression

Reduces size significantly.

9.3 Test Restore Time

Always test restore on EC2 before real migration.

10. Dry Run (VERY IMPORTANT)

Never migrate directly.

Do a full test:

1. Backup

2. Transfer

3. Restore

4. Validate

11. Create Migration Runbook

Write down:

* Step order

* Commands

* Downtime window

* Rollback plan

12. Rollback Plan

Always prepare failure plan:

* Keep original DB untouched

* Be ready to switch back

13. Pre-Migration Checklist (Mental Model)

Before moving to nextstepyou should be able to answer:

* Do I know all databases and sizes?

* Did I test backup and restore?

* Is AWS environment ready?

* Is SQL Server configured?

* Do I have login scripts?

* Do I have rollback plan?