How to add an article to existing replication

Question

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?

Answer 1

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.

Answer 2

just adding my 2 cents. today I removed some articles from a publication for a unrelated reason. then when I added them back I created a mess because I had not checked whether the subscriber ALREADY EXISTED, before I created them. so I am posting here the script I have used:

-- to verify whether the subscriber already exists

USE mydatabase
GO

--========================================================================
-- GET A LIST OF SUBSCRIBERS
-- if the subscribers are already there
-- NO NEED TO CREATE THEM
-- Marcelo Miorelli
-- 17-nov-2014
--========================================================================
select  
db_name() PublisherDB 
, sp.name as PublisherName 
, sa.name as TableName 
, UPPER(srv.srvname) as SubscriberServerName 
from dbo.syspublications sp  
join dbo.sysarticles sa on sp.pubid = sa.pubid 
join dbo.syssubscriptions s on sa.artid = s.artid 
join master.dbo.sysservers srv on s.srvid = srv.srvid 



EXEC sp_addarticle @publication = N'mydatabase', 
                   @article = N'InfoscoreAddressValidation',
                   @source_table=N'InfoscoreAddressValidation'
GO

EXEC sp_addarticle @publication = N'mydatabase', 
                   @article = N'InfoscoreCreditScoreCheck',
                   @source_table=N'InfoscoreCreditScoreCheck'
GO


EXEC sp_addarticle @publication = N'mydatabase', 
                   @article = N'OpenInvoiceAddress',
                   @source_table=N'OpenInvoiceAddress'
GO

--========================================================================
-- Only add the subscribers if they don't exist
--========================================================================
--EXEC sp_addsubscription
--  @publication = N'mydatabase',
--   @article = N'OpenInvoiceAddress',
--  @subscriber = N'my_subscriber_01',
--  @destination_db = N'mydatabase'
--GO

-- EXEC sp_addsubscription
--  @publication = N'mydatabase',
--   @article = N'OpenInvoiceAddress',
--  @subscriber = N'SQLmy_subscriber_02',
--  @destination_db = N'mydatabase'
--GO


EXEC sp_refreshsubscriptions @publication = N'mydatabase'
GO

-- Start the Snapshot Agent job.
EXEC sp_startpublication_snapshot @publication = N'mydatabase'
go