6

I have a transactional replication between two SQL Server 2008 SP1 servers, both on Windows 2003 Server. I discovered that there are discrepancies between some of the tables of the main database and the subscription database. I've done a manual validation over the record counts and there are small differences. I know there's a latency between the two sides, so that's not to worry too much, but there is a specific table that will not synchronize no matter what (new rows appear, but some specific older rows do not appear, so they look like they were removed after synchronization).

The situation is that the subscription database is not read-only, so an application may read and also write data to this database.

Now I'd like to know what solutions would you use for synchronizing the two sides. I can think of two right now:

  • use the tablediff.exe utility for each article to compare source and target tables and generate scripts

  • reinitialize the subscription so it will invalidate last snapshot and generate a new one.

Are there any other solutions to fix this?

Improve this question

1 Answer 1

Reset to default
3

I'll answer this in 2 parts.

First, if you want to get the data back in sync, then you should reinitialize. Check to see what is changing the data though. If this is a big table, reinitialization could take a while.

Secondly, If the subscriber isn't read only and you are trying to do transactional replication, you are going to run into this error a lot. People, apps, whatever can change a couple rows and boom, the emails start flying in. If you need both sides to be able to CRUD data, then I would use merge replication. This enables both sides to update data.

Improve this answer
3
  • I agree with you, I was just searching for other means of fixing this problem. I'll synchronize by hand using tablediff and I'll validate again the replication. One of my colleagues has fixed an older replication conflict by removing some rows, but it seems his operation wasn't complete. Anyway, merge replication is not a choice now, because we don't need data modifications propagated to the publisher. Thanks!
    – Marian
    Commented Jun 1, 2011 at 7:11
  • 1
    Check this out - technet.microsoft.com/en-us/library/ms151331.aspx. You could always skip the errors and continue you on your way.
    – SqlSandwiches
    Commented Jun 1, 2011 at 20:25
  • I'll study the possibility of skipping some errors for future. Thank you!
    – Marian
    Commented Jun 2, 2011 at 6:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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