Migrate legacy database

Hello.

To migrate your legacy Microsoft SQL Server databases to AWS with near-zero downtime, follow this simple approach using AWS Database Migration Service DMS.

1. Set Up Amazon RDS for SQL Server

• Launch an Amazon RDS instance for Microsoft SQL Server as your target database on AWS.

2. Use AWS Database Migration Service DMS

• Create a DMS Replication Instance Set up a DMS replication instance that will handle the data transfer.

• Configure the Source and Target Define your on-premises SQL Server as the source and your Amazon RDS SQL Server as the target.

Enable Change Data Capture (CDC) This feature allows you to replicate ongoing changes from your source database to the target, ensuring data synchronization during the migration process.

3. Start the Data Migration

• Begin the migration process with DMS, which will copy your existing data from the legacy SQL Server to Amazon RDS.

• While the initial data is being migrated, CDC will capture and apply any changes happening on your source database.

4. Final Cutover

• Once the data is fully synchronized, perform the final cutover by pointing your applications to the new Amazon RDS instance.

• This cutover can be scheduled during a maintenance window to minimize impact.

5. Post-Migration

• Test the new setup to ensure everything works as expected and decommission the old database if everything is functioning properly.

https://docs.aws.amazon.com/dms/latest/sbs/dms-sbs-welcome.html

https://dev.to/marufhossain/a-step-by-step-guide-to-migrating-legacy-applications-to-aws-kem#:~:text=A%20Step-by-Step%20Guide%20to%20Migrating%20Legacy%20Applications%20to,...%207%20Step%207%3A%20Optimize%20and%20Fine-Tune%20

The solution that best meets the requirement of migrating critical Microsoft SQL Server databases with near-zero downtime and modernizing the architecture is:

Option C:Use native database high availability tools. Connect the source system to an Amazon RDS for Microsoft SQL Server DB instance. Configure replication accordingly. When data replication is finished, transition the workload to an Amazon RDS for Microsoft SQL Server DB instance.

Explanation:

Near-Zero Downtime: By using native high availability tools (such as Always On Availability Groups or transactional replication), you can achieve near-zero downtime during the migration. These tools allow continuous data replication from the on-premises SQL Server to the target Amazon RDS instance, keeping the data synchronized until the cutover.

Modern Data Architecture: Moving the database to Amazon RDS for Microsoft SQL Server allows the databases to benefit from a managed service that provides automated backups, patching, and scaling, aligning with modern data architecture practices.

Minimal Complexity: This solution leverages familiar SQL Server high availability features, reducing the complexity and risk of the migration process.

Hello Isaac, in my opinion solution B would be the most suitable solution.

Seeing that you are bringing up key words such as "legacy systems", "move the databases to a modern architecture" and "near-zero downtime". I would suggest you to look into Babelfish for Aurora PostgreSQL. Set the Microsoft SQL Sever as the source database and the Babelfish target as the destination using AWS DMS. AWS SCT can be used to perform the migration as well but you'll have to use DBeaver with it.

While B is the most suitable answer out of the four. It could do better when it comes to moving to a modern architecture; to enhance database performance and scalability using Aurora Postgres. Aurora Postgres uses a distributed storage system that automatically replicates data across multiple Availability Zones or region providing high availability and durability which the legacy MSSQL database couldn't provide. On top of that you get to reduce operating cost as you move away from licensing.