0

Currently running several databases on an Active/Passive SQL Server 2014 Failover Cluster on Windows Server 2012 R2. Recently there was an event that caused the active server to failover. The failover was just a quick moment.

The problem is when the passive node came online, the data was a month old. It wasn't just one database, it was multiple (all six) databases that must have rolled back ~30 days. This is the first time this has ever happened. We've had plenty of failovers; virtual host update/failure, SQL server update/failure, or even failures on the SQL server itself.

We don't yet know why the SQL machine instance itself failed over yet, and we don't know why all the data was rolled back. We fortunately have full and transaction log backups we are currently restoring to.

However, we'd like to understand why did this happen and how do we prevent this in the future; how can we troubleshoot this issue?

3
  • There is no known reason for this to happen. There isn't even a technical mechanism for a SQL Server database to "roll back ~30 days" without restoring old backups. Commented May 23 at 18:43
  • 1
    As David says... this is an odd one. Is your FCI in the same datacenter using shared storage or are you relying on SAN replication to "fake" shared storage? If so then I would start asking the SAN admin some questions... all I can think of is that the SAN presented an old snapshot of the LUN that contained the databases. Commented May 23 at 19:37
  • Our FCI is in our private datacenter on shared storage in the same datacenter. Myself and my other Sr Engineer would swear it's impossible for this to have happened too, but somehow it did. It's somewhat comforting to know that we didn't miss something obvious and this isn't a normal event. Good news is our backup/restore worked and our true data loss and downtime was minimal. We did find errors on the failed SQL server where it lost connection to the shared storage before the failover, but that doesn't explain where/how/why 30 days of data just disappeared. Commented May 23 at 20:33

1 Answer 1

1

The problem is when the passive node came online, the data was a month old. It wasn't just one database, it was multiple (all six) databases that must have rolled back ~30 days.

Much as others have said in the comments, there is no way SQL Server can go back in time 30 days on its own. Given that this is a FCI and uses "shared" disk, the most likely explanation is an improper mapping of the disks or otherwise incorrect setup at the storage/VM level.

You'll need to work with whomever owns the storage setup to check the mappings (current and historical) and if it's a virtual machine, whomever owns the configurations for that and go through each item.

2
  • Our storage solution is an HP Alletra. The SQL cluster is on virtual machines, connected to the SAN via iSCSI. I'm not knowledgeable enough to find where things went sideways. I have my engineer looking into these items, but I haven't gotten much feedback yet. Commented May 28 at 17:05
  • @DavidGerst The storage engineer should be able to match up the initiator and targets and see if they are misconfigured for iscsi. They should also be able to make sure the targets aren't having something else done to then, such as pointing towards snapshots. Commented May 29 at 11:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.