SQL SERVER 2008 R2 PEER TO PEER TRANSACTIONAL REPLICATION SYNC STATUS SCRIPT/3RD PARTY SOFTWARE/OPEN SOURCE

Question

As per previous posts; in SQL SERVER 2008 R2 peer to peer transactional replication we does not have an option to view sync status of each article across say 1 (central) plus 19 (locations) servers; is there any reliable script/inbuilt function or 3rd party software or open source software which gives it's status accurately (will be checked in no transactions/no work load time frame); if in sync or not. Manually tracking is too time consuming and at times inaccurate.

Also, tried to find out a reason as to why replication skips records over internet (replication monitors shows no records pending and in green color). Any suggestion(s) as to how to avoid it in first place will prevent situation above from arising. If there is any guideline for deploying replication in such an environment over internet is appreciated. Thanks in advance.

Answer 1

Hi @DM ,

Welcome to Microsoft Q&A!
Routine monitoring can refer to this Microsoft document:
https://learn.microsoft.com/en-us/sql/relational-databases/replication/monitor/monitor-replication-agents?view=sql-server-ver15

According to your description, there seems to be a data conflict. Please see this document: Peer-to-Peer - Conflict Detection in Peer-to-Peer Replication.
There are some Conflict scenarios in the last part of this article.
Peer-to-peer transactional replication lets you insert, update, or delete data at any node in a topology and have data changes propagated to the other nodes. Because you can change data at any node, data changes at different nodes could conflict with each other. If a row is modified at more than one node, it can cause a conflict or even a lost update when the row is propagated to other nodes.

Best regards,
Seeya

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

I suggest you read this:
https://learn.microsoft.com/en-us/sql/relational-databases/replication/monitor/programmatically-monitor-replication?view=sql-server-ver15#Tsql

Replication does not "skip records". In Merge replication, you have the opportunity for "conflicts" which are resolved by rules. I suggest you read this:
https://learn.microsoft.com/en-us/sql/relational-databases/replication/view-and-resolve-data-conflicts-for-merge-publications?view=sql-server-ver15