August 9, 2010 at 3:16 pm
I am having a failure with replication and I'm not sure what is wrong.. I set up replication between two SQL 2000 machines. One machine is both the publisher and the distributor. The other is the subscriber. I used the wizard to replication all tables in a database. The database on the subscriber was am empty database at the start of this process.
The snapshot was created and applied to the subscriber. That subscriber database now has tables and data. However, if I make a change in a table on the publisher, the change is not propagated to the subscriber. This is a push subscription. The distribution agent is running with no errors and the status says "no replicated transactions are available." The properties of the publication show that the table I am changing data in is, in fact, part of the publication.
On the subscriber, if I look in the replication folder, under Subscriptions, I see the subscription. It's status is "Running" and the last action was "Synchronization in progress." Under replication type, it says "Snapshot replication", which is incorrect. This is a transactional replication and it says that in the publication properties on the publisher.
Any ideas? This is a huge database and the snapshot took 16 hours to transfer, so I really don't want to have to do that again.
Thanks,
Shaun
August 9, 2010 at 3:30 pm
It's odd that the replication type is showing snapshot. I would certainly call that suspect unfortunately. 🙁
You can drop that publication and create a new transactional publication and when you are adding the subscription you can choose to not initialize. I haven't worked with replication in 2000 but the choice becomes available either 1 or 2 steps after the agent security profile is configured via the GUI.
You would have to manually sync any data that has been changed since the snapshot though.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
August 9, 2010 at 3:37 pm
Yeah, the problem is, this is an accounting database and I don't have a clue what all the missing data might be. I tried manually initializing the subscription over the weekend and ran into this same problem, so I went with the wizard, thinking I might have made a mistake somewhere and let the wizard do everything to eliminate that possibility.
If it matters, this database has been replicated to this server before, using the same database names. We've dropped and re-created the subscription a couple times. I'm wondering if some piece of the old replication is hanging around. Never used snapshot replication though, so I'm not sure why that is showing up.
I don't really want to redo this unless I have some sort of evidence this won't happen again after another 16 hours of downtime.
August 11, 2010 at 10:05 am
I ended up completely removing replication manually from the publisher/distributor by following the steps listed here: http://support.microsoft.com/kb/324401. That completely removed the distributor database and all publication info. I then re-enabled the server for replication and configured the publication and subscription again. Now it is working.
August 11, 2010 at 10:56 am
Glad it is working for you now. Definitely the metadata can cause problems and I have had success in cleaning that out manually in the past without removing replication completely. Pretty good idea that MS knows it doesn't always clean up well after itself as they still leave these "system" tables as something that can be updated / deleted from by the end user without being in maintenance mode. 🙂
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
August 11, 2010 at 10:59 am
Yeah, I was going to try cleaning up the tables manually, but this was the only publication on the server and I was under time constraints, so I used the nuclear option 🙂
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply