Cannot reinitialize replication after huge DML changes

asked 2021-03-12T14:38:15.923+00:00
Mounika Vemula 1 Reputation point

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
8,473 questions
No comments
{count} votes

2 answers

Sort by: Most helpful
  1. answered 2021-03-12T22:41:08.057+00:00
    Erland Sommarskog 67,636 Reputation points Microsoft MVP

    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.

    No comments

  2. answered 2021-03-22T02:25:53.797+00:00
    Cris Zhan-MSFT 6,561 Reputation points

    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.

    No comments