Views: 7925
Number of votes: 4
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 ?

    (By Guest , 21 September 2010 10:32, Permanent link)

    We're using Synchronous Database Mirroring (High-Safety Mode) for one of our clients. The High-Safety Mode with Automatic Failover (involving a Witness Server) seemed like the most simple, yet safe option to use for us "part-time DBAs". :-)

    Recent White Paper - High Availability with SQL Server 2008 (by Paul S. Randal)
    http://msdn.microsoft.com/en-us/library/ee523927.aspx
    / Leif Boström

    (By Per Bjurström , 21 September 2010 10:32, Permanent link)

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

    (By Per Bjurström , 21 September 2010 10:32, Permanent link)

    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,

    (By Per Bjurström , 21 September 2010 10:32, Permanent link)

    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.

    (By Leif Boström , 21 September 2010 10:32, Permanent link)

    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

    (By Guest , 21 September 2010 10:32, Permanent link)

    With regards to clustering the frontend I just came across this blogpost http://blogs.iis.net/thomad/archive/2009/10/27/iis7-and-failover-clustering.aspx and this KB article http://support.microsoft.com/kb/970759/
    / Lars

    (By Dan Matthews , 25 May 2011 17:09, Permanent link)

    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 post a comment