Migration - AWS RDS SQL Server database unencrypted to AWS RDS SQL Server RDS Encrypted

You're on the right path with your high-level approach, and it’s great to see you're thinking carefully about minimizing downtime, especially with a 24x7 workload. Leveraging native backups for the initial seed and then using AWS DMS for change replication is a well-established pattern when moving between RDS SQL Server instances with different encryption configurations, since you can't enable encryption in-place.

Let’s walk through each step, confirm what works, and refine a few areas for clarity and reliability.

1. Create a new RDS SQL Server instance with encryption enabled Absolutely correct. Make sure this new instance has the same or higher version and edition as your source, and ensure encryption is configured during launch since it cannot be applied retroactively. More on that here: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Overview.Encryption.html

2. Enable CDC on the source database This works, but take note: AWS DMS relies on either CDC or MS-Replication to capture changes. For SQL Server, DMS can leverage the database transaction log, and enabling CDC on specific tables is valid. However, confirm that the necessary SQL Server Agent jobs are running and that log truncation doesn’t interfere with CDC retention during the migration window. Helpful reference: https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.SQLServer.html

3. Take full native backup of the source database to S3 Correct. You’ll need to enable the rds_backup_database procedure via the SQL Server RDS instance. This method produces a .bak file and stores it in an S3 bucket you designate. Ensure the option group includes the correct S3 integration settings and IAM permissions. Details: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/SQLServer.Procedural.Importing.html

4. Extract the last LSN from msdb.dbo.backupset This is a critical and often overlooked step. You're spot-on here: capturing the exact LSN at the time of backup gives DMS a consistent point to begin change data replication. Be sure to capture both the first and last LSN from the backup metadata, so you can validate continuity later if needed.

5. Restore the backup to the encrypted target RDS SQL Server No issues here. Use rds_restore_database to restore the full backup onto the new encrypted instance. Make sure the target instance has sufficient storage and performance configuration to support restore and post-migration load.

Reference: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/SQLServer.Procedural.Importing.html#SQLServer.Procedural.Importing.Restoring

6–8. Configure DMS and begin CDC from the captured LSN Here’s where things can get a bit nuanced: When creating your DMS replication task, you’ll need to select CDC only, as you correctly stated. The Start from LSN option can be configured using the LSN captured from the full backup step. If you're using a recent DMS version (v3.4 or later), setting this in the task settings under cdcStartPosition should work as expected. Ensure DMS has permissions to read from both the source and target endpoints, and that the endpoints are configured with correct schema mappings and table selection rules. Official DMS task documentation: https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Tasks.CustomizingTasks.TaskSettings.html

9. Cutover process This is solid. One key recommendation is to perform at least one test cutover before production. Validate that the encrypted RDS target is fully in sync, and perform integrity checks, including row counts, identity values, triggers, constraints, and any custom logic that may have been affected during replication.

Also, don’t forget to plan for DNS or connection string switching, possibly using RDS endpoints or a Route 53 record to abstract the database endpoint if your application supports it.

Additional Considerations: Maintenance Window Alignment: Align your change window with your RPO/RTO goals. DMS has eventual consistency during CDC, and brief replication lag can occur under heavy write loads. Monitoring: Use Amazon CloudWatch and the DMS console to track replication health. Set up alarms for latency or error counts. Security Review: Ensure encryption at rest is configured with the appropriate KMS key, and evaluate whether client-side SSL/TLS encryption is also needed in-transit. Post-Migration: Once cutover is complete, ensure backups, monitoring, and performance baselines are established for the new instance.

Summary Your plan shows strong architectural thinking. The move from unencrypted to encrypted RDS SQL Server using a combination of native backup and DMS is well-founded, and your focus on minimizing downtime is exactly what stakeholders expect from a cloud professional. Keep pushing into the deeper corners of DMS tuning and SQL Server internals, knowing how and when to fall back on native features (CDC, LSNs, backup metadata) while leveraging AWS-managed services is what separates a strong practitioner from someone just following the wizard. Would be glad to dive into a whiteboard session or help guide a pilot migration run if you're moving forward. There's a lot you can validate in a sandbox before making the production jump.

Your proposed approach for migrating from an unencrypted AWS RDS SQL Server to an encrypted one with minimal downtime is generally on the right track, but I'd like to offer some refinements.

The overall strategy of using a full backup to seed the target and then using AWS DMS for ongoing replication to minimize downtime is sound. Here's feedback on your approach:

For step 2, enabling CDC on the source database is correct, as AWS DMS will use this to capture ongoing changes.

Steps 3-5 involving the native backup to S3 and restore to the encrypted target are appropriate. Capturing the last_lsn is important for establishing the starting point for change replication.

For steps 6-8, your DMS configuration approach is correct. Setting up the DMS replication instance, defining source and target endpoints, and creating a CDC-only task starting from the captured LSN will allow you to replicate changes that occurred after the backup was taken.

Step 9 on monitoring replication is critical - you'll want to ensure that the DMS task is keeping up with changes and that latency is minimal before attempting cutover.

Your cutover strategy in step 10 is appropriate - stopping the application, ensuring DMS has processed all changes, then redirecting the application to the new encrypted database.

A few additional considerations:

• You might want to add a validation step using DMS validation to ensure data integrity between source and target before cutover
• Consider using Amazon RDS Proxy to facilitate a seamless switchover to the newly encrypted instance
• Test the migration process thoroughly in a non-production environment first
• Have a rollback plan ready in case of issues during cutover

This approach should provide you with a migration path that meets your requirement for minimal downtime while moving to an encrypted RDS SQL Server instance.
Sources
How BCM One migrated data from an unencrypted Amazon RDS for PostgreSQL database instance to a new encrypted instance using AWS DMS | AWS Database Blog
Encrypt an existing Amazon RDS for PostgreSQL DB instance - AWS Prescriptive Guidance
Migration from RDS for MySQL to RDS for SQL Server Using AWS DMS | AWS re:Post