Navigating the Challenges of AWS DMS with SQL Server
Written on
Chapter 1 Understanding AWS DMS
AWS Database Migration Service (DMS) is designed to facilitate ongoing replication, ensuring that your source and target databases remain in sync. However, it's important to recognize certain challenges that may arise during its use in a production environment. This article outlines some common issues associated with maintaining a target database synchronized with a source RDS SQL Server database.
Section 1.1 Data Integrity Concerns
One significant issue that users may face is the appearance of corrupted data in the target database. This occurs when some rows display values from other rows, particularly with nvarchar(max) columns during replication.
To identify this, it's advisable to establish a separate full load DMS task that syncs the same data to reference target tables. Upon completion, you can compare the original replicating target tables with the reference tables. If discrepancies are found, a temporary solution could involve periodically reloading the target tables. However, this method may not be effective if your target table contains foreign key references.
To ensure ongoing replication without issues, it’s recommended to convert nvarchar(max) columns on the source database to fixed-length columns. This adjustment will necessitate the recreation of any constraints, indexes, and statistics associated with those columns.
Subsection 1.1.1 Handling Minor Release Bugs
DMS version 3.4.3 introduces numerous bug fixes and features, which may tempt users to upgrade from version 3.4.2 or automatically upgrade if the minor version upgrade setting is enabled on the replication instance. However, be cautious, as enabling backups for your SQL Server RDS may result in DMS tasks failing with the error message indicating that AlwaysOn BACKUP-ed data is unavailable.
Downgrading can be complicated since DMS does not support reverting to a previous engine version. To address this, you'll need to create a new replication instance running the desired lower version and then manually recreate all your tasks.
Section 1.2 Managing Transaction Logs
DMS employs a dummy transaction to maintain transactions on the transaction log, which enhances replication speed compared to reading from backups. However, if DMS loses track of this transaction, the transaction logs may grow uncontrollably, consuming all available storage space.
The most effective way to resolve this issue is to reach out to AWS Support. They can assist in identifying tables with Change Data Capture (CDC) enabled, disabling CDC, truncating the log, and re-establishing CDC.
This problem is particularly likely to occur when syncing large volumes of data daily. In such cases, the most reliable solution may involve transitioning that specific workload out of DMS and implementing a custom solution.
Chapter 2 Addressing Common Issues with DMS
DMS can occasionally encounter task failures and table errors, even if the replication instances are well-provisioned and the update volume remains typical. To mitigate these errors, consider setting up a monitoring Lambda function that checks the status of tasks and tables. This allows you to either manually or automatically restart tasks or reload table data, though be aware that target data may be temporarily incomplete during this process.
This video titled "AWS Database Migration Service (DMS) SQLServer to S3 bucket - Full load + CDC" delves into the intricacies of using AWS DMS for data migration, including techniques for ensuring data integrity.
In the video "How to use AWS DMS for Data Migration in Redshift from SQL Server?", viewers will learn effective strategies for migrating data from SQL Server to Redshift using AWS DMS.
DMS is indeed a valuable tool for data migration. However, it is not a “set it and forget it” solution for ongoing replication. Regular monitoring of both the source and target databases is essential to ensure that everything functions as intended.