0

I'm using SQL Transactional Replication with pull subscriptions to replicate databases (hosting their own distribution database) from several servers across a VPN to a central server. I've got the first 2 databases working fine but the 3rd one is causing me problems.

My subscription server is SQL 2008, the source systems are all SQL 2005. The source databases are a few 100Mb in size and contain audit data so are simply growing slowly by adding new records at approx 1kb a second.

As far as the replication monitor, Agent logs and event logs show everything is working fine - except that no data appears in my subscription database.

The distribution agent doesn't seem to want to read the snapshot (and hence the initial state and schema) from the publisher. New transactions aren't applied although they do seem to be arriving OK as the replication monitor shows things like '5 transactions with 10 commands were delivered'. I would expect (as in previous times) to see statements about data being BCPed in the replication monitor.

The snapshot is on the publisher on a shared folder. The subscriber can view the snapshot OK (\\repldata) and the alt snapshot folder is pointing at it. But the distribution agent doesn't seem to be making an attempt to do read it. I tried changing the snapshot path to something that's incorrect and didn't even get an error saying that it couldn't access it.

After lots of googling etc I found that sp_MSget_repl_commands is called by the subscriber on the distribution database on the publisher. Running a profiler I can see that it's only called for one agent Id. After a reinit it's called for sequence number 0x0 as expected so I thought that would mean it's would look for the snapshot.

However, looking on the publisher I see that there's data for two agents - the snapshot agent and the log reader agent (which is being queries). So I guess I need to tell the distribution agent to get the data for both. But how? and more importantly - why? It worked fine on the other two servers I've replicated.

I'm not an SQL novice but this is pretty much my first go at replication so don't be afraid to accuse me of missing something obvious/stupid!

I can get log files (eg from the distribution agent) if you want but they don't seem to have any errors in them - it just starts up and starts applying log reader agent changes.

Cheers Dave

Marcello Miorelli
  • 215
  • 1
  • 12

2 Answers2

1

Check the Publication Properties-->Snapshot-->Location of snapshot Files-- Make sure put files in the default folder is checked, in my case it was't check marked instead put files in the following folder was checked with a location address(C:\REPL) but i had my distributor in a different server other than publisher and the location address was misleading to distributor agent .

Sam
  • 11
  • 1
0

I've got this working now by redoing it for the 4th time - but annoyingly I'm not really sure how.

The only thing that I can see that is different is that Snapshot and log reader agents are running as the domain admin rather than a local admin. I don't think that should make any difference but without any error messages it's hard to tell...