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?
