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.