Our experts are thinkers AND doers focused on accelerating business outcomes. To showcase our deep expertise, we created a blog series called “The Digital Build.”
Introduction:
Before cloud computing, all organizations managed Database Management Systems (DBMS) using on-premises hardware devices, which were costly and difficult to maintain. With the introduction of Cloud Computing, it is now a breeze to manage and operate DBMS systems. But the current challenge is how to efficiently move the on-premises database systems to the cloud to run a business consistently. Let’s discuss some of the difficulties with DB migrations and their viable solutions.
Problem statement:
Whether big or small, many organizations choose AWS DMS for their database migrations and migrate their on-prem database to the RDS (Relational DB Services). This option offers homogenous, heterogeneous (between different database platforms, such as Oracle or Microsoft SQL Server to Amazon Aurora), data warehouses and NoSQL database migrations. It provides ease of data migration from on-prem to the cloud with no fear of data loss. It also offers a wide range of database engines.
AWS DMS performs well for homogenous migrations, but when it comes to migrating heterogeneous databases (where the source and target database engines are different), such as migrating from Oracle to Postgres, or Postgres to Microsoft SQL Server or MySQL, using only DMS will not be sufficient. The reason for this is that the schema, data types, and database codes of the source and target databases can be different. This makes the migration of heterogeneous databases a two-step process, requiring a schema and code conversion tool before the migration process.
Solution:
AWS introduced SCT (Schema Conversion Tool) to deal with the migration of heterogeneous relational databases. It comes with other features that help in migrating data warehouses to AWS.
But before jumping more into SCT, first, let us understand the features and benefits of the AWS DMS service.
Architecture diagram 
AWS DMS and its features:
The AWS DMS (Database Migration Service) assists with moving the database to the cloud quickly and securely. During migration, the source database remains fully functional and operational, thus reducing the downtime to the application it relies on.
Benefits:
- Supports several types of replications (it is nothing but how you want to migrate your data).
- Perform a one-time migration from the source endpoint to the target endpoint for existing data.
- Perform a one-time migration from the source to the target of existing data, and then continue replicating data changes from the source to the target database.
- Does not perform a one-time migration but continues to replicate data changes from the source to the target.
- You can enable validation so that AWS DMS compares the data at the source and target immediately after a full data load. Validation ensures that the data migrated correctly, but it takes an additional amount of time.
- During the migration process, we can log information using Amazon CloudWatch and control the severity level of logs.
- We can also enable a pre-migration assessment. It warns you of potential migration issues before starting your database migration task. You can choose one or more pre-migration assessments for an assessment run that you create for a task.
- Variety of popular database types are supported as source endpoints. Some of them are as follows:
- Oracle versions 10.2 and later (for versions 10.x), 11g and up to 12.2, 18c, and 19c for the Enterprise, Standard, Standard One, and Standard Two editions
- Microsoft SQL Server versions 2005, 2008, 2008R2, 2012, 2014, 2016, 2017, and 2019 for the Enterprise, Standard, Workgroup, and Developer editions. The Web and Express editions are not supported.
- MySQL versions 5.5, 5.6, 5.7, and 8.0.
- Other benefits include:
- Simple to use
- Minimal downtime
- Supports widely used databases
- Low cost
- Supports ongoing replication
- Reliable
Additional Features that are offered by SCT include:
- SQL Script conversions.
- Supports NoSQL databases.
- Data extraction agents to migrate data from data warehouses. SCT can also help in managing the agents.
- Can use SCT to migrate ETL processes to AWS glue.
- SCT also provides extension packs for certain database features that cannot convert to Amazon RDS equivalents.
- DMS endpoints and tasks can be created and managed using SCT.
How does the process work with SCT and DMS?
When we attempt to migrate the relational database to Amazon RDS, the following happens:
DMS will try to load the data randomly if it does not know the primary, foreign or secondary key relationships. If you do not create the schema first and try to load the data, the migration will fail. Due to the random loading of data by DMS, it will always confuse where to place which data. This is because the key relationship is absent, which will cause an error, and eventually, migration will fail.
This is where SCT comes into the picture. We can connect to the source, target databases, and see the different schemas present in each database. We first transfer the data into the target database and then apply the schema over the database.
Then you might wonder what would happen in the case of continuous replication. Will the same problem arise? Suppose you are doing continuous replication or ongoing changes (the changes happening at the source database in real-time will be replicated right away to the target database). In that case, the replication occurs on a thread basis with no random data insertion.
Process Architecture Diagram
Challenges
Our team was working on a project where one of the requirements was to migrate the on-prem MySQL database to AWS cloud. The problem was that as the on-prem MySQL database was a relational database, it had all the constraints like primary key, secondary key, indexes, etc. The main challenge was how we were going to move everything to AWS RDS. We thought of DMS, but it didn’t allow us to move all these things in the cloud. After some research SCT came into the picture, which offers all the schema-related functionality.
Our team successfully migrated the on-prem database to AWS RDS instance with all the schemas combined with DMS and SCT.
References: –
- https://docs.aws.amazon.com/dms/latest/sbs/dms-sbs.pdf
- https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Welcome.html
Written by: Priyanka Ghanwat and Nilesh Choudhary
Priyanka Ghanwat
