Blog Post

SQL Quiz #5


Another SQL Quiz started by His Evilness, Chris Shaw. Okay, Chris isn't evil; I just wanted to say "evilness" and these quizzes are of value to the community. I was tagged by Jorge Segarra, better known as @SQLChicken. So here goes.

Do you feel that you have a reliable SAN Solution? If so what is the secret?

Yes, I do. The secrets aren't so secret:

  • We looked for a quality vendor with an excellent and established reputation.
  • We were willing to spend what it required to put in a viable solution that met our needs and most of our wants.
  • Since we were new to that vendor's equipment, we engaged outside expertise with a proven track record on that product.
  • We ensure all the environmentals are taken care with respect to where the SAN is located.
  • We consider what kind of use disk space corresponds to (is it just a file share, does it handle Exchange message stores or SQL Server databases, etc.) and allocate # of spindles and RAID type accordingly.

Like I said, nothing in any of that is rocket science.


Explain Database Mirroring in layman’s terms

Let's say I've got two very well trained monkeys. They throw and catch pretty near perfect. However, they can only do one or the other at any given time, you just have to tell them which they are doing. If either monkey tries to both catch and throw, we're in for it. What do these monkeys toss around? These monkeys get bits of information in, process it, and then toss it over to the other monkey. And they do this blazingly fast. So at any given time both monkeys should have the same information. In asynchronous mode, that's not guaranteed. The monkey doing the tossing could take a break, go chase a banana, or what have you after he processes some information and forgets to make the throw. Then again, the receiving monkey could be on break himself and not catch a bit of information heading his way. You're in this for speed, not accuracy. But speaking of that, you're only allowed to talk to one monkey. That's one making the throws. You are providing him the info, and then he processes it and tosses it over to the other monkey. Should you try and talk to the other monkey, he's going to ignore you. Now, if the monkey you're talking to decides to wander off, as monkeys are known to do, you've got to go over and specifically tell the receiving monkey that he's up. He'll listen to those words, because he wants to be the thrower. And once he does, you can start talking to him and passing him info.

Now let's say you want to ensure that both monkeys have the same information all the time. This is synchronous mode and now we need another monkey. This monkey we'll call the witness, but he's effectively the ref because he's wearing the zebra shirt. He's carefully watching all the information coming in to the first monkey and ensuring that after it gets processed, that it gets thrown over to the second monkey and processed there, too. Should any of that fail, the witness is going to blow his whistle and call a foul. The first monkey must then take out that bit of processed information. Either both monkeys process it or no monkeys do. As you might expect, this is a bit slower than the other mode. But that's required for data consistency. Oh yeah, the witness can do one other thing if you want. If the witness notices the first monkey keel over, say it was trying to keep up with SQL Rockstar in viewing all 24 hours of PASS, then the ref, er, witness, can call a foul and tell the receiving monkey to start talking. In this scenario, the second monkey can start handling your data and will. However, it sets aside the information in hopes that the first monkey comes back on-line. If the first monkey stays down for the count and the second monkey keels over, too, then you potentially have some of your information lost.