T-SQL Tuesday

T-SQL Tuesday is a monthly blog party hosted by a different community member each month. This month, Marlon Ribunal (blog) asks us to talk about that one SQL Server outage we’ll never forget:

Tell us about your most memorable outage. The whole story, including what you learned from the experience.

Tell us about your most memorable SQL Server outage. It could have been a midnight page, a failed failover, a runaway query, a storage problem, a bad deployment, a server that simply would not come back up, or something else that brought production to a stop.

I’m interested in the whole story. What happened? How did you discover the problem? What did you check first? What did you try that worked, and what didn’t? How did you eventually get things back to normal?

Most importantly, what did you learn from the experience?

If this story sounds familiar, it means you’ve seen my Documenting Your Work for a Worry-Free Vacation talk because I tell an abridged version of it there.

The Setup

A buddy of mine invited me to take a road trip and join him in Philadelphia, PA to take a tour of the USS New Jersey while she was in drydock for its required museum ship maintenance. Quite possibly a once in a lifetime opportunity - we wouldn’t be touring the inside of the ship, we would be walking around underneath her, on the floor of the drydock. I hit the road on Friday, and picked him up at the airport. We got dinner, unwound from our respective travels, and planned out the following day.

Disaster Strikes

I woke up at 6 AM to over 250 notifications on my phone, all emails in Outlook. Never a good sign when it’s Saturday morning and you don’t have maintenance activities scheduled. It looked like my two-node Failover Cluster Instance had failed over. Failed over a lot. I bolted out of bed, fired up the work laptop, and logged in. Yep, failovers definitely happened. But why? Making matters worse, I saw “server offline” alerts fired for both nodes in my failover cluster at the same time.

Which meant that both servers were rebooting and whichever came back online first would be the primary node. I hope. This is not an ideal situation.

OK, so I knew (well, I thought I knew) what happened, even if I didn’t yet know why. Up next - is everything OK? Especially since we had overnight jobs that could have been running while we had a ping-ponging cluster. I connected to the instance, checked a few tables, and let out an OH %*#@^ that was probably loud enough to wake the neighbors at 6:45 AM.

All the dates on the tables I was checking were from about two months in the past. Every bit of work that had been done in the database over the past 2 months was gone. Completely. Schema changes, transactions, all of it - GONE.

Wake People Up!

I texted my boss. And his boss. Then I tried calling both. No answers. It’s not even 7 AM on a Saturday, no one’s awake and if they are, they aren’t thinking about work. So I made the decision myself - to prevent the situation from getting any worse, I stopped the SQL Server services entirely. Which shut many of the company’s key internal systems down and took some public-facing services offline as well.

By 7:15, I was getting calls back and we needed to start getting a plan together. We sent out an all-company notification that things were shut down, called and texted a couple more people, and I took my laptop to breakfast to start getting a handle on the situation.

After breakfast, we hopped in the car and drove to the shipyard. We had tickets for a 9:00 AM tour but that wasn’t possible now. The folks running the tours were very accommodating and told us we could jump in later in the day. By this time, everyone I needed was awake and online, so the conference call started. Not in a cozy office, not in the back corner of a diner, but in my car. In a parking lot. In the Philadelphia Naval Shipyard. The laptop was sitting in the rear hatch area of the car so I could still see it while I was pacing back and forth.

We put a damage assessment together. What we discovered told us that we shouldn’t trust this cluster at all. We had two options to get to a good state:

1. Invoke our Disaster Recovery plan. Fully cutting over would require a full day and restoring databases from backup.
2. Restore backups to a cold spare SQL Server on-premises we had set up for the sole purpose of replicating it to our DR environment.

Option 1 was not feasible as it was far too disruptive to operations. The DR strategy was built around a complete data center loss, not just one server/cluster. So I got to work on option 2 - restore from backup to about 5 minutes before the time the reboots and failovers started. The conference call ended there, because there was no reason for me to make people listen to me watch databases get restored.

It was at this point that I settled into the back seat of my car, let my guard down, and vented a bit. My buddy’s response was “this must be really bad because I’ve never heard you use those words. I didn’t even think you could use those words.”

Yeah. It was that bad.

Recovery Begins

