Requirements for SQL Server Source with MDC on AWS DMS

To use SQL Server as a source with AWS DMS for MDC replication, you must ensure proper configuration of the SQL Server port, credentials, backups, and Microsoft Change Data Capture (MS CDC) settings.

Before you begin

The following requirements will ensure a smooth and efficient data replication process:
  • SQL Port Accessibility:
    • The SQL Server port must be accessible to allow connectivity.
    • SQL user credentials must be provided to establish a source endpoint connection.
    • Two SQL user accounts are needed:
      • One for the source endpoint connection from AWS DMS to SQL Server using SQL Server authentication.
      • Another SQL user dedicated to purge activities for incremental/periodic purge activity.

      The configuration scripts referred to in the Setting up AWS DMS Replication Instance without Sysadmin Role page are based on AWS DMS Documentation.

  • Full Backup Configuration:
    • SQL Server must be configured to support full backups.
    • Perform a full backup before beginning data replication.
    • Set the SQL Server recovery model to either Bulk Logged or Full. The recovery model determines how transaction log backups are managed and is essential for supporting point-in-time recovery and replication.
    • SQL Server backups that are spread across multiple disks are not supported.
  • CDC requirements for transaction log backups:
    • AWS DMS requires access to SQL Server transaction log backups to read changes.
    • Transaction log backups must be created using SQL Server's built-in backup tools; third-party backup software that does not create backups in this format is not supported.
  • CDC requirements for full transaction logging:
    • Ensure full transaction logging is enabled in SQL Server.
    • The MS-REPLICATION feature must be enabled.
    • SQL Server transaction log entries will not be marked for reuse until the MS CDC capture job processes the changes.
  • Distribution Database:
    • AWS DMS CDC requires a distribution database by default when using Amazon Elastic Compute Cloud (EC2) or an on-premises SQL Server as the source.
    • Ensure the distributor is activated while configuring MS replication for tables with primary keys.
Note:
After completing the steps in this section, the non-sysadmin DMS user will have permissions to do the following:
  • Read changes from the online transaction log file.
  • Access disk to read changes from transactional log backup files.
  • Add or modify the publication that DMS uses.
  • Add articles to the publication.

What to do next

Setting up AWS DMS Replication Instance without Sysadmin Role.