Re: bi-directional vs updatable subscriptions

Tech-Archive recommends: Fix windows errors by optimizing your registry

Bi-directional replication strictly speaking is where you have data
modification on any node moving to all nodes in a system, it includes merge
replication, bi-directional transactional replication, snapshot replication
with immediate updating, snapshot replication with queued updating,
transactional replication with
immediate updating, transactional replication with queued updating, and peer
to peer replication.

I am ignoring immediate updating with queued failover here.

Now, let me climb down from my soap box,

bi-directional transactional replication doesn't scale well to multiple
nodes, it locks you into a schema, but there are no schema modifications.
Immediate uses MSDTS (distributed transactions) which is not resilient to
network hicupps, and adds latency to all DML originating on the subscriber.
It adds a guid column to each table you are replicating, it is not really
scalable beyond 10 subscribers, and it will log conflicts but not allow you
to roll them back. It is best used when the majority of the DML originates
on the published. Queued updating uses a queue to replicate DML, it is not
scalable beyond 10 subscribers, adds a guid column to every table you are
replicating, adds latency to all DML originating on the subscriber, and is
best used when the majority of the DML originates on the publisher.
Conflicts are logged but cannot be rolled back.

Merge replication is designed for large numbers of subscribes, works well
when the majority of the DML originates on the publisher or subscriber, has
rich conflict logging and resolution, and adds a guid column to every table
you are replicating.

I use bi-directional replication in DR environments where it can work well,
keeping in mind there is no automatic failover and you are locked into a
schema.

HTH
--
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



"Rob" <Rob@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:9C060D84-80E7-4754-B1B0-6280E64ADDFE@xxxxxxxxxxxxxxxx
What is the difference between Bi-directional and Updatable Subscriptions
Transactional Replication? Conceptually, they seem to be the same... or
are
they?


.

Relevant Pages

  • Re: Synchronising In-House and Web Data
    ... Since transactional replication is transaction based, ... Director of Text Mining and Database Strategy ... The CSV files we send to the web-server are in the ...
    (microsoft.public.sqlserver.replication)
  • Re: Replication Architecture/Design advise
    ... bi-directional transactional replication to be ... publisher goes offline for an extended time period. ... > Does this mean that the standby server can update the primary? ...
    (microsoft.public.sqlserver.replication)
  • Re: Help in setting up SQL Server 2005 Replication
    ... The reason i wanted to chose transactional replication with updatable ... active database as it is a push/pull situation. ... bi-directional transactional replication may be a better fit. ... Looking for a SQL Server replication book? ...
    (microsoft.public.sqlserver.replication)
  • Re: Which Replication Option???
    ... So, since both the publisher and subscriber will updated via users, use ... Transactional Replication with immediate updating? ...
    (microsoft.public.sqlserver.replication)
  • Re: a different identiy columns in replication question
    ... with bi-directional transactional replication you have to drop both ... > "Hilary Cotter" wrote in message ... >> queued and bi-directional transactional replication are options. ...
    (microsoft.public.sqlserver.replication)