Cannot reinitialize replication after huge DML changes

Question

Hi All,
We have a very huge database , which is also a publisher and replicating data to 1 subscriber.
Every time an application-level upgrade happens, millions of update statements are generated.
For some reason, they dont instantly replicate to the subscriber : The status ends up as 'poor' showing a few million records yet to be committed on distributor.
Reinitializing replication using backup\restore takes upto 2 hours, which we would like to avoid.
Is there any way where we can pause replication + Make data changes , add\remove articles + Reinitialise and expect all data be replicated instantly (or in a few minutes) .

Please advise.

Thank you,
Mounika

Answer 1

There is no magic bullet. If there are millions of update statements, they have to be replicated one way or another. Yes, it could be that the net change is small, but determining that is not trivial.

You can pause replication to make your changes, but it will take a while for Replication to catch when you re-start it.

Answer 2

Hi,

When large amounts of changes need to be sent to Subscribers, reinitializing them with a new snapshot might be faster than using replication to move the individual changes. But for a large databases, it can take a very long time to create an initial snapshot and reinitialize the Subscribers from that generated snapshot.

The method of specifying the backup is generally considered to be the fastest way to deliver the data to the subscriber.