SQL Server – Automatically Add Table to Publisher in Replication

database-designreplicationsql servertransactional-replication

I am using SQL 2012 transactional replication for an entire database. Everytime I add a new table, say Table A, I have to manually go to the SSMS GUI, and add the table to be publisher, then I resync to import data.

Is there a way to automatically add tables with data into replication, or do I have to write a dynamic/t-sql/powershell script, which checks if sys.tables is replicated and then automatically adds to replication sp_addarticle ?

Best Answer

There is no inbuilt way of automatically adding new table to replication. This means, you have to carve out your own method suited to your environment.

Couple of thing that you should do ..

  • Poll sys.tables or have a trigger to fire on CREATE_TABLE
  • Check if table has primary key (must for replication)
  • modify publication property - immediate_sync to 0 --> should be done once to avoid generating snapshot for all articles.
  • Call sp_addarticle and sp_addsubscription to add article to publication and subscription.
  • Kick off snapshot .. this will generate ONLY shapshot for the article that is newly added.
Related Question