Re: Network traffic with transactional replication with immediate updates (SQL 2005)

Done:

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=168891
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=168892

The bug was assigned 2 different IDs. I had a problem with Microsoft
Transfer Manager ActiveX (which I had not installed) and IE asked me to
install it. Probably the form was posted twice for this reason.

Can you, Hilary, and any of you that might find this behaviour as buggy,
give some votes for it on the former urls?

Thanks a lot.

--
J.A. García Barceló
http://jagbarcelo.blogspot.com/


"J.A. García Barceló" <jagbarcelo@xxxxxxxxxxxxxx> escribió en el mensaje
news:%234qU7wxrGHA.3324@xxxxxxxxxxxxxxxxxxxxxxx
Of course I could schedule it to run every 1, 2 or 5 minutes instead of 5
seconds (by default), but in that case the latency of transferred commands
will increase accordingly. That will soften the pain but it will not be
the cure.

This kind of configuration using 28 publications for a db, a single
publisher/distributor running on the main office and a subscriber at the
branch has been working without such a big network impact for years with
SQL Server 2000. This is a new SQL Server 2005 behaviour, and IMHO is
against slow-links network efficience. If there is not a really important
reason for running those commands against the subscriber when there is
nothing to replicate, the agent should execute them (maybe the agent just
launches those commands in order to be able to calculate average
transactions lacencies, other statistical reasons).

I think I will open it as a bug at
http://connect.microsoft.com/feedback/default.aspx?SiteID=68 or, at least,
as a comment.

--
J.A. García Barceló
http://jagbarcelo.blogspot.com/

"Hilary Cotter" <hilary.cotter@xxxxxxxxx> escribió en el mensaje
news:eFOJOjxrGHA.3324@xxxxxxxxxxxxxxxxxxxxxxx
I have confirmed that it does run every 5 seconds. Perhaps if you were to
schedule your agents to run every minute it would replicate all
transactions and related commands in the queue, go to sleep for 1 minute
and then wake up and process the rest. It really depends on what your real
time latency requirements are.

--
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:O$7i9fvrGHA.2464@xxxxxxxxxxxxxxxxxxxxxxx
As suggested in microsoft.public.es.sqlserver (where I already posted
this problem, but translated), I run profiler on the subscriber:

I do not need to wait until off-hours to run a trace on the subscriber.
Right now the database is divided in 28 publications (with 155 tables in
total, it is not a good idea to have a huge single publication with the
added problems of reinitialisations, schema changes, etc). Some of the
publications contain tables seldomly used or almost not updated at all.
In particular, I run profiler on the subscriber with a filter:
HostName : publisher name (MYSERVER)
ApplicationName : MYSERVER_mydb_mypublication

And, right now, with a PollingInterval=60 secs, with the trace running
filtered by a publication with tables not updated at all, I can read
EVERY 60 seconds the following RPC:Completed command:

exec sp_executesql N'update MSreplication_subscriptions set
transaction_timestamp = cast(@P1 as binary(15)) +
cast(substring(transaction_timestamp, 16, 1) as binary(1)), "time" = @P2
where UPPER(publisher) = UPPER(@P3) and publisher_db = @P4 and
publication =
@P5 and subscription_type = 0 and (substring(transaction_timestamp, 16,
1) =
0 or datalength(transaction_timestamp) < 16)', N'@P1 varbinary(14),@P2
datetime,@P3 nvarchar(5),@P4 nvarchar(5),@P5 nvarchar(7)',
0x00004F3200000032000100000000, 'Jul 24 2006 9:03:59:000AM',
N'MYSERVER',
N'mydb', N'mypublication'

Oviously, if, instead of running this every 60 seconds it was executed
every 5 seconds (and taking into account that we have 28 agents doing
the same) we would have: 60x28/5 = 336 RPCs/min or more than 5 RPCs/sec
24/7. If we add legitimate user transactions, usual network traffic
(mail, web browsing, dfs transfers) we get the network permanently
congested at 300kbps.

Can someone else confirm that their subcribers receive these RPC
commands every 5 seconds (default PollingInterval for Distribution
Agents)? If this is proved to be true, we would be facing a severe
problem from the performance (and scalability) point of view when the
number of subscribers grow.

Regards.

PS: I forgot to mention: the RPC command sent to subcriber is not always
the same: transaction_timestamp and time changes every time ;)

--
J.A. García Barceló
http://jagbarcelo.blogspot.com/



--
J.A. García Barceló
http://jagbarcelo.blogspot.com/

"Hilary Cotter" <hilary.cotter@xxxxxxxxx> escribió en el mensaje
news:%23jbWYaSrGHA.4016@xxxxxxxxxxxxxxxxxxxxxxx
What autosenses does is if the connection fails the distribution agent
will restart if the connection comes back. I am wondering if this isn't
pinging the subscriber even if you are running continuously. This
behavior should be disabled if you are running at scheduled intervals,
ie every 2 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:uVHmKNRrGHA.3564@xxxxxxxxxxxxxxxxxxxxxxx
Yes, they are running continuosly:
[...]-Continuous

Specifies whether the agent attempts to poll replicated transactions
continually. If specified, the agent polls replicated transactions
from the source at polling intervals, even if there are no
transactions pending.[...]

This is by default when you set up a new subscription with
transactional replication with immediate updates. Besides, it should
not matter how frecuently you query the distribution database since
the agents are running in the same server as the distribution database
is. The right question is: Why is subscriber contacted (traffic) when
there is nothing to replicate?

I could try to change things next monday, in the meantime... what is
that autosense conection functionality you mentioned? I can't find a
word about it now (I don't have BOL in this machine either).

Thanks and regards.

--
J.A. García Barceló
http://jagbarcelo.blogspot.com/


"Hilary Cotter" <hilary.cotter@xxxxxxxxx> escribió en el mensaje
news:u1qZ%23GRrGHA.3680@xxxxxxxxxxxxxxxxxxxxxxx
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/













.

Relevant Pages