Implementing Change Data Capute (CDC) with Aurora Serverless v2
Tags: aws, database, dms, rds
Published: 2022-04-22 20:46:57

Aurora Serverless v2 is now available after a long wait and it claim to solve many issues and limitations from its predecessor.
One of the limitations of Aurora Serverless v1 is the impossibility of using CDC (Change Data Capture) with AWS DMS (Database Migration Service) but now it is possible using the v2.
In this guide we will review step by step the configuration and implementation of CDC with Aurora Serverless v2 and AWS DMS. 


![Solution diagram](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/d7or2gfh0bmj2a71yq9g.png)


Actually, the configuration is pretty much the same as configuring CDC on a provisioned Aurora DB Cluster. Also consider that all the configurations are based on us-east-1 (N. Virginia) region.

**Creating the Database**
As first step, we need to create the Parameter Group, Subnet Group and the Database cluster. I am using almost all the default parameters, changing only the required ones to enable CDC (those parameters are in _italic letters_).


<u>Parameter Group</u>
Click on the 'Create Parameter Group' button:
![Create Parameter Group](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ay54l2ek8b2cxfplmn8g.png)

Select:
Parameter Group family: aurora-mysql8.0
(Currently it is the only one compatible with Aurora Serverless v2)

Type:
_DB Cluster Parameter Group_

Group Name:
pg-servelessv2-cdc

Description:
Enable change data capture

![Configuring Parameter Group](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/dvj31t6j30dypnk4skr5.png)

Once the Parameter group is created, select it and click on 'Edit parameters':
![Configuring Parameter Group](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/w7rp7zcg3h2mmw67yx2u.png)

We need to change these parameters:
_binlog_format: ROW_
_binlog_checksum: NONE_

![Configuring Parameter Group](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/lnj33qa7ku226kvxuzr3.png)

![Configuring Parameter Group](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/33xhybjs6mhymlvry1xm.png)

And save changes:
![Parameter Group created](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/tzsnetodqf4mrc23a3me.png)
  
<u>Subnet group</u>
Using subnet group we can specify in which subnets the database will be deployed. So in this case I will select public subnets to be able to connect to the DB easily.

Click on the 'Create DB subnet group' button:
![Creating subnet group](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/irj3youwyujw2lcw2e32.png)

Name:
sg-db-publicaccess
Description:
Using public subnets for demo only
VPC:
your_VPC_id
Availability Zones:
az_ids (is mandatory to select at least 2)
![Configuring subnet group](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/zz0bi0oo5s15vd1s06t1.png)
![Configuring subnet group](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/wcd7273egenh7u9njnps.png)

And now the subnet groups is created:
![Subnet group created](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/jnrp6hyihsejtri0f13u.png)
 


<u>Database</u>

Click on the 'Create Database' button:
![Creating database](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/0eojz3i74zog0i2najcz.png)

Database creation method: Standard create
_Engine type: Amazon Aurora_
_Edition: Amazon Aurora MySQL-Compatible Edition_
Replication features: Single-master (default)
_Engine version: Aurora MySQL 3.02.0 (compatible with MySQL 8.0.23)_
Templates: Dev/Test
DB cluster identifier: db-test-cdc
Master username: admin
Master password: supersecretpassword
_DB instance class: Serverless v2 - new_
Capacity range - Minimum ACUs: 0.5
Capacity range - Maximum ACUs: 1
Multi-AZ deployment: Don't create an Aurora Replica
Virtual private cloud (VPC): your_VPC_id
_Subnet group: sg-db-publicaccess_ (the subnet group previously created)
Public access: Yes
VPC security group: Create new
New VPC security group name: rds-publicaccess
Availability Zone: No preference
Database port: 3306
Database authentication options: Password authentication
Initial database name: sampledb
_DB cluster parameter group: pg-servelessv2-cdc_ (the parameter group previously created)
DB parameter group: default.aurora-mysql8.0


![Creating database](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/uvj0rjxbviskteixyjko.png)

![Creating database](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/gdl0wa20jmci6g5pescv.png)

![Creating database](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/r6j1p1u2ge2jlzxy734r.png)

![Creating database](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/9ydqw376iwyvv6mrme51.png)
 
![Creating database](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/3fc7l7x6922q481z4gwg.png)
 

![Creating database](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/1im4oexk4vlkg8cwz8da.png)

![Creating database](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/j0gqgxwtmn9a2njbgef7.png)
 
![Creating database](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/y7xdcyolczlstxobfo7y.png)

