We have two SQL Server 2008R2 Standard servers, and we've set up replication of a single database between them. The replication is about as simple as it can get - it's a transactional replication from server A to server B; effectively a one-way push of data.
It's been running for a couple of years, and when it works it works fine -
UPDATEs on server A appear on server B within a few seconds. However, at least 3 or 4 times a year, the replication stops for no apparent reason; sometimes after a reboot (mainly due to Windows Updates), but at other times halfway through the day.
I've spent hours reading documents and trawling through log files, but I still can't determine WHY the replication has stopped. A
tracer token normally gets from Publisher to Distributor but no further.
Every time this happens, I set out determined to finally crack why it's failing, only to give up, blow away the Subscriber and start again from scratch. This fixes the problem (after a couple of hours bulk copying etc), but I know it will happen again. I may be being unfair, but it does seem to be a ragbag collection of SQL agent jobs that keep the whole thing hanging together, and it seems very difficult to troubleshoot.
So with due apologies for the subjective nature of this question, but: is SQL replication fit for purpose? Are there best practises that I should be following to maintain the stability of replication? Is it just me, and SQL replication is normally pretty solid?