Re: Merge versus Two Way, Bidirectional Transactional Replication

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: Best replication architecture?
    ... Looking for a SQL Server replication book? ... So if it is subscribing to Publisher 1, ...
    (microsoft.public.sqlserver.replication)
  • Re: Best replication architecture?
    ... Looking for a SQL Server replication book? ... So if it is subscribing to Publisher 1, ...
    (microsoft.public.sqlserver.replication)
  • Re: Best replication architecture?
    ... deprecated for SQL Server 2005 without any alternative having been provided ... So if it is subscribing to Publisher 1, ... Looking for a SQL Server replication book? ...
    (microsoft.public.sqlserver.replication)
  • Re: Best replication architecture?
    ... Looking for a SQL Server replication book? ... So if it is subscribing to Publisher 1, ...
    (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)