Derrick Leggett Blog

Ramblings, grumblings, and other nonintelligible nonsense about SQL Server.

<b>SQL Server redundancy options</b>

This recently came up as a discussion on the forums.  I posted the options below and Kristen suggested I post it in my blog, so here it is.  :)  This is by no means meant to be comprehensive.  I would be interested in the feedback people have.  Is there interest in actually having an article describe in detail several options?  Does anyone have any options to add, or other options they have implemented?

1. Active/passive cluster
--This provides you with server redundancy, not "drive" redundancy. If one server fails though the other picks up.
--The applications need to be cluster aware.
--Cheapest to set up. You don't have to pay for SQL licensing on passive server unless you are doing something else on it.

2. Log Shipping.
--You have to pay for SQL Server licensing on both servers.
--It's not "immediate" failover. You would have a few minutes of downtime in a dr situation.

3. Replication.
--You have to pay for SQL Server licensing on both servers.
--It's not "immediate" failover. You would have a few minutes of downtime in a dr situation.
--Generally faster failover than log shipping.
--Harder to maintain.

4. Active/Active Cluster with replication.
--Expensive and more maintenance than any of the above.
--Provides server reduncdancy mixed with drive redundancy.
--Very complex.

5. Cold Site Redundancy.
--Use log shipping to ship to remote site.
--Slower recovery than above.
--Covers facility loss.

6. Hot Site Redundancy.
--Most expensive solution.
--Many times requires rewriting application or hosting different apps in different location.
--Involves expensive hardware and software solutions from providers that specialize in this.
--Need enough bandwidth to pull it off.
--Most redundant of all solutions if implemented correctly.

Legacy Comments

Justin Pitts
re: <b>SQL Server redundancy options</b>
Having done only active/active clustering, I dont perceive it as overly complex - especially with SQL Server 2000. MSCS Clustering IIS is MUCH harder (aka dont do it. really. you dont need to.)

Then there's SQL Server 2005 mirroring..... I cant wait!

re: <b>SQL Server redundancy options</b>
You list active/passive as the cheapest, but really it isn't....

You can implement Log Shipping or replication (ick!) on SQL Server Standard Edition, which is a LOT cheaper than Enterprise Edition, which is a requirement for doing clustering. And you have to buy a clusterable OS, which means (I am pretty sure) Windows 2K Advanced Server or Datacenter Server, not just plain old Win2K server.

Otherwise, cool write-up. It would be great to have all that info in one article, as most articles seem to touch on just one option.

I have log shipping and A/P clustering where I am, so I'd be happy to contribute if you like.

crazyjoemama at hotmail dot com

Kevin Tunis
re: <b>SQL Server redundancy options</b>
Derrick, I had posted a scenario as such, with SQL and SQL Express -