By using AWS re:Post, you agree to the AWS re:Post Terms of Use
AWS re:Postre:Post

Migrating from SQL Server to Dynamodb table using DMS

0

Hi, I am working on a SQL table to Dynamodb table data migration.

I have a view created which I am going to use in DMS. This will fetch the source data from SQL Server.

But this view is joining 2 very big tables, so I am worried about the performance of the Source SQL. Usually when there is lot of data to be migrated, I create a stored procedure and do it in small batches without impacting the workload in source sql.

I wanted to know if that approach is possible with DMS. Can I create a stored procedure with a while loop, which once called goes on till all the records are migrated.

In short, I wanted to know if I can call a stored procedure instead of a table or view in source in my DMS replication task?

Thanks

Topics
Migration & Modernization
Tags
AWS Database Migration Service
Language
English
Udhayan
asked 7 months ago289 views
1 Answer
1

Hello.

You can accomplish your objective of transferring data from SQL Server to DynamoDB in smaller increments by combining SQL Server and DMS capabilities. Here is a broad approach:

Develop a Stored Procedure: Within SQL Server, create a stored procedure responsible for retrieving and processing data from the two substantial tables in manageable portions. This stored procedure should select a batch of data, execute any necessary transformations, and then insert this data into a staging table.

Establish a Staging Table: Create a staging table in SQL Server dedicated to holding the data earmarked for migration to DynamoDB.

Scheduled Execution: Set up a schedule for running your stored procedure at regular intervals, which can be achieved through mechanisms like SQL Server Agent or a cron job. During each execution, the stored procedure will process a batch of data and insert it into the staging table.

DMS Migration: Configure a DMS replication task that reads data from the SQL Server staging table and mirrors it to DynamoDB. You can use the staging table as the source for your DMS task.

Incremental Replication: Fine-tune your DMS task to perform incremental replication, ensuring that it exclusively captures and migrates new records from the SQL Server staging table to DynamoDB during each execution. DMS incorporates features for managing ongoing changes.

Best regards, Andrii

profile picture
EXPERT
Andrii S
answered 7 months ago
  • Udhayan
    7 months ago

    Hi @Andrii S

    Thanks for your response. Yes I did think about the staging table approach. Considering the fact that the table is almost 5TB and I would need another 5TB to go with the staging table approach, I wanted to check if calling the stored procedure directly from DMS replication task is possible or not. If that is not possible, then the approach you outlined looks optimal in terms of performance overhead in the Source sql.

    Thanks

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions

Relevant content