question

MounikaVemula-8178 avatar image
0 Votes"
MounikaVemula-8178 asked ·

Cannot reinitialize replication after huge DML changes

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-general
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
1 Vote"
ErlandSommarskog answered ·

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.

·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Criszhan-msft avatar image
0 Votes"
Criszhan-msft answered ·

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.

·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.