go for merge and when for transactional?
Ravi,
have a look on BOL for loads of details on this, but I'll have a go at a
very basic synopsis:
Merge is ideal when you have bi-directional flow of data. Subscribers can be
offline and still make changes, and there is a rich array of conflict
resolvers for those cases where both the publisher and subscriber change the
same data. Also, it scales well to those situations where there are lots of
subscribers, and lots of subscriber changes.
Transactional in its basic form is unidirectional - changes made on the
publisher go to the subscriber but the subscriber must be treated as RO from
any other point of view. There are variations in which there is
bidirectional data flow - immediate updating and queued updating. Queued
updating really is quite similar to merge in many ways, but is designed for
those cases where there are few changes on the subscriber and the conflict
resolution options are limited.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com/default.asp
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I'd like to quibble with Paul. Too many people when they hear bi-directional
they think merge. This is incorrect. When you hear occasionally off line, or
frequently offline - that's when you should think merge.
For server to server replication, uni-directional or bi-directional you
should think transactional. For server to client, think merge. By server I
mean most transactions originate on the server, by client I mean most
transactions originate on the server
Paul is correct, merge does ship with functionality rich conflict resolution
functions, whereas transactional replication "breaks" or the agents fail
when they encounter conflicts. This is because transactional replication
replicates transactions transactionally. It guarantees consistency between
databases if you design for it and don't use the continue on data
consistency errors. Transactions are durable. Once they hit the publisher
and subscriber they remain there.
Merge replication is loosely consistent. Your publisher and subscriber will
reach a consistent state, but transactions are not durable; they could be
rolled back.
Merge also replicates only changes. So if you update a row 1000 times only
the last change which occurs before the merge agent synchronizes the
publisher and subscriber is merged. With transactional replication all 1000
updates will go across the wire.
I hope this doesn't confuse you more.
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
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uIGSnBORFHA.248@.TK2MSFTNGP15.phx.gbl...
> Ravi,
> have a look on BOL for loads of details on this, but I'll have a go at a
> very basic synopsis:
> Merge is ideal when you have bi-directional flow of data. Subscribers can
> be offline and still make changes, and there is a rich array of conflict
> resolvers for those cases where both the publisher and subscriber change
> the same data. Also, it scales well to those situations where there are
> lots of subscribers, and lots of subscriber changes.
> Transactional in its basic form is unidirectional - changes made on the
> publisher go to the subscriber but the subscriber must be treated as RO
> from any other point of view. There are variations in which there is
> bidirectional data flow - immediate updating and queued updating. Queued
> updating really is quite similar to merge in many ways, but is designed
> for those cases where there are few changes on the subscriber and the
> conflict resolution options are limited.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com/default.asp
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||My synopsis was labelled as 'very basic', but perhaps 'massively basic'
would have been more accurate
. On the plus side I'd say thatbi-directional transactional replication has an importand niche and in many
cases is significantly faster than merge replication, however it generally
involves more setting up, doesn't come out of the box and there is more work
to create the conflict resolution routines where these are required. Anyway,
I agree it's definitely an important tool in the box.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com/default.asp
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||very true - as I said it was a quibble. A massive one at that
bi-directional replication is not available through the wizards or GUI. Not
even in SQL 2005.
There are no conflict resolution mechanisms that ship with bi-directional
transactional replication either. It just goes boom. Massively. However, you
can always encapsulate the conflict logic into the replication procs, which
I venture to guess is what you are referring to.
Another point in bi-directional transactional replication's favor is that
its easier to debug when it fails.
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
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:O28VxkQRFHA.996@.TK2MSFTNGP09.phx.gbl...
> My synopsis was labelled as 'very basic', but perhaps 'massively basic'
> would have been more accurate
. On the plus side I'd say that> bi-directional transactional replication has an importand niche and in
> many cases is significantly faster than merge replication, however it
> generally involves more setting up, doesn't come out of the box and there
> is more work to create the conflict resolution routines where these are
> required. Anyway, I agree it's definitely an important tool in the box.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com/default.asp
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
>
No comments:
Post a Comment