Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

This is the first time I am going to add tables to replication.

I have outlined the below steps to add a new table to existing replication. Please correct me if I am wrong. The table is just created - no data.

exec sp_addarticle @publication = N'pub1', @article = N'tab1', @source_owner = N'dbo', @source_object = N'tab1'

 EXEC sp_addsubscription
  @publication = 'pub1',
  @subscriber = 'sub1',
  @destination_db = 'MLT-Rep'
GO
 EXEC sp_addsubscription
  @publication = 'pub1',
  @subscriber = 'sub2',
  @destination_db = 'MLT-Rep'

After this, I am supposed to run Snapshot right? How do I go about doing that? Just navigate to the "Local Publication" -> "View Snapshot Agent Status" -> Start

Are the above steps right?

share|improve this question

1 Answer 1

up vote 1 down vote accepted

Adding an a new article involves adding the article to the publication, creating a new snapshot, and synchronizing subscriptions to apply the schema and data for the newly added articles. Reinitialization is not required, but a new snapshot is.

Your steps are correct if the publication only has existing push subscriptions. If the publication has existing pull subscriptions, you will also need to execute sp_refreshsubscriptions. And you are correct, to generate a new snapshot you can use the View Snapshot Agent dialog, or you can execute sp_startpublication_snapshot.

To avoid generating a full snapshot when adding a new article, publication properties @immediate_sync and @allow_anonymous must be set to 0. Execute sp_addarticle, then execute sp_addsubscription. If the publication has existing pull subscriptions, you must also call sp_refreshsubscriptions. Then generate a snapshot and only a snapshot for the newly added article will be generated. Note that this approach is not necessary but is typically used when Administrators want to avoid generating a full snapshot when adding a new article.

Please see Add Articles to and Drop Articles from Existing Publications for more information.

share|improve this answer
    
Many thanks for your reply.. It always helps to get my process verified by somebody else! immediate_sync and allow_anonymous are set to 0. I am running a agent job of type "Replication Snapshot". after running sp_addarticle and sp_addsubscription. However, now my replication seems to be stuck in live. Performed the same steps successfully - without any issues in PreProd before doing this in live. Not sure what is happening? –  user32027 Jan 28 at 6:06
    
@brandonwilliams.. do you have some idea as to why this is happening? BTW, I think there is a bug with the site. I am unable to tag any of the members –  user32027 Jan 28 at 6:07
    
did you check all logs in sql server agents jobs related to this publication/subscriptions ? What type of replication it is ? do you have separate distributor db/job ? –  Kuba Miazek Jan 28 at 13:09
    
Have you checked Replication Monitor? Drill down on the subscription and post any errors back here. –  Brandon Williams Jan 28 at 17:14
    
No errors there! My theory is: i performed this activity during a time of the day when replication was seriously lagging behind.. there were a lot (> 5 million) unsubscribed messages.. that is why my snapshot agent started acting funny –  user32027 Jan 31 at 4:16

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.