Reset topology using “Replication Support Only”

If you need to reset your Replication topology you can quickly rebuild subscribers using the “no initialize” also called “Replication Support Only (RSO)”. 

WARNING: These steps are designed to remove ALL of your Replication configuration and settings, recreating with new metadata tables. The RSO option skips the Snapshot data reinitialization phase. It only pushed down new subscriber replication metadata such as required replication stored procedures and replication tracking tables. Users tables are not dropped, truncated, or recreated.

To better understand impact in your own environment, test these steps on test replication topology.

1. Stop all data changes on Publisher.
2. Execute: “DBCC OPENTRAN” to verify “Oldest non-distributed LSN : (0:0:0)” indicating no undelivered transactions.
3. Insert Tracer Token and verify token was delivered to all Subscribers. This ensures data match between publisher and subscriber, a requirement for initializing subscribers via RSO option. Tracer Token should be last transaction delivered from Publisher to Subscribers.
4. Script out the Publication and Subscription setup commands via SSMS.
5. Drop the Publication
6. Verify no other publication are using this Distributor ( SELECT * FROM distribution.dbo.MSpublications).  If other publication are found, those also must have data changes stopped, scripted out, and recreated.
7. Once you’ve verified all publication and subscription have been scripted, select option to Disable Publishing and Distribution.  This step turns off ALL replication using this distributor.
8. Re-Enable Publishing and Distribution creating a new empty Distribution database
9. Modify the Replication sp_addsubscription command changing to @sync_type =’Replication Support Only’
10. Execute replication script to recreate the Publication.
11. Execute scripts to create Subscriptions. Optionally, after the Publication is create add Subscriptions via SSMS but clear “Initialize” option
12. Insert Tracer Token to ensure new data changes are flowing to all Subscribers
13. Re-enable production data flow to Publisher

Key points:

• Using “Replication Support Only” option (or clearing Initialize option when adding subscriber via SSMS) no table bulk export\import Snapshot steps are performed.  Only the objects needed by Replication are created on the Subscriber.
• Publisher and subscriber must have identical data and no new data changes are occurring on publisher until replication is re-enabled.
• These steps work even if subscriber were initially configured using backup\restore or default Snapshot replication.
• From start to finish, these steps can be completed in couple of hours.