There's another SQL Quiz (from Chris Shaw) doing the rounds where people blog the answer and then tag someone. This I got tagged by two people (Jason Massie and Gail Shaw) in the same day for the same quiz (albeit over a week ago). They either think I'm going to say something profound or funny, or maybe profoundly funny. Can you say something funnily profound? Ah, got it: profound or strange, or strangely profound, or profoundly strange. Whatever. On with quiz. I'll try not to disappoint.
Question 1: Do you feel that you have a reliable SAN Solution? If so what is the secret?
No. Well, that was easy, eh?
Ok, seriously – we don't have a SAN or any production databases per se, as we're a training and consulting company. We do have a lot of storage hardware (3 x DELL MD3000i's packed with 26TB (unformatted)), but it's not managed by a SAN. However, we do have a lot of clients that DO have SANs. So how do we know they have a reliable SAN solution? I guess there are a number of different factors off the top of my head, and I'm not a SAN expert:
-
Was it designed for the job it's doing?
-
Are there redundant components to protect against hardware failure?
-
Was it configured by someone who knows what they're doing, with that brand of SAN?
-
Was it load tested to ensure it's can handle the job it was designed for? Was SQLIOSim run to simulate overloading the SAN to flush out any issues?
-
At the time it was configured, was the firmware all up-to-date, with no known bugs? I saw 'at the time it was configured' because you have to be careful about willy-nilly upgrades to firmware in the various components. Someone that doesn't know what they're doing can destabilize a SAN by upgrading a piece of firmware that subtly changes the behavior.
-
Are page checksums configured on the SQL databases to help detect I/O problems? Are regular consistency checks being run?
I would say that a 'no' answer to any of these is cause for concern.
Question 2: Describe database mirroring in laymen’s terms.
I'll try a few different answers. You be the judge.
1) I could make this very, very simple and just say "It's really technical and you don't want to know". That's not really in the spirit of things though.
2) Imagine 2 seven year-old girls, in separate rooms (like my youngest daughter and one of her friends). Maybe even separate countries. Girl #1 is painting a picture, using the standard easel setup. There's a webcam pointing at the painting that girl #1 is doing. Girl #2 has a monitor and can see what girl #1 is doing. They're also on the phone with each other. Whenever girl #1 paints a brush-stroke, she can't paint any more until girl #2 has made the exact same brush-stroke and said "Done it." That's the synchronous part of mirroring. Girl #1 can't get ahead of girl #2. Asynchronous mirroring is where girl #1 doesn't have to wait for girl #2 to keep up. With a witness, there's a third girl, with two webcams and another phone…
Hmm – ok this analogy isn't working. It seemed so promising! Let's try again…
3) This came to mind after taking a shower this morning in the hotel north of Houston. Database mirroring is like having redundant hot-water heaters. If the hot water fails from one heater, the heat-operated valve flips and the hot water is drawn from the other hot-water heater. You need two hot water heaters, and a fast-operating valve. If the first hot water heater is fixed/warmed up again, you can manually switch the water-flow valve back. If both hot-water heaters are unavailable, no hot water. See, failover clustering won't work, because then you've only got one hot water heater, with redundant pipes coming out of it. And replication won't work because there's a lot of latency between the water leaving the hot water heater and reaching the shower head. Ok – got a bit carried-away there.
Luckily we have a redundant hotel across the street, so if there's no hot water by the time we return from a day of bird-watching on the Gulf Coast, we can move to the redundant hotel. Although that's a lot more hassle, and a lot slower than if this hotel had a redundant hot water heater...
PS Some other folks (that I know of) have replied to the quiz – here are links to their answers:
- Jack Corbett (@UncleBigUns)
- Grant Fritchey (@GFritchey)
- Tom LaRock (@SQLRockstar)
- Brian Kelley (@kbriankelley)
- Jason Massie (@StatisticsIO)
- Brent Ozar (@BrentO)
- Chris Shaw (@SQLShaw)
- Gail Shaw
And you can follow me on Twitter at @PaulRandal
PPS I'm not tagging anyone – been too long since the quiz started I think – the usual suspects have all been tagged already.
4 thoughts on “SQL Quiz #5: SANs and mirroring”
you had it until you tried to introduce the witness – different concept.
You have two little girls trying to act as one – like the movie The Parent Trap. The parents (app) think they have one kid, so each of the kids needs be aware of what the other did so they don’t blow their cover (one kid does the work, the other becomes aware via a late night phone call) and vote which one is currently playing the part of the "real" kid. The two kids can swap roles. Of course any of the kids can be run over by a bus – so a third person needs to be monitoring the situation and if something bad happens, call the Mirror kid and say "you’re now the principal child."
Then you can throw in some esoteric concept like that of TCP/IP and taking the hill and sending a messenger across the valley with the Generals attack orders etc.
I wasn’t serious either.
Actually the whole kids analogy wasn’t serious :-)
The way you wrote it, I though you were – lol!