My answer will assume you are doing this during downtime.
Once downtime start (confirm app servers are not making any new connection/writing to publishing articles):
- Continue running the distribution agent jobs till undelivered
command count is zero (This is key that you do not have any undelivered command).
- Stop the distribution agent job/jobs.
- Script out replication.
- Remove replication (publisher and subscriber only) but keep the
distributor as is. That is my preference but you can also decide to remove subscriber only.
- Reinstall replication with 'replication support only' when running sp_addsubscription. See details here.
- Run snapshot jobs. It will not resync your table schema+data, rather create system objects if any missing.
replication support only Provides automatic generation at the
Subscriber of article custom stored procedures and triggers that
support updating subscriptions, if appropriate. Assumes that the
Subscriber already has the schema and initial data for published
tables. When configuring a peer-to-peer transactional replication
topology, ensure that the data at all nodes in the topology is
identical. For more information, see Peer-to-Peer Transactional
Not supported for subscriptions to non-SQL Server publications.
You can use this query to find undelivered commands in distribution database.
SELECT ss.srvname AS publisher,
[name] AS Agent,
FROM distribution.dbo.msdistribution_status AS s WITH (nolock)
FULL OUTER JOIN distribution.dbo.msdistribution_agents AS a WITH (nolock)
ON s.agent_id = a.id
JOIN master.dbo.sysservers AS ss WITH (nolock)
ON a.publisher_id = ss.srvid
JOIN distribution.dbo.msarticles AS art WITH (nolock)
ON s.article_id = art.article_id
WHERE subscriber_db <> 'virtual'
AND subscriber_db = 'SubscriberDBName'
ORDER BY undelivcmdsindistdb DESC