Part 4 - Using Database Migration Service

AWS Database Migration Service task requires at least a source, a target, and a replication instance. Your source is the database you wish to move data from and the target is the database you’re moving data to. The replication instance processes the migration tasks and requires access to your source and target endpoints inside your VPC. Replication instances come in different sizes depending on your performance needs. If you’re migrating to a different database engine, AWS Schema Conversion Tool can generate the new schema for you.

In this section of the lab you will Logon to the AWS console and create a DMS cluster, endpoints (Source & Target) and create a replication task. After the replication completes, you will come back to EC2 instance and verify the newly replicated tables in Amazon Aurora from MySQL Workbench.

2. Enter the following information for the Replication Instance and click Next

Setting Value
Name LAB-DMS-Replication
Description LAB-DMS-Replication
Instance Class dms.t2.medium
Replication Engine Version
VPC -xxxxxxxxxxxxx-DmsVpc
Multi-AZ No
Publicly Accessible Yes (checked)
Advanced -> VPC Security Group(s) xxxxx-ReplicationInstanceSecurityGroup

3. Enter the following information for the Database endpoints and Run test respectively.

Source database connection details:

Setting Value
Select RDS DB Instance uncheck
Endpoint Idenitfier SourceDB-MSSQL2008
Source Engine sqlserver
Server name
Port 1433
SSL mode none
User name awssct
Password Password1
Database Name SalesDB

Target database connection details:

Setting Value
Select RDS DB Instance unchecked
Endpoint Idenitfier TargetDB-AmazonAurora
Source Engine aurora
Server name
Port 3306
SSL mode none
User name admin
Password <Password from step 1.1.2>

4. Once the tests have passed on both the source and the target, click the Next button.

5. Enter the following information for the Create Task and click Next

Setting Value
Task name LAB-DMS-Task-MSSQL-to-Aurora
Task description LAB-DMS-Task-MSSQL-to-Aurora
Source endpoint SourceDB-MSSQL2008
Target endpoint TargetDB-AmazonAurora
Replication instance TFC-DMS-Replication
Start task on create checked
Task Settings:
Target table preparation mode Do nothing
Include LOB columns in replication Limited LOB mode
./media/DMS_006.png Max LOB size (kb) 32
Enable validation checked
Enable logging checked
Table mappings:
Schema name is dbo
Table name is like %
Action Include

6. Click Add Selection Rule, then click create task.

7. Next, Once the task has a status of Ready select the task and click Start/Resume

8. After the task completes and you see the status as ‘Load complete’, select the task ‘LAB-DMS-Task-MSSQL-to-Aurora’ and check the Table statistics tab. You can see the 4 tables and their rows count loaded successfully in the target Aurora MySQL instance.

9. Logon to the EC2 instance and within MySQL Workbench connect to the Target Aurora instance (username: admin password: <Password from step 1.1.2>) and run the following SQL command to get the same row count.

SELECT 'dbo.Customers' As Table_Name, count(*) AS rows_count FROM
dbo.Customers

UNION

SELECT 'dbo.Employees' As Table_Name, count(*) AS rows_count FROM
dbo.Employees

UNION

SELECT 'dbo.Products' As Table_Name, count(*) AS rows_count FROM
dbo.Products

UNION

SELECT 'dbo.Sales' As Table_Name, count(*) AS rows_count FROM dbo.Sales;

Output:

Table_Name rows_count
dbo.Customers 19759
dbo.Employees 23
dbo.Products 504
dbo.Sales 6715221

Conclusion:

You have successfully completed this lab by creating a Replication Task on DMS that copies 4 tables and their data from source to target. You validated the data before and after the migration using the client tools on the EC2 instance.