I'm facing an issue when using DMS to replicate data from a SQL Server (RDS) to Kinesis Streams. I'm replicating ~20 tables (~1TB) in an environment A and the replication is only ON GOING (through CDC), not full load. The process goes fine until it fails and stops.
I tested that in our B and C envs (the environments are supposed to be identical), and it worked fine (CDC on SQL Server -> DMS -> Kinesis), but in env A (the RDS SQL Servers in the 3 envs has the same configs: version, year, etc.), I'm getting the following error logs:
2020-02-19T20:22:06 [SOURCE_CAPTURE ]E: Failed (retcode -1) to execute statement [1022502] (ar_odbc_stmt.c:XXXX)
2020-02-19T20:22:06 [SOURCE_CAPTURE ]E: RetCode: SQL_ERROR SqlState: 42000 NativeError: 21089 Message: [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Only members of the sysadmin fixed server role can perform this operation. Line: 1 Column: -1 [1022502] (ar_odbc_stmt.c:XXXX)
2020-02-19T20:25:06 [SOURCE_CAPTURE ]E: mssql_execute_log_lsn_peeper(...) failed upon SQLExecute with LSN parameters '0x','0x' [1020102] (sqlserver_log_queries.c:XXX)
2020-02-19T20:25:06 [SOURCE_CAPTURE ]E: sqlserver_capture_source_loop (...) encountered an unexpeceted error. Aborting... [1020102] (sqlserver_endpoint_capture.c:XXX)
2020-02-19T20:25:06 [SOURCE_CAPTURE ]E: Error executing source loop [1020102] (streamcomponent.c:XXXX)
2020-02-19T20:25:06 [TASK_MANAGER ]E: Task error notification received from subtask 0, thread 0 [1020102] (replicationtask.c:XXXX)
2020-02-19T20:25:06 [TASK_MANAGER ]E: Stream component failed at subtask 0, component st_0_XXXXXXXXXXXXXXXXXXXXXXXXXX [1020102] (subtask.c:XXXX)
2020-02-19T20:25:06 [TASK_MANAGER ]E: mssql_execute_log_lsn_peeper(...) failed upon SQLExecute with LSN parameters '0x','0x'; sqlserver_capture_source_loop (...) encountered an unexpeceted error. Aborting...; Error executing source loop; Stream component failed at subtask 0, component st_0_XXXXXXXXXXXXXXXXXXXXXXXXXX ; Stream component 'st_0_XXXXXXXXXXXXXXXXXXXXXXXXXX' terminated [1020102] (replicationtask.c:XXXX)
2020-02-19T20:25:06 [SOURCE_CAPTURE ]E: Stream component 'st_0_XXXXXXXXXXXXXXXXXXXXXXXXXX' terminated [1020102] (subtask.c:XXXX)
... And it repeats over and over...
I also did the same replication (CDC, on going only) using S3 as the target endpoint in all the 3 envs, and it worked beautifully. However, with Kinesis, I can see the replication starts and goes for, let's say, 4 or 5 hours, and then it dies.
Any ideas on how I could solve it?
