Re: Replication bandwidth comparison

It really depends on your DML. As far as I know there is no typical
workloads. Some workloads are insert heavy. Some are update heavy, some are
a mix of insert and delete. Some are a mix of all three.

Have a look at the workload MS used here for benchmarking.

http://www.microsoft.com/technet/prodtechnol/sql/2005/mergrepl.mspx

Looks pretty update heavy to me! Are you perhaps insinuating that MS is
cooking the books?

The merge agent by default runs hourly. The distribution agent by default
runs continuously. By default the merge agent only sends a delta per row
during a sync no matter how many have updates occurred since the last sync.
With transactional there is no such consolidation.

Merge is also tuned for low bandwidth lines.

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

"Michael Hotek" <mike@xxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:Oqpum4HGGHA.1388@xxxxxxxxxxxxxxxxxxxxxxx
> That is only if the updates happen between cycles of the replication
> engine. Which is an extremely explicit case and also incredibly hard to
> actually do in a production environment. Merge will only send what it
> needs to send. It does this by picking up the ID for a row that changed
> and then pull the data directly from the row. If you happen to be lucky
> enough to update a row multiple times, quickly enough, merge will only
> pick up the most recent change and send it. But, that is a very unlikely
> scenario.
>
> --
> Mike
> http://www.solidqualitylearning.com
> Disclaimer: This communication is an original work and represents my sole
> views on the subject. It does not represent the views of any other person
> or entity either by inference or direct reference.
>
> "Hilary Cotter" <hilary.cotter@xxxxxxxxx> wrote in message
> news:ujkqL0uFGHA.752@xxxxxxxxxxxxxxxxxxxxxxx
>> This is not quite accurate. With transactional replication all the logged
>> changes go across the wire, and are decomposed into individual
>> singletons. So an update transaction affecting 100 rows would be
>> decomposed into 100 separate singleton updates.
>>
>> The same behavior in merge, but with merge replication its the net change
>> which goes across the wire. Suppose the same row is updated 100 times.
>> With transactional replication 100 changes flow. With merge only the net
>> change, i.e. the final image of the row will flow if the sync occurs
>> after 100 changes. This is why by default merge replication agents run
>> each hour.
>>
>> --
>> 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
>>
>> "Z" <z@xxxxx> wrote in message
>> news:u3BpwtuFGHA.2212@xxxxxxxxxxxxxxxxxxxxxxx
>>> Both. It has no bearing on inserts. You get essentially the same
>>> amount of data. It also has no bearing on deletes, because the
>>> transaction is simply pointed at an ID and is also basically the same
>>> size. If you are using purely default settings, then by default merge
>>> transfers less data. You can change the default settings for
>>> transactional and it will then only transfer just those columns which
>>> have changed.
>>>
>>> Both replication methods will transfer data across a 64K link without
>>> any prolems. The issue is with how much data, # of transactions, you
>>> plan on sending between the two.
>>>
>>> "Tom" <mcseman@xxxxxxxxxxx> wrote in message
>>> news:OzH2p9iFGHA.2444@xxxxxxxxxxxxxxxxxxxxxxx
>>>> I'm trying to find out which type of replication takes the least
>>>> network bandwidth to transfer the same amount of data between two
>>>> servers.
>>>> I need to transfer new data from one to another SQL2005 server. I could
>>>> use merge or transactional replication
>>>> or even create my own scripts that would select the right data from one
>>>> server and import it on another but my real concern is what method
>>>> performs best on slow connections (64Kb/s range)?
>>>> I'm planning to test all methods but would still like to get some info
>>>> from people with more experience in that area..
>>>> Thanks.
>>>>
>>>> Tom
>>>>
>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>


.

Relevant Pages

  • Re: MSRepl_Commands question
    ... make all database changes to the ... publisher and the subscriber and then reinitialize replication. ... We considered checking the status of the distribution agent but realized ... by checking their transaction number against this value. ...
    (microsoft.public.sqlserver.replication)
  • Re: Merge & Transaction Replication Question
    ... Director of Text Mining and Database Strategy ... Looking for a SQL Server replication book? ... different 'reporting' SQL 2000 server and use Transaction Replication ...
    (microsoft.public.sqlserver.replication)
  • Re: Distributed transactions, replication, views
    ... If you have significant processors on your server you can partition the ... The way replication works is that if a transaction is committed on the ... Looking for a SQL Server replication book? ... I have been tasked with restructuring a database. ...
    (microsoft.public.sqlserver.replication)
  • Re: Merge vs. Transactional
    ... The decision on when to use transactional replication over merge replication ... lose the transaction. ... consistency between the publisher and subscriber. ...
    (microsoft.public.sqlserver.replication)
Loading