Re: Merge vs. Transactional

From: Hilary Cotter (hilary.cotter_at_gmail.com)
Date: 12/03/04 

Date: Fri, 3 Dec 2004 12:33:46 -0500

The decision on when to use transactional replication over merge replication
is not always cut and dried.

Transactional replication was designed for server to server data
transfer/replication when the publisher and subscriber tend to be always
connected - although this is not a requirement.

Transactional replication is one way, but you can make it bi-directional.
When you make it bi-directional (using updatable subscribers (immediate
updating or queued updating, or immediate with queued failover)) in general
it is not resilient to conflicts. The agents will either stop, or you will
lose the transaction.

Merge is designed for bi-directional replication from the ground up.
However, with transactional you are pretty well guaranteed to have
consistency between the publisher and subscriber. In other words a
transaction committed on the publisher will make it to the subscriber, or
the agent will report a failure and you will have to resolve it.

Merge on the other hand has a more loose consistent model. A transaction
which occurs on a subscriber can be rolled back by the publisher. These lost
transactions are stored in the conflict tables.

In general transactional replication requires less processing than merge
replication, but there are some exceptions.

HTH 

-- 
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Mario Splivalo" <majk@fly.srk.fer.hr> wrote in message 
news:slrncr15gg.4qa.majk@fly.srk.fer.hr...
>I have merge replication set up for more than 2 years now, I'm more than
> happy with it. I have several dozens of publications, a lot of 
> subscribers,
> totalny 7 databases being replicated (for various branches, all on one
> central publisher).
>
> The bussines i'm serving is trade-import-export kind of thing. Remote
> store-shops where people buy goods, documents traveling back and forth.
>
> So, maybe because of it, I'm kind of lost when it comes to transactional
> replication. When would you need to use something like that?
>
> I have several snapshot publications wich delivers some key tables (as, 
> for
> instance, articles, sock names, account numbers and stuff), all others are
> merge.
>
> I don't think I would benefit from transactional replication, esp because
> most of the subscribers are on analog modem lines or ISDN with dial on
> demand. Between two 'head' offices there is high-bandwith leased line so I
> set up immediate merge replication (i have a lot of inserts/updates on 
> both
> publisher and subscriber).
>
> But, what would a scenario be when one would use transactional 
> replication?
>
> Mike
>
> -- 
> "I can do it quick. I can do it cheap. I can do it well. Pick any two."
>
> Mario Splivalo
> msplival@jagor.srce.hr

Relevant Pages

  • Re: Transactional Replication 2005
    ... replication from production database as the publisher and subscribe it to ... Create the distributor on the publisher server ... I then create the subscriber, ... the identity NFR property setting has to match between publisher ...
    (microsoft.public.sqlserver.replication)
  • Re: Sp_adjustpublisheridentityrange and resetting identity range
    ... > identity ranges you have set on your publisher. ... > ranges which will work for the lifetime of their replication solution. ... > should apply this value on the subscriber. ... >> restored the published database from a backup. ...
    (microsoft.public.sqlserver.replication)
  • Re: replicated commands not executed on the subscriber side?
    ... Unlike the replication procedure when changes made at Subscriber, ... Distribution Agent. ... In our case, when running following the distributed transaction, the ...
    (microsoft.public.sqlserver.replication)
  • Re: Merge Repl Failure - Rollback or not
    ... Looking for a SQL Server replication book? ... > Publisher box now shipped to new location. ... > Generate a single insert at subscriber and replicate. ... > Disconnecting from Subscriber 'GENDEV003' ...
    (microsoft.public.sqlserver.replication)
  • Re: Disaster Recovery for Bi-Directional Transactional Replication
    ... With un-directional replication you can have multiple publishers publishing ... If the main publisher went off line a standby publisher could go online and ... subscriber, and then you would be faced with the same question as to what ... Looking for a SQL Server replication book? ...
    (microsoft.public.sqlserver.replication)
Loading