bi-directional vs updatable subscriptions

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@.discussions.microsoft.com> wrote in message
news:9C060D84-80E7-4754-B1B0-6280E64ADDFE@.microsoft.com...
> What is the difference between Bi-directional and Updatable Subscriptions
> Transactional Replication? Conceptually, they seem to be the same... or
> are
> they?
No, most of it is experience
The caveats about where the majority of the DML occurs is documented. Have a
look at these links for more info.
http://msdn2.microsoft.com/en-us/library/aa237486(SQL.80).aspx
http://msdn2.microsoft.com/en-US/library/aa237271(sql.80).aspx
http://msdn2.microsoft.com/en-US/library/aa237346(SQL.80).aspx
http://msdn2.microsoft.com/en-US/library/aa256086(SQL.80).aspx
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@.discussions.microsoft.com> wrote in message
news:8E39CB8A-06F6-4318-A3A2-00948098CC07@.microsoft.com...[vbcol=seagreen]
> Wow! Thank you for taking the time to explain this so well.
> Just wondering on the some of the limitation that you had mentioned. For
> instance, "...not scalable beyond 10 subscribers"; "...locks you into a
> schema, but there are no schema modifications". Is that documented
> anywhere
> in BOL and/or on Microsoft's website where more info on those limitations
> are
> discussed in depth?
> Thanks again.
> "Hilary Cotter" wrote: