Using AWS DMS to migrate & replicate data from an unmanaged database server to another

Introduction to AWS DMS

AWS DMS?(Database Migration Service) is a managed migration and replication service that helps to move your database and analytics workloads to AWS quickly, securely, and with minimum downtime with zero data loss.

It supports migration between 20-plus databases and analytics engines such as?Oracle to Amazon Aurora MySQL,?MySQL to Amazon Relational Database (RDS) for MySQL,?Microsoft SQL Server to Amazon Aurora PostgreSQL,?MongoDB to Amazon DocumentDB (with MongoDB compatibility),?Oracle to Amazon Redshift.

Not just that it also supports migration between the homogeneous database servers (be it managed or unmanaged database servers).

The?AWS DMS?can be used to perform three things:-

1. Migrate existing data from the source database to the target database
2. Replicate data changes
3. And both, migrate and replicate ongoing changes

In this example, we have?two unmanaged database?servers running on two different AWS EC2 instances. Both databases are?Microsoft SQL Server 2022?edition. The migration & data syncing needs to happen from one unmanaged database server to another.

Architecture

Checking database connectivity

In this example, we already have two database servers running on EC2 machines named?source?&?target?(as depicted below in the screenshot)

First, check the connectivity of both databases using SQL Client, e.g.?Microsoft SQL Server Management Studio.

Here in this source database server, I have created a new database called?company?within?a new schema as?emp?with a table as?Employee?containing some sample data in it.

With DMS, we will migrate this to the target database server.

Migration & Replication using DMS

The task at DMS can be divided into three parts:-

1. Creating replication Instances
2. Endpoints
3. DB Migration Tasks

Creating replication instances?— Go to AWS DMS service → Click on Replication Instances (under Migrate data) and then click on the ‘Create replication instance’ button and follow the wizard.

Give a meaningful name to the replication instance, select DMS instance class (say?dms.t3.large), engine version as?3.4.7, availability as?Single-AZ?(for Dev/Test workload) or?Multi-AZ?(for Prod workload), network type as?IPv4, and appropriate VPC & subnet to create a replication instance.

Endpoints?— Two endpoints need to be created. One for the source database and another for the destination/target database. These endpoints will be later attached to the replication instance (created above).

To create an endpoint, first, select endpoint type (source/target), and select source endpoint.

Give the endpoint identifier a meaningful name, select the source engine as?SQL Server,?and provide access information manually like server name, Port (1433?for SQL Server), username, password, and database.

Similarly, create another endpoint for the target database.

DB Migration Tasks?— Go to DMS → Database migration tasks (under Migrate data) and click on the ‘Create task’ button. Give a unique name to the task identifier, choose the replication instance, source database endpoint, and target database endpoint create in the above steps, and then select the right migration type.

A migration type can be to:-

• Migrate existing data
• Migrate existing data and replicate ongoing changes
• Replicate data changes only

Select, the 2nd option (Migrate existing data and replicate ongoing changes) as this will ensure a full load migration first and then data replication to the target database during any changes in the source database using the concept of CDC (Change Data Capture).

Please note for CDC, you need to set up the distribution in the SQL Server database. Refer to this?link?to configure it using?Microsoft SQL Server Management Studio.

You need to give the table mapping rules on both source and target databases. The selection rule is for the source database where you need to select a schema (emp?in my case), source table name as?%?(wildcard), and action as?include.

The transformation rules are for the target database where you must select the rule target as schema (in our case), source name as?emp, and action as ‘Make lowercase’ (to convert to lowercase during the migration).

Once a task has been created and then you start it to perform the migration and replication of ongoing changes to the target database.

Check the Table Statistics tab in the task to see what all tables got migrated.

Migration result

Voila!!! We got our data migrated into the target database which is again an unmanaged database server (MS SQL Server).

Cost of using DMS

In this example, I have used the below configuration in DMS service hence the approximate cost would be around USD 114.45 monthly.

• Region —?us-east-1 (Northern Virginia)
• Availability —?Single-AZ
• Instance Type —?t3.large (vCPU — 2, Memory — 8 GiB, Storage — EBS)
• No. of Instances —?1
• Storage —?50 GB (general purpose SSD, gp2)

Hope you liked this article :-)

Summary

In this blog, we have learned how we can use AWS DMS service to migrate data from an unmanaged database server to another. We also looked into the cost associated with using the DMS service.

Feel free to share your feedback.

If you have any queries related to your AWS Architecture, Serverless, or Kubernetes, feel free to reach out to me on?LinkedIn?or?Twitter.