DMS Task Stuck or Failing During CDC

Sign in, start onprem-sql and the endpoints, and add the helpers. Invoke-Sql runs T-SQL on onprem-sql; Get-DmsHealth shows the task, its tables and both row counts:

cd C:\aws-lab
aws login --profile saat-admin
$env:AWS_PROFILE = 'saat-admin'
$v = Get-Content C:\aws-lab\saat-vpc.json | ConvertFrom-Json
.\Start-SsmEndpoints.ps1
$src = aws ec2 describe-instances --filters "Name=tag:Name,Values=onprem-sql" "Name=instance-state-name,Values=stopped,running" --query "Reservations[0].Instances[0].InstanceId" --output text
$srcIp = aws ec2 describe-instances --instance-ids $src --query "Reservations[0].Instances[0].PrivateIpAddress" --output text
$srcSg = aws ec2 describe-security-groups --filters "Name=group-name,Values=saat-onprem-sql-sg" --query "SecurityGroups[0].GroupId" --output text
aws ec2 start-instances --instance-ids $src | Out-Null
do { Start-Sleep 20; $p = aws ssm describe-instance-information --filters "Key=InstanceIds,Values=$src" --query "InstanceInformationList[0].PingStatus" --output text } until ($p -eq 'Online')
function Invoke-Ps([string]$Line) {
  @{ commands = @($Line) } | ConvertTo-Json | Set-Content src-cmd.json
  $cmd = aws ssm send-command --document-name AWS-RunPowerShellScript --instance-ids $src --parameters file://src-cmd.json --query Command.CommandId --output text
  do { Start-Sleep 5; $st = aws ssm get-command-invocation --command-id $cmd --instance-id $src --query Status --output text 2>$null } until ($st -in 'Success', 'Failed', 'TimedOut')
  (aws ssm get-command-invocation --command-id $cmd --instance-id $src --query StandardOutputContent --output text).Trim()
}
function Invoke-Sql([string]$Database, [string]$Sql) { Invoke-Ps ('sqlcmd -S . -E -C -h -1 -W -s " " -d ' + $Database + ' -Q "SET NOCOUNT ON; ' + $Sql + '"') }
function Get-DmsHealth {
  aws dms describe-replication-tasks --filters "Name=replication-task-arn,Values=$task" --query "ReplicationTasks[0].[Status,StopReason,LastFailureMessage]" --output text
  aws dms describe-table-statistics --replication-task-arn $task --query "TableStatistics[].[TableName,TableState,Inserts,AppliedInserts]" --output text
  "source $(Invoke-Sql SaatShop 'SELECT COUNT(*) FROM dbo.Orders;')  target $(Invoke-Sql SaatShopReplica 'SELECT COUNT(*) FROM dbo.Orders;')"
}
Invoke-Sql SaatShop 'SELECT COUNT(*) FROM dbo.Orders;'

You should see something like this:

50372

1Prepare the source and the replica database

Paste this whole block. It sets a new password for dms_user, turns on FULL recovery and MS-CDC, and creates the target database:

$dmsPw = aws secretsmanager get-random-password --password-length 20 --exclude-punctuation --require-each-included-type --query RandomPassword --output text
Invoke-Sql master "ALTER LOGIN dms_user WITH PASSWORD = '$dmsPw'; ALTER SERVER ROLE sysadmin ADD MEMBER dms_user; CREATE DATABASE SaatShopReplica;" | Out-Null
Invoke-Sql SaatShop "ALTER DATABASE SaatShop SET RECOVERY FULL; BACKUP DATABASE SaatShop TO DISK = 'C:\sqlbak\dms64-full.bak' WITH INIT; EXEC sys.sp_cdc_enable_db;" | Out-Null
foreach ($t in 'Orders', 'Customers', 'OrderAudit') { Invoke-Sql SaatShop "EXEC sys.sp_cdc_enable_table @source_schema = N'dbo', @source_name = N'$t', @role_name = NULL, @supports_net_changes = 1;" | Out-Null }
Invoke-Sql SaatShop "SELECT name + ' ' + CAST(is_tracked_by_cdc AS varchar(1)) FROM sys.tables WHERE schema_id = SCHEMA_ID('dbo') ORDER BY name;"

You should see something like this:

Customers 1
OrderAudit 1
Orders 1

All three tables are tracked, the log chain has started, and SaatShopReplica waits as the target. Using the same server for both keeps the bill down; DMS treats them as two separate endpoints.

2Start the replication instance and endpoints

Paste this whole block (about 15 minutes):

