I have a transactional replication publication from the primary server (SQL Server 2008 R2, with the DB in 2000 compatibility) to a read-only copy used for reporting/spreadsheets/etc. Replication is primarily log-based with snapshots on-demand. This publication includes an article for a table of company data (basic stuff: name, address etc).
It was brought to our attention this morning that the data in this table was not being updated on the subscriber when changed on the publisher. We verified that, yes, the table was missing some newly-added rows. Generating a new snapshot fixed that, but further tests show it's still not updating real-time based on the transaction log.
Replication is running, and Replication Monitor is showing data flow between servers with just a few seconds' latency, and so insists that nothing is wrong. The oddest thing is that when I ran a validation on the subscription, I didn't get any result, pass or fail, for the company table (all other tables in the publication passed). It's almost as if the replication process is completely ignoring the table, despite its inclusion in the publication.
The only other clue is that when we generated the new snapshot, the company table errored due to an unsupported column type (
varchar(max), not supported by 2000). We fixed the issue by allowing the snapshot to change the affected columns to text types, then manually changing them back, which is a common workaround that's worked fine before. However, replication was recently reinitialized (couple weeks back) for this database between the two servers to add some new tables to the publication, which involved another snapshot generation. There was no problem with that table for that snapshot, and nothing's changed on it since. In fact, the columns that needed to change to legacy types have been in place long before any replication issues were reported.
This one's really baking our noodles; any help tracking down the issue would be appreciated.
EDIT: My sharp-eyed boss caught a message on the subscription status window that the initial snapshot specifically for the company table article wasn't present. We generated another snapshot, and problem solved.