SQL Server Merge Replication merge agent gone missing

Question

We've got a pair of SQL2000 servers that are syncing a couple of databases using merge replication. At some stage in the last few months, the merge agent for one of the databases disappeared. It's hard to say when this happened, this system is only really used for a couple of months a couple of times a year.

As far as I can tell (I'm no replication expert) both subscriptions and publications still exist, but one of them isn't doing the merge replication anymore due to the lack of merge agent. The other one is merge replicating away perfectly.
The schema is still the same between the databases, but the data has changed. The subscriber server has newer data than the publisher server.

What's the best way to get the merge agent back? I've looked in BOL, scoured the web, all this replication stuff is confusingly scary!

Answer 1

Yes it is scary.

I remember a while back, that if the replication wasn't done atleast every 2 weeks (I think), by default, the subscription would expire and disappear. Its been years since I've done this so this is really hazy. For us when the end use went on vacation for 2 weeks the workstations subscription would expire (before the days of high high speed internet, we used merge replication for field units).

We changed the setting to 6 months before it would expire.

Don't know if that is your issue though, there were a lot of late nights and head scratching on that project all due to the replication........and nightmares when we did sleep.

EDIT : Something else I just remembered. The replication will run using the permissions that the SQL Agent account is running. So if there are any issues with the account the SQL Agent service is using, that will adversely the replication.

Answer 2

I'm not much of an expert on replication either, but when we've had to redo merge replication I just make sure the publisher has the latest data with a sql data compare tool and recreate the whole thing. I don't know if that's an option for yall, but our databases are pretty small and not geographically distant.

The tool we use is Apex SQL Data Diff. I'm sure there's plenty of other options available out there though.