Click on Create database (it takes approximately 15 minutes to create completely):
![Creating database](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/iy2setm0mdep7g5iqp82.png)

Also modifiy the created Security Group (rds-publicaccess) to allow inboud traffic on port 3306 from 0.0.0.0/0

![Modifying Security Group](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/dra7b1rm3nx9u6fr1bae.png)
 

**Creating the S3 bucket**
The next step is to create an S3 bucket which will be used as the target destination for the full load and change data capture task.
It does not required any special configuration, we can use a bucket with default creation parameters:
Name: test-dms-s3-target

![Creating S3 bucket](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/y3c4ffbe9m3gth53nlvx.png)
 
![Creating S3 bucket](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/rgo1rpvx0xkvhae9rqtx.png)

![Creating S3 bucket](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/kx523zbqvyugn0g5l67v.png)
 
**Creating the DMS Replication Instance**

We need to create the DMS replication subnet group. Similar to database subnet group, it helps to specify the subnets that will be used. In this case I am selecting private subnets but can be any with proper connectivity to the database instance and S3 bucket.
Name: sg-dms

![Creating subnet group](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ndeermgo3lmkexqic2kq.png)


Additionally, create the Security group that will be attached to the DMS replication instance. It requires allow outbound traffic.
![Creating DMS security group](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/hy4sk1eouhzj4w948czw.png)

And create the DMS Replication Instance:
![Creating DMS Replication Instance](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ju22vyvw7pe6qsppz69b.png)

Name: dms-instance
Instance class: dms.t3.small
Engine version: 3.4.6
Allocated storage: 20GB
VPC: The same VPC as the one RDs instance belongs to
Multi AZ: Dev or test worload
Publicly accessible: No
Replication subnet group: sg-dms (the previously created)
Availability zone: No preference
VPC security group(s): dms-replication-instance (the previously created)
KMS key: Default

![Creating DMS Replication Instance](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/3j4vrfdtdgxsqpieg98o.png)
 
![Creating DMS Replication Instance](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/g8ycz3xxumlur6gxfli1.png)
 
