How do I add a new table to an existing transaction replication on SQL server 2017 where secondary replica is setup using AWS DMS service?. While setting up we have only created distribution and publication on sql server and the client team had created endpoint for the publication using AWS DMS (I am not sure exactly how client had created endpoint using AWS DMS service).
Now I have a add a new article in this publisher which ideally I do my setting on Allow_annonymious and Auto_sync to false and add new table and reinitiate the snapshot/subscriber. but since I dont have any subscription present on SQL server (not able to see via SSMS) I am nit able to reinitiate the snapshot.
Can someone please tell me if what steps I need to perform to add a new article in replication OR any new steps are required form AWS DMS side to reinitiate the snapshot.
I would like to provide an answer to my question. If we have to re-add a new article in an existing replication which is combined with AWS DMS service as a replica.
We simply need to re-add the new article in the publisher by going into publisher properties. We not have to set Allow_annonymious and Auto_sync to false. Once added we have to perform a FULL LOAD on AWS side to make a fresh copy of snapshot.
FYI, I have also noticed that if we create any new table with PK key in SQL it is automatically added into replication. We have to manually remove that article for publisher if we dont have to consider it for replication.