Re: Merge versus Two Way, Bidirectional Transactional Replication

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Thanks Hilary. I don't need to hang out here for a while to know what you
are talking about. I've been working with merge replication for longer than
you've been working with any version of replication period. If it has
happened, I can pretty much guarantee that I've seen it happen in a
production environment. I've architected and managed the largest merge
architectures that exist in the world. Hundreds of subscribers? Try
designing and managing a merge architecture that has more than 12,000
subscribers with more than 30% of them also acting as republishers and using
literally every communications protocol imaginable from LAN, WAN, wireless,
sattelite, bluetooth, etc. I was the first one to use merge with a TB level
database way back in the early stages of SQL Server 7.0. I have over 500
individual merge architectures that I've designed, deployed, and managed
spanning across more than 400 different companies since merge was introduced
in SQL Server 7.0. Don't patronize me, I don't have any patience for it.
There is a reason that people continue to come to me when it deals with
replication. I came back out to this newsgroup, because a couple of friends
of mine requested that I come back out here and clean up the large volume of
mis-information that is being posted into this newsgroup with respect to
replication.

If you are going to throw out a broad statement like "it's very difficult to
recover from", then you sure as hell better back it up. Because, that
statement is absolutely, 100% false. Does nonconvergence happen? Yep. But
it is an extraordinarily rare event that is actually fixed relatively
easily. (For someone who does know what nonconvergence is, it is simply
when the system gets out of synch and the merge engine doesn't transfer a
change because it think it has already been sent.) The validation "problem"
you are referring to has been VERY well documented and KB articles already
exist for it. But, the system didn't have any problems at all to fix,
because the merge engine had in fact sent all of the data. I have never
come across a case where the merge engine destroyed data and blew it out of
the system. If you have something like that, you have some other piece of
code causing this to happen. If you are having performance problems with
merge, it tells me that you didn't do your homework before implementing it
and didn't understand what the tradeoff was for implementing certain
features. That most certainly isn't a replication issue. It is working
exactly as designed and is also working at the performance levels as
designed. If you didn't understand what a feature was doing and why it
required certain trade-offs, then don't point the finger at the feature.

How about going back to square one and understand that there is ALWAYS
someone who knows more about a topic than you do and get off your high and
mighty "expert" soapbox. I'm definitely not going to accept watching you
post invalid crap out here and I really don't give a damn if you wrote a
book or are a SQL Server MVP. Both of which are meaningless to me.

--
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com

