AWS

AWS Database Migration Service

Amongst the many services being offered by AWS, one of the most anticipated from a database perspective is the Database Migration Service – DMS. It is now readily available across all regions after being in preview for while.

What better reason to give it a trial run. The AWS Blog has a nice article on how to get it going in a few clicks so instead of repeating the same steps I’ll use the cli to setup a replication.

The DMS is a heterogeneous replication tool allowing you to migrate between different database systems. In my test I’ll keep it simple and migrate a few tables from an on premise Oracle 11.2.0.3 database to an AWS Oracle RDS Instance.

The service also supports change data capture (CDC) allowing you to keep the tables in sync. I’ll configure my replication to use CDC.

The basic setup of the service is as follows:

  • Create a Replication Subnet Group
  • Create a Replication Instance
  • Create your Replication Endpoints (source and target)
  • Create your Replication Task
  • Before we start we need to prep our databases to make sure they meet the requirements of the service and in my case CDC. Archivelog mode needs to be enabled and we need to setup supplemental logging on the source. You also need to add supplemental logging to each tables primary keys. Oracle source requirements are detailed here.

    Another prep item is to configure the user (I’m using awsdms) that will be used for the connection endpoints. This user must be granted certain privileges. The source user ddl can be found here while the target ddl can be found here.

    Once that is all done we can move onto the DMS service. First lets create the replication subnet group using two private subnets in the VPC.

    Now we can create the replication instance.

    Once we have our replication instance up and running we can create our endpoints starting with source. For server name we need to specify an IP address as the DMS service cannot resolve on premise hostnames. A workaround to this is to create an AWS Route 53 address that points back to our on premise hosts IP but we’ll leave it as is for this test.

    And now lets test connectivity and make sure our replication instance can talk to our source database.

    Now lets create the target endpoint. You can run the same tests as above but I’ll omit it for this exercise.

    We are just about ready to create our replication task. The replication task requires two json files. One for our source table mappings and one for our task settings. Here is my source table mapping:

    And now the task settings. There are a number of configurable options here but I settled for the following:

    Putting this all together we can create the replication task with a migration type of full-load-and-cdc.

    And finally lets start it.

    After a few minutes you should see the tables created on the target database. I can update, insert, delete data on these two tables and they’ll be kept in sync.

    Overall a pretty straightforward and simple tool to use to migrate and keep data in sync. I came across a few issues while testing this and currently have a support case open. Issues include:

  • Target user permissions missing delete any table in DMS Online documentation – bug raised
  • When delete fails due to permission problem, exception does not get logged properly
  • DMS Logminer reader doesn’t like standby databases configured on source database and tries to read non existent logs – working with AWS support on this
  • DMS cannnot resolve our on premise hostnames and we can’t influence which DNS server it uses. Route 53 workaround.
  • Diagnosing replication issues with RDS target can be tricky as its a managed service. I had to replicate on an EC2 instance.
  • 2 Comments

    1. Thanks for the blog, mate! It worked awesome. I just extended it a little bit to migrate 1TB of data (about 30 databases). I was migrating from MySQL to RDS MySQL, I created a replication task per every database and it worked good.

      I just encountered an error that I hope you can help me to understand and overcome, please.

      While migrating on DB, I got that error:
      Last Error [120112] A field data conversion failed. Stop Reason RECOVERABLE_ERROR Error Level RECOVERABLE

      and the task stopped. If I tried to start it again, it also fails too.

      1. From the error looks like some conversion problem so maybe check the version you are using and/or the collation. See if there is anything in the attrep_apply_exceptions table and enable logging on the tasks to see if you can get any more detail.

    Leave a Reply

    Your email address will not be published. Required fields are marked *

    CAPTCHA * Time limit is exhausted. Please reload CAPTCHA.