Configure AWS DMS with the Always On Availability Group

Procedure

Set up the Microsoft SQL server.
  1. Set up a distribution database as a system administrator by running the Configure Distributor wizard in the Microsoft SQL server. For instructions, refer to https://docs.microsoft.com/en-us/sql/relational-databases/replication/configure-publishing-and-distribution?view=sql-server-2016. Follow the steps in the Using SQL Server Management Studio section.
  2. Delete the publication that is created.
  3. Refer to https://docs.aws.amazon.com/dms/latest/sbs/chap-manageddatabases.sqlserveralwayson.ag.html for setting up AWS DMS for replication on Always On Availability Group.
  4. To read the transactional log, a system administrator must create a wrapper SQL procedure called rtm_dump_dblog so that AWS DMS can call the read-only function fn_dump_dblog of Microsoft SQL. You must perform this step inside the Microsoft SQL database (msdb) and not the actual capture database. The query to create these procedures is in the Setting up AWS DMS Replication Instance without Sysadmin Role.
  5. A system administrator must grant a read-only privilege to the AWS DMS user in the master database by running the following command: 
    use master

grant view server state to dmsuser
  6. If articles are used or you have chosen to use Microsoft SQL to read the logs, a system administrator or a user with the db_owner privilege must create a wrapper for the Microsoft SQL procedures in each capture database so that AWS DMS can call the Microsoft SQL server's read-only procedures sp_helppublication, sp_helparticle and fn_dblog. The query to create these procedures is available in the Setting up AWS DMS Replication Instance without Sysadmin Role.
  7. A system administrator or a user with the db_owner privilege must grant the AWS DMS user a read-only privilege by running the following command. This step is required after a new source database is set up on each node. 
    use capdb
EXEC sp_addrolemember N'db_datareader', N'dmsuser'