Adding fields to Table - Transactional Replication

  • I am going to add NEW 15-20 fields to an existing 3-tables & also going to add NEW TABLE(7-Fields). As this one is Transaction Replication , so i would like to know how i can do that & to make sure that new Fields & Table are added correctly on Publisher & Subcriber & replication will work. will like to know what steps i need to follow to acheive this. Any help on this will be appreciated & thanking in advance.

  • When adding columns to a published table, you will need to use sp_repladdcolumn. ALTER TABLE...ADD COLUMN will not work because replication does not notice changes done this way.

    For new tables, just create the table and then add it to your publication either by using the publication properties and adding a new articles and making sure that each subscriber's subscription is updated to include the new articles or by using the stored procedure sp_addarticle and then sp_addsubscription.

    From memory, you will then need to generate a new snapshot. Run the SQL Agent snapshot job will do this for you. Have a look for jobs that have a category of "REPL-snapshot".

  • sp_repaddcolumn is used to add to a published article (table). We do not need to reinitialize the snapshot agent. It is very useful for a big article.

    If the article is small, we can reinitialize the the snapshot after adding the column. Otherwise, the replicaiton does not notice the change in the article.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply