How to work with native CDC support in AWS DMS

AWS Database Migration Service (AWS DMS) today launches native CDC support and the ability to start and stop the AWS DMS replication from a specific checkpoint. When you work with this feature, you can use checkpoints such as a log sequence number (LSN) in Microsoft SQL Server, a system change number (SCN) in Oracle, and an AWS DMS–specific recovery checkpoint. As part of this release, we’re also launching the ability for you to stop data replication and start it again using the AWS DMS checkpoint feature.

With this launch, AWS DMS enables customers to use the same mechanic that the database uses for commit sequencing, which is the log sequence number (LSN). The launch also opens more integration use cases. For example, now you can use Oracle Data Pump or SQL Server BCP to do the initial data load into a target database and then use the DMS log sequence numbers to start change data capture (CDC). With the checkpoint feature launched alongside the native start point support, you can hibernate environments and process changes since the last time replication was done. For example, you can replicate changes once a day from the last checkpoint.

At launch, we support the Oracle, SQL Server, and MySQL databases, and also Amazon Aurora with MySQL compatibility. Support for other databases is planned to follow.

AWS DMS: A primer

AWS Database Migration Service helps you migrate databases to AWS easily and securely. The source database remains fully operational during the migration, minimizing downtime to applications that rely on the database. AWS Database Migration Service can migrate your data to and from most widely used commercial and open-source databases.

The service supports homogenous migrations such as Oracle to Oracle, and also heterogeneous migrations between different database platforms, such as Oracle to Amazon Aurora or Microsoft SQL Server to MySQL. You can also use AWS DMS to stream data to Amazon Redshift, Amazon DynamoDB, and Amazon S3 from any of the supported sources, including Aurora, PostgreSQL, MySQL, MariaDB, Oracle, SAP ASE, SQL Server, and MongoDB. In addition, you can use AWS DMS for continuous data replication with high availability.

AWS DMS performs continuous data replication using change data capture (CDC). By using CDC, you can determine and track data that has changed and provide it as a stream of changes that a downstream application can consume and act on. Most database management systems manage a transaction log that records changes made to the database contents and to metadata. By using engine-specific API operations and functions, AWS DMS reads the transaction log. AWS DMS captures the changes made to the database in a nonintrusive manner.

Introducing Example Corp.

Our fictitious customer Example Corp. has a typical set of considerations to address using AWS DMS. Example Corp. has a 10 TB Oracle database and wants to create a read-only copy of the database in Aurora with PostgreSQL compatibility for reporting. They also want to possibly migrate this application to AWS. They’re considering using Aurora with PostgreSQL compatibility instead of an Oracle database. They also want to test the application on AWS with real production data.

In this blog post, let’s look at how Example Corp. can meet their requirements using the new features in AWS DMS.

Transaction logs in databases

Transaction logs capture every change made to the database system. The log contains sufficient information about each transaction executed that the database server should be able to recover the database cluster. It does so in case of a server crash by replaying the changes and actions in the transaction log. The transaction log also records an incrementing number called the log sequence number or LSN.

Oracle uses the term redo logs for transaction logs and the term system change number (SCN) for log sequence number (LSN). Oracle redo log files, similar to transaction logs, track redo records. A redo record is a set of change vectors that describe the changes made to a single block on the database. A data block is the smallest unit of data used by a database.

During a transaction, a user process is notified of successful completion of the transaction only after the necessary redo records are flushed to the disk. Every time a commit happens, the database assigns an SCN, similar to an LSN, to identify the redo records for each committed transaction.

Example Corp.’s reporting requirements

Example Corp. wants to get their reporting application running on an Aurora with PostgreSQL compatibility database and to migrate their on-premises Oracle database to Aurora with PostgreSQL compatibility.

To convert the schema using AWS SCT and migrate the data using AWS DMS, Example Corp. followed the instructions in the post A quick introduction to migrating from an Oracle database to an Amazon Aurora with PostgreSQL compatibility database on the AWS Database Blog.

