Mirroring Vs Replication
jasondev
I am comparing and contrasting replication vs mirroring in SQL Server
2005. I'm mainly looking for information on latency and performance
differences, but any other
info you have would be good too. I'd greatly appreciate it!
Cheers,
Jason
Hilary Cotter
three modes and two of the modes require a witness server. The entire
database is mirrored.
Replication will copy transactions from one database to another, or if you
are using merge replication, its after images of the data. Replication can
be directional and you can filter the data horizontally and vertically. You
can also replicate to a diverse number of data sources. You can also massage
the data as it is being replication to look completely different from the
original transaction which occured on the publisher. With replication you
can access the subscriber database and do work. You can't with database
mirroring.
So think of mirroring as done on the database level, and replication done on
the transaction level (in other words you can select what to replicate).
--
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
"jasondev" <jason...@gmail.com> wrote in message
news:1133213366....@f14g2000cwb.googlegroups.com...
jasondev
We're trying to decide on the best method of replicating a
transactional db for a reporting application using SQL Server 2005.
Microsoft's website suggests that transactional replication is best for
reporting/data warehousing scenarios.
Do you have any insite on that?
In terms of performance on the primary db/server, what's the difference
between replication and mirroring? Its important that the data
replication (be it mirroring or replication) not tax the primary db too
much.
In terms of latency, i.e., the time it takes for the data to go from
the primary server to being available on the secondary server, what's
the difference between replication and mirroring?
Thanks again!
Jason
Hilary Cotter
Warehousing or to move data to a different server for reporting services is
generally a good one. Another reason is for application requirements; scale
out functionality.
I heard a MS guy say that data mirroring in high protection mode adds a
performance hit but I can't recall the exact amount - IIRC it was pretty
small I think under 10% increase in latency. Sp transactions which took 10
ms to commit, would take 11ms.
Replication does cause a performance hit. In one test I did with 2500 tps
there was a 10-15% performance hit on a dual proc machine.
With Mirroring, which I must note is not supported yet in SQL 2005, latency
has no real meaning as its a form of continuous log shipping. So
transactions in high protection mode take a little longer to commit. The log
is continuously applied to the destination server. When the failover occurs
the remaining transactions are shipped and applied. Failover is very fast.
Ron Talmage has written an excellent white paper on this -
http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx
So to give you an idea with database mirroring in high protection mode the
destination server is almost always in sync with the source, so latency is
small; but its not available until failed over, and the failover time is
very small; several seconds or less IIRC.
With replications it is at best between 10-20 s under load.
--
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
"jasondev" <jason...@gmail.com> wrote in message
news:1133295239.6...@z14g2000cwz.googlegroups.com...
Scott Hunley
Have you ever had to configure/implement Transactional Replication between
SQL Server 2000 and SQL Server 2005 databases over the internet? I am trying
to do just that and not finding a lot of articles that deal with the topic.
Thanks,
--
Scott E. Hunley
Measure Twice, Cut once.
k_s
U have provided very good information of mirroring.
regards,