SQL Server 2005 – Data Mismatch in Subscriber Database During Transactional Replication

replicationsql serversql-server-2005

We have Transactional replication set up between an OLTP Database server 'A' and reporting DB server 'B', where the Data from OLTP DB(Size 400 GB) "AR" is getting replicated on the server B as database "ARB".

There have been issues reported saying for the Data mismatch on the subscriber database ARB. As per them the latest data is for last two weeks and it should have been the latest instead.

We tried to figure if replication is broken or any error message at publisher or subscriber, but no luck.

In Replication monitor the status shows 'OK'

And status "Running"; Performance "Excellent" and latency varies between 2-3 seconds.

Please help me on how can i find for what could have been possibly going wrong where data does not match between both the DB.

I am new on Tran Replication, any inputs/helps would be appreciated, thanks!

Best Answer

Please help me on how can i find for what could have been possibly going wrong where data does not match between both the DB.

You can use tablediff utility to find out the difference in data between your Publisher and Subscriber server.

Check How to run TableDiff utility for ALL replicated tables ? article by Chris Skorlinski on REPLTalk. This blog has all the required scripts that will help you out.

If you have 3rd party tools like redgate schema and data compare, then your life would become much easier.

As to find out what went wrong, you should check if

  • replication was changed to have filters applied or some column properties has changed to NOT FOR REPLICATION ?
  • distribution agent is working fine along with the log reader agent ?

  • Check where the data is missing -

    • Is data present in distribution database ?
    • Is data in distribution database and not in subscriber database ?
Related Question