Cannot reinitialize replication after huge DML changes

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,

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

2 answers

Sort by: Most helpful
  1. Erland Sommarskog 78,026 Reputation points 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.

    1 person found this answer helpful.
    0 comments No comments

  2. Cris Zhan-MSFT 6,566 Reputation points


    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.

    0 comments No comments