on May 23, 2009
This past week at work we found an instance where a replicated table (transactional push) was out of sync on the subscriber. In evaluating how to address the situation, we did some testing in pre-production and discovered the following (using profiler).
- If you update a row on the published table by setting a date column to itself, the row will not be updated at the subscriber. No check is performed to verify the value at the subscriber.
- If you update a row on the published table by incrementing a date column by one millisecond, the row will not be updated at the subscriber.
- If you update a row on the published table that is not at the subscriber, an error will result and the row will not be inserted.
The resolution we went with was to pause processing, re-push the article, reset the application, and use a verify process in our application to detect what had changed in the replicated table. Happily the table was only 14GB so the whole thing went pretty smoothly.
Why was the table out of sync? I found the root cause, but that’s a different story.