POST-MIGRATION of OnPrem SQL Server to AWS EC2 With VLDBs

 POST-MIGRATION of OnPrem SQL Server to AWS EC2 With VLDBs

Think of this phase as:

“Make the system stable, fast, resilient, and production-ready”

We will go step by step in a logical sequence, just like a real DBA would operate in the first days and weeks after migration.

1. Immediate Post-Migration Validation (First 1–2 Hours)

Before doing anything advanced, confirm the system is correct and usable.

1.1 Verify Database State

SELECT name, state_desc 

FROM sys.databases;

```

Expected Result: `ONLINE`

1.2 Run DBCC CHECKDB (Integrity Check)

This is non-negotiable for mission-critical systems.

DBCC CHECKDB ('YourDB') WITH NO_INFOMSGS, ALL_ERRORMSGS;

```

1.3 Validate Data Consistency

Check:

* Row counts

* Key business tables

* Financial totals (if applicable)

Example:

SELECT COUNT(*) FROM CriticalTable;

```

1.4 Validate Application Connectivity

* Application connects successfully

* No login failures

* No timeout errors

2. Fix Common Post-Migration Issues

---

2.1 Orphan Users

ALTER USER username WITH LOGIN = loginname;

```

2.2 Missing SQL Agent Jobs

Recreate jobs:

USE msdb;

SELECT name FROM sysjobs;

```

2.3 Fix Broken Linked Servers

EXEC sp_addlinkedserver 

    @server = 'LinkedServerName',

    @srvproduct = '',

    @provider = 'SQLNCLI',

    @datasrc = 'RemoteServer';

```

3. Configure BACKUP STRATEGY (CRITICAL)

If you skip this, you are running blind.

3.1 Full Backup Strategy

BACKUP DATABASE YourDB

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

WITH COMPRESSION;

```

Frequency:

* Daily (large DBs: weekly full + differential)

3.2 Differential Backup

BACKUP DATABASE YourDB

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

WITH DIFFERENTIAL;

```

3.3 Transaction Log Backup

BACKUP LOG YourDB

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

```

Frequency:

* Every 5–15 minutes

3.4 Store Backups in AWS S3

Best practice:

* Copy backups to S3 for durability

* Use lifecycle policies

4. Setup High Availability (HADR)

Now we make the system resilient.

OPTION 1: ALWAYS ON AVAILABILITY GROUPS (BEST PRACTICE)

4.1 Architecture

* Primary replica → EC2 instance 1

* Secondary replica → EC2 instance 2

* Optional → third replica

4.2 Enable Always On

EXEC sp_configure 'show advanced options', 1;

RECONFIGURE;

EXEC sp_configure 'Always On Availability Groups', 1;

RECONFIGURE;

```

4.3 Create Availability Group

CREATE AVAILABILITY GROUP YourAG

FOR DATABASE YourDB

REPLICA ON 

    'PrimaryServer' WITH (

        ENDPOINT_URL = 'TCP://PrimaryServer:5022',

        AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,

        FAILOVER_MODE = AUTOMATIC

    ),

    'SecondaryServer' WITH (

        ENDPOINT_URL = 'TCP://SecondaryServer:5022',

        AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,

        FAILOVER_MODE = AUTOMATIC

    );

```

4.4 Join Secondary Replica

ALTER DATABASE YourDB 

SET HADR AVAILABILITY GROUP = YourAG;

```

4.5 Test Failover

ALTER AVAILABILITY GROUP YourAG FAILOVER;

```

OPTION 2: LOG SHIPPING (Simpler DR)

Setup Steps:

1. Backup logs from primary

2. Copy to secondary

3. Restore with NORECOVERY

OPTION 3: AWS Multi-AZ (Manual Design on EC2)

* Use multiple EC2 instances

* Combine with Always On

5. Performance Tuning (VERY IMPORTANT)

After migration, performance often changes.

5.1 Update Statistics

EXEC sp_updatestats;

```

5.2 Rebuild Indexes

ALTER INDEX ALL ON YourTable REBUILD;

```

5.3 Check Slow Queries

SELECT TOP 10

    total_elapsed_time/execution_count AS AvgTime,

    execution_count

FROM sys.dm_exec_query_stats

ORDER BY AvgTime DESC;

```

5.4 Configure MAXDOP

EXEC sp_configure 'max degree of parallelism', 8;

RECONFIGURE;

```

5.5 Optimize TempDB

* Multiple data files

* Equal size

6. Monitoring and Alerting

6.1 Use AWS CloudWatch

Monitor:

* CPU

* Disk IOPS

* Network

6.2 SQL Server Monitoring Queries

SELECT 

    wait_type, 

    wait_time_ms

FROM sys.dm_os_wait_stats

ORDER BY wait_time_ms DESC;

```

6.3 Enable Alerts

* Disk full

* Job failure

* High CPU

7. Security Hardening

7.1 Disable Unused Accounts

ALTER LOGIN username DISABLE;

```

7.2 Enforce Encryption

* Use SSL connections

* Enable TDE if required

7.3 Restrict Ports

Only allow:

* 1433 (SQL)

* 3389 (RDP, restricted)

8. Cost Optimization (AWS-Specific)

8.1 Right-Size EC2

* Avoid over-provisioning

* Monitor usage

8.2 Optimize Storage

* Use gp3 if possible

* Reduce unused volumes

8.3 Stop Non-Production Servers

9. Disaster Recovery Planning

9.1 Cross-Region Backup

* Copy backups to another AWS region

9.2 Test Restore Regularly

Never assume backups work—test them.

9.3 Define RPO and RTO

* RPO → Data loss tolerance

* RTO → Recovery time

10. Real DBA Daily Operations

After migration, your daily job includes:

Daily Tasks

* Check backups

* Check failed jobs

* Monitor performance

Weekly Tasks

* Index maintenance

* Review slow queries

Monthly Tasks

* Capacity planning

* Cost review

11. Troubleshooting Common Issues

## Issue: High CPU

Fix:

* Identify query

* Optimize index

---

## Issue: Slow Disk

Fix:

* Increase IOPS

* Move to faster storage

---

## Issue: Blocking

SELECT * FROM sys.dm_exec_requests;

```

---

## Issue: Deadlocks

Enable trace:

DBCC TRACEON (1222, -1);

```

---

12. Final Mental Model (Production Readiness)

You are production-ready if:

* Backups are running

* HA is configured

* Monitoring is active

* Performance is stable

* DR is tested

---

SUMMARY

BEFORE MIGRATION:

* Inventory complete

* AWS ready

* Backup tested

* Migration plan documented

---

DURING MIGRATION:

* Full restore completed

* Logs continuously applied

* Final cutover executed

* Application switched

---

AFTER MIGRATION:

* DB validated

* Backups configured

* HA implemented

* Performance tuned

* Monitoring active