Do you want to migrate your on-premise databases to AWS with minimal or no downtime? In today’s fast-paced world, 24/7 application and database availability are the basic criteria to consider. In this case, some of your applications may not be able to afford significant downtime while migrating on-premises databases. In this situation, AWS Relational Database Service (RDS) is the best solution. In this blog, we will discuss RDS and how quickly we can migrate data with minimum downtime.
In this blog, we will cover:
- What is RDS?
- What is DMS?
- Benefits of DMS
- DMS Pricing
- Use cases of DMS
- Homogeneous Database Migrations
- Heterogeneous Database Migrations
- Development and Test using DMS
- Database Consolidation using DMS
- Continuous Data Replication using DMS
- Companies using DMS
- Hands-on – Database migration from EC2 hosted MySQL server to AWS RDS using AWS DMS service.
What is RDS?
According to the official definition from AWS, Amazon Relational Database Service (Amazon RDS) makes it easy to set up, operate, and scale a relational database in the cloud. It provides cost-efficient and resizable capacity while automating time-consuming administration tasks such as hardware provisioning, database setup, patching, and backups.
What is DMS?
AWS Database Migration Service (AWS DMS) is a cloud service that makes it easy to migrate relational databases, data warehouses, NoSQL databases, and other types of data stores. You can use AWS DMS to migrate your data into the AWS Cloud, between on-premises instances (through an AWS Cloud setup), or between combinations of cloud and on-premises setups.
In this blog, we will be performing a heterogeneous migration from the MySQL database on an EC2 instance to AWS Aurora using the DMS service.
Benefits of DMS
- Low Cost – Pay only for the compute resources used during the migration process and any additional log storage.
- Easy to use – Does not require any driver or application installation, and neither requires any changes to the source database most of the time.
- Reliable – Multi-AZ option allows you to have high availability for database migration and continuous data replication by enabling redundant replication instances.
- Minimal Downtime – In the presence of DMS your source database is continuously replicated, even when your source database remains operational. This enables switching databases at your own ease.
- On-going Replication – With an ongoing replication task you are able to keep your source and target databases in sync.
- Supports a wide range of Databases – It supports homogeneous migrations such as Oracle to Oracle, as well as heterogeneous migrations between different database platforms, such as Oracle to Amazon Aurora. It can also move data between SQL, NoSQL, and text-based targets.
AWS DMS Pricing
Use cases of DMS
Homogeneous Database Migration
In this case, source and target database engines are the same or compatible like Oracle to Amazon RDS for Oracle, MySQL to Amazon Aurora, MySQL to Amazon RDS for MySQL, or Microsoft SQL Server to Amazon RDS for SQL Server. The source database can be located in your own premises outside of AWS, running on an Amazon EC2 instance, or it can be an Amazon RDS database. The target can be a database in Amazon EC2 or Amazon RDS.
Heterogeneous Database Migration
The source and target databases engines are different, like in the case of Oracle to Amazon Aurora, Oracle to PostgreSQL, or Microsoft SQL Server to MySQL migrations.In the first step first use the AWS Schema Conversion Tool to convert the source schema and code to match that of the target database, and then use the AWS Database Migration Service to migrate data from the source database to the target database. Secondly, All the required data type conversions gets automatically done by the AWS Database Migration Service during the migration.The target is the database in Amazon EC2 or Amazon RDS.
Development and Testing
DMS can also be used out of the cloud for development purposes. Developers and testers can use copies of real production data and can copy updates back to the on-premises production system. Also avoids disruption to existing DevOps processes while ensuring the up-to-date representation of your production system. When developers and testers use copies of real production data and copy updates back to the on-premises production system.
Database Consolidation
DMS allows you to consolidate multiple source databases into a single target database. This can be done for homogeneous and heterogeneous migrations, and you can use this feature with all supported database engines.For example, one of the source databases can be in your own premises outside of AWS, while the second one in Amazon EC2, and the third one is an Amazon RDS database. The target can be a database in Amazon EC2 or Amazon RDS.
Continuous Data Replication
You can use DMS for both homogeneous and heterogeneous data replications for all supported database engines. Continuous data replication has a multitude of use cases including Disaster Recovery instance synchronization, geographic database distribution and Dev/Test environment synchronization. DMS uses both homogeneous and heterogeneous data replications for all supported database engines. It is easy to replicate data from a single database to one or more target databases or data from multiple source databases can be consolidated and replicated to one or more target databases.
Companies Using Database Migration Services
Let’s do hands-on:
Prerequisites:
Prepare the source database server:
- Create a user which will be used for replication with proper permissions.
- Modify mysql.cnf to allow replication.
- Allow the firewall/security groups to access this instance.
We will be performing the following steps for the migration:
- Create a replication instance
- Create target and source endpoints
- Refresh the source endpoint schemas
- Create a migration task
- Monitor your migration task
Create a replication instance
- Open the AWS DMS console, and choose Replication Instances from the navigation pane.
- Choose Create replication instance.
- Enter your replication instance name, description, instance class, Amazon Virtual Private Cloud (Amazon VPC), and Multi-AZ preference.
Note: Be sure to choose an instance class that’s sufficient for your migration workload. If the instance isn’t sufficient for your workload, you can modify the replication instance later. - From the Advanced section, choose your VPC security groups, or choose the default option.
- Choose Create replication instance.
Create target and source endpoints
- Open the AWS DMS console, and choose Endpoints from the navigation pane.
- Choose to Create endpoint to create the source and target database.
- For Endpoint type, choose Source.
- Enter the endpoint’s engine-specific information, such as the server name, port, SSL mode, or the Amazon Simple Storage Service (Amazon S3) bucket name, if you use Amazon S3 as the source.
- Choose Run Test.
- After the test is complete, choose Save.
- Repeat steps 3-6, but for Endpoint type, choose Target.
Note: Be sure to complete this step for both the target and the source.
Refresh the source endpoint schemas
- Open the AWS DMS console, and choose Endpoints from the navigation pane.
- Select the source endpoint, and choose Refresh schemas.
- Choose Refresh schemas.
Note: You must refresh the source so the source schemas appear in the table mappings when you create an AWS DMS task.
Create a migration task
- Open the AWS DMS console, and choose Database migration tasks from the navigation pane.
- Choose to Create a task.
- Specify the Task identifier, Replication instance, Source database endpoint, Target database endpoint, and Migration type. Choose one of the following migration types:
Migrate existing data only—Use this migration type for one-time migrations.
Migrate existing data and replicate ongoing changes—Use this migration type to migrate large databases to the AWS Cloud with minimal downtime.
Migrate ongoing replication changes—Use this migration type when you have already migrated the existing data and want to synchronize the source database with the target MySQL database hosted on the AWS Cloud. - From the Task Settings section, modify the task as needed.
- From the Table mappings section, choose Guided UI.
- Choose to Add a new selection rule, and specify your Schema and Table name.
Note: To change or transform the source schema, table, or column name of some or all of the selected objects, expand the Transformation rules section. Choose to Add a new transformation rule. Then select the Target, Schema name, and Action. - Choose to Create a task.
Monitor your migration task
- Use the Task Monitoring view to monitor the migration tasks. You can see which tables have been migrated successfully and which tables are in the process of migration. Pay attention to the following message types:
I – indicates an informational message
W – indicates warnings
E – indicates errors that occurred when migrating the database
- Verify that the databases have been migrated successfully by connecting to the source and target instances through the terminal.
Migrating from MySQL to Amazon Aurora
When migrating from MySQL to Amazon Aurora, use engine native tools when possible. During migration, schemas and tables are migrated to the same name on the target. If you want to migrate tables to a different schema on target, create a mapping rule to specify the new schema on the target database:
Navigate to the AWS DMS console to quickly get started. Click on “Create replication instance”.
Fill in the details as required. Enter a name for the instance, description, instance class as “dms.t3.micro”, engine version as “3.4.3”, allocated storage as “25” and select a VPC.
Under the “Advanced security and network configuration”, select the replication subnet group from the dropdown, availability zone and a KMS master key as shown in the image below.
Under the “Maintenance” section, select the start day, start time and duration as per your needs and select the minor version automatic upgrade as “Yes”.
Once done, you will see the newly created replication instance in the list as shown below.
Now, click on the “Endpoints” in the left navigation pane and click on “Create endpoint”.
Select the Endpoint type as “Source endpoint”, enter the endpoint identifier.
Select the source engine as “MySQL”, under the access to endpoint database select “provide access information manually”, enter the server name as shown in the image below, enter the port as “3306”, enter a user name and a password.
Under the “Endpoint-specific settings”, enter additional attributes (if any).
Under the “Test endpoint connection” section, select the VPC from the dropdown, replication instance from the dropdown and click on “Run test”. Once the status changes to “successful”, click on “Create endpoint”.
Now, navigate to the Amazon RDS console and click on “Create database”. Select the database creation method as “Standard create” and select the engine type as “Amazon Aurora”.
Under the Edition section, select “Amazon Aurora with MySQL compatibility”, under the Capacity type select “Provisioned” and for the engine version, choose the version as shown below from the dropdown.
Now, enter the “Settings” section and enter the details as shown in the below attached image.
For the DB instance size, select “Burstable classes” and “db.t3.small” from the dropdown and for multi-AZ deployment, select “Don’t create an Aurora Replica”.
For the “Connectivity” section, you can make the configuration as shown in the image below.
If you have an already existing “VPC security groups” then select “Choose existing” and select the existing security group or create a new one.
Under the “Additional configuration”, enter a name for your database, select the DB cluster parameter group, DB parameter group and once done, click on “create database”.
You will see the newly created Aurora database in the list as shown below.
You can view the connection details by clicking on the newly created Aurora database. A modal will appear with the connection details.
Navigate to the “Endpoints” section from the left navigation pane and create a new endpoint for the target. Select the endpoint type as “Target endpoint”.
Enter a name for the endpoint identifier, select the target engine as “Amazon Aurora MySQL”, access to the endpoint database as “Provide access information manually”, enter a server name, enter the port “3306”, a user name and a password.
Under the “Test endpoint connection” section, select the VPC, the replication instance and “Run test”. Once the status changes to “successful”, click on “Create endpoint”.
On success, you can view the created target endpoint in the list.
Navigate to the “Database migration tasks” from the left navigation pane and click on “Create task”.
Under the “Task configuration” section, enter a name for the task identifier, select the replication instance, source database endpoint, target database endpoint and migration type.
Under the “Task settings” section, select the options as shown in the image below.
Under the “Table mappings” section, select the editing mode as “Wizard”.
Expand the “Selection rules” section and for schema, select “Enter a schema”, for schema name and table name enter % and for the action, select “Include” from the dropdown.
Once done, under the “Migration task startup configuration” section, select “Automatically on create” and then click on “Create task”.
Once done, you will see the newly created task in the list as shown below and the status changes to “Ready” as well as you can see the “Progress” bar for the same.
Conclusion
Database migration can be a challenging project that requires expertise and tools. In this blog, we have explored that AWS Database Migration Service (DMS) has the required expertise to help data migration to the cloud easily and securely. You can use DMS service for both homogenous migrations such as MySQL to MySQL, and heterogeneous migrations between different database platforms, such as MySQL to Amazon Aurora or Microsoft SQL Server to MySQL. Based on your requirements and preferences, you can use the DMS service to handle the complete migration or to help with only some aspects of the migration.
Stay tuned to keep getting all updates about our upcoming new blogs on AWS and relevant technologies.
Meanwhile …
Keep Exploring -> Keep Learning -> Keep Mastering
This blog is part of our effort towards building a knowledgeable and kick-ass tech community. At Workfall, we strive to provide the best tech and pay opportunities to AWS-certified talents. If you’re looking to work with global clients, build kick-ass products while making big bucks doing so, give it a shot at workfall.com/partner today.
