Cannot reinitialize replication after huge DML changes

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

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.
12,674 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 100.9K Reputation points MVP
    2021-03-12T22:41:08.057+00:00

    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
  2. Cris Zhan-MSFT 6,601 Reputation points
    2021-03-22T02:25:53.797+00:00

    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.

    0 comments