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 - INSERTs or 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?

  • Have you ever found the solution?
    – MichaelD
    Dec 10, 2015 at 16:41
  • We eventually scrapped the "build-in" migration, and hand-knitted our own solution based on Log File Shipping :( I've no idea if it's improved in more recent versions, but it did seem pretty badly broken in 2008R2.
    – KenD
    Dec 10, 2015 at 16:51


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.