After you have defined what High Availability (HA) means to you and your shop, it’s time to pick the type of HA you’re going to use.
Even though there’s quite a bit of overlap among HA technologies, they’re typically meant to protect against different things. But there’s good news: many of them work together quite well!
Here are the more popular approaches, and what they’re primarily for:
Clustering
Clusters don’t protect against data failure of any kind. A SQL Server cluster does protect against Windows issues. Depending on your setup, it could also protect against application issues.
Clustered SQL Server protects against:
- Hardware failure
- Some software failure, possibly application issues
- Windows issues
Failover: Automatic.
Note that distance is a huge factor with clusters.
Replication
There are some who don’t consider replication to be an HA solution, but I beg to differ. This can protect against hardware and Windows failure, accidental object drops, and more. Failover isn’t automatic though, so you’ll need to consider that in your scenario.
Replication protects against:
- Hardware failure
- Windows failure
- A limited amount of data failure (depending on how it’s set up)
- Accidental object drops (the act of replicating an object protects it from being dropped by accident)
Failover: Not automatic.
Log Shipping
No, seriously! Sometimes these low-tech solutions are the best.
Log shipping protects against the same things that replication does, if you do it right. And again, failover isn’t automatic.
Log shipping protects against:
- Hardware failure
- Windows failure
- A limited amount of data failure (depending on how it’s set up)
- Accidental object drops (if there’s a planned delay between the backups and the secondary restores)
Failover: Not automatic.
Availability Groups
Availability groups are the best of both worlds. You get the power of clustering, replication, and log shipping. Plus you get the bonus of distance, and protection against those DDoS attacks we talked about last week.
Availability Groups protect against:
- Hardware failure
- Windows failure
- Some software failure, possibly application issues (if the app is on the server)
Failover: Automatic.
Geo-clusters/Stretch clusters
Despite the name, geo- or stretch clusters bear little resemblance to failover clustering. Depending on the solution you go with, it is much closer to replication or log shipping. Depending on your solution you may even get automatic failover.
The big advantage for stretch clusters is distance. This bridges the distance limitations of failover clustering and some of the other solutions.
Choosing a Solution
I don’t typically believe in a single HA solution. Surprise!
A single HA solution usually won’t cover all a shop’s requirements. Here’s a good example:
Let’s say that you need to protect against physical failure, and also against personnel doing something stupid (like deleting the wrong data). You decide to implement an Availability Group, but the AG won’t save you from deleted data. (Don’t believe me? Just try it.)
To cover data loss, you need log shipping or scheduled replication. Your team to go with log shipping, and to keep the log shipping restores 12 hours behind the production server. That gives you time to recover deleted data and any objects you drop by mistake.
The moral is: don’t get hung up on a single solution. More than likely, it won’t meet all your needs.
Next week, we’ll talk about using your chosen solution…or solutions! And this week…our live session:
Watch the “Learn SQL Server: Uptime!” webinar
Sign up for our newsletter for content and events in your inbox.
