Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Trsansactional Replication: distribution agent working very slow Expand / Collapse
Author
Message
Posted Thursday, July 4, 2013 11:51 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, August 25, 2014 5:48 AM
Points: 35, Visits: 496
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
Post #1470562
Posted Friday, July 12, 2013 8:50 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 5:21 AM
Points: 197, Visits: 730
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.
Post #1473080
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse