---

The Ultimate Guide to AWS RDS SQL Server Administration With VLDBs (Part 4)

24. The Philosophy of Resilience: Stoicism in the Server Room

As a DBA, you are the guardian of the "Truth" (the data). Applying the logic of thinkers like Plotinus or the Stoics helps in high-pressure DR scenarios. They taught that we should focus only on what we can control. You cannot control a hurricane hitting a Virginia data center, but you can control the readiness of your failover scripts.

A "Resilient" system is one that accepts failure as a statistical certainty and builds the "Pilot Light" to reignite the business when the primary spark goes out.

---

25. Defining Your DR Metrics (RTO & RPO)

Before running a drill, you must define what success looks like. For a 100TB database, these 

numbers are harder to hit than for a 100GB one.

• Recovery Point Objective (RPO): How much data can you afford to lose? (e.g., 5 minutes of transactions).

• Recovery Time Objective (RTO): How long can the "Open for Business" sign be turned off? (e.g., 4 hours).

The 100TB Reality Check: Restoring a 100TB database from a snapshot to a new region can 

take 12 to 24 hours just for the storage to "hydrate" from S3. If your RTO is 4 hours, a simple "Backup and Restore" strategy will fail. You must use a Cross-Region Read Replica.

---

26. Multi-Region Architecture: The Pilot Light vs. Warm Standby

For mission-critical 100TB systems, we typically choose between these two patterns:

A. The Pilot Light (Cost-Effective DR)

You replicate your Automated Backups to a secondary region (e.g., from us-east-1 to us-west-2). There is no database "running" in the second region.

• Pros: Very cheap. You only pay for storage.

• Cons: High RTO. You have to wait for a 100TB restore to finish before you can point your apps to it.

B. The Warm Standby (Mission-Critical DR)

You create a Cross-Region Read Replica. This is a live, running SQL Server instance in a different region that is constantly receiving transaction logs from your Primary.

• Pros: Near-zero RPO (usually < 1 second lag) and low RTO (minutes to promote).

• Cons: Expensive. You pay for a full second instance 24/7.

---

27. Setting Up a Cross-Region Read Replica

If you have a 100TB database, AWS uses Distributed Availability Groups under the hood to keep the replica in sync.

Step-by-Step Configuration:

1. Encryption: Ensure your Primary is encrypted with a KMS key. You must create a corresponding KMS key in the DR region.

2. Network: Use VPC Peering or AWS Transit Gateway to allow the two regions to talk over the private AWS backbone.

3. The Command: In the RDS Console, select your Primary -> Actions -> Create Read Replica. Select the destination Region and your R-series instance class.

---

28. The Advanced DR Drill: "Operation Blackout"

A DR plan that isn't tested is just a wish. You should perform a "Planned Failover" drill every 6 months.

Drill 1: The Planned Switchover (No Data Loss)

This tests your application's ability to reconnect and your team's ability to promote the replica.

1. Quiesce Traffic: Stop the application servers.

2. Verify Lag: Check the ReplicaLag metric in CloudWatch. It must be 0.

3. Promote: In the RDS Console, select the Replica -> Actions -> Promote.

4. Reconfigure: Update the application connection strings to the new DR Endpoint.

5. Validate: Run a row-count check on your largest 100TB tables.

Script: Monitoring Replica Lag via SQL

Run this on the Replica instance to see how many seconds you are behind the Primary:

SELECT 
    DB_NAME(database_id) AS [Database], 
    replication_state_desc, 
    last_redone_time, 
    DATEDIFF(second, last_redone_time, GETDATE()) AS [Lag_In_Seconds]
FROM sys.dm_hadr_database_replica_states
WHERE is_local = 1;

---

29. The 100TB "Ghost" Challenge: Storage Hydration

When you restore a 100TB snapshot or promote a replica that hasn't been "touched" in a while, you may encounter Lazy Loading.

• What it is: AWS restores the metadata immediately, but the actual data blocks are pulled from S3 only when they are first accessed.

• The Symptom: Your first few hours in the DR region will have terrible performance (high PAGEIOLATCH waits).

The Senior DBA Hack: Use a "Hydration Script" to force the storage to pull data from S3 before the users arrive.

-- Run a full table scan on a non-indexed column to force every page into memory/storage
SELECT COUNT_BIG(*) FROM HugeTable WITH (NOLOCK) WHERE SomeColumn = 'ForceHydrate';

---

30. Post-Failover: Repairing the "Orphaned" Landscape

When you fail over to a new region, your SQL Logins exist, but they are often disconnected from the Database Users.

Script: Automated Fix for Orphaned Users

DECLARE @Username NVARCHAR(255)
DECLARE UserCursor CURSOR FOR 
    SELECT name FROM sys.database_principals WHERE type = 'S' AND name NOT IN ('guest', 'sys', 'information_schema')

OPEN UserCursor
FETCH NEXT FROM UserCursor INTO @Username

WHILE @@FETCH_STATUS = 0
BEGIN
    BEGIN TRY
        EXEC sp_change_users_login 'Auto_Fix', @Username
    END TRY
    BEGIN CATCH
        -- Skip if user doesn't have a matching login
    END CATCH
    FETCH NEXT FROM UserCursor INTO @Username
END

CLOSE UserCursor
DEALLOCATE UserCursor

---

31. Automating DR with AWS Route 53

For a truly "Senior" setup, you shouldn't be manually changing connection strings in .config files. Use Route 53 CNAMEs.

1. Create a Private Hosted Zone (e.g., db.internal).

2. Create a CNAME record: prod-sql.db.internal pointing to the RDS Primary Endpoint.

3. During a DR event, you only update one record in Route 53 to point to the DR Endpoint. Every application server will follow the new path within 60 seconds (depending on TTL).

---

32. Summary Checklist: The DR Drill

Step	Action	Responsibility
T-Minus 1 Hour	Validate ReplicaLag is < 10 seconds.	DBA
Start	Redirect Traffic to "Maintenance Page".	Web/App Team
Promotion	Execute "Promote Read Replica" in AWS.	DBA
Networking	Update Route 53 CNAME to DR Endpoint.	DevOps/DBA
Validation	Run "Orphaned User" script & "Hydration" scan.	DBA
Sign-off	Business units verify data integrity.	QA / Product

---