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.. .
>
Showing posts with label agent. Show all posts
Showing posts with label agent. Show all posts
Friday, March 30, 2012
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 
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
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
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.Monday, March 12, 2012
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
>
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
>
Subscribe to:
Comments (Atom)