We help IT Professionals succeed at work.

Long delay b/w subscriber & Publisher

saratcm
saratcm asked
on
Hi All,
I have a case in trnsactional replication,
Our subscribers are getting updated with a long delay from publisher.
So what should I do to reduce the delay b/w publisher n subscriber?
Comment
Watch Question

Senior Database Administrator
Commented:
Hi,

This is likely a network question, in that the only way to reduce the latency is to get a better network connection - don't be fooled by network ping times, as you are after through-put rather than speed. Talk to your network people/supplier.

Anyway, there are some small things you can do, and that is make sure that you are not attempting to replicate more than you need. That is, there are no extra columns, and no extra rows are being replicated.

That is, if replicating to two branch offices, if you can't replicate everything to each branch in a meaningful timeframe, then can you split the publication in two, and replicate only what each branch needs?

Regards
  David
Commented:
To speed up the commands from publisher to subscriber,
1) You can drop the indexes if exists. This will give better performance.
2) You can increase log reader agents batch size and distributor agents batch size.
 

Author

Commented:
Hi TAB8,
U mean dropping indexes on subscriber side?
will it work?
What is meant by increasing batch size?
David ToddSenior Database Administrator
Commented:
Hi TAB8,

saratcm has a good thought or two on increasing performance, but my question remains un-answered. What is the quality/speed of your link?

Firstly, go to the subscriber - remote desktop is good here - and look at the network utilisation in Task Manager.

On a 1GB server link on a lan, copying a file will maybe get to 50% utilisation. If utilisation is a few percent - average 5% occasional peaks at 10% I'd suggest that your bottleneck is the amount of data you are replicating and the throughput of your network link/VPN etc.

Do you have SQL 2005/2008/2008 R2 installed anyway handy? In REplication Manager, there is a tab which allows tracer tokens to be inserted. Another tab lists latency. What do these numbers say?

Regards
  David

Author

Commented:
Thanks Guys.