$dmsSg = aws ec2 create-security-group --group-name saat-dms-sg --description "DMS replication instance" --vpc-id $v.VpcId --query GroupId --output text
aws ec2 authorize-security-group-ingress --group-id $srcSg --ip-permissions "IpProtocol=tcp,FromPort=1433,ToPort=1433,UserIdGroupPairs=[{GroupId=$dmsSg,Description=1433-from-dms}]" | Out-Null
aws dms create-replication-subnet-group --replication-subnet-group-identifier saat-dms-subnets --replication-subnet-group-description "saat-vpc private subnets" --subnet-ids $v.PrivateA $v.PrivateB | Out-Null
$ri = aws dms create-replication-instance --replication-instance-identifier saat-dms --replication-instance-class dms.t3.medium --allocated-storage 20 --replication-subnet-group-identifier saat-dms-subnets --vpc-security-group-ids $dmsSg --no-publicly-accessible --no-multi-az --tags Key=course,Value=saatvedha-aws Key=lesson,Value=64 --query ReplicationInstance.ReplicationInstanceArn --output text
aws dms wait replication-instance-available --filters "Name=replication-instance-arn,Values=$ri"
$srcEp = aws dms create-endpoint --endpoint-identifier saat-shop-src --endpoint-type source --engine-name sqlserver --server-name $srcIp --port 1433 --database-name SaatShop --username dms_user --password $dmsPw --query Endpoint.EndpointArn --output text
$tgtEp = aws dms create-endpoint --endpoint-identifier saat-replica-tgt --endpoint-type target --engine-name sqlserver --server-name $srcIp --port 1433 --database-name SaatShopReplica --username dms_user --password $dmsPw --query Endpoint.EndpointArn --output text
foreach ($ep in $srcEp, $tgtEp) { aws dms test-connection --replication-instance-arn $ri --endpoint-arn $ep | Out-Null }
foreach ($ep in $srcEp, $tgtEp) { aws dms wait test-connection-succeeds --filters "Name=endpoint-arn,Values=$ep" }
aws dms describe-connections --filters "Name=replication-instance-arn,Values=$ri" --query "Connections[].[EndpointIdentifier,Status]" --output text

You should see something like this:

saat-shop-src  successful
saat-replica-tgt        successful

3Run full load and CDC, and check its health

Paste this whole block. The mapping excludes DMS’s own control tables:

'{ "rules": [ { "rule-type": "selection", "rule-id": "1", "rule-name": "dbo-all", "object-locator": { "schema-name": "dbo", "table-name": "%" }, "rule-action": "include" }, { "rule-type": "selection", "rule-id": "2", "rule-name": "no-dms-tables", "object-locator": { "schema-name": "dbo", "table-name": "awsdms%" }, "rule-action": "exclude" } ] }' | Set-Content mappings64.json
'{ "Logging": { "EnableLogging": true }, "FullLoadSettings": { "TargetTablePrepMode": "DROP_AND_CREATE" } }' | Set-Content task64.json
$task = aws dms create-replication-task --replication-task-identifier saat-shop-cdc64 --source-endpoint-arn $srcEp --target-endpoint-arn $tgtEp --replication-instance-arn $ri --migration-type full-load-and-cdc --table-mappings file://mappings64.json --replication-task-settings file://task64.json --query ReplicationTask.ReplicationTaskArn --output text
aws dms wait replication-task-ready --filters "Name=replication-task-arn,Values=$task"
aws dms start-replication-task --replication-task-arn $task --start-replication-task-type start-replication | Out-Null
aws dms wait replication-task-running --filters "Name=replication-task-arn,Values=$task"
do { Start-Sleep 30; $ts = aws dms describe-table-statistics --replication-task-arn $task --query "TableStatistics[?TableName=='Orders'].TableState | [0]" --output text } until ($ts -eq 'Table completed')
Invoke-Sql SaatShop "INSERT dbo.Orders (Customer, Amount, CustomerId) SELECT TOP (25) CONCAT(N'CDC ', ROW_NUMBER() OVER (ORDER BY (SELECT NULL))), 99.00, 1 FROM sys.all_objects;" | Out-Null
Start-Sleep 60
Get-DmsHealth

You should see something like this:

running None    None
Orders  Table completed 25      25
Customers       Table completed 0       0
OrderAudit      Table completed 0       0
source 50397  target 50397

The task is running with no failure message, the 25 new orders were captured and applied, and the counts match. This is the picture you want to see every morning.

✅ Checkpoint: Both endpoints test successful, the task is running after full load, Orders shows 25 inserts applied, and source and target both have 50,397 orders.