0

Firstly, my SQL Server Replication knowledge is not massively great so apologies if I've missed anything obvious so far!

I have a master database with 9 merge replicating subscribers all of which have been working fine for a couple of years now. I recently received a support ticket that data on one of the subscribers looked a little "out of date" one day so the customer restarted SQL Server and everything seemed to "catch up" however an order that was placed just before the user carried out the restart seem to have been lost.

I had a look at the error logs and indeed there does appear to have been a replication issue just before the reset took place "The process could not connect to the subscriber" - this appears to have been fixed by the user carrying out a restart as the messages stop appearing shortly after however I'm at a loss as to how an order is missing.

I was highly tempted to pass this off as user error (incorrectly processed order/connected to test db etc) however there is a whole chunk of missing log data during the period where replication appears to not be working, suggesting that data is indeed missing.

My understanding is that any transactions that are not replicated during a problem period will be stored at the subscriber and then replicated once the connection is re-established. All subscribers in question have regular periods of unreliable network connectivity and we often see these errors appearing however we've never lost data before - it's always propagated eventually.

Is there any way I can investigate this further and ideally recover the data? I don't really want to go to the client and just tell them the data is lost and potentially could happen again.

NB Data has been lost previously however only when a re-initialisation has occurred and the "upload changes" option was missed however in this case, no re-initialisation occurred (at least not that I know of - is there any way I can tell for sure?).

Gavimoss
  • 365
  • 2
  • 4
  • 22
  • Just curious how you were able to fix this issue and if you managed to fully recover lost data. Could you comment, please, or post an answer? – andrews Feb 26 '18 at 13:46
  • @andrews Sorry but no-one ever replied so I didn't get any further with it - we just accepted the loss of data and it hasn't happened again. – Gavimoss Feb 27 '18 at 16:21

1 Answers1

0

@Gavimoss - I know it's a little late, but I had a similar situation and was able to fix it by generating a new snapshot.

I right clicked on the publication that had missing transactions and selected "Reinitialize All Subscriptions". Then in that popup I selected the "Use a new snapshot" option with "Generate the new snapshot now", then clicked "Mark For Reinitialization".

After doing that, the next time replication ran it got all the data and replicated. Took longer to replicate because it was basically wiping everything out and rebuilding the table with all the data, but it worked.

Hopefully this will help whoever runs across this next and prevents the hours of searching that I've gone through over the last few days!

Shawn
  • 45
  • 2
  • 5