Re: Can merge replication be used to keep two database in sync

The presentation is #336 - SQL Server 2005 Replication: Lesson's learned
from Early Adopters, in a slide entitled Peer to Peer Topology, in response
to an inaudible question, Phil has this to say "Realistically speaking when
once you get to about 10-12, you start sending around so many changes you
get to a point of diminishing returns, but about 10-12 nodes is where it
peaks out, cause all changes flow everywhere."

The transcription is mine. You can order this cd from the pass website. I
suggest you follow up with Phil if you have more questions about his remarks
or figures. If you are able to make this scale out to 30 servers I am sure
Microsoft would be very interested in speaking with you.

Phil also says (another quote from the same slide) in reference to
bi-directional transactional replication - "it supported one node, and two
nodes, but you couldn't extend it beyond 2."

If you want to contact me I can play these sound clips for you. I can
contact Kevin Kline president of Pass and ask him for permission to publish
the audio's for these slides if you require it, but I urge you to contact
Phil or to follow up with your Microsoft contacts.

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

"Hilary Cotter" <hilary.cotter@xxxxxxxxx> wrote in message
news:uIq8356GGHA.3100@xxxxxxxxxxxxxxxxxxxxxxx
> Most of my figures come from a recent presenation that Phil Vaughn did at
> Pass this year on replication. I'll listen to it again and verify these
> numbers. If I am incorrect I will post back here with the corrections.
> I'll also ping him to verify these quotes. Paul Ibison has a copy of the
> same presentation.
>
> While I have no doubt that you have built such systems let me quote from
> BOL
>
> In a section entitled Queued Updating - Queued updating is most
> appropriate for applications where users mostly read data and only
> occasionally update data.
> In a section entitled Immediate Updating - . Immediate updating benefits
> applications in which snapshot or transactional publications are preferred
> but occasional updates need to be made at the Subscriber.
>
> While BOL has occasionally being inaccurate, it is my belief and
> experience that it is completely correct here.
>
> When I say something is rolled back, I mean it in the same sense a
> transaction is rolled back and the system is left in the state is was in
> before. You can use the conflict viewer to "rollback" replication changes,
> or as they put it "keep the Wining Change", resubmit delete, insert,
> update.
>
> Note that in SQL 2000 you have an option to compensate for errors which
> had a default of false. In SQL 2005 it has a default of true. In other
> words conflicts will be logged but the changes will not win on the
> subscriber with this setting as false.
>
> As I have stated previously in this newsgroup Paul and I have a
> committment to accuracy and helping people with correct information. I
> trust you have the same committment.
>
> --
> 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:OFKLzQ6GGHA.2696@xxxxxxxxxxxxxxxxxxxxxxx
>> That's really baffling. Those are some pretty absolute numbers being
>> thrown around. How does conflict detection have anything at all to do
>> with scalability?
>>
>> If I want to, I can build a bi-directional transactional configuration in
>> a linear chain that has 1000 servers in it and make it work perfectly
>> fine, so that blows your "can't scale" theory out of the water, because
>> it can in fact be done. (Although I don't have an explicit business
>> application for that.)
>>
>> How do you figure that peer-to-peer is not scalable beyond 12 - 15 nodes?
>> Just what exactly was your test platform that gave you those explicit
>> numbers. I'd really like to know, because my testing has taken it out to
>> 30 nodes and everything was still working.
>>
>> I've done updating subscribers in configurations where not only where
>> there dozens of subscribers, but over 90% of the changes occured on the
>> subscribers.
>>
>> There is no such thing as rolling back from a conflict. A rollback
>> occurs within a transaction space and causes changes to be undone before
>> they are committed to the database. A conflict is thrown against
>> committed and completely valid data in the database. The only thing that
>> conflict resolution can do is produce a compensating transaction which is
>> then applied, but it is still a completely separate transaction that
>> modifies data. (It most certainly doesn't rollback.)
>>
>> Merge is scalable to 1000 subscribers? Really. How do you figure? I've
>> had a merge architecture in place and running perfectly fine since SQL
>> Server 7.0 that had over 10,000 subscribers in the architecture. I've
>> also had merge architectures which couldn't scale beyond 5 subscribers.
>>
>> I really like these nice blanket statements being thrown out.
>> Particularly since they have zero basis in reality. The reality of
>> replication is that scalability has a direct correspondence to the volume
>> of data per unit time that is being sent through the engine. It has ZERO
>> correspondence to the number of subscribers or the pattern of
>> modifications. When you say "does not scale beyond x", that means it
>> plain and simply does not work if you try it. That is obviously a
>> completely false statement, because you can ALWAYS get something to scale
>> beyond x.
>>
>> Keep in mind there are a LOT of people out here reading this. When you
>> say that a technology can't scale beyond X, then people are going to
>> start looking for different tecnologies to apply, because according to
>> you, the replication engine can't meet their business requirements. It
>> also makes it really difficult for SQL Server DBAs to architect systems,
>> because their managers point to your posts which say that SQL Server
>> can't do what they are proposing to do and no amount of testing is going
>> to change their minds once they've decided. So, how about giving the
>> people who wrote the code for the replication engine a break and if you
>> are going to post a scalability number, back it up with enough
>> information to explicitly define the entire environment that drew that
>> conclusion.
>>
>> --
>> 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:eqHw513GGHA.1388@xxxxxxxxxxxxxxxxxxxxxxx
>>> Further note that bi directional replication is not really scalable due
>>> to no conflict mechanism; peer-to-peer is not scalable beyond 12-15
>>> nodes. Updateable subscription types are best when the majority of DML
>>> occurs on the publisher. Updateable Subscriptions allow conflicts to be
>>> logged but not rolled back.
>>>
>>> Merge replication is scalable to 1,000 or subscribers, it has a rich
>>> conflict detection and resolution mechanism, and there is no restriction
>>> with where the majority of DML occurs.
>>>
>>> --
>>> 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:u%23sNLR0GGHA.1192@xxxxxxxxxxxxxxxxxxxxxxx
>>>> Mike,
>>>> I would not include transactional with immediate updating in this mix.
>>>> If the primary server is down, the poster wants to be able to use the
>>>> secondary one, and the inability to do a 2PC will prevent any changes
>>>> being made on the subscriber in this case.
>>>> Cheers,
>>>> Paul Ibison SQL Server MVP, www.replicationanswers.com
>>>> (recommended sql server 2000 replication book:
>>>> http://www.nwsu.com/0974973602p.html)
>>>>
>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>


.

Relevant Pages

  • Re: Can merge replication be used to keep two database in sync
    ... You can use the conflict viewer to "rollback" replication changes, ... Looking for a SQL Server replication book? ... > there dozens of subscribers, but over 90% of the changes occured on the ...
    (microsoft.public.sqlserver.replication)
  • Re: Merge or Trans with QUS best when publishing partitions of database to slow subscribers
    ... With queued replication the single queue does serve all subscribers. ... No real conflict detection and resolution. ... The majority of the DML should originate on the publisher. ...
    (microsoft.public.sqlserver.replication)
  • Re: Can merge replication be used to keep two database in sync
    ... the number of subscribers before it started slowing down. ... If you are going to post numbers, particularly with the replication engine, ... in SQL Server 6.5, I've had implementations in production that have ALWAYS ... Broken down by transaction per minute ...
    (microsoft.public.sqlserver.replication)
  • Re: Can merge replication be used to keep two database in sync
    ... I've done updating subscribers in configurations where not only where there ... There is no such thing as rolling back from a conflict. ... scalability has a direct correspondence to the volume of data per unit time ... managers point to your posts which say that SQL Server can't do what they ...
    (microsoft.public.sqlserver.replication)
  • Re: Trasactional Replication problems
    ... >should have 13000 lines after a snapshot. ... Enable Replication Agents for Logging to Output Files in SQL ... >all data will be transfer to the subscribers in this transactions? ... which means that all available transactions are ...
    (microsoft.public.sqlserver.replication)