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 ?