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

Log Shipping Question

All-
Is their a utility that does log shipping? Maybe something
in the SQL 2000 Resource Kit? Or do I have to upgrade to
the Enterprise Edition from my current Standard?
Also, where do I find/install the SQL 2000 Resource Kit?
Thanks,
DH
On the resource kit there is a set of procedures which are unsupported.
Depending on your service agreement, you may already have the disk
containing the resource kit but if not, you can buy it from
Amazon:http://www.amazon.com/exec/obidos/tg...8/103-0789888-
8246260?v=glance
Regards,
Paul Ibison
|||OK, thanks Paul...
Another question.
We would like to have a Hot Standby SQL server at another
site connected via a VPN. What in your opinion is the most
efficient way to replicate 2 SQL servers over a VPN?
Log Shipping? Transactual Replication?
Thanks,
DH
>--Original Message--
>On the resource kit there is a set of procedures which
are unsupported.
>Depending on your service agreement, you may already have
the disk
>containing the resource kit but if not, you can buy it
from
>Amazon:http://www.amazon.com/exec/obidos/tg/detail/-
/0735612668/103-0789888-
>8246260?v=glance
>Regards,
>Paul Ibison
>
>.
>
|||Darius,
transactional replication has lower latency than log shipping, but the 2 are
not entirely equivalent as far as DR goes. Transactional replication won't
take defaults, user defined datatypes, users or permissions - these you'll
need to script out and apply on the subscriber manually. Also, some schema
changes to published articles are not directly allowed - eg changing the
length of a varchar column. If you need it to be more in synch, then
transactional replication is better (using a short pollinginterval value)
but the above issues ned to be taken into account.
Regards,
Paul Ibison
sql

Log Shipping and loading service packs

Hi All
Does anyone know if there will be any issues with
upgrading a SQL service pack on a SQL server that is using
log shipping? I want to upgrade the standby server last.
Is this order correct?
Thanks
H
Hi,
No problem, You can update the service pack in production server first and
later the stand by servers.
Thanks
Hari
MCDBA
"H" <anonymous@.discussions.microsoft.com> wrote in message
news:743301c430ab$29489520$a501280a@.phx.gbl...
> Hi All
> Does anyone know if there will be any issues with
> upgrading a SQL service pack on a SQL server that is using
> log shipping? I want to upgrade the standby server last.
> Is this order correct?
> Thanks
> H

Log Shipping and loading service packs

Hi All
Does anyone know if there will be any issues with
upgrading a SQL service pack on a SQL server that is using
log shipping? I want to upgrade the standby server last.
Is this order correct?
Thanks
HHi,
No problem, You can update the service pack in production server first and
later the stand by servers.
Thanks
Hari
MCDBA
"H" <anonymous@.discussions.microsoft.com> wrote in message
news:743301c430ab$29489520$a501280a@.phx.gbl...
> Hi All
> Does anyone know if there will be any issues with
> upgrading a SQL service pack on a SQL server that is using
> log shipping? I want to upgrade the standby server last.
> Is this order correct?
> Thanks
> H

Log Shipping and loading service packs

Hi All
Does anyone know if there will be any issues with
upgrading a SQL service pack on a SQL server that is using
log shipping? I want to upgrade the standby server last.
Is this order correct?
Thanks
HHi,
No problem, You can update the service pack in production server first and
later the stand by servers.
Thanks
Hari
MCDBA
"H" <anonymous@.discussions.microsoft.com> wrote in message
news:743301c430ab$29489520$a501280a@.phx.gbl...
> Hi All
> Does anyone know if there will be any issues with
> upgrading a SQL service pack on a SQL server that is using
> log shipping? I want to upgrade the standby server last.
> Is this order correct?
> Thanks
> H