SQL University – HA/DR Week

Welcome back to another week of SQL University. The winter season is behind us and Spring has sprung (finally) and that’s why I haven’t been wearing pants or shoes since March 21st. Give it a try yourself and you’ll be surprised as to how the promise of good weather can really improve your daily outlook.

Today’s topic will be on HA/DR and I am here to help you get yourself familiar with what that means. Let’s get started!

tempdb

First things first, a few definitions to review:

HA – Stands for High Availability. The word you want to think about here is this: uptime. It’s that simple. If your servers have a high uptime percentage (five-nines) then they are highly available.

DR – Stands for Disaster Recovery. The word you want to think about here is this: recovery. It’s that simple. If you are able to recover your data then you have the makings of a DR plan.

Now, here is the very important piece of information that you need to know: HA IS NOT THE SAME AS DR. For the developers that might stumble upon this blog I would explain it like this: HA <> DR

There, I hope that clears everything up. You would be surprised as to how many people confuse these two terms. I know I was sure surprised that some folks would either confuse the terms, or try to classify issues as “events” versus a “disaster”. To me it doesn’t matter if one server or one hundred servers are wiped out, a disaster is a disaster and you need to be able to recover the data. That means you had best have a recovery plan along with a recovery point objective (RPO) and a recovery time objective (RTO).

For most folks the DR plan is simple: recover the server from a tape backup and restore the databases from backup files (also written to tape). Now, some folks will tell you that they have replication deployed as a DR solution. But I like to play a game called “what if?” So, if your shop is using SAN replication and claim it is their DR solution, ask some simple questions such as:

“What if a corruption happens at Site A and is replicated immediately to Site B?”

And see where that leads you. (HINT: it should lead you to your current DR solution, most likely recovering from tape.)

msdb

Now that you know the difference between HA and DR, it is time for you to know the difference between the many features of SQL Server that help you achieve either HA or DR. Depending upon your version of SQL Server you will be able to use clustering, database mirroring, log shipping, and even replication. And, COMING SOON in the next version of SQL Server is something shiny called AlwaysOn.

You want to know enough about these features so that you can help make an informed decision regarding the architecture needed for your shop, either for one system or even for all of them. For a great summary of the HA options available in SQL Server go here. While there I want you to notice how they only discuss those options in terms of HA. Do you know why? Because none of those options alone will help you in terms of DR. Know why? Because HA <> DR, that’s why.

Did I make that point clear yet?

model

Now that you have an idea about all the different features of SQL server you need to start using them. No, you don’t need to deploy each of them in your shop (despite what some of the worst job descriptions might have you believe), but you should at least try to get your hands dirty here. Find a way to practice with each of them in a test environment, even if that means building some VMs somewhere. You need to configure them in order to have an idea about what it takes to get them up and running as well as to remain stable.

Believe me, if you are sitting in a meeting and someone insists that you need to implement merge replication you had better have an idea about what it takes to get that beast up and running, what steps to take *when* merge replication fails you, and all the additional overhead that goes with merge replication (additional agents, transaction logs, network utilization, etc.). If database mirroring is a better solution for your situation AND you know you can have that up and running (and keep it running) with little administrative overhead then you will want to suggest mirroring and not let yourself be talked into merge replication simply because someone else in that meeting happens to know one buzzword.

But you can’t have that discussion unless you (1) know the differences and (2) have actually tried using the features you are talking about.

master

By now you are aware of the differences between HA and DR, you are familiar with many of the features of SQL Server, and you have even tried your hand at them. To be a master though, requires something a bit more. The best word to describe what that “more” would be is this: foresight.

You don’t need to be a master at each technology, having 5,000 hours of working with everything under the SQL sun. But you *do* need to have an awareness of each, having at least put your hands on the features to understand the strengths and weaknesses of each. And with the experience comes your ability to have some foresight into possible pitfalls.

For example, your company may be leaning towards implementing a particular solution, and you might even agree to it except for one thing: it won’t scale easily. So you take a moment to ask about the expected load for the next year, three years, and beyond. Then you take the time to document the discussion. From that point forward you will be able to help raise awareness for everyone else regarding the current technological needs, make sure they are met for the time being, and also make certain you can start taking steps to build for the future.

And that is what a master does: they help plot the course of actions that will need to be taken over the course of time. They are constantly being proactive and looking ahead, in order to avoid problems, as opposed to those around them that are strictly in a reactionary mode.

resourcedb

Here is a short list of SQL Server professionals that are well versed in all things HA/DR related.

• Robert Davis (blog | @SQLSoldier)
• Geoff Hiten (blog | @sqlcraftsman)
• Allan Hirt (blog | @SQLHA)
• Kendal Van Dyke (blog | @SQLDBA)
• Denny Cherry (blog | @mrdenny)

And here are some links to whitepapers that I know you will find very useful as well:

High Availability with SQL Server 2008

SQL Server 2008 R2 High Availability Architecture White Paper

See you next week!