I have 1 transaction replication (peer to peer topology). After I setup, I found that the data on 2 table was difference.

The first peer table [VPMYN204].[AdventureWorks2019].[Person].[AddressType] have 6 rows. And the second table [AdventureWorks2019].[Person].[AddressType] have 7 rows.

enter image description here

  1. What should I do? (P/S: I am new to transaction replication feature.)

I've seen certain operations throw Transactional Replication out of whack before, and the first turn it off, turn it on again solution I'll go to when that happens is reinitializing the subscription for that Publication. Per the aforementioned documentation:

Connect to the Publisher in Management Studio, and then expand the server node.

Expand the Replication folder, and then expand the Local Publications folder.

Expand the publication that has the subscription you want to reinitialize.

Right-click the subscription, and then click Reinitialize.

In the Reinitialize Subscription(s) dialog box, select options, and then click Mark for Reinitialization.

After that I'll usually monitor the snapshot agent status since the above generates a new snapshot that your Transactional Replication Publication will work off of, and I like to ensure it finishes replicating correctly.

After that's all done, you should be in sync now from the new snapshot, but you should try a data test, as I've seen worse cases where this didn't fix the issue. If you're still hosed at this point, you may have to drop and recreate your Transactional Replication Publisher and Subscriber(s), unfortunately.

  • Seem like I have to drop and recreate. This is the error if I use reinitial-snapshot. "Cannot reinitialize the article 'all' in subscription 'VPMYN204:AdventureWorks2019' to publication 'Pub_AdvWork2019_4'. The publication is enabled for peer-to-peer transactional replication, which does not allow subscriptions to be reinitialized with a snapshot. Drop and re-create the subscription instead. Changed database context to 'AdventureWorks2019'. (.Net SqlClient Data Provider)"
    – YHTAN
    Jun 1 at 1:24
  • Beside this, may I know is that any feature that can use to detect the data variance on the 2 server tables?
    – YHTAN
    Jun 1 at 1:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.