By 10:15 or so, I was ginning up the scripts to get the databases restored from their backups hosted in Azure Blob Storage. After retrieving the Transparent Data Encryption certificate backups from the enterprise password vault, I started restoring the largest database - projected to take about three hours. With time on my hands now, I ran through a number of the scripts that were generated by Export-DbaDatabase on a regular basis from the cluster to get things in sync on this “new” one and restored a couple smaller (but also important) databases.

At 11:30, I had another problem brewing. Remember that I was running this whole thing out of the back of my car in a parking lot. Laptop batteries don’t last forever, and the folks at the shipyard didn’t have power available to me. Everything was running in an RDP session so if I did lose power (or connectivity), it’d keep running. But walking away from the restores wasn’t possible. So I located an auto parts store nearby, bought an inverter to plug into the car, and then went back to the shipyards. It wasn’t even a “good” inverter (they only had one or two left in stock) - whatever $30 would get me and it probably had the potential to fry my laptop or at least the power adapter. But I was desperate.

Light at the End of the Tunnel

By 13:45, everything critical (logins, jobs, databases, etc.) had been restored. Over the next half hour, my colleagues on the operations and app support/development side of the house confirmed that things were working again, and we brought everything back online. Jobs scheduled between the point in time where everything went sideways and when we brought things online were started and ran without incident.

At this point, I sent out another all-company email telling folks that we were back online but still working on getting to 100%. I could finally breathe, and my buddy & I got to take our tour. It was amazing.

Finishing Touches

That evening back at the hotel, I spent about two more hours to restore additional items (mostly non-critical databases and Agent jobs). When I returned home the following evening, I monitored things for a little while to ensure that everything would be running correctly for the start of business on Monday. Satisfied, I sent out one final email telling the company that all services had been restored and we expected Monday to be “business as usual.”

Monday morning, people logged on and went about their day as though nothing had happened. Which is the best possible outcome from a situation like this

Postmortem

About two months prior to the event during an maintenance where we moved the virtual disks for the cluster from one storage location to another, we landed ourselves in a “split brain” situation (but didn’t realize it at the time). One of the VMs was pointing to Location A for the disks, and the other VM was pointing Location B. When I checked out the cluster after that maintenance, everything seemed fine. Because it was fine at that point in time. We’d shut down the servers for the storage move, so when we brought them back up Location A and Location B had identical virtual disks - for about 10 seconds. So everything would keep running perfectly normally, as long as we didn’t have a failover.

We didn’t expect a failover to happen. The platform used to manage patching in this environment didn’t handle failover clusters well, so we had the servers marked as “do not patch” there - we manually patched them, with manual failovers. Somehow, that setting got un-set and both servers in the cluster were enabled and set to be patched during the same maintenance windows.

On that fateful weekend, patching kicked in right on schedule and both servers got the treatment. After all the reboots and failovers, the primary node for the cluster cluster was the VM that had its storage pointed to Location B whereas before the patching, the primary node was the one using Location A. Location B’s storage hadn’t been touched in two months. Which is why our databases “time traveled.”

The correct data still existed in Location A and was probably OK there, but we didn’t know for certain and didn’t want to risk making things worse. Restoring to the last known good point in time was the safest approach.

Ultimately, it was a series of events over a couple months that led to this outage, with several points where something that would have prevented it could have been checked more closely. The combination wasn’t even what tripped us up. For example, even if the patching wasn’t done automatically the cluster failover on the next manual patching would have pointed the new primary node back at Location B, giving us old data.

Backups Saved Me

Yes, we’re DBAs, backups are our bread and butter. But this event reinforced the importance of doing more than just backing up databases.

• Testing your backups. I was running weekly test restores of backups. I could confidently tell everyone on that conferecne call that we could restore from backup.
• Off-machine and off-site backups. I’ve seen some people write backups to volumes directly attached to the SQL Server hosts themselves. One argument is “what if the network is down? Will the backup fail?” I had that cluster backing up to a network share for a while and eventually moved backups to Azure Blob Storage. Had the backups been written to local volumes, I would have been sunk.
• Transparent Data Encryption certificates and their passwords backed up and stored in the enterprise secrets vault. I was using TDE on several of these databases. Which means the backups were also encrypted, and restoring the backups would be impossible without being able to restore those certificates.
• Backing up the “other stuff.” I was regularly (every 8 hours) running Export-DbaInstance to a network share. This generates scripts for all the things that you won’t find in a user database backup (including the restore script for the user databases). Piecing the rest of the instance together was much smoother and more complete than it would have been otherwise.