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
|
|
|
|
|
|