**Inserting sample data into the database**
Before create the DMS task I am going to insert sample data into the database to validate the task works as expected. The database imported is from MySQL sample, specifically, [the Sakila sample database](https://downloads.mysql.com/docs/sakila-db.zip). These are 2 scripts to execute so it is very quick to replicate.
Connect to the database using the user and password configured and setting the Database endpoint properly (in case of connection errors check the SG allow traffic on port 3306 for the IP range):

![Connecting to DB](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/l412nl9a6a8wv1rlszla.png)

![Connecting to DB](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/z5slekhpqfy4arllgxlb.png)

Then execute the downloaded scripts: sakila-schema.sql and sakila-data.sql

![Inserting sample data](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/1lmxqc2jndanksa83f79.png)

And now we have populated the database:
![Inserting sample data](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/3w16g6r9okmlxqan5i9q.png)


**Creating the DMS Tasks**
Create the Source Endpoint to connect to the database and the Target Endpoint to connect to the S3 bucket:

![Creating endpoint](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/fjyw93rfwx3y4qabbzjb.png
)

![Creating source endpoint](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/qgkujhm9gfjcj8m6bkox.png)

![Creating source endpoint](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/1qkeywerjprtq834v95r.png)

![Creating source endpoint](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/4sjkgbq7d6k5odzro50r.png)
 
And test the connection:
![Testing connection](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/x480y3lbu0lxwii4z326.png)

![Testing connection](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/38gk21wv6zaxxqcrbn2b.png)


Before create the Target Endpoint for the S3 bucket, we need to create an IAM role granting access to put/delete objects on the bucket:

Select DMS as the AWS service:
![Creating IAM Role](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/4u6fvq78tv15f1ssthx6.png)

Don't select any policies for now:
![Creating IAM Role](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/7gl4iou8gjtifq09966y.png)

Select an proper name and create the role:
![Creating IAM Role](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/dv7ev25knraz62as1hzy.png)

![Creating IAM Role](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/2nb2nq4yowly822qz6rk.png)

And Add permissions -> Create inline policy
![Creating IAM Role](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/v3puwmzfw1vp0fzgshaw.png)

The policy is as following (change the bucket name for the corresponding value):

```
{
    "Version" : "2012-10-17",
    "Statement" : [ 
      {
        "Effect" : "Allow",
        "Action" : [
          "s3:PutObject",
          "s3:DeleteObject"
        ],
        "Resource" : "arn:aws:s3:::test-dms-s3-target/*"
      },
      {
        "Effect" : "Allow",
        "Action" : "s3:ListBucket",
        "Resource" : "arn:aws:s3:::test-dms-s3-target"
      } 
    ]
  }
```
![Creating IAM Role](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/hnxqa0bjohxtauy8jjay.png)

![Creating IAM Role](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/nlk5v9jq2av3avcfvdmx.png)

Now, we can create the Target endpoint:
![Creating Target Endpoint](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ykv46drluk9kmact35n1.png)


![Creating Target Endpoint](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/zyf8fiyubxogc6f3aphe.png)

![Creating Target Endpoint](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/27vhxj6ke6opeir80p3n.png)

And test the connection:
![Testing Target Endpoint](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/aasfk65sy9a40fi8h49m.png)


To create the Database Migration Task we use the resources previously created:

Task identifier: full-load-and-cdc-auroraserverless

![Creating dms task](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/yyehyg2b500srb904vmp.png)

For Task setting in this demo we can leave the default parameters except for the Enable CloudWatch logs which will be helpful to validate everything is Ok:

![Creating dms task](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/rtl0ifjeku0afoqaycwp.png)

![Creating dms task](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/hvdxb5yybh44h8th7lpw.png)

For Table mappings we only need to specify the schema (sakila in this case) and leave the other parameters with default values:

![Creating dms task](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/u8l0x2amirz3nvvejmdd.png)

![Creating dms task](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/5nmhce171bhfgcu1o6g2.png)

And finally click on Create task:
![Creating dms task](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/net8xscccksxpi795ey1.png)


**Validating the CDC feature**
Lets keep the task running for some minutes untill the state is "Load complete, replication ongoing":
![Validating task](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/2yyk9ntyr70qvbsmzrb7.png)

Check from table statistics that the data was loaded successfully:
![Validating task](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/a3tzs8v05h4ljlho4215.png)

The relevant fields are Full load rows and Total rows:
![Validating task](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ywjxxkn4i7yodbkwrhia.png)

If you navigate through the bucket objects, you will notice there is a folder for each table:
![Validating task](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/nr8enmma7fkqb6szkj6x.png)

And for the first load is structured as follows:
![Validating task](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/7oqfrny4493utcnsm62p.png)

To check the CDC in action, lets insert some rows:

```
INSERT INTO `sakila`.`customer` (`customer_id`, `store_id`, `first_name`, `last_name`, `email`, `address_id`, `active`, `create_date`, `last_update`) VALUES ('600', '2', 'JOHN', 'SMITH', 'JOHN.SMITH@sakilacustomer.org', '605', '1', '2006-02-14 22:04:37', '2006-02-15 04:57:20');
INSERT INTO `sakila`.`customer` (`customer_id`, `store_id`, `first_name`, `last_name`, `email`, `address_id`, `active`, `create_date`, `last_update`) VALUES ('601', '2', 'WILL', 'CARTER', 'WILL.CARTER@sakilacustomer.org', '605', '1', '2006-02-14 22:04:37', '2006-02-15 04:57:20');
INSERT INTO `sakila`.`customer` (`customer_id`, `store_id`, `first_name`, `last_name`, `email`, `address_id`, `active`, `create_date`, `last_update`) VALUES ('602', '2', 'DONALD', 'JACKSON', 'DONALD.JACKSON@sakilacustomer.org', '605', '1', '2006-02-14 22:04:37', '2006-02-15 04:57:20');
```

And also delete some rows from the customer table:

```
DELETE FROM `sakila`.`customer` WHERE (`customer_id` = '9');
DELETE FROM `sakila`.`customer` WHERE (`customer_id` = '15');
DELETE FROM `sakila`.`customer` WHERE (`customer_id` = '4');
DELETE FROM `sakila`.`customer` WHERE (`customer_id` = '433');
DELETE FROM `sakila`.`customer` WHERE (`customer_id` = '599');
```

After a couple of minutes (it could take 2 or 3 minutes), the changes should be reflected on both the task statistics and S3 bucket:
![Validating task](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/pencvicbzwdhd160w6c0.png)
![Validating task](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/af3wgcs52yj4vkfp67gm.png)

![Validating task](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ctt3p3rfgwo4y9gchbmk.png)
 
 
**Conclusion**
With the new version of Aurora serverless there is not limitation on setting up DMS migration tasks with Change Data Capture. It allows to take advantage of the serverless model and also the configuration is very similar to provisioned version.