The Ultimate Guide to AWS RDS SQL Server Administration WITH VLDBs (Part 3)

16. The Physics of 100TB: Why You Can’t Just "Upload"

When a database is "Mission Critical," your downtime window is likely measured in hours, not weeks. To bridge the gap between your on-premise data center and the AWS Cloud, we use a hybrid approach:

1. Bulk Load (The Ship): Physical movement of data using AWS Snowball Edge.

2. Ongoing Sync (The Sync): Real-time replication using AWS Database Migration Service (DMS).

---

17. Phase 1: Pre-Migration Audit

Before you move a single byte, you must ensure the destination can handle the cargo.

A. The 256TB Multi-Volume Advantage

As of 2026, AWS RDS for SQL Server supports Multi-Volume Storage, allowing you to scale up to 256 TiB.

• The Primary Volume: Holds the OS and system databases (up to 64 TiB).

• Additional Volumes: You can attach up to three more volumes (64 TiB each).

• Strategy: For a 100TB database, you will provision a primary volume and at least one additional volume. Best Practice: Place your Transaction Log on a separate high-performance io2 volume to prevent data-write latency from slowing down log-write speed.

B. Compatibility Check

Run the Microsoft Data Migration Assistant (DMA) against your on-premise instance. It will flag:

• Unsupported Features: (e.g., Windows Authentication with local accounts).

• Breaking Changes: (e.g., deprecated SQL syntax).

• Connectivity: Ensure your local firewall allows the Snowball Edge and AWS DMS to "talk" to your SQL instance.

---

18. Phase 2: The Bulk Load (AWS Snowball Edge)

Think of Snowball Edge as a rugged, encrypted hard drive the size of a suitcase that AWS mails to your office.

Step 1: Ordering the Device

In the AWS Console, go to AWS Snow Family.

• Select Snowball Edge Storage Optimized.

• Choose your S3 bucket as the destination.

• AWS will ship the device to you. It arrives in a hardened case with a built-in E-Ink shipping label.

Step 2: Taking Compressed, Multi-File Backups

To fill a 100TB Snowball efficiently, you must use Striped Backups. Writing to one single 100TB .bak file is slow. Writing to fifty 2TB files is significantly faster.

Script: Striped Backup with Compression

BACKUP DATABASE [YourMissionCriticalDB]
TO DISK = 'Z:\Backup\Part1.bak',
   DISK = 'Z:\Backup\Part2.bak',
   DISK = 'Z:\Backup\Part3.bak',
   DISK = 'Z:\Backup\Part4.bak'
WITH COMPRESSION, STATS = 5, CHECKSUM;

• Note: Use the CHECKSUM flag. If a file gets corrupted during shipping, you want to know before you start the restore.

Step 3: Loading the Snowball

Once the device arrives, plug it into your network. Use the AWS OpsHub software (a GUI for Snowball) to copy your .bak files onto the device.

• Security: Data is encrypted as it hits the Snowball. Even if the truck is hijacked, your data is safe.

• Shipping: Once loaded, the E-Ink label automatically updates to the AWS return address. Drop it off at UPS/FedEx.

---

19. Phase 3: The Native Restore in RDS

\

Once AWS receives your Snowball, they upload the files to your Amazon S3 bucket. Now, you tell RDS to "pull" that data in.

Step 1: The S3 Integration

Attach an IAM Role to your RDS instance that has s3:ListBucket and s3:GetObject permissions for your backup bucket.

Step 2: Running the Restore

You cannot use the standard RESTORE DATABASE command in RDS. You must use the RDS-specific stored procedure.

Script: Restoring a Striped Backup from S3

exec msdb.dbo.rds_restore_database
        @restore_db_name='YourMissionCriticalDB',
        @s3_arn_to_restore_from='arn:aws:s3:::your-s3-bucket/Part*.bak',
        @with_norecovery=1; -- CRITICAL: Use 1 if you plan to sync logs later

Wait! If you use @with_norecovery=1, the database stays in a "Restoring" state. This is good because it allows you to apply later transaction logs or use DMS to catch up.

---

20. Phase 4: Ongoing Sync (AWS DMS)

While your Snowball was in the mail, your on-premise database was still being used. New data was added. AWS Database Migration Service (DMS) captures these changes and sends them to RDS in real-time.

Step 1: The Replication Instance

Provision a DMS Replication Instance. Think of this as the "Engine" that sits between your office and AWS. For a 100TB migration, choose a high-memory instance like the dms.r6i.xlarge.

Step 2: The Endpoints

• Source Endpoint: Your on-premise SQL Server.

• Target Endpoint: Your new AWS RDS SQL Server.

Step 3: The Replication Task

Create a task for CDC (Change Data Capture). DMS will read the SQL Server Transaction Log and "replay" every INSERT, UPDATE, and DELETE on the RDS side.

---

21. Phase 5: The Final Cutover

The moment of truth. Your RDS database is now "in sync" with on-premise.

1. Stop Traffic: Put your on-premise application in maintenance mode.

2. Verify Sync: In the DMS Console, wait until the "Latency" metric hits zero. This means the cloud has caught up.

3. Complete the Restore: If you used @with_norecovery=1 earlier, run the final recovery command:

   
   

   exec msdb.dbo.rds_finish_restore @db_name='YourMissionCriticalDB';
   

4. Update DNS: Point your application connection strings to the RDS Endpoint (e.g., mydb.cxyz.us-east-1.rds.amazonaws.com).

---

22. Post-Migration Checklist: The First 24 Hours

• Statistics: After a 100TB migration, your statistics are likely stale. Run EXEC sp_updatestats immediately.

• Permissions: Re-map your "Orphaned Users." When you restore a DB, the SIDs (Security Identifiers) from on-premise won't match the SIDs on RDS.

• Backups: Immediately take a manual snapshot. This is your "Ground Zero" backup.

Script: Finding Orphaned Users

SELECT name, sid FROM sys.database_principals
WHERE type_desc = 'SQL_USER' AND sid NOT IN (SELECT sid FROM sys.server_principals);

---

23. Summary of the Migration Path

Phase	Tool	Purpose
Audit	DMA	Check for compatibility issues.
Bulk	Snowball Edge	Move 100TB without killing your internet.
Restore	rds_restore_database	Native SQL restore from S3 into RDS.
Sync	AWS DMS	Real-time replication of new data.
Cutover	DNS / SSMS	Final switch to the cloud.