Add new table to transactional replica

Question

Add new table to transactional replica

steve.roberts_boohoo

SSChasing Mays

Points: 646
More actions
August 15, 2018 at 2:05 am

#415260

Hi,
We have currently a DB replica setup - we have added in a new table.
I used the restore from backup method to create the subscribers. Immediate sync is false.
So I should add in the article and then a snapshot will just pick up the new table and not the whole thing?

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

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

Sue_H SSC Guru Points: 90860 More actions · Answer 1

steve.roberts 86619 - Wednesday, August 15, 2018 2:05 AM
Hi,
We have currently a DB replica setup - we have added in a new table.
I used the restore from backup method to create the subscribers. Immediate sync is false.
So I should add in the article and then a snapshot will just pick up the new table and not the whole thing?

If allow anonymous and immediate sync are set to false then when you start the snapshot agent, it should generate a snapshot for just the newly added article.

Sue

steve.roberts_boohoo SSChasing Mays Points: 646 More actions · Answer 2

I setup a demo test DB for replication and followed the instructions and was ok.

If I have multiple subscribers do I need to run this command per subscriber?

EXEC sp_addsubscription @publication = 'yourpublication', @article = 'test',
@subscriber =‘subs_servername', @destination_db = 'subs_DBNAME',
@reserved='Internal'

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/12c21ca8-7031-4a9e-b813-0122f59a7b9d/no-subscriptions-needed-initialization-when-adding-new-article?forum=sqlreplication

Sue_H SSC Guru Points: 90860 More actions · Answer 3

steve.roberts 86619 - Wednesday, August 15, 2018 6:50 AM
I setup a demo test DB for replication and followed the instructions and was ok.
If I have multiple subscribers do I need to run this command per subscriber?
EXEC sp_addsubscription @publication = 'yourpublication', @article = 'test',
@subscriber =â€˜subs_servername', @destination_db = 'subs_DBNAME',
@reserved='Internal'
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/12c21ca8-7031-4a9e-b813-0122f59a7b9d/no-subscriptions-needed-initialization-when-adding-new-article?forum=sqlreplication

Yes so that each subscriber gets the updated subscription definition and the snapshot with the new article (table).

Sue