"Hilary Cotter" <hilary.cotter@xxxxxxxxx> wrote in message
news:%23PLUdU0EGHA.3036@xxxxxxxxxxxxxxxxxxxxxxx
> What I am referring to is data going missing and not logged in the
> conflict tables; things like sever performance problems especially with
> dynamic join filters which can only be solved by architectural changes;
> replication non-convergence; validation problems like the checksum
> question posed a couple of days ago.
>
> Exactly what do you do when you can't get merge agents to sync?
> Reinitialization isn't always an option when you have hundred's of
> subscribers subscribing over phone lines. Let me tell you something,
> restoring backups won't help you no matter how many times you repeat it.
>
> Another problem difficult to recover from is when identity range
> management ranges are blown and transactions are kicked back and lost.
>
> Hang out here for a while and you will discover what I am talking about.
>
> --
> 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:eC5WazzEGHA.1180@xxxxxxxxxxxxxxxxxxxxxxx
>> "Merge replication is difficult to recover from and troubleshoot."
>>
>> That's interesting. That has to be the first time I've ever heard anyone
>> say that merge is difficult to recover from. Merge is in fact the most
>> resilient replication method that you could ever possibly choose.
>> Recovering it is about as point and click as it can possibly get. There
>> are no special switches required when doing backups like all forms of
>> transactional require. You can restore a publisher or subscriber without
>> having to touch anything else and merge will automatically, incrementally
>> resynch to point in time. In fact, merge can actually enable recovery
>> options and do things that absolutely no other technology that I have
>> ever come across can do.
>>
>> For example: Setup merge between 2 databases. Once the snapshot
>> completes, backup both databases. (NOTE: there are ZERO special flags
>> that are needed, you just back it up like you normally do.) Now issue
>> several transactions against the publisher and wait for them to be sent
>> to the subscriber. Stop the merge agent. Now restore the publisher from
>> the backup. Once restored, you will very quickly realize that the
>> subscriber is further forward in time than the publisher and contains
>> transactions which were sent from the publisher to the subscriber. How
>> do you fix it and get everything resynchronized without losing a single,
>> solitary transaction even though you might not know what those
>> transaction actually are?
>> 1. Start the merge agent
>> 2. Oh, sorry, there isn't a second step
>>
>> If you had the situation above with any form of transactional
>> replication, you are completely out of luck. The only way to fix it with
>> transactional, is to find every difference between the two databases,
>> extract the data manually, and stuff it back into the publisher manually.
>> Then once you are done, you need to reinitialize the subscriber which in
>> effect completely wipes out anything there and replaces it with what is
>> on the publisher. (Better hope you got everything, because if you didn't
>> it is gone forever.)
>>
>> It is this capability of merge that makes it very easy to build merge
>> architectures with dozens, hundreds, even thousands of servers without
>> having to do a LOT of specialized work and basically spend your entire
>> life baby sitting something.
>>
>> As far as troubleshooting goes, I wouldn't classify any replication
>> method in SQL Server 2000 or before as "easy" to troubleshoot. The error
>> messages are esoteric, convoluted, and rarely tell you what is going on.
>> If you've spent LOTS of time digging through code, cross analyzing stuff,
>> and asking lots of questions, it might get a bit easier. But, only
>> someone who's sole job is to play with SQL Server and nothing else has
>> that kind of time. Sure, there are dozens of error messages that
>> replication spits out that I know exactly what the issue that is causing
>> it is. But, there isn't a single error message that I have ever seen
>> come out of the replication engine since back when it was called Sybase
>> Rep Server, that made sense to anyone but the person who wrote the code.
>>
>> --
>> Mike
>> Mentor
>> Solid Quality Learning
>> http://www.solidqualitylearning.com
>>
>>
>> "Hilary Cotter" <hilary.cotter@xxxxxxxxx> wrote in message
>> news:OkjeLOxEGHA.516@xxxxxxxxxxxxxxxxxxxxxxx
>>> 1) Merge will work. The problems are that the merge triggers will
>>> increase the latency of every transaction, that you will be adding a
>>> GUID column to each table, and that latencies will be larger than what
>>> you will have with transactional. Bi-directional transactional is a good
>>> fit if all of your tables have pk's and your schema is static. It
>>> doesn't tolerate frequent schema modifications. For this type of a dr
>>> requirement log shipping is most often used however. This will require
>>> some manual intervention however. Merge replication is difficult to
>>> recover from and troubleshoot.
>>> 2) These procs are autogenerated by sp_scriptpublicationcustomprocs. I
>>> have modified them for some problems they have with identity keys. What
>>> you really have to make sure is that you have different seeds on each
>>> side and an increment of 2. Merge and queued updating does have
>>> automatic identity range management which does seamlessly handle the
>>> identity problems - but they can be problematic.
>>>
>>> For more information on how to handle the replication identity range
>>> issues consult
>>>
>>> http://www.simple-talk.com/2005/07/05/replication/
>>>
>>> --
>>> 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
>>>
>>> "CLM" <CLM@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>>> news:AE3ADFE2-91FF-4570-B5D3-3C6D477FC1EF@xxxxxxxxxxxxxxxx
>>>>I just received some great help on this forum on two way, bidirectional
>>>> transactional replication. But I'm not sure it's really the best
>>>> solution
>>>> after finding out more about it. I'm working with 2000 servers btw.
>>>> I see two issues that (I think) will make merge replication (or maybe
>>>> more
>>>> suitable for what I'm looking for:
>>>> 1. My situation is that I have 25 tables on ServerA that will be
>>>> modified
>>>> by web processing on a 24/7 basis. The concept is that these 25 tables
>>>> will
>>>> be replicated over to ServerB and if ServerA goes down, then we'd like
>>>> to be
>>>> able to point the web to ServerB and then be able to re-sync ServerA to
>>>> ServerB once ServerA is back online and then repoint the web to
>>>> ServerA. Is
>>>> merge best for this? (Note: the web will only hit one server at a
>>>> time.)
>>>> 2. 17 of these tables have identities. If I understand the two way,
>>>> bidirectional transactional replication, that means I need to create 51
>>>> stored procs to handle insert, update and delete. That sounds possible
>>>> but
>>>> painful to create and maintain. Doesn't merge handle identities much
>>>> more
>>>> easily?
>>>
>>>
>>
>>
>
>


.

Relevant Pages

  • Re: Adding a nosync article to an existing transactional publication
    ... Queued updating is not scalable where a large ... portion of the DML originates at the Subscriber. ... Looking for a SQL Server replication book? ... online, accepting transactions. ...
    (microsoft.public.sqlserver.replication)
  • Re: Merge and transactional replication
    ... For server to server replication, ... mean most transactions originate on the server, ... Your publisher and subscriber will ...
    (microsoft.public.sqlserver.replication)
  • Re: merge Replication with a Notebook not in domain
    ... not associated with a trusted SQL Server connection. ... I have set up all SQL Server login in both publisher and subscriber.Also ... Looking for a SQL Server replication book? ... subscriber ...
    (microsoft.public.sqlserver.replication)
  • Re: Server to server Replication
    ... Replicated data is mostly read-only at the Subscriber. ... You need updates to be propagated on a transaction basis, ... Use snapshot replication or transactional replication with immediate ... Paul Ibison SQL Server MVP, ...
    (microsoft.public.sqlserver.replication)
  • Re: Problem with merge replication
    ... subscriber changes before the new snapshot comes down. ... Looking for a SQL Server replication book? ... I use merge replication with windows CE clients and SQL Server 2005. ... If someone collects data in an existing table on the client (the ...
    (microsoft.public.sqlserver.replication)