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?
Best Answer
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.