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

asked 2021-11-17T18:32:35.3+00:00
DM 401 Reputation points

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.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
8,455 questions
{count} votes

Accepted answer
  1. answered 2021-11-18T06:02:52.517+00:00
    Seeya Xi-MSFT 12,511 Reputation points Microsoft Employee

    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.


1 additional answer

Sort by: Most helpful
  1. answered 2021-11-17T20:59:35.04+00:00
    Tom Phillips 17,521 Reputation points