info@techdevops.com
TechDevOps.com
Explore Tools
Experts in Microsoft SQL Server Operations, Performance, Replication, Cloud & Migrations



Add a new Article to an existing Publication in Transactional Replication (Push Subscription) using T-SQL
by BF (Senior MSSQL Operations - Enterprise, Cloud, Strategy)
2016-12-08








These are the steps in order to add a new table as an Article in Microsoft SQL Server Transactional Replication. This method uses T-SQL Code and does not reply any any GUI work. Once the article is added you can add some records and execute a full Subscription Validation to confirm everything is working as expected.



Step 1: Confirm current Publication Settings

sp_helppublication 'PubSalesSales'

immediate_synch = 0
allow_anonymous = 0
replication frequency = 0

immediate_sync bit Indicates whether synchronization files are created or re-created each time the Snapshot Agent runs.
allow_anonymous bit Indicates whether anonymous subscriptions can be created for the given publication.
replication frequency tinyint 0 = Transactional




Step 2: Confirm the Article does not exist

sp_helpsubscription @publication = 'PubSalesSales', @article = 'CLIENT'

Expected result: The article 'CLIENT' does not exist.




Step 3: Create the new table schema on the PUBLISHER DB and all SUBSCRIBER DB's




Step 4: Execute sp_addarticle

use [SALES]
exec sp_addarticle
@publication = N'PubSalesSales',
@article = N'CLIENT',
@source_owner = N'dbo',
@source_object = N'CLIENT',
@type = N'logbased',
@description = N'',
@creation_script = N'',
@pre_creation_cmd = N'none',
@schema_option = 0x000000010A03008F,
@identityrangemanagementoption = N'none',
@destination_table = N'CLIENT',
@destination_owner = N'dbo',
@status = 24,
@vertical_partition = N'false',
@ins_cmd = N'CALL [sp_MSins_dboCLIENT]',
@del_cmd = N'CALL [sp_MSdel_dboCLIENT]',
@upd_cmd = N'SCALL [sp_MSupd_dboCLIENT]'
GO




Step 5: Execute sp_addsubscription - Do this for EVERY Subscriber for this Publication

use [SALES]
exec sp_addsubscription @publication = N'PubSalesSales', @subscriber = N'SQL01',
@destination_db = N'SALES', @subscription_type = N'Push', @sync_type = N'
replication support only', @article = N'CLIENT', @update_mode = N'read only',
@subscriber_type = 0

*Note: For @article = N'CLIENT', MS case recommends using @article = N'all' (both work)




Step 6: Confirm the Article does exist

sp_helpsubscription @publication = 'PubSalesSales', @article = 'CLIENT'




Step 7: Validate Subscriptions

sp_publication_validation