1

I am trying to use AWS DMS to move data from a source database ( AWS RDS MySQL ) in the Paris region ( eu-west-3 ) to a target database ( AWS Redshift ) in the Ireland region ( eu-west-1 ). The goal is to continuously replicate ongoing changes.

I am running into these kind of errors : 

An error occurred (InvalidResourceStateFault) when calling the CreateEndpoint operation: The redshift cluster datawarehouse needs to be in the same region as the current region. The cluster's region is eu-west-1 and the current region is eu-west-3.

The documentation says :

The only requirement to use AWS DMS is that one of your endpoints must be on an AWS service.

So what I am trying to do should be possible. In practice, it's seems it's not allowed.

How to use AWS DMS from a region to an other ? In what region, should my endpoints be ? In what region, should my replication task be ? My replication instance has to be on the same region than the RDS MySQL instance because they need to share a subnet

Hugo
  • 1,195
  • 2
  • 12
  • 36

3 Answers3

1

AWS provides this whitepaper called "Migrating AWS Resources to a New AWS Region", updated last year. You may want to contact their support, but an idea would be to move your RDS to another RDS in the proper region, before migrating to Redshift. In the whitepaper, they provide an alternative way to migrate RDS (without DMS, if you don't want to use it for some reason):

  1. Stop all transactions or take a snapshot (however, changes after this point in time are lost and might need to be reapplied to the target Amazon RDS DB instance).
  2. Using a temporary EC2 instance, dump all data from Amazon RDS to a file:
    • For MySQL, make use of the mysqldump tool. You might want to compress this dump (see bzip or gzip).
    • For MS SQL, use the bcp utility to export data from the Amazon RDS SQL DB instance into files. You can use the SQL Server Generate and Publish Scripts Wizard to create scripts for an entire database or for just selected objects.36
    • Note: Amazon RDS does not support Microsoft SQL Server backup file restores.
    • For Oracle, use the Oracle Export/Import utility or the Data Pump feature (see http://aws.amazon.com/articles/AmazonRDS/4173109646282306).
    • For PostgreSQL, you can use the pg_dump command to export data.
  3. Copy this data to an instance in the target region using standard tools such as CP, FTP, or Rsync.
  4. Start a new Amazon RDS DB instance in the target region, using the new Amazon RDS security group.
  5. Import the saved data.
  6. Verify that the database is active and your data is present.
  7. Delete the old Amazon RDS DB instance in the source region
Fabio Manzano
  • 2,847
  • 1
  • 11
  • 23
  • I can't accept this answer since it's not using AWS DMS and can't be used to replicate ongoing changes. – Hugo Sep 03 '18 at 16:34
  • I probably wasn't clear, but you can use DMS to migrate RDS to RDS (between different regions) and then use DMS again to migrate to Redshift, in the same region. – Fabio Manzano Sep 03 '18 at 16:38
  • Thanks for updating your answer. It's clearer. Moving database dumps around is not an option for us. I will try to get a confirmation from AWS that using DMS between regions is impossible. – Hugo Sep 04 '18 at 12:34
  • Thanks @Hugo. It would be good if you could come back here later and share the confirmation. Note that, as you noted in the whitepaper, it seems that DMS is able to migrate RDS between regions. You may need to do that before migrating to Redshift. – Fabio Manzano Sep 04 '18 at 12:46
  • yep I will share AWS support answer when I get it – Hugo Sep 05 '18 at 10:55
  • I finaly got the answer (explained below) – Hugo Sep 07 '18 at 16:04
1

I found a work around that I am currently testing.

I declare "Postgres" as the engine type for the Redshift cluster. It tricks AWS DMS into thinking it's an external database and AWS DMS no longer checks for regions.

I think it will result in degraded performance, because DMS will probably feed data to Redshift using INSERTs instead of the COPY command.

Hugo
  • 1,195
  • 2
  • 12
  • 36
  • I won't accept this answer for the stack-overflow question since it's a work around – Hugo Sep 05 '18 at 11:01
1

Currently Redshift has to be in the same region as the replication instance.

The Amazon Redshift cluster must be in the same AWS account and the same AWS Region as the replication instance.

https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Target.Redshift.html

So one should create the replication instance in the Redshift region inside a VPC Then use VPC peering to enable the replication instance to connect to the VPC of the MySQL instance in the other region

https://docs.aws.amazon.com/vpc/latest/peering/what-is-vpc-peering.html

Hugo
  • 1,195
  • 2
  • 12
  • 36