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.

Peer2Peer Transactional Replication

Hi,
Anyone know why the view/ store procedure does not replicate from ServerA to
ServerB?
Thanks,
Kenny
They will be in place if you deploy your subscribers from a backup. If you
create the subscription manually you will need to create them on each node
as well.
Note that if you create a new view or stored procedure they will not be
replicated to each node.
http://www.zetainteractive.com - Shift Happens!
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
"Kenny" <Kenny@.discussions.microsoft.com> wrote in message
news:5CA78A4E-78B4-488C-BF83-2D4B88A1FCA9@.microsoft.com...
> Hi,
> Anyone know why the view/ store procedure does not replicate from ServerA
> to
> ServerB?
> Thanks,
> Kenny

peer-2-peer Replication

I am using Transactional publication with updatable subscriptions of
database In this I have to enable Peer-2-Peer Replication. How ever
this is disable any reason for this
Peer-to-peer can not be enabled if you are using queued updating or
immediate updating.
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.
<dineshasanka@.gmail.com> wrote in message
news:1142322592.136227.68820@.i40g2000cwc.googlegro ups.com...
>I am using Transactional publication with updatable subscriptions of
> database In this I have to enable Peer-2-Peer Replication. How ever
> this is disable any reason for this
>
sql

peer-2-peer Replication

I am using Transactional publication with updatable subscriptions of
database In this I have to enable Peer-2-Peer Replication. How ever
this is disable any reason for thisPeer-to-peer can not be enabled if you are using queued updating or
immediate updating.
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.
<dineshasanka@.gmail.com> wrote in message
news:1142322592.136227.68820@.i40g2000cwc.googlegroups.com...
>I am using Transactional publication with updatable subscriptions of
> database In this I have to enable Peer-2-Peer Replication. How ever
> this is disable any reason for this
>

peer-2-peer Replication

I am using Transactional publication with updatable subscriptions of
database In this I have to enable Peer-2-Peer Replication. How ever
this is disable any reason for thisPeer-to-peer can not be enabled if you are using queued updating or
immediate updating.
--
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.
<dineshasanka@.gmail.com> wrote in message
news:1142322592.136227.68820@.i40g2000cwc.googlegroups.com...
>I am using Transactional publication with updatable subscriptions of
> database In this I have to enable Peer-2-Peer Replication. How ever
> this is disable any reason for this
>

Peer to Peer Replication Problem with BLOBs

I've set up a development peer to peer replication between 2 SQL Server 2005 on 2 Windows XP SP2 systems.

The distribution agent is configured to use UseOledbStreaming. When saving smaller BLOBs of approx 10000 KB all works fine. When I try to replicate larger data I get the following error message in the Synchronisation status window and in the history of the distributor to the subscriber tab in the replication monitor:

Der Verteilungs-Agent verwendet die OLE DB-Streamingoptimierung für die Replikation von BLOB-Daten (Binary Large Objects) mit mehr als 16384 Bytes. Der Schwellenwert für die Optimierung kann über die Befehlszeile mithilfe des -OledbStreamThreshold-Parameters festgelegt werden.
(Quelle: MSSQL_REPL, Fehlernummer: MSSQL_REPL21060)
Hilfe abrufen: http://help/MSSQL_REPL21060

I also tried different values of OledbStreamThreshold.

I need to be able to replicate BLOBs in size of up to 1 GB.

I'm writing the BLOB data in chunks to the table DB_BLOBData , that isn't an article for replication and at the end copy it to the destination table MyBLOBTable which is an article. I do this, because I noticed that the transaction log on the subscriber database becomes incredibly huge (>60 GB for maybe 50 1000KB BLOBs written in blocks size of 65536 bytes) when directly updating the row using BLOB.Write(...). I think, that for each write at least the whole BLOB contents is written to the transaction log.

The replication uses Stored procedures, Update using SCALL syntax.

The code of the SP:

...
IF @.action = 1
BEGIN
UPDATE MyBLOBTable
SET DocContent = (SELECT BLOB from DB_BLOBData where GuidId = @.id)
, DocSize = (SELECT DATALENGTH(BLOB) from DB_BLOBData where GuidId = @.id)
WHERE DocId = @.id
DELETE FROM DB_BLOBData WHERE GuidId = @.id
END

IF @.action = 0
BEGIN
IF @.Offset = 0
BEGIN
INSERT DB_BLOBData( GuidId, BLOB )
VALUES ( @.id, @.value )
END
ELSE
BEGIN
UPDATE DB_BLOBData
SET BLOB.Write( @.value , @.Offset , @.Length )
WHERE GuidId = @.id
END
END

SELECT @.ModDttm = modDttm
, @.ModUser = modUser
, @.ModClient = modClient
, @.ModAppl = modAppl
FROM MyBLOBTable
WHERE DocId = @.id

RETURN 0
END

TIA,

Hannoman

Hi Hannoman,

While my ancestors are from Germany, my German is severly lacking, thus my inability to decipher the error message , but you might look at the following in BOL:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rpldata9/html/11061c08-661a-4386-8996-9685cd81de05.htm

Particuarly, the section that talks about LOB's. I don't know if that answers your question, but if it doesn't hopefully it will point you in the right direction.

Scott

|||

Hello Scott

I'm using transactional peer to peer replication.

The error message means something like

The distribution agent uses OLE DB-Streaming optimization for replicating BLOB data with size larger than 16384 bytes. The threshold for the optimizationcan be defined on the command line using the OledbStreamThreshold parameter.

(Source: MSSQL_REPL, Error number: MSSQL_REPL21060)
get help: http://help/MSSQL_REPL21060

The problem is, that this kind of error deadlocks the replication. As I don't know how to handle this, I have to delete the publications and subscriptions and set them up again. In an production environment this wouldn't be a solution.

Hannoman

|||Unfortunately this is a known bug for Peer to Peer in SQL 2005, as a workaround you can try setting -CommitBatchSize to 1.

Peer to Peer Replication Problem with BLOBs

I've set up a development peer to peer replication between 2 SQL Server 2005 on 2 Windows XP SP2 systems.

The distribution agent is configured to use UseOledbStreaming. When saving smaller BLOBs of approx 10000 KB all works fine. When I try to replicate larger data I get the following error message in the Synchronisation status window and in the history of the distributor to the subscriber tab in the replication monitor:

Der Verteilungs-Agent verwendet die OLE DB-Streamingoptimierung für die Replikation von BLOB-Daten (Binary Large Objects) mit mehr als 16384 Bytes. Der Schwellenwert für die Optimierung kann über die Befehlszeile mithilfe des -OledbStreamThreshold-Parameters festgelegt werden.
(Quelle: MSSQL_REPL, Fehlernummer: MSSQL_REPL21060)
Hilfe abrufen: http://help/MSSQL_REPL21060

I also tried different values of OledbStreamThreshold.

I need to be able to replicate BLOBs in size of up to 1 GB.

I'm writing the BLOB data in chunks to the table DB_BLOBData , that isn't an article for replication and at the end copy it to the destination table MyBLOBTable which is an article. I do this, because I noticed that the transaction log on the subscriber database becomes incredibly huge (>60 GB for maybe 50 1000KB BLOBs written in blocks size of 65536 bytes) when directly updating the row using BLOB.Write(...). I think, that for each write at least the whole BLOB contents is written to the transaction log.

The replication uses Stored procedures, Update using SCALL syntax.

The code of the SP:

...
IF @.action = 1
BEGIN
UPDATE MyBLOBTable
SET DocContent = (SELECT BLOB from DB_BLOBData where GuidId = @.id)
, DocSize = (SELECT DATALENGTH(BLOB) from DB_BLOBData where GuidId = @.id)
WHERE DocId = @.id
DELETE FROM DB_BLOBData WHERE GuidId = @.id
END

IF @.action = 0
BEGIN
IF @.Offset = 0
BEGIN
INSERT DB_BLOBData( GuidId, BLOB )
VALUES ( @.id, @.value )
END
ELSE
BEGIN
UPDATE DB_BLOBData
SET BLOB.Write( @.value , @.Offset , @.Length )
WHERE GuidId = @.id
END
END

SELECT @.ModDttm = modDttm
, @.ModUser = modUser
, @.ModClient = modClient
, @.ModAppl = modAppl
FROM MyBLOBTable
WHERE DocId = @.id

RETURN 0
END

TIA,

Hannoman

Hi Hannoman,

While my ancestors are from Germany, my German is severly lacking, thus my inability to decipher the error message , but you might look at the following in BOL:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rpldata9/html/11061c08-661a-4386-8996-9685cd81de05.htm

Particuarly, the section that talks about LOB's. I don't know if that answers your question, but if it doesn't hopefully it will point you in the right direction.

Scott

|||

Hello Scott

I'm using transactional peer to peer replication.

The error message means something like

The distribution agent uses OLE DB-Streaming optimization for replicating BLOB data with size larger than 16384 bytes. The threshold for the optimizationcan be defined on the command line using the OledbStreamThreshold parameter.

(Source: MSSQL_REPL, Error number: MSSQL_REPL21060)
get help: http://help/MSSQL_REPL21060

The problem is, that this kind of error deadlocks the replication. As I don't know how to handle this, I have to delete the publications and subscriptions and set them up again. In an production environment this wouldn't be a solution.

Hannoman

|||Unfortunately this is a known bug for Peer to Peer in SQL 2005, as a workaround you can try setting -CommitBatchSize to 1.

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

Peer to Peer replication and cascading deletes

I was hoping someone could explain how cascading deletes work in Peer to Peer replication on SQL 2005 SP2, the child tables are also being replicated.

Did some of my own testing and here's the results:

Peer to Peer Replication Testing FK Cascading deletes Test 1 1 Create 2 tables with a foreign key dependency, both tables are being replicated. 2 Create FK relationship with the various option below. FK Options Behaviour SQL1 SQL2 Note FOR REPPLICATION Rows are removed as expected All rows are delete as with SQL1, not errors are generated. NOT FOR REPLICATION Rows are removed as expected All rows are delete as with SQL1, not errors are generated. Test 2 1 Create 2 tables with a foreign key dependency, only 1 table is replicated replicated. 2 Create FK relationship with the various option below. FK Options Behaviour SQL1 SQL2 Note FOR REPPLICATION Rows are removed as expected All rows are delete as with SQL1, not errors are generated. NOT FOR REPLICATION Rows are removed as expected The cascading delete failed, only the primary record was removed. This does not break replication as the table that retained the row is not replicated.

peer to peer replication

Hi,

I set up a peer to peer replication on servers, but only one side seem to be working. I am getting Log Reader Agent job error.

Unable to start execution of step 2 (reason: Error authenticating proxy domain\user, system error: Logon failure: unknown user name or bad password.). The step failed.

I am using the same windows account on both servers, why is one side working and the other side not?

thanks

doublecheck the account and password that starts the job, sqlserver and sqlagent service, and doublecheck their permissions on the machine.

Peer to Peer difficulty when adding a column

I'm trying to add a column to my peer to peer replication. According to the
help I've found:
Adding Columns
a.. To add a new column to a table and include that column in an existing
publication, execute ALTER TABLE <Table> ADD <Column>. By default, the
column is then replicated to all Subscribers. The column must allow NULL
values or include a default constraint.
b.. To add a new column to a table and not include that column in an
existing publication, disable the replication of schema changes, and then
execute ALTER TABLE <Table> ADD <Column>.
c.. To include an existing column in an existing publication, use
sp_articlecolumn (Transact-SQL), sp_mergearticlecolumn (Transact-SQL), or
the Publication Properties - <Publication> dialog box.
For more information, see How to: Define and Modify a Column Filter
(Replication Transact-SQL Programming) and How to: Define and Modify a
Column Filter (SQL Server Management Studio). This will require
subscriptions to be reinitialized.
d.. Adding an identity column to a published table is not supported,
because it can result in non-convergence when the column is replicated to
the Subscriber. The values in the identity column at the Publisher depend on
the order in which the rows for the affected table are physically stored.
The rows might be stored differently at the Subscriber; therefore the value
for the identity column can be different for the same rows.
However, while I am able to add the column to the publisher and it is
replicated to the subscriber, I cannot update this column at the subscriber
and have it replicate to the publisher. However when I update at the
publisher it IS pushed to the subscriber.
Outside of the new column, my updates bi-directionally work. Can someone
tell me how I can get updates on newly added columns to be bi-directional?
Thanks,
Adam P. Cassidy
Did you quiesce the system as outlined in this link?
http://msdn2.microsoft.com/en-us/library/ms147385.aspx
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Adam Patrick Cassidy" <apcassidy@.aol.com> wrote in message
news:eoBB5en4GHA.1200@.TK2MSFTNGP02.phx.gbl...
> I'm trying to add a column to my peer to peer replication. According to
> the help I've found:
> Adding Columns
> a.. To add a new column to a table and include that column in an existing
> publication, execute ALTER TABLE <Table> ADD <Column>. By default, the
> column is then replicated to all Subscribers. The column must allow NULL
> values or include a default constraint.
> b.. To add a new column to a table and not include that column in an
> existing publication, disable the replication of schema changes, and then
> execute ALTER TABLE <Table> ADD <Column>.
> c.. To include an existing column in an existing publication, use
> sp_articlecolumn (Transact-SQL), sp_mergearticlecolumn (Transact-SQL), or
> the Publication Properties - <Publication> dialog box.
> For more information, see How to: Define and Modify a Column Filter
> (Replication Transact-SQL Programming) and How to: Define and Modify a
> Column Filter (SQL Server Management Studio). This will require
> subscriptions to be reinitialized.
> d.. Adding an identity column to a published table is not supported,
> because it can result in non-convergence when the column is replicated to
> the Subscriber. The values in the identity column at the Publisher depend
> on the order in which the rows for the affected table are physically
> stored. The rows might be stored differently at the Subscriber; therefore
> the value for the identity column can be different for the same rows.
> However, while I am able to add the column to the publisher and it is
> replicated to the subscriber, I cannot update this column at the
> subscriber and have it replicate to the publisher. However when I update
> at the publisher it IS pushed to the subscriber.
> Outside of the new column, my updates bi-directionally work. Can someone
> tell me how I can get updates on newly added columns to be bi-directional?
> Thanks,
> Adam P. Cassidy
>
|||Yes. That is the same article I used as a reference originally.
Adam P. Cassidy
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%234SYrOq4GHA.1188@.TK2MSFTNGP05.phx.gbl...
> Did you quiesce the system as outlined in this link?
> http://msdn2.microsoft.com/en-us/library/ms147385.aspx
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> 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
>
> "Adam Patrick Cassidy" <apcassidy@.aol.com> wrote in message
> news:eoBB5en4GHA.1200@.TK2MSFTNGP02.phx.gbl...
>

Peer to Peer 64bit and 32bit question

If I have an existing 64bit P2P transactional replication setup, can I join
a 32bit machine without any expected problems or does it also have to be a
64bit machine?
Any direction to resources would be great as well.
Adam P. Cassidy
Adam,
For SQL Server 2000, the issues were summarised as follows:
As the Microsoft Jet engine is not supported, Microsoft Jet push
subscriptions for merge replication are not supported.
Unless the subscriber provides a 64-bit ODBC or OLE DB driver, transactional
or snapshot push subscriptions for ODBC or OLE DB subscribers are not
supported.
Because of the unavailability of 64-bit Data Transformation Services (DTS),
transformable push subscriptions are not supported.
In Peer-to-peer between 64bit and 32bit I am therefore aware of no issues.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||This is a tested configuration
"Adam Patrick Cassidy" <apcassidy@.aol.com> wrote in message
news:O%23gjeUCCHHA.4864@.TK2MSFTNGP04.phx.gbl...
> If I have an existing 64bit P2P transactional replication setup, can I
> join
> a 32bit machine without any expected problems or does it also have to be a
> 64bit machine?
> Any direction to resources would be great as well.
> Adam P. Cassidy
>
sql

Peculiar Replication (Could be OT)

We have 2 types of Production Database. One is an OLTP database another contains all the Master Tables. We also have 2 copies of each and this repeats itself for each country where the plant is located. Is there someone who can explain to me how we might
setup this Replication so that the Master tables which are maintained in one location gets replicated over to the other places and also the updated from those locations are also propogated to all locations. We had MS come into the picture and they have cr
eated triggers such that the data is copied over but they seem to delete the record and insert it back again for an Update and sometimes inbetween the delete and insert the leased line goes down and the result is a total failure.
Regards,
Trevor Benedict R
Trevor,
this is typically implemented using merge replication. You'll need to consider partitioning of data for each subscriber, or the posibility of conflicts and how they should be resolved. There's a good set of explanations of general concepts in BOL.
The network failures you experience are usually solved in merge by simply restarting the merge agent.
HTH,
Paul Ibison

pb replication with sql express 2005

Hi,
I have a main database server running sql server 2000 with sp4.
I have a a subscriber running sqlexpress 2005 server.
I use transactionnal replication with updates autorise on the subscriber.
My configuration seems correct as i can modify my tables content with INSERT
command and found the information replicated in both server.
But each time i try to modify my data using Update commands
update planning set planning_previ_valide = 1 where id_plan = 3656
an error message is displayed
(this is the french version of the error message saying that i have overtake
the limit of 32 triggers, stored procedures, functions ...)
Msg 217, Niveau 16, tat 1, Procdure UserUpdatePlanning, Ligne 10
Le niveau maximal d'imbrication des procdures stockes, des fonctions, des
dclencheurs ou des vues est dpass (limite 32).
Do you have an idea ?
Regards,
Marc
On the server where you get the error, have a look for update triggers on the
Planning table - it seems there is recursion going on which exceeds the max
permitted looping of 32.
HTH,
Paul Ibison

Pausing Replication

Several times over the course of a month, our network admin team performs
necessary maintenance due to company moves and general growth. During this
time the connection between my Publisher and Distributor is disconnected. My
Subscriber and Distributor are on the same server. I have three
transactional publications set up and I would like to Pause them until the
connection is restored since I know when the connection is going down.
Is this the correct way to handle this?
From the Distributor in the enterprise manager drill down through the
replication monitor to the Distibution Agents and click on Stop Agent for
all of the publication that I have. Do I also have to Stop Agent on all of
the publications in the Log Reader Agents folder?
This also brings up the question of unplanned connection loss. If for some
reason the connection is lost late at night and I dont know about it until
the next morning do I have to recreate snapshots or re-initialize the
publication?
I would stop SQL Server agent, disable the jobs in the management folder and
then restart the agents. You should only have to stop the distributor or
merge agents.
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 Butler via droptable.com" <forum@.nospam.droptable.com> wrote in
message news:4FFDFDD1EDC20@.droptable.com...
> Several times over the course of a month, our network admin team performs
> necessary maintenance due to company moves and general growth. During
> this
> time the connection between my Publisher and Distributor is disconnected.
> My
> Subscriber and Distributor are on the same server. I have three
> transactional publications set up and I would like to "Pause" them until
> the
> connection is restored since I know when the connection is going down.
> Is this the correct way to handle this?
> From the Distributor in the enterprise manager drill down through the
> replication monitor to the Distibution Agents and click on "Stop Agent"
> for
> all of the publication that I have. Do I also have to "Stop Agent" on all
> of
> the publications in the Log Reader Agents folder?
> This also brings up the question of "unplanned" connection loss. If for
> some
> reason the connection is lost late at night and I don't know about it
> until
> the next morning do I have to recreate snapshots or re-initialize the
> publication?
|||Does it matter what order I do this in?
|||no, you can disable the jobs first, which will mean that if they are
scheduled they won't run the next time they are scheduled. However if they
are in continuous mode, you must stop them before or after disabling them.
Then you should bounce SQL Server agent.
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 Butler via droptable.com" <forum@.droptable.com> wrote in message
news:4FFEBB746E630@.droptable.com...
> Does it matter what order I do this in?

Pause Transactional Replication

Is there an easy and quick way to temporarily pause replication on a specific
publication/subscription and then start it back up again?
Thanks,
Mark
You could stop the distribution agent and then disable it.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
sql

patch application procedure

Hi,
I am having an mssql server with sp3. It is in sql cluster
(NT) as well as it participates in replication (merge). My
comany is very paranoid about doing things and all the
more there is no testing environment. Could any one please
explain how to apply SP4 for this server? We need to
consider the problematic conditions and disaster recovery
as well. My recovery mode is set to full.
james bond...-- james bond wrote: --
> Hi,
>> I am having an mssql server with sp3. It is in sql cluster
> (NT) as well as it participates in replication (merge). My
> company is very paranoid about doing things and all the
> more there is no testing environment. Could any one please
> explain how to apply SP4 for this server? We need to
> consider the problematic conditions and disaster recovery
> as well. My recovery mode is set to full.
--
Hi James Bond,
The best source of information for a service pack install is the README.htm of the service pack itself.
Hope this helps,
-Eric Cárdenas
SQL Server support

PASV ftp?

Hi!
We installed a new router and now the ftp server only works in passive mode.
Is there a way to configure the replication agent to transfer the snapshot
using PASV?
Replication is merge pull.
Thanks.
Carlos Gutirrez
carlosg@.sca.com.mx
no, SQL Server replication only supports the ftp command set used by
Microsoft FTP server. You are free to use any other vendor's FTP server, but
are restricted to Microsoft's FTP command set.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Carlos Gutierrez" <carlosg@.sca.com.mx> wrote in message
news:wk0Nc.20534756$Of.3407707@.news.easynews.com.. .
> Hi!
> We installed a new router and now the ftp server only works in passive
mode.
> Is there a way to configure the replication agent to transfer the snapshot
> using PASV?
> Replication is merge pull.
> Thanks.
> --
> Carlos Gutirrez
> carlosg@.sca.com.mx
>