SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Trsansactional Replication: distribution agent working very slow


Trsansactional Replication: distribution agent working very slow

Author
Message
nikhil.desai1
nikhil.desai1
SSC-Enthusiastic
SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)

Group: General Forum Members
Points: 128 Visits: 587
Hi,

There are three servers
A-server
B-server
C-server

A-server: is Publisher server (for B-server) with distribution database
Configuration of A-Server
Windows server 2003 R2 64-bit, SQL Server Enterprise Edition 64-bit with SP4 and 10 GB RAM

B-server: is Publisher server (for C-server) and subscriber server (for A-server) with distribution database
Configuration of B-Server
Windows server 2003 R2 64-bit, SQL Server Standard Edition 64-bit with SP4 and 10 GB RAM

C-server: is Subscriber server
Configuration of C-Server
Windows XP 32- bit, SQL Server Standard Edition 32-bit with SP2 and 4 GB RAM


Scenario

There is one table named ‘XYZ’

name rows reserved data index_size unused
XYZ 397031 545544 KB 541616 KB 3816 KB 112 KB

Replicated from A-server (publisher) to B-server (subscriber) every 6 hours from
Morning 5:59 AM to Night 11:59 PM (Replication Type: Transactional)

The same table is replicated form the above B-server (here publisher) to C-server (subscriber) every 6 hours from Morning 6:59 AM to Night 11:59 PM
(Replication Type: Transactional)

When data is replicated from A-server to B-server during its scheduled time it is replicated within 30-45 seconds.


But when data is replicated from B-server to C-server during its scheduled times it takes more than 1 hour i.e. distribution agent is taking more time to replicate the data from B-server to C-server

Note:
Server A and Server B are in same Location.
But server C is in another location

Please give me some solution,

Thanks in advance,

Nikhil Desai
WolfgangE
WolfgangE
SSC-Addicted
SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)

Group: General Forum Members
Points: 489 Visits: 793
There are many reasons why the performance is different.
Without getting in detail I would say investigate in 2 directions:

1) network traffic
Is the distribution agent slow because it cannot read oder transfer the data throught the network?

2) database and server usage
Analyse the usage of the table on the servers. Are there blocking processes? Are there different numbers of indexes to be updated? Is the storage used that much that the table cannot be written that fast? Is the server overloaded at all?
Check anything that slows down write processes on a table.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search