Peer-to-peer replication and Oracle real application clusters

Hello,
I have a question I hope can be answered fully.
Some time last year, I attended a Microsoft presentation in Dallas and a sql
server specialist from microsoft (I think his name was srikan) mentioned
during Q&A that SQL Server 2005 has peer-to-peer replication which is a
comparable solution to oracle's real application cluster with some
restriction.
I recently attended another presentation also at Microsoft but this time by
their partner that are suppose to be SQL server experts. This speaker said
that it is not a comparable solution. He gave several reasons and they
seemed to make sense but then again, a microsoft technical specialist said
differently last year.
Did the product change? Who should I listen to? My co-workers say I should
listen to microsoft because they created the product but the other guy seems
to know a lot also and said he used to be an oracle dba. I am confused.
Anybody can help here? I don't want to ask Oracle because they want a lot of
information from me before they will answer and their newsgroup people are
quite unfriendly.
Thank you.
aK.
No, peer-to-peer replication is not a replacement to RAC. Unfortunately,
the TS would be wrong in a literal interpretation. If you look at the basic
data flow, there is some similarity in the solution which disappears when
you start looking a lot deeper.
I don't know a huge amount about RAC, but I understand the basics. With RAC
you are essentially plugging N servers into an Oracle architecture that
access a single database. What you wind up with is theoretically pooling
all of your hardware resources together (memory, processors, network I/O)
which can be basically treated by an application as a single massive server.
They still point to a single database on the backend. You still have to
deal with all of the issues related to changes coming from multiple servers
into a single database which now has to resolve conflicting changes on the
fly and several other implementation related issues.
Peer-to-peer replication is a logical extension to bi-directional
transactional replication. This handles the data layer only. The
replication engine is used to replicate like it says "from everyone - to
everyone". There is ZERO conflict detection or resolution, so your data
changes must be partitioned. If two users were to change the same piece of
data on different servers at the same time, it would create a huge
synchronization issue.
What problem are you trying to solve?
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.
"Angie" <NO_Angie_Kong_SPAM@.hotmail.com> wrote in message
news:ORao5BrMGHA.648@.TK2MSFTNGP14.phx.gbl...
> Hello,
> I have a question I hope can be answered fully.
> Some time last year, I attended a Microsoft presentation in Dallas and a
> sql server specialist from microsoft (I think his name was srikan)
> mentioned during Q&A that SQL Server 2005 has peer-to-peer replication
> which is a comparable solution to oracle's real application cluster with
> some restriction.
> I recently attended another presentation also at Microsoft but this time
> by their partner that are suppose to be SQL server experts. This speaker
> said that it is not a comparable solution. He gave several reasons and
> they seemed to make sense but then again, a microsoft technical specialist
> said differently last year.
> Did the product change? Who should I listen to? My co-workers say I should
> listen to microsoft because they created the product but the other guy
> seems to know a lot also and said he used to be an oracle dba. I am
> confused.
> Anybody can help here? I don't want to ask Oracle because they want a lot
> of information from me before they will answer and their newsgroup people
> are quite unfriendly.
> Thank you.
>
> aK.
>
sql

Peer-to-peer Replication Agent Error

