Re: Network traffic with transactional replication with immediate updates (SQL 2005)
- From: "Hilary Cotter" <hilary.cotter@xxxxxxxxx>
- Date: Fri, 21 Jul 2006 18:06:42 -0400
This could be the autosense connection functionality. Are your agents
running continuously? What happens if you schedule them for every 5 minutes
or something like that?
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"J.A. García Barceló" <jagbarcelo@xxxxxxxxxxxxxx> wrote in message
news:eXVOAqQrGHA.4680@xxxxxxxxxxxxxxxxxxxxxxx
We have a VPN set with two xDSL between our main office and a branch site.
There are two sql servers (at each side) with transactional replication with
immediate updates. As you might imagine, bandwidth is critical (it is not a
T1 line, we have only 300kbps maximum) and it must be used for other
purposes also (file transfer/replication using Windows DFS, web browsing,
email, etc.)
We have also set up a network bandwidth monitoring software
(http://www.cacti.net/) to be able to check the status of the phisical
lines, and VPN, and see their evolution in time, if there are bottlenecks,
and so on.
We have been using SQL Server 2000 for quite a long time, both at the
publisher and at the subscriber. One month ago, more or less, we installed
SQL Server 2005 at the main office as new publisher (we are migrating step
by step, subscriber will go next), and the network traffic has boosted. We
are now consuming all the 300kbps (uploading from main office to the branch
site) permanently, and 95kbp of downloading (from branch to main office).
We have confirmed that all that traffic is caused by SQL Server, and not
other kind of traffic. Furthermore, after some tests, we have trace it down
and guessed that Distributor Agent is to blame. Default agent profile for
distributor agents has as profile parameters one called PollingInterval, set
by default to 5 seconds. We have created a new user profile based on the
default one, and changed PollingInterval from 5 to 60 seconds.
The traffic has been reduced in somewhat near 12 times less
60/5 = 12
from 300kbps to something near 40kbps (being so small, those 40kbps can
contain other kind of traffic)
However, I cannot find a way to explain why Distributor Agents are to blame.
According http://msdn2.microsoft.com/en-us/library/ms147328.aspx and
regarding PollingInterval parameter:
[...]Is how often, in seconds, the distribution database is queried for
replicated transactions. The default is 5 seconds.[...]
Both the agents and the distribution database are at the same computer: the
publisher at the main office. Being that way, in theory, changing that
parameter from 5 to 60 seconds would only affect local traffic. Even more,
during the night, when there is no activity at none of the offices, no
matter the frecuency the agent checks the distribution database, there
should be no pending transactions to be applied to subscriber, and the
subscriber should not be contacted. Our experience shows that there is
traffic between publisher and subscriber, even when there are no
transactions to be applied, and the bandwidth of that traffic depends
directly on the value of Distributor Agents' PollingInterval parameter.
I simply cannot understand it.
I attach a pair of images:
In this one we can see how, since the middle of June, the date when SQL
Server 2005 started to act as publisher, the traffic reaches 300kbps.
In this capture, we can see the last 24 hours. The valley in traffic around
13:00h belongs to a change of all Distributor Agents' profiles, with a
PollingInterval=60 and a restart of the SQL Server Agent service. Half an
hour later (more or less), in order to check, the default agent profile is
set again (PollingInterval=5) for every Distributor Agent and a new restart
of the SQL Server Agent service is done also.
It is very extrange that the traffic stalls permanently around 300kbps, even
during the night, when there is no activity (just a few jobs that call to
some stored procedures to update a few administrative tables, scheduled to
run at 00:00 and 5:00am)
Can someone find a reasonable explanation for this? Might it be a SQL Server
bug? Shoud the subscriber be contacted by distributor agents even when there
is nothing to replicate?
Regards and thanks a lot for your time.
PS: Publisher is SQL Server 2005 x64 + SP1 running on a Dual Xeon with 4Gb
RAM and Windows Server 2003 R2 x64.
--
J.A. García Barceló
http://jagbarcelo.blogspot.com/
.
- Follow-Ups:
- Re: Network traffic with transactional replication with immediate updates (SQL 2005)
- From: J.A. García Barceló
- Re: Network traffic with transactional replication with immediate updates (SQL 2005)
- References:
- Network traffic with transactional replication with immediate updates (SQL 2005)
- From: J . A . García Barceló
- Network traffic with transactional replication with immediate updates (SQL 2005)
- Prev by Date: Snapshot Creation
- Next by Date: Re: Snapshot Creation
- Previous by thread: Network traffic with transactional replication with immediate updates (SQL 2005)
- Next by thread: Re: Network traffic with transactional replication with immediate updates (SQL 2005)
- Index(es):
Relevant Pages
|
Loading
