sjimmo - Wednesday, January 18, 2017 4:57 PM

TheSQLGuru - Wednesday, January 18, 2017 4:07 PM

sjimmo - Wednesday, January 18, 2017 3:18 PM

rogelio.vidaurri - Wednesday, January 18, 2017 1:30 PM

"It sounds first like the Latency Warning parameters are set as the default, which will definitely give the warnings such as you are describing over the internet connection."
That's right, but it has been working correctly for years with default values. It all began when trying to substitute a table with million of rows with a new one (same structure, indexes, etc.).

"Do you use a dedicated distributor or is the distributor also on the publisher server."
Yes, we do.

"Are you using one publication with subscriptions to each location?"
Complete configuration:
A has a push subscription to B (Internet)
B has a push subscription to A (Internet)
A has a push subscription to C (LAN)
C has a push subscription to A (LAN)

ICMP requests are blocked on both ends, we'd need to request them be opened.
BTW, our server hosting providers said everything looks normal re the load on the lines.

"If this is causing issues on the publisher, and the distributor is also on the publisher, separate them."
A SQL instance for the distributor and another SQL instance for the rest? Wouldn't it require more SQL licensing?

"If there is still an issue then create 2 packages, one to be used to the subscriber(s) locally, and the other to be used for those via internet. This will make the load on the distributor more efficient as well."
I didn't get this idea. Could you please explain?

Thanks for sharing!

Separating the load across multiple packages will lift some of the load on the distributor. The latency over the internet line may impact the local subscriber and publisher stacking up commands waiting to be distributed.
Yes, I would expect the ISP to say everything is OK. Are you using a dedicated line from your company to the ISP and from the ISP to the subscriber? If so, then it sounds like there may be noise on the line. The only way to isolate it is end to end out of service testing by the ISP. Otherwise take the bandwidth and split it up between all of the locations using that line.
Good luck.

BTW, I have an extremely large replication system using internet/broadband and local lan without a latency problem with 9 publishers, 2 distributors and 85 subscribers pushing several million transactions per day. It is all OLTP. Yes, I have had discussions with our ISP's for similar issues, and they resolved it. My latency at worse is still under 10 sec over the internet connections.

From my understanding Steve this can't be a problem with connectivity because one table in same database replicates just fine and the other does not. And it is repeatably so apparently.

I was under the impression that all coming across the internet connection was an issue.

If not, I'm sorry.