SQL Server – How to re-initialize just a single article in transaction replication

 SQL Server – How to re-initialize just a single article in transaction replication -- Upskill 2020. 

1. Turn off @allow_anonymous and @immediate_sync on the publication.

use PUBLICATION_DB_NAME

go

EXEC sp_changepublication

@publication = 'Upskill2020_Test',

@property = N'allow_anonymous',

@value = 'false'

GO

 

EXEC sp_changepublication

@publication = 'Upskill2020_Test',

@property = N'immediate_sync',

@value = 'false'

GO

The reason we must disable @immediate_sync is because every time 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.

EXEC sp_addarticle

@publication = 'Upskill2020_Test',

@article = 'Employee',

@source_object = 'Employee',

@force_invalidate_snapshot = 1

3. Refresh the subscription

EXEC sp_refreshsubscriptions @publication = 'Repl_RBD'

GO

4. Check the current snapshot agent history.

use distribution

go

select * from dbo.MSsnapshot_history

5. Start Snapshot agent.

EXEC sp_startpublication_snapshot @publication = 'Upskill2020_Test';

GO


6. Check the Snapshot Agent history again. You should see a snapshot generated only for the newly added article/s.


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

use PUBLICATION_DB_NAME

go

EXEC sp_changepublication

@publication = 'Upskill2020_Test',

@property = N'immediate_sync',

@value = 'true'

GO

EXEC sp_changepublication

@publication = 'Upskill2020_Test',

@property = N'allow_anonymous',

@value = 'true'



Comments

Post a Comment

Popular posts from this blog

SQL Deadlock Report - Extended Events

  SQL Deadlock Report - Extended Events Event Creation create EVENT SESSION EE_Deadlock ON SERVER     ADD EVENT sqlserver.xml_deadlock_report     ADD TARGET package0.ring_buffer     (SET max_memory = 4096) WITH (MAX_DISPATCH_LATENCY = 30 SECONDS); create Procedure  DeadlockReport as Begin alter event session EE_Deadlock ON SERVER state=Stop; if not exists (select * from Archival.sys.objects where name like '%Deadlock_Data%') Begin Create TABLE Archival..Deadlock_Data (     Rowid  INT IDENTITY PRIMARY KEY,     event_data XML) End Truncate Table Archival..Deadlock_Data insert into Archival..Deadlock_Data(event_data) ( select cast(XEventData.XEvent.value('(data/value)[1]', 'varchar(max)') as xml) as DeadlockGraph FROM (select CAST(target_data as xml) as TargetData from sys.dm_xe_session_targets st join sys.dm_xe_sessions s on s.address = st.event_session_address where name = 'EE_Deadlock') AS Data CROSS APPLY TargetData.nodes...
Read more

DBA Daily Commands in Handy

Image
sp_who2 active:  View details of active processes on SQL Server Engine DBCC INPUTBUFFER(session id):  View the current query being executed by a specific proc ess Check the open transaction in SQL Server: DBCC OPENTRAN Check the blocked process: SELECT * FROM sysprocesses where blocked > 0 and spid = 23; select * from sys.sysprocesses where blocked != 0; Check the lock information: SELECT * from sys.dm_tran_lock Check the connection information from view in SQL Server: SELECT * FROM sys.dm_exec_sessions; Check the database files location, size, and max size in SQL Server SELECT * FROM sysaltfiles Check the collation Setting on the SQL Server SP_HELPSORT Check the log Usage on SQL Server databases select * from sys.databases where name like '%log_reuse_wait_desc%'
Read more