Views: 9089
Number of votes: 2
Average rating:

Notes on SQL Server 2008 High Availability and EPiServer CMS

SQL Server 2008 provides several high availability options, these are my notes from a recent research I did to get an overall idea of the alternatives. What I found was that all are being used by CMS customers to different degrees but my guess is that clustering is most common and replication the least common.

Option 1: Failover clustering

SQL Server 2008 acts as one server but can failover to different nodes in a cluster. The nodes share a single SAN where the actual data files are stored but there is only one node at a time acting as a SQL Server instance. You can have multiple active SQL Server instances if you have several databases that you want scale out on different nodes.

Pros:

  • Automatic fail-over.
  • Guaranteed data consistency.
  • Best practice.

Cons:

  • A failover is not instant, it can take seconds to a few minutes.
  • Expensive hardware and licensing.

 

Option 2: Database mirroring

Database mirroring can be used to get a hot standby database that operates in read-only mode. All transactions are copied to the mirror either synchronously or asynchronously and an instant failover can be configured using a witness server.

Pros:

  • Instant failover if you need to a hot standby server.
  • The mirror can be used as a read-only view of data (for custom queries).
  • Can also be used without automatic failover to get a warm stand-by.
  • Mirror can be on another physical location and no special hardware required (compared to clustering).

Cons:

  • You can only have one mirror.
  • Mirror is not guaranteed to have the latest data if you are running in asynchronous mode (high performance mode).

 

Option 3: Log shipping

Log shipping is comparable to database mirroring but you can have multiple destination servers and a configurable delay before the changes are restored on the destination. You are basically shipping transaction log backups from a folder to the remote server so changes are not instant by design and the database is locked when backups are restored.

Pros:

  • Recommended when you need multiple warm standby databases.
  • Server can be on another physical location and no special hardware required (compared to clustering).

Cons:

  • No automatic failover, you need to take one of the copies of the database online.
  • You are not guaranteed that all data have been shipped before a failure.

 

Option 4: Replication

Replication is the most complicated of them all because you need knowledge of the CMS database and a skilled DBA to operate the configuration. You setup which tables and stored procedures are replicated, requires that you make changes to the database to comply with replication requirements. I’ll get back to replication in a later post since its a whole subject of its own.

Pros

  • High degree of control what is replicated.
  • You could offload semi-read-only traffic from the master to one of the destination servers (you can have some tables writable).

Cons:

  • High degree of control what is replicated is a complex.
  • Requires database changes.
  • No automatic failover.
  • You need some other mechanism to protect the master since the targets are not identical copies.

 

Option 5: Everything

Seriously, you can combine these technologies if you want to build a fort. Just make sure you have a skilled DBA ;-)

 

Do you use any of these technologies with EPiServer CMS ?

Oct 02, 2009

pb
(By pb, 9/21/2010 12:32:39 PM)

Great! And thanks for the link Leif, I have not read that whitepaper.

Lars Flågan
(By Lars Flågan, 9/21/2010 12:32:39 PM)

This is exactly what I was looking for. Do you also have advice for HA (failover) scenarios for the frontend involving several dislocated servers? can be done with load a load balancer but then again you need to have two for redundancy. We have looked into Windows failover clustering and file replication but not sure that is the way to go. Any advice appreciated.

Cheers

pb
(By pb, 9/21/2010 12:32:39 PM)

Are you saying that you are planning an Active-Active hosting on two different physical locations or that you will have an disaster recovery somewhere else. Active-Active normally involves a replicated SAN. Don't know why you need clustering on the frontends since you have the load balancers,

Lars Flågan
(By Lars Flågan, 9/21/2010 12:32:39 PM)

Thanks for your reply. Our scenario is that we have one active location and one passive location that is supposed to take over automatically if the other location goes down. We have been looking into replicated SANs but the pricing does not make it feasible. Our goal is to achieve high availability not balancing load... the to are sort of different.

The customers system engineers tells us to look into Windows failover clustering, but I'm not sure how this would work with EPiServer. Also seen this approach recommended in Theo Schlossnagles book http://scalableinternetarchitectures.com/

pb
(By pb, 9/21/2010 12:32:39 PM)

You should contact our expert services (consulting) if you want help on this case so that they can go through your requirements, I can just give you the generic answer that clustering of front-end servers is not common practice.

Leif Boström
(By Leif Boström, 9/21/2010 12:32:39 PM)

Here's a new KB article on database Mirroring that was just published (Oct. 14, 2009).

Things to consider when setting up database mirroring in SQL Server (by Paul S. Randal)
http://support.microsoft.com/kb/2001270

Dan Matthews
(By Dan Matthews, 5/25/2011 7:09:22 PM)

Can someone provide some more information/links on what I would need in my config files to support Database Mirroring with a primary and witness server (I dont know whether they are going to be synchronous or asynchronous at this time) ?

Thanks

Please login to comment.