Does anyone know why if I setup a p2p replication I get:
"Could not find a valid Agent Id for the subscription to Publisher
XXXServer, ...
Source: MSSQLServer, Error number: 21758"
This message only happens on the replication between SeverA to ServerB...
ServerB replicates to ServerA just fine...
Any help would be appreciated since the link to Microsoft help says the
error is not documented.
Thanks!
Can you post the exact steps you took to configure this? What you are
describing is not peer-to-peer replication, but seems to be bi-directional
transactional instead.
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"Mick Kowitz" <mkowitz@.cinci.rr.com> wrote in message
news:f%wwf.14644$%D1.9161@.tornado.ohiordc.rr.com.. .
> Does anyone know why if I setup a p2p replication I get:
> "Could not find a valid Agent Id for the subscription to Publisher
> XXXServer, ...
> Source: MSSQLServer, Error number: 21758"
> This message only happens on the replication between SeverA to ServerB...
> ServerB replicates to ServerA just fine...
> Any help would be appreciated since the link to Microsoft help says the
> error is not documented.
> Thanks!
>
|||I actually got it working. The problem was when I was in the peer-to-peer
topology and Adding the SQL Server, I was trying to use Windows
Authentication to connect to the peer, but since I was in a WORKGROUP that
wasn't working. As soon as I made it connect with SQL Authentication it
worked...
I'm wondering if you have any ideas, though, on a new problem...
I have a table in the p2p replication that has an IDENTITY column as the key
and an on INSERT trigger to add a second record. When I replicate, it fails
telling me to turn NOT FOR REPLICATION to NO. I found the MS article
http://support.microsoft.com/?kbid=908711 on this. However, I can't set NOT
FOR REPLICATION to NO in a p2p situation as far as I can tell. It's easily
reproduced. Just create a simple table with primary key as an IDENTITY
column and then an ON INSERT trigger to add a second row. When you create
the publication and try to run the P2P Topology stuff it blows off telling
you that you can't do p2p when set to manual.
Any ideas?
Mick...
"Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
news:u9eJiGVFGHA.1028@.TK2MSFTNGP11.phx.gbl...
> Can you post the exact steps you took to configure this? What you are
> describing is not peer-to-peer replication, but seems to be bi-directional
> transactional instead.
> --
> Mike
> Mentor
> Solid Quality Learning
> http://www.solidqualitylearning.com
>
> "Mick Kowitz" <mkowitz@.cinci.rr.com> wrote in message
> news:f%wwf.14644$%D1.9161@.tornado.ohiordc.rr.com.. .
>
|||I actually got it working. The problem was when I was in the peer-to-peer
topology and Adding the SQL Server, I was trying to use Windows
Authentication to connect to the peer, but since I was in a WORKGROUP that
wasn't working. As soon as I made it connect with SQL Authentication it
worked...
I'm wondering if you have any ideas, though, on a new problem...
I have a table in the p2p replication that has an IDENTITY column as the key
and an on INSERT trigger to add a second record. When I replicate, it fails
telling me to turn NOT FOR REPLICATION to NO. I found the MS article
http://support.microsoft.com/?kbid=908711 on this. However, I can't set NOT
FOR REPLICATION to NO in a p2p situation as far as I can tell. It's easily
reproduced. Just create a simple table with primary key as an IDENTITY
column and then an ON INSERT trigger to add a second row. When you create
the publication and try to run the P2P Topology stuff it blows off telling
you that you can't do p2p when set to manual.
Any ideas?
Mick...
"Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
news:u9eJiGVFGHA.1028@.TK2MSFTNGP11.phx.gbl...
> Can you post the exact steps you took to configure this? What you are
> describing is not peer-to-peer replication, but seems to be bi-directional
> transactional instead.
> --
> Mike
> Mentor
> Solid Quality Learning
> http://www.solidqualitylearning.com
>
> "Mick Kowitz" <mkowitz@.cinci.rr.com> wrote in message
> news:f%wwf.14644$%D1.9161@.tornado.ohiordc.rr.com.. .
>

Peer-to-Peer Replication (Programmatic Control and Setup)

I have reviewed the BOL documentation on how to configure Peer-to-Peer replication via T-SQL and how to use the Replication Wizard to implement replication.

What I would like to find out is how do I configure the peer-to-peer replication process to use an existing column on a table that contains a GUID instead of creating an extra column with a uniqueidentifier GUID value. When you use the Wizard each table article has this extra column added to it.

I don't seem to be able to find it in the books-on-line. Can some one point me to the correct article or BOL page.

Thank you.

...cordell...

I do not think that P2P replication adds a uniqueidentifier column to a published table. Transactional queued replicaiton and merge replication will add required columns to a table that you want to publish. Please confirm which type of replication you are trying to use.

A general suggestion is to use the UI to implement the replication scenario that you want, then use the scripting featues in the UI to see the syntax for the new column that was added.

i.e. Merge replication will add the following column to a table that is merge published...

[rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT

Check out this link for more details on how merge replcation adds a rowguid uniqueidentifier column if one does not exist -- http://msdn2.microsoft.com/en-us/library/aa256294(SQL.80).aspx

Hope this helps,

Tom

This posting is provided "AS IS" with no warranties, and confers no rights.

Peer to peer replication on SQL 2000?

Hello,
reading about peer-to-peer transactional replication in SQL 2005
(http://msdn2.microsoft.com/ms152536.aspx), I would like to do the same
thing with 3 SQL 2000 servers. Of course peer-to-peer replication is not
available, but can anybody suggest a way to simulate this kind of
replication with SQL 2000?
thanks,
al.
AL,
the nearest thing is probably bi-directional transactional replication:
http://support.microsoft.com/default...b;en-us;820675
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Bi directional transactional replication only works between 2 nodes. The
closest you can get is merge, but this would require your publisher to be
always accessible. If it goes down, the other two nodes are out of luck.
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@.Pygmalion.Com> wrote in message
news:%23AE7C%23REGHA.3984@.TK2MSFTNGP14.phx.gbl...
> AL,
> the nearest thing is probably bi-directional transactional replication:
> http://support.microsoft.com/default...b;en-us;820675
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Maybe I'm missing something it but according to the MS document, you can use
a central subscriber topology to use BiDirectional Transactional replication
to cater for more than two nodes?
Cheers,
Paul
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23P67ZKSEGHA.828@.TK2MSFTNGP10.phx.gbl...
> Bi directional transactional replication only works between 2 nodes. The
> closest you can get is merge, but this would require your publisher to be
> always accessible. If it goes down, the other two nodes are out of luck.
> --
> 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@.Pygmalion.Com> wrote in message
> news:%23AE7C%23REGHA.3984@.TK2MSFTNGP14.phx.gbl...
>
|||Paul Ibison wrote:
> Maybe I'm missing something it but according to the MS document, you can use
> a central subscriber topology to use BiDirectional Transactional replication
> to cater for more than two nodes?
> Cheers,
> Paul
That's what I'm trying to do. Manually, as stated in that article. Still
working on it ;)
|||Well what do you know!
Last time I tried this I could not get it to work. Changes originating on
Server B would go to Server A and C but not back to B. Then them C would
loop back to A and go to B, and collision. Let me try to repro this again.
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@.Pygmalion.Com> wrote in message
news:OlJqkTSEGHA.1384@.TK2MSFTNGP11.phx.gbl...
> Maybe I'm missing something it but according to the MS document, you can
> use a central subscriber topology to use BiDirectional Transactional
> replication to cater for more than two nodes?
> Cheers,
> Paul
>
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%23P67ZKSEGHA.828@.TK2MSFTNGP10.phx.gbl...
>
|||Hilary,
I just managed to let this work, but it's a mess of things to do
manually (including generating - and then modifying!! - stored
procedures that will handle inserts/updates/deletes on other servers).
If you follow the instructions, and start with a simple table, you get
it working. The problem is then using this for a large db...
I'm still evaluating if it's worth the effort, but this is the only
solution I found (thanks Paul) to simulate what under SQL 2005 is called
"peer-to-peer transactional replication".
If you or anybody else is using this kind of replication, I'd like to
hear your opinions.
bye,
al.

> Well what do you know!
> Last time I tried this I could not get it to work. Changes originating on
> Server B would go to Server A and C but not back to B. Then them C would
> loop back to A and go to B, and collision. Let me try to repro this again.
>
|||It absolutely can not be done. You wind up with endlessly looping
transactions.
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"AL" <albeNOSPAM@.ihnetNOSPAM.it> wrote in message
news:uAJszyREGHA.376@.TK2MSFTNGP12.phx.gbl...
> Hello,
> reading about peer-to-peer transactional replication in SQL 2005
> (http://msdn2.microsoft.com/ms152536.aspx), I would like to do the same
> thing with 3 SQL 2000 servers. Of course peer-to-peer replication is not
> available, but can anybody suggest a way to simulate this kind of
> replication with SQL 2000?
> thanks,
> al.
|||Mike,
I like your explanation of avoiding closing the loop, but as there is only a
requirement for 3 servers (and the poster wants the nearest method in SQL
2000 to simulate peer-to-peer), why won't
ServerA <--> ServerB <--> ServerC
work? (you mention this in another reply ).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Al,
it would help us to know if it is a requirement for all the nodes to
communicate with eachother (rather than having a simple chain). As you can
see from the thread, this is the crux of whether or not bidirectional TR is
applicable or not.
Rgds,
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
sql