Part 2 - Use DMS to migrate the database

Now that you have successfully used the AWS Schema Conversion Tool you can actually perform the database migration using the AWS Database Migration Service. We have already pre-built the database migration replication instance as well as the two database migration endpoints (one for the source Microsoft SQL Server database and one for the target Aurora Postgres database). You will now create the database migration task that will perform the migration.

Confirm Connections

  1. In the AWS Management Console, navigate to Database Migration Service.

    AWS Console DMS

    You will see a screen that looks like this. As mentioned previously we pre-created one replication instance and two endpoints for you.

    DMS Console View

  2. You will confirm that the prepared endpoints connect to our databases correctly. Click on “Endpoints” from the left nav.

    Endpoints Button

  3. You will see the two endpoints. Let’s test the source connection first. Mark the box next to the “unishop-legacy-mssql-endpoint”. Then, from the “Actions” menu click “Test connection”.

    Test Endpoint 1

  4. Click “Run test”. The test may take a little bit of time. Confirm that the Status is successful.

    Running Test Endpoint 1

    Testing Test Endpoint 1

    Successful Test Endpoint 1

  5. Click “Endpoints” from the left nav again. Repeat steps 3 and 4 for the other target (Amazon Aurora PostgreSQL)

    Test Endpoint 2

    Running Test Endpoint 2

    Testing Test Endpoint 2

    Sucessful Test Endpoint 2

Create Database Migration Task

  1. In the left nav, click “Database migration tasks”.

    DMS Task View

  2. Click the “Create task” button.

    DMS Create Task Button

  3. Under Task configuration, enter the following:

    • Task Identifier: Enter Unicorn-Inventory-Migration

    • Replication Instance: Select “unishop-replication-instance”

    • Source database endpoint: Select “unishop-legacy-mssql-endpoint”

    • Target database endpoint: Select “unishop-inventory-postgres-endpoint”

    DMS Task Configuration

  4. Under Task settings, enter the following:

    • Target table preparation mode: Select “Do nothing”

    • Include LOB columns in replication: Select “Don’t include LOB columns”

    • Enable validation: Leave box unchecked

    • Enable CloudWatch logs: Leave box unchecked

    DMS Task Settings

  5. We only want to migrate the inventory table to Aurora Postgres. You can specify this in the Table mappings configuration. Under table mappings, click “Add new selection rule” and do the following things:

    • Schema: Select “Enter a schema”

    • Schema name: Enter dbo

    • Table name: Enter inventory

    • Action: Select “Include”

    DMS Task Table Mapping

  6. Expand the Transformation rules section by clicking “Transformation Rules”. Click “Add new transformation rule”.

    • Target: Select “Schema”

    • Schema name: Select “Enter a schema”

    • Schema name: Enter dbo

    • Action: Select “Add prefix”

    • Value: Enter unishop_

    DMS Task Transformation Rule Button

  7. Add a second transformation rule by clicking “Add new transformation rule” again.

    • Target: Select “Column”

    • Schema name: Select “Enter a schema”

    • Schema name: Enter dbo

    • Table name: Enter inventory

    • Column name: Enter year_model

    • Action: Select “Remove column”

    DMS Task Transformation Rule Button

    Why are we doing this? (Optional reading)
  8. You can leave the default Tags and Advanced task settings as is. Click “Create task”.

    DMS Task Creation

    It may take some time, but given the small nature of our database the migration task will be relatively quick. Still, while you are wating for the task to complete, you can direct yourself to your elastic beanstalk instance in the browser in your dev environment. You will be able to see the web site still working as expected, despite the fact that the migration is taking place.

    DMS Task Creating DMS Task Starting DMS Task Running DMS Task Loaded

    The migration is complete when the status is Load complete. Let’s move on to the next part to put everything together and test that they work as expected!