Introduction

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

There are several reasons why you might choose to use AWS Database Migration Service (DMS) to replicate an on-premise database to AWS. Here are some of the key benefits and use cases:

1. Data Migration: AWS DMS simplifies the process of migrating your on-premises SQL Server database to AWS. It supports both homogeneous migrations (SQL Server to SQL Server) and heterogeneous migrations (e.g., Oracle to SQL Server). DMS handles schema conversion, data type mapping, and other necessary transformations during the migration process.

2. Continuous Data Replication: Once the initial migration is complete, AWS DMS allows you to set up ongoing replication between your source SQL Server database and a target database in AWS. This enables near real-time data replication and synchronization between the two environments, ensuring that your AWS database remains up to date with the changes in your on-premises SQL Server database.

3. Database Consolidation and Reporting: AWS DMS allows you to consolidate multiple SQL Server databases into a single database instance in AWS. This consolidation can simplify management and reduce costs. Additionally, you can use the replicated data in AWS for reporting and analytics purposes without impacting the performance of your production database.

4.  

The Scope

• Let us assume that the SQL Server database is a third party vendor provided service and can only be accessed via a VPN from the client's corporate HQ. The assumption is also that this is a production DB with a primary and a read-only secondary. The architecture, after a DMS integration would look like:

 

Pre-requisites

	Change Data Capture (CDC) should be enabled on source database
	Distribution server should configured
	Login credentials for the database
	User Permissions should be set: VIEW DATABASE PERFORMANCE STATE, VIEW DATABASE STATE, VIEW DEFINITION, VIEW PERFORMANCE DEFINITION, BACKUP DATABASE, BACKUP LOG

 

Execution Plan

1. 1. Setup a Bastion host and install the client vpn. this will be the proxy and it will forward the traffic to source DB. We can use socat.

• #INSTALL SOCAT $ sudo apt-get install socat -y

#RUN SOCAT TO LISTEN TO PORT 1433 AND FORWARD THE TRAFFIC TO SOURCE DB $ socat -d -d tcp-listen:1433,fork,reuseaddr tcp:<SOURCE-DB-IP-ADDRESS> & #CHECK IF THE SOCAT LISTENING TO 1433 $ lsof -i tcp:1433

 

1. 2. Since we are minimizing the migration task, we can initially use a small instance for replication instance then we can monitor it and upgrade if needed.

1. 3. We can configure the source endpoint using the IP address of the bastion host which will forward the request to source DB. For the target endpoint, we will use the AWS RDS DB endpoint.

1. 4. Create the migration task, its safe to use the default task settings as it is set to use a limited LOB instead of full LOB mode which will consume more resources of source db.

   

1. 5. We can tweak the configure of task so It wont consume a lot of cpu / memory resources.

   

1. 6. Since the source DB instance is used in production and the resources are almost maxed out, we need to minimize the migration task. In AWS DMS you can create multiple task and we can do schema wise or table wise approach.

   Schema wise:

   

   Table wise:

   

Conclusion

Once migration task is set up, we can run a pre-migration assessment before running the actual migration task to get an insight on what could go wrong during the migration.

Additionally for more observability, AWS DMS migration task can be configured to send an alarm to administrator by configuring AWS DMS event subscription to AWS SNS topic and we can install a cloudwatch agent on bastion host to monitor the metrics and even the logs. It will also help on debugging and troubleshooting when the task fails.

AWS Services Used