1

(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.

  • 2
    What do you see on the Replication Monitor? – Edgar Allan Bayron Jun 20 '18 at 17:45
  • Sorry, I removed my last comments because I realized I'm being an idiot and was looking at the wrong node. If I look at the publisher of server, I see the Transactional Repliction publisher. If I click on it, under the "All Subscriptions" tab it says Status = Running, Performance = Excellent, Latency = 00:00:00, Last Synchronization = 6/20/2018 3:19:54 PM (literally half a minute ago). – J.D. Jun 20 '18 at 19:20
  • The Agents tab shows the Log Reader Agent job Status = Running, Last Start Time = 6/19/2018 10:54:09AM (Yesterday morning), Duration = 1.04:26:52 (so I'm guessing that means 1 day 4 hours 26 minutes 52 seconds), Last Action = "No replicated transactions are available". – J.D. Jun 20 '18 at 19:22
  • In the Tracer Tokens tab I added a Tracer and the latencies are as follows: Publisher to Distributor = 6s, Distributor to Subscriber = 3s, Total Latency = 9s. – J.D. Jun 20 '18 at 19:24
  • 1
    @J.D. check this link here and make sure you’ve got the latest patch in you SQL Server instances – user37701 Jun 21 '18 at 14:14
0

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.