Writing this, I’m at home but still recovering from open heart surgery at the start of August. I’m working a little, but not full time yet. So when Marlon Ribunal asked to write about outages, I was tempted to give more details about my current predicament. I mean, they did stop my heart for a while, but I get that’s pretty dark humour. And Marlon is asking specifically about SQL Server outages, of which I definitely have plenty of stories. I’m the kind of person people call when they have SQL outages, after all. And often I haven’t had a chance to help them avoid the outage, I’m just there to fix it and help them after things are back online.
Naturally when there’s a failure, there are questions about High Availability solutions. If the customer has an Availability Group, maybe they’ve avoided an outage completely – the primary fails over to the secondary, and the race starts to get the original machine back in case another failure (on the new primary) happens. Those situations aren’t the ones that keep coming back to mind, but are still stressful for the temporary loss of the safety net.
The bigger issues are when there is no quick failover available. About thirty years ago, I was dealing with a SQL Server 6.0 environment, where the customer’s DBA was cycling tapes (yes, tapes) for differential backups, except that he’d reused the tape that the full backup was on. When that system went down, I had a quick and harsh introduction to salvaging files off the disks from a failed computer. Fun times.
But the one that sticks in my head was a problematic disk controller that had sprayed corruption through hundreds of data pages and even many backup files. I’m good with corruption (I won a competition some years back), because I’ve studied (and taught) database internals. If a page of data has corruption, I can often tell what needs to happen to resolve that. Recovering from hundreds of corrupt pages though? That was interesting.
Other people were trying to retrieve a “good enough” backup, but I’d been called in because they didn’t think they had were going to be able to find anything useful. I figured that even a restore from a really old backup could be useful for the sake of reconstructing tables, so I was happy for people to keep looking for whatever they could find.
We (being me and the CTO of the customer) took a table-by-table approach, rather than the usual method of working through the list of suspect pages. We ran “SELECT *” queries against each table to test clustered indexes, ordering by the clustered index keys forwards and backwards, filtering to ranges, to identify which blocks of data we had and which ones we didn’t. Some tables were fine, of course. Most had several missing rows. We figured that corruption in nonclustered indexes could be fixed by recreating them after the clustered indexes were good. But we kept the nonclustered indexes around (even the corrupt ones) because they might contain many of the values that were missing in the clustered indexes.
Some tables did have nonclustered indexes that we could use to work out some missing values, by querying specific ranges of the NCIXs (to avoid the corruption in them), and making sure not to have lookups hitting the corrupt pages in the CIXs. A few tables had enough NCIXs to allow all the missing values to be found, which was great, but generally there were still some values we couldn’t get. DBCC PAGE helped us get a lot of data even from corrupt pages, of course. A restored backup from a few months earlier even helped, and some emailed reports allowed us to infer probable values for the rest. We didn’t care where we got the data – we just wanted to recreate the tables as best we could.
Once the values were known (enough), it was possible to create new versions of each table and get new NCIXs in there. The odd missing value remained missing, but these were very rare, all things considered. After the best part of a day we were able to bring the application back up, and everyone was happy enough. I should have a special rate for that kind of work, but I do kinda enjoy it. 😉
It stuck in my head for years because we used so many different methods for retrieving values. The significant thing for all disaster recovery is having extra copies of your data. Ideally the copies are stored in backups (that have been tested) on other servers, forming proper HA solutions. But if you’re desperate enough, any copy of the data might do the job.
@robfarley.com@bluesky (previously @rob_farley@twitter)
