Pros and Cons

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Hardmandez - 24 Oct 2006 13:21 GMT

Hi,

I'm writing a proposal for a DR solution for a SQL based application. I've
been looking at clustering, database mirroring, etc. Has anyone come across
any documentation discussing the pro's and cons of the various options
available?

Thanks

Reply to this Message

Hilary Cotter - 24 Oct 2006 22:31 GMT

Clustering has a distance limitation. Database mirroring is not scalable
beyond 10 or so databases but has no clustering limitation. Clustering
requires expensive hardware. Database Mirroring does not. Database
Mirroring is available on developer and Enterprise and above versions of SQL
Server 2005. Clustering is available on EE versions of SQL 7 and above, and
also on SQL Server Standard in SQL 2005.

Database Mirroring does add some latency to each transaction and as it is
hostbased it works best for low loads on SQL Server. If you have high cpu
utilization your database mirroring solution is likely to fail.

You neglect to mention log shipping and replication in your list of DR
solutions. Both of these word well as well. Log shipping does increase your
exposure to data loss, and is not really scalable beyond a certain size and
number of databases. Replication replicates on an object level and has no
limitation. Failback can be complex.

Signature

Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.

This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.

Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

> Hi,
>
[quoted text clipped - 6 lines]
>
> Thanks

Reply to this Message

Hardmandez - 25 Oct 2006 10:17 GMT

Thanks for that Hilary, some good points there. Still to get to log shipping
and replication in my proposal. SQL isn't really my area of expertease so
one more question, does SQL come with replication capabilities out of the box
or when you talking about replication are you refering to products like
Neverfail?

> Clustering has a distance limitation. Database mirroring is not scalable
> beyond 10 or so databases but has no clustering limitation. Clustering
[quoted text clipped - 23 lines]
> >
> > Thanks

Reply to this Message

Kevin3NF - 25 Oct 2006 13:35 GMT

SQL has native replication built in to all versions of the product (with
varying levels of functionality).

Please be aware that depending on how replication is set up, it may or may
not make changes to your schema and databases (rowguid columns,
distribution database, etc.)

Exceptions exist, but replication is generally more for distributed
data-entry/reporting server type situations than for DR.

Signature

Kevin Hill
3NF Consulting
www.3nf-inc.com

> Thanks for that Hilary, some good points there. Still to get to log
> shipping
[quoted text clipped - 35 lines]
>> >
>> > Thanks

Reply to this Message

Hilary Cotter - 26 Oct 2006 01:43 GMT

Neverfail IIRC uses Windows DFS to replicate any byte activity which occurs
on the array, disk, folder, files you are replicating. It does work with SQL
Server but it is termed a host based replication technology which means it
consumes resources on your host when it is replicating - so expect some
(albeit a little) performance impact. The db files will be replicated to the
standby/mirror server until you failover. Failover is not instantaneous nor
is failback. The database files on the standby server are offline until you
failover.

I am referring to sql server replication where you specify which database
objects you wish to replicate to the destination server. The destination
database will be accessible after the initial snapshot is sent there and it
will be kept synchronized with the publisher or source server with a
relatively low latency - seconds typically. You can select which objects you
wish to replicate as well as a subset of the tables - ie only some columns
or only some rows. You also have the ability to massage the data in flight
so it looks completely different when it hits the subscriber.

Neverfail and most other products of its ilk require their support folks to
configure your topology and to monitor it. Surprisingly their solutions are
quite cheap. I believe double take is well under 5k us.

Signature

Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

> Thanks for that Hilary, some good points there. Still to get to log
> shipping
[quoted text clipped - 35 lines]
>> >
>> > Thanks

Reply to this Message

daveberm - 26 Oct 2006 15:10 GMT

I do not believe Neverfail uses Windows DFS for replication. They have
developed their own replication engine, just like the competitors in
this host based replication space, DoubleTake, WanSync and Steeleye.

David A. Bermingham, MCSE, MCSA:Messaging
Senior Systems Engineer
www.steeleye.com

> Neverfail IIRC uses Windows DFS to replicate any byte activity which occurs
> on the array, disk, folder, files you are replicating. It does work with SQL
[quoted text clipped - 79 lines]
> >> >
> >> > Thanks

Reply to this Message

Hilary Cotter - 27 Oct 2006 02:20 GMT

Hi David.

Last time I spoke with Neverfail they told me they licensed their technology
from Doubletake. You are correct about DFS and Doubletake. My mistake, DFS
does provide very similar services to Doubletake, and in some cases DFS is a
better choice than Doubletake (for example in file replication).

Signature

>I do not believe Neverfail uses Windows DFS for replication. They have
> developed their own replication engine, just like the competitors in
[quoted text clipped - 113 lines]
>> >> >
>> >> > Thanks

Reply to this Message

daveberm - 30 Oct 2006 16:28 GMT

I also have heard that rumor that Neverfail licenses their replication
from DoubleTake, however, this post from the VP of Product Management
from Neverfail denies that is the case. How long ago did Neverfail
tell you they licensed from DoubleTake? I suppose it may have been
true at one point, but apparently no longer.

http://groups.google.com/group/microsoft.public.sqlserver.server/browse_thread/t
hread/375548336a1c2343/f7328f5f4ef3dfc1?lnk=st&q=Neverfail+DoubleTake&rnum=5&hl=
en#f7328f5f4ef3dfc1

If you ever compare the two products in terms of performance of the
replication, I think you will find that Neverfail's replication is much
more efficient. It is surprising that DoubleTake, being one of the
oldest data replication products around, really is behind everyone else
in terms of performance of their replication IMHO.

David A. Bermingham, MCSE, MCSA:Messaging
Senior Systems Engineer
www.steeleye.com

> Hi David.
>
[quoted text clipped - 134 lines]
> >> >> >
> >> >> > Thanks

Reply to this Message

Anthony Thomas - 30 Oct 2006 03:57 GMT

Also check out Stretch Clustering, which can be configured for SQL Server
failover clusters. This can extend the distance limitation mentioned
previously.

http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/hasog05.mspx

Anthony Thomas

> Thanks for that Hilary, some good points there. Still to get to log shipping
> and replication in my proposal. SQL isn't really my area of expertease so
[quoted text clipped - 29 lines]
> > >
> > > Thanks

Reply to this Message