Creating a Publication for AWS DMS in SQL Server

Creating a publication for AWS DMS in SQL Server is a critical step in setting up SQL Server as a data source for AWS DMS. It involves configuring SQL Server replication to enable AWS DMS to continuously replicate data from the source SQL Server to the target database. This setup ensures that data is consistently and efficiently replicated to the target database.

Before you begin

  1. In the AWS DMS console:
    • Create a replication instance.
    • Configure source and target endpoints.
    • Create a migration task and choose the SQL Server publication as the source.
  2. Ensure that the SQL Server instance is configured to support replication:
    • Enable the SQL Server service.
    • Configure the server as a publisher.
  3. Review the SQL script for publication creation and adjust the articles as needed for your use cases.

About this task

A publication includes two agent jobs:
  • Snapshot Agent Job: Creates the initial snapshot of the published data.
  • Log Reader Agent Job: Monitors the transaction log and sends changes to the distribution database.
Note:
If an existing publication (example: HVRPUB) already has a snapshot agent and log reader agent jobs, you can skip creating a new snapshot agent by removing the corresponding job creation script. Only one log reader agent is allowed per publishing database in SQL Server, so an existing log reader agent job will be used if available.

Procedure

  1. Temporarily provide the DMS_User with sysadmin privileges until the publication and all required articles have been created in SQL Server for the publishing database.
  2. After creating the publication and articles, remove the sysadmin privileges from the DMS_User.
    Note:
    If sysadmin privileges cannot be granted to the DMS_User, you can bypass the Graphical User Interface (GUI)-based publication creation by using a SQL script to create the publication and its articles.
  3. Create the publication and add the necessary articles using the SQL script.
    Note:
    Review and modify the script to include only the articles required for your use case.
  4. If an existing publication has snapshot and log reader agent jobs, modify the script to avoid creating duplicate jobs.
  5. Ensure that the publication and the necessary components are correctly configured and operational.