How do I resolve task failures and CDC errors for AWS DMS tasks that use SQL Server as the source?

Resolution

To identify the issue, use Amazon CloudWatch to review the task logs. Then, take action based on the issue that you identified.

"Insufficient Permissions" error

For AWS DMS CDC tasks, the AWS DMS user must have the db_owner permision on the source SQL Server database. If your task is missing this permission, then you receive the following error message:

"[SOURCE_CAPTURE ]E: RetCode: SQL_ERROR SqlState: 42000 NativeError: 22904 Message: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Caller is not authorized to initiate the requested action. DBO privileges are required."

To grant the AWS DMS user the db_owner permission, run the following command:

use [db_name]
ALTER ROLE [db_owner] ADD MEMBER dms_user;

Note: Replace db_name with the source database name.

Make user that you grant the AWS DMS user the required permissions on the source SQL Server.

"Full backup missing" error

If you don't configure your on-premises database to fully back up changes from a source SQL Server database, then you receive the following error message:

"[SOURCE_CAPTURE ]E: No FULL database backup found (under the 'FULL' recovery model). To enable all changes to be captured, you must perform a full database backup."

You can configure the database either in full recovery mode or bulk-logged mode.

To check whether a full backup exists, run the following command:

SELECT bs.database_name, bs.backup_start_date, bs.backup_finish_date, CASE bs.type WHEN 'D' THEN 'Full' WHEN 'I' THEN 'Differential' WHEN 'L' THEN 'Transaction Log' END AS BackupTypeFROM msdb.dbo.backupset bs WHERE bs.database_name = 'db_name' AND bs.type = 'D'ORDER BY bs.backup_finish_date DESC;`

Note: Replace db_name with your database name.

MS-Replication or MS-CDC full logging activation issues

For a self-managed SQL Server source or Azure managed instance source, AWS DMS uses MS-Replication to capture changes for tables with primary keys. To manually configure MS-Replication, grant the sysadmin permission to the AWS DMS endpoint user on the source SQL Server instance. To capture changes for tables without primary keys, AWS DMS uses MS-CDC. Activate MS-CDC at the database level and individually for each table.

Note: For a self-managed SQL Server source or Azure managed instance source, MS-Replication is required for AWS DMS CDC. If all your tables have a primary key in the source database, then MS-CDC is optional.

For an Amazon Relational Database Service (Amazon RDS) for SQL Server source, AWS DMS uses MS-CDC for tables with or without primary keys.

If you didn't install your distributor, then you receive the following error message:

"[TASK_MANAGER ]E: Failed (retcode -1) to execute statement; RetCode: SQL_ERROR SqlState: 42000 NativeError: 20028 Message: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The Distributor has not been installed correctly. Could not enable database for publishing. Line: 1 Column: -1; Failed while preparing stream component"

To check whether you configured the distribution, run the following command:

EXEC sp_get_distributor

If the output is NULL for the distribution column, then use the SQL Server Management Studio (SSMS) to set up distribution.

Complete the following steps:

1. Use SSMS to connect to the SQL Server source database. For more information, see Quickstart: Connect and query a SQL Server instance using SQL Server Management Studio (SSMS) on the Microsoft website.
2. Open (right-click) the Replication folder, and then choose Configure Distribution.
3. Complete the Configure Distribution Wizard with the default values.

To turn on MS-CDC at the database level for a self-managed SQL Server, run the following command:

use [DBname]
EXEC sys.sp_cdc_enable_db

Note: Replace DBname with your database name.

To turn on MS-CDC for an Amazon RDS for SQL Server, see Setting up ongoing replication on a cloud SQL Server DB instance.

To turn on CDC for each table that you want to replicate, run the sp_cdc_enable_table command. To turn on CDC for all tables, you can configure the SetUpMsCDCForTables=true endpoint setting on your AWS DMS source endpoint. For more information, see How can I add or modify endpoint settings for AWS DMS endpoints?

Transaction log backup issues

AWS DMS tries to capture unread changes from the active transaction log (TLOG). However, the TLOG might not capture all unread changes because of truncation. For more information, see Transaction log backups (SQL Server) on the Microsoft website. If TLOG doesn't capture your unread changes, then AWS DMS accesses the log backup to capture the missing changes.

Make sure that your TLOG backups are available.

Note: AWS DMS doesn't support encrypted backups.

For Amazon RDS for SQL Server source databases, turn on automated backups.

If AWS DMS can't find the required TLOG backups, then you receive the following error message:

"02760599: 2023-11-14T21:38:25 [SOURCE_CAPTURE ]E: Failed to access LSN '0004be15:000045ad:0005' in the backup log sets since BACKUP/LOG-s are not available."

If you moved the TLOG backups to a different location, then restore them to the original path for a self-managed instance. If you deleted your backups, then you must restart the task.

To make sure that the log sequence number (LSN) is available when you resume tasks, retain the transaction log on your source SQL Server.

To retain the logs for Amazon RDS for SQL Server instances, specify the pollinginterval, maxtrans, and maxscans parameters. Retain the logs in TLOG for a longer duration. For more information, see How can I troubleshoot T-Log full issues on my RDS for SQL Server source when CDC is turned on for my AWS DMS task?

Also, check that you have the required permission to download the TLOG backups from Amazon RDS. To grant the download permission, run the following command:

USE msdb;
    CREATE USER dms_user FOR LOGIN rds_user;
    GRANT EXEC ON msdb.dbo.rds_dms_tlog_download TO dms_user;
    GRANT EXEC ON msdb.dbo.rds_dms_tlog_read TO dms_user;
    GRANT EXEC ON msdb.dbo.rds_dms_tlog_list_current_lsn TO dms_user;
    GRANT EXEC ON msdb.dbo.rds_task_status TO dms_user;

For self-managed SQL Server or Azure managed instances, make sure that you set the ActivateSafeguard endpoint setting to True. Then, specify the log reader agent job parameters. To configure the log reader, see Enhance transactional replication performance on the Microsoft website.

Related information

Troubleshooting issues with Microsoft SQL Server

Limitations on using SQL Server as a source for AWS DMS