SQL Server Merge Replication Problems

Question

I have Merge replication setup on a CRM system. Sales reps data merges when they connect to the network (I think when SQL detects the notebooks are connected), and then they take the laptops away and merge again when they come back (there are about 6 laptops in total merging via 1 server).

This system seems fine when initially setup, but then it almost grinds to a halt after about a month passes, with the merge job taking nearly 2 hours to run, per user, the server is not struggling in any way.

If I delete the whole publication and recreate all the subscriptions it seems to work fine until about another month passes, then I am back to the same problem.

The database is poorly designed with a lack of primary keys/indexes etc, but the largest table only has about 3000 rows in it.

Does anyone know why this might be happening and if there is a risk of losing data when deleting and recreating the publication?

Answer 1

The problem was the metadata created by sql server replication, there is an overnight job that emptys and refills a 3000 row table. This causes replication to replicate all of these rows each day.

The subscriptions were set to never expire which means the old meta data was never being deleted by sql server.

I have set the subscription period to 7 days now in the hope tat it will now clean up the meta data after this period. I did some testing and proved that changes were not lost if a subscription expired. But any updates on the server took priority over the client.