Merge versus Two Way, Bidirectional Transactional Replication
CLM
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?
Hilary Cotter
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" <C...@discussions.microsoft.com> wrote in message
news:AE3ADFE2-91FF-4570...@microsoft.com...
Hilary Cotter
backwards or forward - in other words it allows you to resolve them in a
manner you see fit. Queued replication will log conflicts but you can't
resolve them. Transactional replication is not resilient to conflicts - the
agents will stop dead until you resolve them manually. It is best to design
solutions in which the chances of conflicts are minimized - especially for
bi-directional transactional 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
"Hilary Cotter" <hilary...@gmail.com> wrote in message
news:OkjeLOx...@TK2MSFTNGP15.phx.gbl...
Michael Hotek
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...@gmail.com> wrote in message
news:OkjeLOx...@TK2MSFTNGP15.phx.gbl...
Michael Hotek
1. If you can have conflicts, you can only use merge or transactional with
queued updating subscribers
2. If you need to process changes against a subscriber while the publisher
is offline, you can NOT use immediate updating subscribers
3. Merge replication takes more overhead and does not perform as well as
transactional, but you get a MUCH more resilient architecture
4. Transactional replication is the highest performance method you can use,
but it is extremely difficult to recover from and takes very specific steps
to get everything working again
5. Bi-directional replication can only be implemented by writing the code
yourself, there is no GUI
6. Just about everything else really comes more down to a matter of
preference
--
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"CLM" <C...@discussions.microsoft.com> wrote in message
news:AE3ADFE2-91FF-4570...@microsoft.com...
Hilary Cotter
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" <mi...@solidqualitylearning.com> wrote in message
news:eC5WazzE...@TK2MSFTNGP09.phx.gbl...
Michael Hotek
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...@gmail.com> wrote in message
news:%23PLUdU0...@tk2msftngp13.phx.gbl...
Hilary Cotter
Regarding the pain points with merge replication and recovering when the
agents consistently fail. I can't find any validation kb articles, please
provide me with links if you have them - I'd really like them for reference.
I'm sure the rest of the community would as well.
I wish I had a dollar for every time merge replication wipes out data.
There are 208 cases of it using these search terms alone.
Replication non-convergence is often seen on SQL CE. There are certain
events which will trigger it on SQL 2000 and 7 as well,
It is also seen if you add a column with a default of a variable value -
like getdate().
What is really nasty about merge is when you blow the identity ranges in
automatic identity range management (and any of the updateable subscriber
methods which use automatic identity range management). It's somewhat ok if
you client is using winforms or something where they get a message saying
the range is blown before their entry disappears into the vapor; and the
client will call you and have you fix the problem, but on a web application,
clients might get this message for days before someone becomes aware of the
problem to fix it. Their entries are gone. OK, so its a bad application
design, but when unanticipated you have lost a lot of data. We get that a
lot of that here. MS has "fixed" this for SQL 2005 - well, actually added a
backup range to prevent it from occurring.
> 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.
I assume these are friends of yours at MS. As I think we know the same folks
there, and it disturbs me that they are unwilling to correct me privately or
publicly, as I have requested that they do so. I invite them to correct me
here as I welcome the learning experience.
> 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.
>
I welcome anyone who knows more than me to post here and correct me as I am
here to learn. I too will correct anyone when I think they are posting
something incorrect, and I only expect the they do the same of me. So,
please be specific in pointing out to me where I am wrong. Generalities
don't cut it.
As the song from Pat Benetar goes: "Hit me with your best shot, fire away."
--
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" <mi...@solidqualitylearning.com> wrote in message
news:eMQ5mI%23EGH...@TK2MSFTNGP11.phx.gbl...
jsa...@hotmail.com
server with a database backend and trying to replicate the data onto a
backup server.
I have been considering both merge and transactional replication, and I
am unsure of which one to use. They both have problems:
Merge Replication:
My biggest problem is that I cannot modify the database schema once the
merge has been initiated. This is a problem if I want to make a quick
correction to a database. It doesnt suit my company's RAD procedure.
Transactional Replication:
Same as above - the identities are a problem. This is not a problem if
everything is working. However, when the server goes down, I would like
to just switch the network wire and have it running again. My problem
is that the identity values will start from 0 again on the backup
server! The only way I can see of resolving this is to setup identity
ranges for both machines - a bit of a pain but could be done.
My question is, which one is better?
Thanks for any advice.
Jagdip Singh Ajimal
Hilary Cotter
the other 2) use an increment of 2. I use this frequently and although it is
as you say a pain, it does work very well for this type of a situation and
is highly reliable.
--
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
<jsa...@hotmail.com> wrote in message
news:1137671617....@g47g2000cwa.googlegroups.com...
jsa...@hotmail.com
code
yourself, there is no GUI
Could you not just set up the replication both ways through Enterprise
manager? Then set the seeds accordingly?
Also, just a quick question related to this. I want to script this in
SQL so that I can just run the script for every database I want to
replicate. Is there a way of doing this, and where should I look?