SQL Server Endpoint Troubleshooting
This section contains replication scenarios specific to SQL Server. To determine what changes to replicate from SQL server Amazon DMS reads the transaction logs, and runs periodic scans on the source database. Replication latency usually results from SQL Server throttling these scans because of resource constraints. It can also result from a significant increase in the number of events written to the transaction log in a short time.
Topics
Index rebuilds
When SQL Server rebuilds a large index, it uses a single transaction. This generates a lot of events, and can use up a large amount of log space if SQL Server rebuilds several indexes at once. When this happens, you can expect brief replication spikes. If your SQL Server source has sustained log spikes, check the following:
First, check the time period of the latency spikes using either the
CDCLatencySourceandCDCLatencySourceCloudWatch metrics, or by checking Throughput Monitoring messages in the task logs. For information about CloudWatch metrics for Amazon DMS, see Replication task metrics.Check if the size of the active transaction logs or log backups increased during the latency spike. Also check if a maintenance job or a rebuild ran during that time. For information about checking transaction log size, see Monitor log space use
in the SQL Server technical documentation . Verify that your maintenance plan follows SQL server best practices. For information about SQL server maintenance best practices, see Index maintenance strategy
in the SQL Server technical documentation .
To fix latency issues during index rebuilds, try the following:
Use the
BULK_LOGGEDrecovery model for offline rebuilds to reduce the events a task has to process.If possible, stop the task during index rebuilds. Or, try to schedule index rebuilds during non-peak hours to mitigate the impact of a latency spike.
Try to identify resource bottlenecks that are slowing DMS reads, such as disk latency or I/O throughput, and address them.
Large transactions
Transactions with a lot of events, or long-running transactions, cause the transaction log to grow. This causes DMS reads to take longer, resulting in latency. This is similar to the effect index rebuilds have on replication performance.
You may have difficulty identifying this issue if you're not familiar with the typical workload on the source database. To troubleshoot this issue, do the following:
First, identify the time that latency spiked using either the
ReadThroughputandWriteThroughputCloudWatch metrics, or by checking Throughput Monitoring messages in the task logs.Check if there are any long-running queries on the source database during the latency spike. For information about long-running queries, see Troubleshoot slow-running queries in SQL Server
in the SQL Server technical documentation . Check if the size of the active transaction logs or the log backups has increased. For more information, see Monitor log space use
in the SQL Server technical documentation .
To fix this issue, do one of the following:
The best fix is to restructure your transactions on the application side so that they complete quickly.
If you can't restructure your transactions, a short-term workaround is to check for resource bottlenecks such as disk waits or CPU contention. If you find bottlenecks in your source database, you can reduce latency by increasing disk, CPU, and memory resources for source database. This reduces contention for system resources, allowing DMS queries to complete faster.
Misconfigured MS-CDC polling interval for Amazon RDS SQL Server
A misconfigured polling interval setting on Amazon RDS instances can cause the transaction log to grow. This is because replication prevents log truncation. While tasks that are running might continue replicating with minimal latency, stopping and resuming tasks, or starting CDC-only tasks, can cause task failures. These are due to timeouts while scanning the large transaction log.
To troubleshoot a misconfigured polling interval, do the following:
Check if the active transaction log size is increasing, and if log usage is close to 100 percent. For more information, see Monitor log space use
in the SQL Server technical documentation . Check if log truncation is delayed with a
log_reuse_wait_desc valueofREPLICATION. For more information, see The Transaction Log (SQL Server)in the SQL Server technical documentation .
If you find issues with any of the items in the previous list, tune the MS-CDC polling interval. For information about tuning the polling interval, see Recommended settings when using Amazon RDS for SQL Server as a source for Amazon DMS.
Multiple CDC tasks replicating from the same source database
During the full load phase, we recommend splitting tables across tasks to improve performance, to separate dependent tables logically, and to mitigate the impact of a task failure. However, during the CDC phase, we recommend consolidating tasks to minimize DMS scans. During the CDC phase, each DMS task scans the transaction logs for new events several times a minute. Since each task runs independently, every task scans each transaction log individually. This increases disk and CPU usage on the source SQL Server database. As a result, a large number of tasks running in parallel can cause SQL Server to throttle DMS reads, leading to increased latency.
You may have difficulty identifying this issue if muliple tasks start gradually. The most common symptom of this
issue is most task scans starting to take longer. This leads to higher latency for these scans.
SQL Server prioritizes a few of the task scans, so a few of the tasks show normal latency.
To troubleshoot this issue, check the CDCLatencySource metric for all of your tasks. If some of the tasks have an increasing
CDCLatencySource, while a few tasks have a low CDCLatencySource, it is likely
that SQL Server is throttling your DMS reads for some of your tasks.
If SQL Server is throttling your task reads during CDC, consolidate your tasks to minimize the number of DMS scans. The maximum number of tasks that can connect to your source database without creating contention depends on factors such as the source database capacity, the rate of transaction log growth, or the number of tables. To determine the ideal number of tasks for your replication scenario, test replication in a test environment similar to your production environment.