I am using AWS DMS to replicate data from MSSQL to Aurora Postgres. After some research, I found out that we need to enable CDC in the SQL server using this documentation. Also, I came to know that DMS queries fn_dblog() to read the logs. As mentioned in that document, I set the polling interval to 1 hour (3599 seconds) which is considered a retention period.
exec sp_cdc_change_job @job_type='capture', @pollinginterval =3599
But what it actually does is execute every hour and delete the logs which are added in less than 5 mins.
I also found that there is a cleanup job where we can specify the retention period using this official documentation.
exec sp_cdc_change_job @job_type='cleanup', @retention=1440
Still, the logs are getting deleted after a few minutes. To keep it simple, how to retain the data from the fn_dblog() function?
