Add new Article to existing Transnational Replication using Script

Add Article to existing Publication

To add the new (articles/SP/Views/Functions) to Transactional replication,  Don't not use GUI to add articles (including Tables, views, SP’s and Functions), always use sp_addarticle and sp_addsubscription like following scripts.

                   

Make sure that your publication has IMMEDIATE_SYNC and ALLOW_ANONYMOUS properties set to FALSE or 0.

use JAIVERMA_DB

select immediate_sync , allow_anonymous from syspublications

If either of them is TRUE then modify that to FALSE by using the following command

EXEC sp_changepublication

@publication = My-Replication',

@property = 'allow_anonymous' ,

@value = 'false'

GO

EXEC sp_changepublication

@publication = My-Replication',

s@property = 'immediate_sync' ,

@value = 'false'

Adding the Tables to Replication -

1)    Add the article to the publication

use JAIVERMA_DB

use [JAIVERMA_DB]

exec sp_addarticle @publication = N’MY-Replication’, @article = N'New_Table_name', @source_object = N'New_Table_name', @force_invalidate_snapshot=1

GO

2)     Add the subscription for this new article

use [JAIVERMA_DB]

exec sp_addsubscription @publication = N’MY-Replication’, @subscriber = N’SECONDARYSERVERNAME’, @destination_db = N'JAIVERMA_DB', @article = N'New_Table_name',  @reserved='Internal', @subscription_type = N'Pull'

GO

use [JAIVERMA_DB]

exec sp_addsubscription @publication = N’MY-Replication’, @subscriber = N'GPC-INISQDB06', @destination_db = N'JAIVERMA_DB', @article = N' New_Table_name',  @reserved='Internal', @subscription_type = N'Pull'

GO

Adding the SP to Replication -

1)      Adding the SP to publication

use [JAIVERMA_DB]

exec sp_addarticle @publication = N’MY-Replication’, @article = N'New_SP_Name', @source_object = N'New_SP_name', @type = N'proc schema only', @destination_table = N'New_SP_name',  @force_invalidate_snapshot=1

GO

2)      Add the subscription for this new SP

use [JAIVERMA_DB]

exec sp_addsubscription @publication = N’MY-Replication’, @subscriber = N’SECONDARYSERVERNAME’, @destination_db = N'JAIVERMA_DB', @article = N' New_SP_Name'',  @reserved='Internal', @subscription_type = N'Pull'

GO

Adding the View to existing replication

1)      Adding the View to publication

use [JAIVERMA_DB]

exec sp_addarticle @publication = N’MY-Replication’,

@article = N'New_View_name',

@source_object = N'New_View_name',

@type = N'view schema only',

@destination_table = N'New_View_name',

@force_invalidate_snapshot=1

GO

2)      Add the subscription for this new view

use [JAIVERMA_DB]

exec sp_addsubscription @publication = N’MY-Replication’, @subscriber = N’SECONDARYSERVERNAME’, @destination_db = N'JAIVERMA_DB', @article = N'New_View_name', @reserved='Internal', @subscription_type = N'Pull'

GO

Adding the User Defined Functions to existing replication

1)      Adding the User Defined Functions to publication

use [JAIVERMA_DB]

exec sp_addarticle @publication = N’MY-Replication’,

@article = N'New_Function_name',

@source_object = N'New_Function_name',

@type = N'func schema only',

@destination_table = N'New_Function_name',

@force_invalidate_snapshot=1

GO

2)      Add the subscription for this new Function

use [JAIVERMA_DB]

exec sp_addsubscription @publication = N’MY-Replication’, @subscriber = N’SECONDARYSERVERNAME’, @destination_db = N'JAIVERMA_DB', @article = N'New_Function_name',  @reserved='Internal', @subscription_type = N'Pull'

GO

3)    Lastly start the SNAPSHOT AGENT job from the job activity monitor. Verify that the snapshot was generated for only newly added articles .