It is once more time for me to step into the T-SQL Tuesday fray with a post of my own.
T-SQL Tuesday is a monthly community blogging event started by Adam Machanic back in 2009. Each month, a host picks a topic, participants write about it on the second Tuesday of the month, and the host posts a recap linking everyone’s contributions together. It has been running for over fifteen years now, which is frankly remarkable for anything on the internet.
This month’s host is Marlon Ribunal, and his topic is both a fun and an evil one: the one outage we all remember.
I know exactly which one I’m going to talk about.
The Patient #
This was back around 2015, and I had been working with SQL Server for the better part of 15 years. I considered myself reasonably skilled at database administration, and I enjoyed high availability as much as the next guy. I was often brought in to either solve SQL Server problems or help architect SQL Server so it wouldn’t become a problem.
This day was one of the former.
The patient was a misbehaving SQL Server 2014 running in a two-node cluster. It was your garden-variety cluster with a shared disk and a remote witness. In my experience, as long as the customer has enough know-how to maintain a cluster like this, it’s essentially bulletproof.
This specific cluster was not.
The Symptoms #
Everything worked great.
For the most part.
It was fast. It failed over quickly during testing. Everything connected to it as it should, just like you would expect from a well-oiled machine.
Then, occasionally, a connection would time out. It didn’t want to come up after a failover. One node would silently stop talking to the world.
Everything was inconsistent and intermittent, and at the time, I couldn’t see a pattern.
My colleague who had set the cluster up was one of the living legends at the company. He’d been in infrastructure for 25 years and had twice as many certifications as I did from three times as many vendors. Linux, IBM, HP, Microsoft, Cisco—you name it, he had it.
So my thinking was that the cluster was set up correctly, and that there was something deeper in the infrastructure.
So of course I thought it was DNS.
In my defense, “It is probably DNS” is one of the titles I’m kicking around for my possible memoirs.
And as general statements go, it’s not half bad. It is embarrassingly common for DNS to be the culprit, so I dove into it.
And it was DNS.
Or, rather, it wasn’t only DNS.
There were some misconfigured records that sent packets up a one-way street from time to time, and that certainly didn’t help.
But it wasn’t the main issue.
Expectation Bias #
After looking at everything I could think of, it was time to take a skeptical look at the whole stack. Starting from the bottom with storage, the OS, networking, and moving upwards.
Everything looked exactly as I expected.
That turned out to be a huge part of the problem.
When learning to fly, pilots are taught that complacency and expectation bias can kill. The time you don’t sump the fuel because you’re sure the fuel you just filled is good might be the time water has, in fact, gotten into the tank and will make your engine quit on takeoff.
Or the time you go through the checklist, possibly even say the item out loud, but don’t really check it because you’re expecting to see the number you expect to see.
This happened to me not long ago.
I was going through the pre-takeoff checklist, and one of the items is to set the altimeter to the correct setting. It depends on the outside air pressure, so it can vary more than you might think between days, and even between morning and afternoon.
I know my airport sits at 180 feet, but the altimeter was set at 280 feet.
I looked at the checklist item, I said “altimeter at 180 feet” out loud, and promptly left it at 280 feet.
This time I had an instructor in the right seat who caught my mistake.
Granted, being 100 feet off on a clear visual flight rules day wouldn’t have made any material difference. As soon as I talked to air traffic control, I would have gotten the correct pressure, dialed that into my altimeter, and realized it had been off.
But that isn’t the point.
I expected the number to be there, so I saw it, despite it not being there at all.
And that was what was happening with my cluster.
It Is Always the Network #
It turned out that the main issue was that the network mask for the private interconnect between the cluster nodes was set incorrectly on one of the nodes.
The network mask controls which addresses a machine considers to be on its local network. With the wrong mask on one node, some traffic took a path that simply didn’t work.
The old SQL Server version—which was state of the art at the time!—didn’t check that both nodes had the same network mask.
And if you’re expecting to see 255.255.255.0, 255.255.0.0 doesn’t look like that much of a difference.
But it is.
I had done exactly what I tell people not to do: I checked what I expected to find, rather than checking what was actually there.
The cluster wasn’t bulletproof.
My assumptions were.
Photo by Pixabay: https://www.pexels.com/photo/close-up-photo-of-ethernet-cable-163047/