Post reply

Trsansactional Replication: distribution agent working very slow

  • July 4, 2013 at 11:51 pm

    #279954

    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

  • July 12, 2013 at 8:50 am

    #1631987

    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.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply