Kinda long setup but background is needed...

We're a Microsoft shop, .Net application with SQL Server RDBMS on Azure Virtual Machines. We use Availability Groups (AG) for failover/redundancy and read-only copies. AG uses Windows clustering, and the recommended MS configuration for clustered Azure VMs is a storage pool of the attached VHDs, with OS virtual disks on top of that. It looks and acts the same as physical hardware. One thing I didn't fully appreciate is that the Windows cluster still aggregates all the storage across nodes even if they aren't cluster resources. Remember this.

About 8-9 months ago we wanted to migrate our databases to a new VM config without incurring any downtime. Standard method with AG is to add a new node to the cluster, include it in the AG config, and manually synchronize the databases to that node. Once done we fail over to the new nodes and evict the old ones. We did several tests and all went well, so we scheduled the production migration for 9:00 pm that night.

We added the new nodes to prod during the day and made a small change in drive letters (originally R:, S: and T:, new nodes were SSD based so consolidated it all on Z:) Turns out that while AG will replicate with database files on different drive letters, it won't fail over unless the drive layout is identical. While this was technically a mistake it saved my ass.

We had all the drive letters on the new nodes and decided to prep early for the migration. I open our PowerShell script to manage the storage pool and virtual disks. I run Get-VirtualDisk and see all the volumes listed, but each name shows 3 times (we had 3 nodes). I've noticed this before but didn't really grasp the implication, because all the volumes had the same names on each node. I decided to remove the old drive letters from the new node and run Remove-VirtualDisk for each of them (local backup drive first, then transaction log drive, then data file drive). Literally 5 seconds later:

"Hey ___, the application isn't responding. Is there something going on with the database?"

Turns out, there was. Since the volumes were all the same name, and it was a Windows cluster, drives on all nodes were dropped.

Fuck.

As this was a storage pool with striped disks, rebuilding it was going to be tricky at best, and if it could be done at all, would need manual work and a lot of time. And no one had any experience doing that. And this was during peak business hours.

Fuck fuck fuck fuck...

You notice the sequence I dropped drives? All local backups were gone. Our cluster disk redundancy consisted of copying to every node in the cluster. Our Azure cloud backups were 12-24 hours old, and would take minimum 4-6 hours to restore.

Fuck ^ Graham's number. (yes, all fucks were spoken out loud during this episode)

Since MS recommends not keeping the system databases on the C: drive, we had moved them to one of the drives that no longer existed, so SQL Server wouldn't even start on the primary node. I'm actually sweating at this point and thinking I'm going to get fired. In any case we get the word out to our support folks and they notify customers.

Fortunately that new Z: drive with all the database files on it was still intact. After the the fastest round of Googling ever, I ended up having to evict the dead nodes from the cluster, and forced a failover (the option for this is helpfully and deliberately named FORCE_FAILOVER_ALLOW_DATA_LOSS). First time I ever had to do this BTW. (have done many planned failovers though)

Everything came back up, and from what we could tell no actual data was lost, any in-flight transactions were rolled back when the disks went away. In effect we performed our migration in the middle of the day instead of that night, and were down for 23 minutes. (We had planned on 1-2 hours)*

Lessons learned and/or reinforced:

0. You CAN recover from catastrophic data loss. Don't panic. Or, panic in a controlled and practiced fashion.

1. Understand your product's features and implement them properly. Fortunately SQL Server's features are designed to preserve data as best it can, even if you fuck up.

2. You cannot have too much redundancy. We now copy our backup files to independent local storage, and have a disaster recovery site in another data center, log shipped on 15 minute intervals. (Sadly we have yet to test failing over to it)

2a. All our production database changes now require additional backups immediately prior. (You should also verify that they can be restored)

3. On a Windows cluster, ensure every disk volume has a unique name that includes the node in it.

4. Practice disaster recovery. And that means true disaster, unexpected issues. Look at these and other threads for examples, and ACTUALLY DO THEM.

5. Practice all production changes multiple times in a separate environment. WRITE DOWN the procedures and DO NOT DEVIATE FROM THEM when actually changing production.

Thanks for reading this long thing.

* Our customers were actually happy after the migration because the SSD performance was much faster than the original disk setup.