(MS SQL Server 2008 R2) I have transactional replication setup from server A to server B.
Server A is an Oracle server, so the publisher and subscriber both reside on server B (SQL 2008 R2).
It was working for a while, but then something dropped the privileges (within the Oracle server) of my Oracle user's access to the tables being replicated.
I fixed the privileges (via re-granting those tables SELECT to my Oracle user.)
Then I marked my transaction replication's snapshot for reinitialization and generated a new snapshot.
This appeared to work and brought over the most recent data.
Transactional replication is still not working as any subsequent data changes in the Oracle database aren't getting replicated across.
I don't see any errors in the Log Read Agent Status or Synchronization Status (or the Snapshot Agent Status – which succeeded when I generated the new snapshot.)
I also don't see any errors in any of the jobs that belong to my transactional replication.
Thoughts on how to debug this or what could be going wrong?
I would assume this is an issue on MS SQL Server's side with the transactional replication if the generation of the new snapshot worked fine now after I fixed the privileges in Oracle.
Best Answer
I'm still not sure what the issue was because nothing indicated a problem, but the fix ended up being to just generate DROP and CREATE scripts on the Transactional Replication Publication using SQL Server Management Studio and running them. After which, I had to reinitialize and generate a new snapshot since it was a new Publication technically. I guess the old turn off / turn back on again trick for the win.