Re: Merge and transactional replication

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@xxxxxxxxxxxxx> wrote in message
news:uIGSnBORFHA.248@xxxxxxxxxxxxxxxxxxxxxxx
> 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)
>
>


.

Relevant Pages

  • Re: Merge versus Two Way, Bidirectional Transactional Replication
    ... ranges are blown and transactions are kicked back and lost. ... Looking for a SQL Server replication book? ... You can restore a publisher or subscriber without ...
    (microsoft.public.sqlserver.replication)
  • Re: Adding a nosync article to an existing transactional publication
    ... Queued updating is not scalable where a large ... portion of the DML originates at the Subscriber. ... Looking for a SQL Server replication book? ... online, accepting transactions. ...
    (microsoft.public.sqlserver.replication)
  • Re: SQLEXPRESS Replication?
    ... The way the Technet SQL BOL is arranged it is difficult to know just how to setup the required permissions to do replication which is the major stumbling block. ... You can replication from SQL Server 2005 Workgroup edition and above ... to a SQL Server Express subscriber using transactional replication. ...
    (microsoft.public.sqlserver.replication)
  • Re: replicated commands not executed on the subscriber side?
    ... My current environment is the most complex sql server environments in my 10+ ... Unlike the replication procedure when changes made at Subscriber, ...
    (microsoft.public.sqlserver.replication)
  • Re: transactional replication problem
    ... If the distribution agen can't connect to the subscriber, ... Looking for a SQL Server replication book? ... > server B. Problem is with only one database. ...
    (microsoft.public.sqlserver.replication)