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.

Pause/Continue distribution, content formatting or delivering.

Notification Services get an event and create notification batch.

Can I pause or stop process of notification delivery with (or without ) ability to continue?

You can disable the distributor, which will stop all formatting/delivery operations. When you re-enable it, the distributor will pick up where it left off. If you set an expiration age in your notification class, then notifications older than that age not be picked up again.

You can disable the distributor via the command line using the nscontrol disable command (check the books online for the syntax for disabling just the distributor). If you're using SQL 2005, you can also disable the distributor from Management Studio: right click on the NS instance, choose properties, and uncheck the distributor from the list of enabled components.

Hope this helps.
-shyam|||

Thanks.

I want to specify the question. I have one NS instance and one application in this instance. This application contains one event class with one parameter (Site_ID). Application gets some events with different Site_ID (e.g. 1,2,3) and creates several notification batches for each event. I want to pause or stop delivery process for notifications (may be, distributor work items?) for some Site_ID (e.g. 2). Can I do this?

|||Unfortunately there isn't a way to enable/disable event classes. You may however, enable/disable a particular event provider.

If this scenario is important you may investigate having an event class per event provider. In your case, that would implying turning each site into an individual event provider and use the management of a particular event provider to achieve what you're looking for. If you do choose to do this, note that there will be implications on how you manage your subscriptions.

Hope that helps,
Anand|||

Hi Roman -

Here's a copy of the reply I posted to your post in the newsgroup.
(http://groups-beta.google.com/group/microsoft.public.sqlserver.notificationsvcs/browse_thread/thread/5161755f07148121/92b74bf86dcf45b1#92b74bf86dcf45b1)

A couple of possibilities come to mind:

1) Depending on your Event Provider, you could alter the how the
events are recognized to prevent the submission of the unwanted
events. For example, if you're using the SQL Server Event Provider,
you can base it on a view that unions together all of the events you
want to recognize. When you need to stop on type of event for a while,
just comment it out of the view. For example:

SELECT Site_ID, Site_Info FROM MyTable WHERE Site_ID = 1
UNION
SELECT Site_ID, Site_Info FROM MyTable WHERE Site_ID = 2
UNION
SELECT Site_ID, Site_Info FROM MyTable WHERE Site_ID = 3
UNION
SELECT Site_ID, Site_Info FROM MyTable WHERE Site_ID = 4

2) You can reactively catch the unwanted notifications in a custom
content formatter and disregard it based upon some criteria that you
set.

Of course I think proactively catching them ahead of time (option #1)
is better. It's more efficient and allows you to resubmit them later
if need be.

HTH...

--
Joe Webb
SQL Server MVP

~~~
Get up to speed quickly with SQLNS
http://www.amazon.com/exec/obidos/tg/detail/-/0972688811

I support PASS, the Professional Association for SQL Server.
(www.sqlpass.org)

|||

For further clarification and present the business need, I would like to restate the question this way: How does one pause, resume, or cancel a specific batch currently being distributed?

E.g. Assume that NS is the core for a messaging platform that includes the ability to send emails to a membership list. For whatever the business reason, the operator sending the email needs to interrupt (suspend) the current batch being distributed and may or may not decide to resume distribution where it left off depending upon the reason for interuption.

Yes it best to catch issues before distributing, but to find a serious issue 10% into the distribution and be able to click a button to stop the remaining 90% seems a reasonable expectation.

Does the NS platform provide this level of control?

Thanks,
--Bob

|||Hi Bob,

Thanks for clarifying. No, there is no reliable way to stop the distribution midway.

Your scenario is interesting and we see the value in enabling it reliably. We will consider this feature for the next release.

Thanks,
Anand|||Perhaps you could implement a custom delivery protocol that checks a criteria indicating whether or not to send the notification or to discard it? Scalability may become an issue for you depending on how you check the send/no send criteria.

HTH...

--
Joe Webb
SQL Server MVP

~~~
Get up to speed quickly with SQLNS
http://www.amazon.com/exec/obidos/tg/detail/-/0972688811

I support PASS, the Professional Association for SQL Server.
(www.sqlpass.org)

|||Hi Anand,

Thank you for considering this as a feature enahncement in the future. I'm sure that there are other circumstances where this level of control may be desireable to more customers than ourselves.

I'm not too sure what we will do in the mean time, but at least we know that this is not doable for now.

Thanks,
--Bob|||

Well, as I can see, it is possible to cancel the distribution at least using filtering at the level of the custom delivery protocol, even if there is no possiblity to resume it again. Distributor will still process all formatted messages, but they won't be sent by protocol.

Moreover, if we put some value indicating delivery failure, can sending process also be resumed later, using standard (or custom?) retry abilities?

|||

I haven't tried it myself, but I can't see any reason why the standard retry logic wouldn't work. Just be mindful of the ExpirationAge, RetrySchedule, and RetryDelay elements. Of course you'll also need to consider those times when you need to turn delivery again before the expiration age of the ones you wanted to cancel has expired.

I suspect this implementation has some scalability issues, too. You'll definitely want to make the filtering check as efficient as possible and then do some load testing.

HTH...

--
Joe Webb
SQL Server MVP

~~~
Get up to speed quickly with SQLNS
http://www.amazon.com/exec/obidos/tg/detail/-/0972688811

I support PASS, the Professional Association for SQL Server.
(www.sqlpass.org)