In this lab we will use Database Migration Service (DMS) and create the Replication instance with Source and Target endpoints and a replication Task that replicates the data from source to target database instances. To create our environment, we will use a CloudFormation template to deploy the resources needed for this workshop in your AWS account in us-east-2 region (alternatively you can use any other US region for this lab). The source database is a Microsoft SQL Server 2008 (MSSQL2008) on an EC2 instance with Windows Server 2008 R2 and a sample database ‘SalesDB’ with 4 tables. The EC2 instance also has the SQL Server Management Studio (SSMS), MySQL Workbench (WB) and Schema Conversion Tool (SCT) pre-installed on it to save us time for the workshop. The target database is an Amazon Aurora MySQL Compatible instance with no user data.
We will use the client tools (SSMS & WB) on EC2 instance to validate data in the source and the target database instances in this lab. To familiarize ourselves with SCT, we will run the assessment report on the source schema (MSSQL2008) against the target (Amazon Aurora MySQL) and review the results. We will NOT use the SCT generated scripts to create the schema on the target for this lab, as we will be using DMS replication task to do just that. Finally, at the end of the lab, we will have a running DMS replication instance with a task that replicates data as a one-time full copy of 4 tables from the source MSSQL2008 instance to the 4 tables in the target Aurora MySQL instance.