Add Single Article Transactional Replication 8

Adding a table

Adding article / table – we can do from GUI and T-SQL. But that will not replicate to subscriber until snapshot agent run again. The drawback is if we start snapshot agent that will generate snapshot for all article, to avoid this we can use T-SQL method to add single article.

How to add table GUI and T-SQL

To add table GUI Expand replication and local publication – Right click the publication property – Article à uncheck show only checked article – Add article and ok.

This will not replicate until we start the snapshot agent as said above. To generate snapshot and replicate that single article use the following T-SQL.

 

--https://sqlconjuror.com/sql-server-re-initializing-single-article-transaction-replication/

--The reason we have to disable @immediate_sync is that everytime you add a new article, and if @immediate_sync is enabled, it will cause the entire snapshot to be applied. Our objective is to only apply a particular article.


use Muthu
--drop table [dbo].[Repl_Tbl11] 
create table [dbo].[Repl_Tbl11] (ID int primary key, N varchar(20))
insert into [Repl_Tbl11] values(1,'A')

-- Drop article from existing replication by T-SQL
use muthu
go
EXEC sys.sp_dropsubscription
@publication = 'muthu_replica',
@article = 'Repl_Tbl11', 
@subscriber = 'Node2', 
@destination_db = 'muthu_replica'

EXEC sys.sp_droparticle @publication = 'muthu_replica',
@article = 'Repl_Tbl11',
@force_invalidate_snapshot = 0

--============
--1. Check nad change allow_anonymous & immediate_sync 0 i.e false
use Muthu
EXEC sp_helppublication @publication='muthu_replica'

use [muthu]
go
EXEC sp_changepublication
@publication = 'muthu_replica',
@property = N'allow_anonymous',
@value = 'false'
GO
               
EXEC sp_changepublication
@publication = 'muthu_replica',
@property = N'immediate_sync',
@value = 'false'
GO
--The reason we have to disable @immediate_sync is that everytime you add a new article, and if @immediate_sync is enabled, it will cause the entire snapshot to be applied. Our objective is to only apply a particular article.

--2.  Add new article and invalidate snapshot.


EXEC sp_addarticle
@publication = 'muthu_replica',
@article = 'Repl_Tbl11',
@source_object = 'Repl_Tbl11',
--@source_owner ='Person',
@force_invalidate_snapshot = 1


--3.  Refresh the subscription (No need to refresh)


--EXEC sp_refreshsubscriptions @publication = 'muthu_replica'
GO
--4.  Check the current snapshot agent history.

use distribution
go
select * from dbo.MSsnapshot_history order by start_time desc
--5.  Start Snapshot agent.

--use muthu
--EXEC sp_startpublication_snapshot @publication = 'muthu_replica';
--GO

-- Run sp_addsubscription to generate @update_mode = 'read only'
DECLARE @publication AS sysname;
DECLARE @subscriber AS sysname;
DECLARE @subscriptionDB AS sysname;
SET @publication = N'muthu_replica';
SET @subscriber = N'Node2'; -- change
SET @subscriptionDB = N'muthu_replica'; --chnage

--Add a push subscription to a transactional publication.
USE [muthu]
EXEC sp_addsubscription 
  @publication = @publication, 
  @subscriber = @subscriber, 
  @destination_db = @subscriptionDB, 
  @subscription_type = N'push',
   @sync_type = 'automatic',
  @update_mode = 'read only'
  ,@reserved='Internal';

  -- Start snapshot agent
use muthu
EXEC sp_startpublication_snapshot @publication = 'muthu_replica';
GO

--6.  Check the Snapshot Agent history again. You should see a snapshot generated only for the newly added article/s.
use distribution
go
--select * from dbo.MSsnapshot_history order by start_time desc

select A.publication,H.* from dbo.MSsnapshot_history H join MSsnapshot_agents A on h.agent_id =A.ID --order by start_time desc
where comments like '%Snapshot of%' order by start_time desc

--7.  Turn ON @allow_anonymous and @immediate_sync on the publication.

use muthu
go

EXEC sp_changepublication
@publication = 'muthu_replica',
@property = N'immediate_sync',
@value = 'true'
GO


EXEC sp_changepublication
@publication = 'muthu_replica',
@property = N'allow_anonymous',
@value = 'true'
GO

use Muthu
EXEC sp_helppublication @publication='muthu_replica'

Dropping a table from replication.

To add table GUI Expand replication and local publication – Right click the publication property – Article – uncheck show only checked article – uncheck article – You will get dropping article will invalidate the snapshot if anything available. – click  ok.