SQL Server redundancy options

This recently came up as a discussion on the www.sqlteam.com 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.

Print | posted on Saturday, August 21, 2004 3:10 PM

Comments on this post

# re: <b>SQL Server redundancy options</b>

Requesting Gravatar...
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!
Left by Justin Pitts on Aug 22, 2004 12:11 AM

# re: <b>SQL Server redundancy options</b>

Requesting Gravatar...
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

Left by crazyjoe on Aug 23, 2004 1:09 PM

# re: <b>SQL Server redundancy options</b>

Requesting Gravatar...
Derrick, I had posted a scenario as such, with SQL and SQL Express - http://kevin.tunis.name/archive/2004/06/30/148.aspx
Left by Kevin Tunis on Aug 23, 2004 8:25 PM
Comments have been closed on this topic.