Sql-server – SQL Server Populate replication server from DPM

replicationsql serversql-server-2012transactional-replication

I've inherited an old SQL Server database with 4 machines: production_server(2008.r2), DPM_server(DPM 2007), and two replication_server1(2008.r2) replication_server2(2012). SQL Server had to be reinstalled on replication_server2, and so all data on that machine was lost.

I need to get transaction replication from production_server to this server back online without hammering the production server to create the initial snapshot.

Is it possible to use my DPM server to provide a recent copy of the data, Either via a Backup or snapshot?

Or, Is it possible to have replication_server1 provide the initial snapshot, and then point it to the production_server publication once everything has caught up?

I haven't been able to find clear answers in the documentation, thanks in advance for your time.

Best Answer

This steps will implement your second option.

Or, Is it possible to have replication_server1 provide the initial snapshot, and then point it to the production_server publication once everything has caught up?

You will setup replication normally between repl1 and repl2. Use the same distribution server that you will use when you setup prod-->repl2 server.

Once downtime start (confirm app servers are not making any new connection/writing to Production publishing articles):

  1. Continue running the distribution agent jobs till undelivered command count is zero on both prod-->repl1 and repl1-->repl2. This is absolutely critical for this approach to work.
  2. Stop the distribution agent job/jobs.
  3. Remove replication (publisher and subscriber only) but keep the distributor as is.
  4. Reinstall replication between prod-->repl2 with 'replication support only' when running sp_addsubscription. See details here.
  5. 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