Example Corp. didn’t migrate existing data from their production database but created a copy of the database at a point in time. They configured the source endpoints to that database to migrate existing changes. This approach ensures that the production database didn’t have an additional load from AWS DMS during the full migration.

Although Example Corp. wanted to start CDC on the Oracle database used by the application. they realized that using a specific time to start CDC was not ideal, due to the high number of transactions per second (TPS). The high TPS made it impossible to find a time that ensured that transactions aren’t duplicated or missed.

Example Corp.’s reporting environment

Example Corp. also followed the instructions in the blog post mentioned preceding to convert their schema and migrate the data to Aurora with PostgreSQL compatibility. After the existing data was migrated, Example Corp. had to change the source endpoint to reflect the production database from which to do CDC and migrate the data. They took the following steps to create a reporting environment in AWS on Aurora with PostgreSQL compatibility.

Creating a DMS task for CDC

On the AWS Management Console, you first create a new task with the new source endpoint reflecting the production database and target endpoints to the Aurora with PostgreSQL compatibility. Select the option to replicate data changes only. Because we have already migrated all the data using DMS previously, select Do nothing for Target table preparation mode.

Obtaining an SCN in Oracle

The following query gives you the latest SCN from which to start database replication. DMS can use this SCN to start the CDC at a point in time.

SELECT
      SCN,
      scn_to_timestamp(scn) SCN_TimeStamp
    FROM (
          SELECT dbms_flashback.get_system_change_number() as SCN
          FROM dual
        )

Task settings for CDC

Next, we need to configure CDC to start from the SCN obtained in the previous step.

In the Log Sequence Number section, you enter the SCN obtained in the previous step from the Oracle database. After the task is created, we can start the task and monitor it from the DMS console.

Starting from a log sequence number helps ensure that we don’t lose any transactions and that the start point for CDC is well-defined.

Example Corp.’s migration environment

As part of the move to Aurora with PostgreSQL compatibility from Oracle Database, Example Corp. wants to stop and start the CDC process. They want this so that they can test their application without the load from the CDC service. The checkpoint feature in DMS makes it possible to stop and start replication without relying on the source database LSN or SCN.

To do this, Example Corp. followed the procedure described preceding in creating a recording environment to create a second replica of the database. However, this time they used the checkpoint feature to stop and start the replication.

Using this approach, Example Corp. can stop the replication at a certain time of day or at a certain commit time.

DMS ensures that CDC stops at the particular server time or the commit time described in the task setting. DMS also continuously writes metadata information into a table in the target database. With this checkpoint data stored, Example Corp. can shut down the replication instance and the tasks to save on cost.

Restarting CDC

Example Corp. wants to replicate data only during the nighttime and to replicate only data that changed since the last replication.

To make this happen, Example Corp. first ran a query against the metadata table in the target instance—awsdms_txn_state—to obtain the checkpoint data. They then started a new replication instance and created a task to start the replication from the checkpoint obtained in the previous step, as shown in the following screenshot.

Doing this ensured that they only paid for the replication instance when replication was being performed and that they hibernated the environment at all other times.

You can also get checkpoint information as part of the results of a call to the describe-replication-tasks API action. You then simply filter by tasks and search for the checkpoint to get this information. You can retrieve the latest checkpoint when the replication task is in a stopped or failed state.

In the next blog post, we can look at how Example Corp. automated this process using AWS Lambda functions and AWS CloudFormation templates.

Conclusion

Although Example Corp.’s requirements were to migrate an Oracle database, AWS DMS can work with both commercial and open-source databases. DMS provides the same functionality for native start points and checkpoints for both.

In this post, we walk through how Example Corp. created a reporting environment using native SCN support. We also saw how Example Corp., using the new checkpoint feature, saved on costs by running the replication instances only when they wanted to and replicated only data that were changed from the previous time.

About the Author

Ganesh Raja is a solutions architect at Amazon Web Services. He works with our customers to provide guidance and technical assistance on database projects, helping them improving the value of their solutions when using AWS.