Migrating a SQL Server Database to Amazon Aurora MySQL
Step 5: Create an AWS DMS Replication Instance
After validating the schema structure between source and target databases, continue with the core part of this walkthrough, which is the data migration. The following illustration shows a high-level view of the migration process.
An AWS DMS replication instance performs the actual data migration between source and target. The replication instance also caches the transaction logs during the migration. The amount of CPU and memory capacity a replication instance has influences the overall time that is required for the migration.
For information about best practices for using AWS DMS, see AWS Database Migration Service Best Practices.
To create an AWS DMS replication instance, do the following:
1. Sign in to the AWS Management Console, and open the AWS DMS console at https://
console.aws.amazon.com/dms/v2/.
2. In the console, choose Create migration. If you are signed in as an AWS Identity and Access Management (IAM) user, you must have the appropriate permissions to access AWS DMS. For more information about the permissions required, see IAM Permissions Needed to Use AWS DMS.
3. On the Welcome page, choose Next to start a database migration.
4. On the Create replication instance page, specify your replication instance information.
Parameter Description
Name Select a name for your replication instance. If
you are using multiple replication servers or sharing an account, choose a name that helps you quickly differentiate between the different servers.
Description Type a brief description.
Instance class Select the type of replication server to create.
Each size and type of instance class has increasing CPU, memory, and I/O capacity.
Generally, t2 instances are for lower load tasks, and the c4 instances are for higher load and more tasks.
VPC Choose the virtual private cloud (VPC) in which
your replication instance will launch. If possible, select the same VPC in which either your source or target database resides (or both).
Multi-AZ If you choose Yes, AWS DMS creates a second
replication server in a different Availability Zone for failover if there is a problem with the primary replication server.
Publicly accessible If either your source or target database resides outside of the VPC in which your replication server resides, you must make your replication server policy publicly accessible.
5. For the Advanced section, specify the following information.
Parameter Description
Allocated storage (GB) Amount of storage on the replication server for the AWS DMS task logs, including historical tasks logs. AWS DMS also uses disk storage to cache certain data while it replicates it from the source database to the target. Additionally, more storage generally enables better IOPS on the server.
Parameter Description
Replication Subnet Group If you are running in a Multi-AZ configuration, you need at least two subnet groups.
Availability zone Generally, performance is better if you locate your primary replication server in the same Availability Zone as your target database.
VPC Security Group(s) Security groups enable you to control ingress and egress to your VPC. AWS DMS lets you associate one or more security groups with the VPC in which your replication server is launched.
KMS key With AWS DMS, all data is encrypted at rest
using a KMS encryption key. By default, AWS DMS creates a new encryption key for your replication server. However, you might choose to use an existing key.
For information about the KMS key, see Setting an Encryption Key and Specifying KMS Permissions.
6. Click Next.
Step 6: Create AWS DMS Source and Target Endpoints
While your replication instance is being created, you can specify the source and target database endpoints using the AWS Management Console. However, you can test connectivity only after the replication instance has been created, because the replication instance is used in the connection.
1. In the AWS DMS console, specify your connection information for the source SQL Server database and the target Aurora MySQL database. The following table describes the source settings.
Parameter Description
Endpoint Identifier Type a name, such as SQLServerSource.
Source Engine Choose sqlserver.
Server name Provide the SQL Server DB instance server name.
Port Type the port number of the database. The
default for SQL Server is 1433.
SSL mode Choose an SSL mode if you want to enable
encryption for your connection’s traffic.
User name Type the name of the user you want to use to
connect to the source database.
Password Provide the password for the user.
Database name Provide the SQL Server database name.
The following table describes the advanced source settings.
Parameter Description
Extra connection attributes Extra parameters that you can set in an endpoint to add functionality or change the behavior of AWS DMS. A few of the most relevant attributes are listed here. Use a semicolon (;) to separate multiple entries.
* safeguardpolicy - Changes the behavior of SQL Server by opening transactions to prevent the transaction log from being truncated while AWS DMS is reading the log. Valid values are EXCLUSIVE_AUTOMATIC_TRUNCATION or RELY_ON_SQL_SERVER_REPLICATION_AGENT (default).
* useBCPFullLoad - Directs AWS DMS to use BCP (bulk copy) for data loading. Valid values are Y or N. When the target table contains an identity column that does not exist in the source table, you must disable the use of BCP for loading the table by setting the parameter to N.
* BCPPacketSize - If BCP is enabled for data loads, then enter the maximum packet size used by BCP. Valid values are 1 – 100000 (default 16384).
* controlTablesFileGroup - Specifies the file group to use for the control tables that the AWS DMS process creates in the database.
KMS key Enter the KMS key if you choose to encrypt your
replication instance’s storage.
The following table describes the target settings.
Parameter Description
Endpoint Identifier Type a name, such as Auroratarget.
Target Engine Choose aurora.
Server name Provide the Aurora MySQL DB server name for
the primary instance.
Port Type the port number of the database. The
default for Aurora MySQL is 3306.
SSL mode Choose None.
User name Type the name of the user that you want to use
to connect to the target database.
Password Provide the password for the user.
The following table describes the advanced target settings.
Parameter Description
Extra connection attributes Extra parameters that you can set in an endpoint to add functionality or change the behavior of AWS DMS. A few of the most relevant attributes are listed here. Use a semicolon to separate multiple entries.
* targetDbType - By default, AWS DMS creates a different database for each schema that is being migrated. If you want to combine several schemas into a single database, set this option to targetDbType=SPECIFIC_DATABASE.
* initstmt - Use this option to invoke the MySQL initstmt connection parameter and accept anything MySQL initstmt accepts. For an Aurora MySQL target, it’s often useful to disable foreign key checks by setting this option to initstmt=SET FOREIGN_KEY_CHECKS=0.
KMS key Enter the KMS key if you choose to encrypt your
replication instance’s storage.
The following is an example of the completed page.
For information about extra connection attributes, see Using Extra Connection Attributes with AWS Database Migration Service.
2. After the endpoints and replication instance are created, test the endpoint connections by choosing Run test for the source and target endpoints.
3. Drop foreign key constraints and triggers on the target database.
During the full load process, AWS DMS does not load tables in any particular order, so it might load the child table data before parent table data. As a result, foreign key constraints might be violated if they are enabled. Also, if triggers are present on the target database, they might change data loaded by AWS DMS in unexpected ways.
ALTER TABLE 'table_name' DROP FOREIGN KEY 'fk_name';
DROP TRIGGER 'trigger_name';
4. If you dropped foreign key constraints and triggers on the target database, generate a script that enables the foreign key constraints and triggers.
Later, when you want to add them to your migrated database, you can just run this script.
5. (Optional) Drop secondary indexes on the target database.
Secondary indexes (as with all indexes) can slow down the full load of data into tables because they must be maintained and updated during the loading process. Dropping them can improve the
performance of your full load process. If you drop the indexes, you must to add them back later, after the full load is complete.
ALTER TABLE 'table_name' DROP INDEX 'index_name';
6. Choose Next.
Step 7: Create and Run Your AWS DMS Migration Task
Using an AWS DMS task, you can specify what schema to migrate and the type of migration. You can migrate existing data, migrate existing data and replicate ongoing changes, or replicate data changes only.
1. In the AWS DMS console, on the Create task page, specify the task options. The following table describes the settings.
Parameter Description
Task name Type a name for the migration task.
Task description Type a description for the task.
Source endpoint Shows the SQL Server source endpoint.
If you have more than one endpoint in the account, choose the correct endpoint from the list.
Target endpoint Shows the Aurora MySQL target endpoint.
Replication instance Shows the AWS DMS replication instance.
Migration type Choose an option.
* Migrate existing data - AWS DMS migrates only your existing data. Changes to your source data aren’t captured and applied to your target.
If you can afford to take an outage for the duration of the full load, then this is the simplest option. You can also use this option to create test copies of your database. If the source SQL Server database is an Amazon RDS database, you must choose this option.
* Migrate existing data and replicate ongoing changes - AWS DMS captures changes while migrating your existing data. AWS DMS continues to capture and apply changes even after the bulk data has been loaded. Eventually the source and target databases are in sync, allowing for a minimal downtime.
* Replicate data changes only - Bulk load data using a different method. This approach generally applies only to homogeneous migrations.
Parameter Description
Start task on create In most situations, you should choose this option. Sometimes, you might want to delay the start of a task, for example, if you want to change logging levels.
The page should look similar to the following:
2. Under Task settings, specify the settings. The following table describes the settings.
Parameter Description
Target table preparation mode Choose an option.
* Do nothing - AWS DMS does nothing to prepare your tables. Your table structure remains the same, and any existing data remains in the table. You can use this method to consolidate data from multiple systems.
* Drop tables on target - AWS DMS creates your target tables for you. AWS DMS drops and re-creates the tables to migrate before migration.
AWS DMS creates the table and a primary key only for heterogeneous migrations.
* Truncate - AWS DMS truncates a target table before loading it. If the target table doesn’t exist, then AWS DMS creates it.
IMPORTANT: If the AWS Schema Conversion Tool already created the tables on the target, choose Do nothing or Truncate.
Include LOB columns in replication Choose an option.
Parameter Description
* Don’t include LOB columns - Do not migrate LOB data.
* Full LOB mode - AWS DMS migrates all LOBs (large objects) from the source to the target regardless of size. In this configuration, AWS DMS has no information about the maximum size of LOBs to expect. Thus, LOBs are migrated one at a time, piece by piece. Full LOB mode can be relatively slow.
* Limited LOB mode - You set a maximum size LOB that AWS DMS accepts. This option enables AWS DMS to pre-allocate memory and load the LOB data in bulk. LOBs that exceed the maximum LOB size are truncated, and a warning is issued to the log file. In limited LOB mode, you get significant performance gains over full LOB mode. We recommend that you use limited LOB mode whenever possible.
Max LOB size (kb) When Limited LOB mode is selected, this option determines the maximum LOB size that AWS DMS accepts. Any LOBs that are larger than this value are truncated to this value.
Enable logging It’s best to select Enable logging. If you enable logging, you can see any errors or warnings that the task encounters, and you can troubleshoot those issues.
3. Leave the Advanced settings at their default values.
4. If you created and exported mapping rules with AWS SCT in the last step in Step 4: Use the AWS Schema Conversion Tool (AWS SCT) to Convert the SQL Server Schema to Aurora MySQL (p. 96), choose Table mappings, and select the JSON tab. Then select Enable JSON editing, and enter the table mappings you saved.
If you did not create mapping rules, then proceed to the next step.
5. Choose Create task. The task starts immediately.
The Tasks section shows you the status of the migration task.
If you chose Enable logging during setup, you can monitor your task. You can then view the Amazon CloudWatch metrics.
1. On the navigation pane, choose Tasks.
2. Choose your migration task.
3. Choose the Task monitoring tab, and monitor the task in progress on that tab.
When the full load is complete and cached changes are applied, the task stops on its own.
4. On the target Aurora MySQL database, if you disabled foreign key constraints and triggers, enable them using the script that you saved previously.
5. On the target Aurora MySQL database, re-create the secondary indexes if you removed them previously.
6. If you chose to use AWS DMS to replicate changes, in the AWS DMS console, start the AWS DMS task by choosing Start/Resume for the task.
Important replication instance metrics to monitor include the following:
• CPU
• FreeableMemory
• DiskQueueDepth
• CDCLatencySource
• CDCLatencyTarget
The AWS DMS task keeps the target Aurora MySQL database up to date with source database changes.
AWS DMS keeps all the tables in the task up to date until it’s time to implement the application migration. The latency is zero, or close to zero, when the target has caught up to the source.
For more information, see Monitoring AWS Database Migration Service Tasks.