November 20, 2015 at 2:23 am
Hello,
I do have set up for Transaction replication from SQL Server 2008 R2 to SQL Server 2012. i.e. Publisher 2008, Distributor 2008 and Pull Subscriber 2012.
Now I am replicating 2 database from Publisher to Subscriber. Db 1 is running fine but Db2 is having latency issue. This latency is varying between 2 hours to 9 hours. When I initialize the snapshot it is in sync and after that it is having difficulty in sending transaction from Distributor to Subscriber. Distributor agent is running continuously. However there are no latency from Publisher to Distributor agent.
Distributor agent configuration is default.
Tried following things but no success:
1. Restarted the distributed agent.
2. Snapshot reinitialized.
Any idea why it is having huge latency? How to resolve this issue?
Thank you in advance.
Best regards,
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
November 21, 2015 at 10:29 pm
Think about where the slow subscriber is physically located, and the available network bandwidth between the distributor and subscriber. If the weakest link in that chain is 300 bps, the bandwidth between them will only be 300 bps, even if the distributor's NIC is rated 1 Gbps. If so, can you replicate stored procedure calls instead of DML?
Inspect the slow subscription database. Does it contain the same indexes that are used in the publication database (and the less latent subscription database)? Are other sessions in the latent subscription database blocking the distribution agent's modifications to articles/tables? Is the more-latent subscriber accumulating a more significant wait_type than the less-latent subscriber? In other words, consider the distribution agent's T-SQL within the subscription database like any other client's T-SQL 🙂
November 23, 2015 at 7:07 am
Are DB1 & DB2 on the same server? If not, run a hardware check. I can't count the number of network issues we had because a NIC was going bad. But we couldn't track it because half the time stuff would work correctly on that server. It was like watching a slow meltdown of the network on one box.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply