Replicating large batch inserts

Question

Replicating large batch inserts

Anil Bajaj

SSC-Addicted

Points: 434
More actions
November 13, 2006 at 7:48 pm

#171266

I have transactional replication set up between 2 SQL Server databases on 2 different
boxes. Both the log reader and distribution agent run in continuous mode.
The distributor is residing on a third SQL Server box. We are having performance issues with replication when there are large batch deletes/inserts happening on the publisher. There is a batch job that runs for about 8-10 hours everyday on the publisher and deletes/inserts thousands of records as part of transactions. The amount
of time to replicate all this data on the subscriber is around 13-15 hours which is
not acceptable to our user community. While monitoring I found that the distribution
database (MSRepl_commands table) at times has millions of records in it which would
explain why the latency is so high. Add to it the fact that there are large transactions occuring on the publisher.
I was wondering if anyone has faced similar problem before. Are there any conifguration
changes I can make to the replication infrastructure to reduce latency?
Would appreciate your help.
Thanks.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

andrewkane17 SSCertifiable Points: 7529 More actions · Answer 1

If you can run the batch process via a stored procedure you could then have that stored procedure as an article as part of you publication. This way you will not be passing the transactions over to the subscriber, but the sp call will be passed and the processing happens at both sites.

Check out this link, that explains the setuo and theory: http://www.sql-server-performance.com/mak_replicating_sp_execution.asp

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

Anil

If Andrew's suggestion doesn't work in your environment, try breaking down your insert into smaller batches. That way, the first batch can be replicating on to the subscriber while the rest of the batches are being written to the publisher.

Hope that makes sense

John

noeld SSC Guru Points: 96590 More actions · Answer 3

Minimizing the data in the distribution can speedup significantly your process. Try reducing the time that distribution commands are kept in the distribution db (default 73hrs)... start with 36... for example.

* Noel