Sql-server – Move transactional replication subscriber to another server without re-snapshotting

replicationsql serversql-server-2016transactional-replication

I have publisher and subscriber at same server. I need to move subscriber's DB to another server but without creating new snapshot. I've tried this steps:

  • Stop all agents
  • Backup subscriber's DB
  • Restore subscriber's DB on
    another server with KEEP_REPLICATION
  • Changed server name in
    syssubscriptions, MSsubscriber_info, MSsubscriber_schedule
  • Changed
    server name in scheduled task for replication

Then I've started agents and got error 20053 (Server not registered). I fixed it with adding linked server to new server

After that I've got an error 20044 (Subscription is outdated or not present), but subscription is present.

What did I do wrong or maybe I have missed any step(s)?

I can't use initialization from backup, because subscriber has some additional tables, views, routines.
Thanks

Best Answer

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

  1. Continue running the distribution agent jobs till undelivered command count is zero (This is key that you do not have any undelivered command).
  2. Stop the distribution agent job/jobs.
  3. Script out replication.
  4. 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.
  5. Reinstall replication with 'replication support only' when running sp_addsubscription. See details here.
  6. 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 Replication.

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, 
       publication, 
       a.publisher_db, 
       subscriber_db, 
       art.article, 
       art.destination_object, 
       art.source_object, 
       undelivcmdsindistdb, 
       delivcmdsindistdb, 
       s.article_id, 
       agent_id, 
       a.publisher_id, 
       subscriber_id 
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 
COMPUTE sum(undelivcmdsindistdb)
Related Question