Log Shipping vs. Clustered Environment

I am embarking on a project to upgrade our SQL server environment. I'm curious for some opinions on the relative benefits of Log Shipping versus a Clustered environment and you have any experience that has helped form your opinion on the issue?
Thanks,
MarcMarc,

I think it depends on what you want to achieve. Log Shipping is useful if you want use a copy of your primary database for read-only queries as it reduces the load on your primary database, although this is not possible if you run Log Shipping continuously as the log shipping restore job requires that there be no connections in the database being log shipped to.
Also, you are building in tolerance against data loss - assuming that the database you are log shipping to is on a different physical disk or server.

You have to have a database in Full or Bulk Logged recovery mode before you can log ship it.

If you have a lot of inserts, updates and deletes occuring on your log shipped database, the size of the log shipping backups and restores can be considerably - something to think about if your network bandwidth is low. In this case you may want to look at using 3rd party log shipping software such as Quest Litespeed which can compress the backups.

Clustering is more of a protection against O/S failures or server failures. When the SQL Server service on the active node fails, is terminated unexpectedly or is shutdown, that is detected by the cluster service and it fails SQL Server (and any other cluster-aware services) over to the inactive node which then becomes the active node.

In SQL 2005 you also have the option of Mirroring which can be combined with clustering...although Microsoft didn't initially support the use of mirroring in a Production environment (so why have it as a feature you may ask!). Not sure whether it is now supported since SP2.

Regards
Lempster|||Lempster -

Can you tell me more about using clustering with mirroring. I'm exploring all of the options (clustering, mirroring, log shipping and replication) and am seeing the benefits of each. I can image the benefit of using clustering with mirroring, but I can't get my head around the actual implementation.

The clusters have to be pointing to a shared storage device. How do you then set it up to mirror? If you have a cluster [Cluster] with [Box A] and [Box B]. Can you set the mirror up to point to the [Cluster] as the principal so that it's not really worried about the individual boxes?

During my research on mirroring, it looks like Microsoft started supporting mirroring with SP1.

Thanks,

Marc|||Marc,

I've not implemented 'clustered mirroring' (for want of a better term), but there's a fair bit about it in SQL 2005 BOL. Essentially, you'd have the principal and mirror on different clusters. The following is taken from BOL:

"The principal server and mirror server both reside on clusters, with the principal server running on the virtual server of one cluster and the mirror server running on the virtual server of a different cluster.

If a cluster failover makes a principal server temporarily unavailable, client connections are disconnected from the database. After the cluster failover completes, clients can reconnect to the principal server on the same cluster or on a different cluster (or on an unclustered computer), depending on the operating mode."

Regards
Lempster