Nirav's Diary

Add article in Replication without initializing

By nirav on June 13th, 2013

Add article in Replication without initializing

By this way you are only creating a snapshot only for newly added article and no need to reinitialize  all subscription.

Step1 : Create New Table on Publisher.

USE Adventureworks2012

CREATE TABLE ReplicationTest
  (
     ID   INT PRIMARY KEY,
     Test VARCHAR(20)
  )

Step2 : Add this table in Article.

First check Article exist or not in a replication.

--Check Exisitng Articles.
SELECT
  sub.srvname    AS SubscriptionServer,
  pub.name       AS Publication,
  art.name       AS ArticleName,
  art.dest_table AS DestinationArticle,
  art.dest_owner AS OwnerName
FROM
  sysextendedarticlesview art
INNER JOIN syspublications pub
        ON ( art.pubid = pub.pubid )
INNER JOIN syssubscriptions sub
        ON ( sub.artid = art.artid )
WHERE
  art.name = 'ReplicationTest'

GO

Run this to add “ReplicationTest” table to your existing article.

--Script to add table in replication.
EXEC Sp_addarticle
  @publication = N'AdventureWorks2012_Pub',
  @article = N'ReplicationTest',
  @source_owner = N'DBO',
  @source_object = N'ReplicationTest',
  @type = N'logbased',
  @description = N'',
  @creation_script = N'',
  @pre_creation_cmd = N'drop',
  @schema_option = 0x00000000080350DF,
  @identityrangemanagementoption = N'manual',
  @destination_table = N'ReplicationTest',
  @destination_owner = N'DBO',
  @status = 24,
  @vertical_partition = N'false',
  @ins_cmd = N'CALL [sp_MSins_DBOReplicationTest]',
  @del_cmd = N'CALL [sp_MSdel_DBOReplicationTest]',
  @upd_cmd = N'SCALL [sp_MSupd_DBOReplicationTest]',
  @force_invalidate_snapshot = 1

GO

Now check article added successfully.

--Now Check again for newly added article.
SELECT
  sub.srvname    AS SubscriptionServer,
  pub.name       AS Publication,
  art.name       AS ArticleName,
  art.dest_table AS DestinationArticle,
  art.dest_owner AS OwnerName
FROM
  sysextendedarticlesview art
INNER JOIN syspublications pub
        ON ( art.pubid = pub.pubid )
INNER JOIN syssubscriptions sub
        ON ( sub.artid = art.artid )
WHERE
  art.name = 'ReplicationTest'

GO

Step3 : Refresh Subscription.

USE Adventureworks2012
go

Sp_refreshsubscriptions 'Adventureworks2012_pub' -- Supply Publication Name

Step4 : Go to replication monitor screen –> Select Agent Tab
–>Select Snapshot Agent in Agent Type –>Right click on Agent and Start Again.

Step5 : Now Go to replication monitor screen –> Select Agent Subscription watch list
–> Double click on your subscription and snapshot applying only for newly added article.

Regards,

Nirav Gajjar