BLOB Merge Replication

Hi,

I have setup a Merge Replication between a SQL Server 2005 and a SQL Server Mobile using native C++ code in Windows CE 5. Replication of normal data types (nvarchar, ints etc) works great, but when I try to replicate a blob about 1.5 MB the performance is awful. The download phase seems to be quick while the applytime (printed in debugger) when the database on my CE device is updated is about 3 minutes for 1.5MB binary data which is unacceptable since the database gets locked up during the applytime. The BLOB column is defined as image data type which shall hold up to 1GB data. I have tried to disable compression during replication, but it doesnt matter. Does anyone have some experience of using merge replication with blobs in SQL Server Mobile?

My Idea was to use merge replication as a way to transfer CAB-files for installation on the Windows CE 5 device by storing them as BLOB:s, but the bad performance may make it impossible.

Ideas? Anyone?

Best regards

Johan Johansson

I'm appending the debug output from the merge replication of the table containing the 1.5 MB BLOB.

SQLCECA30: Client Agent Log Start --
SQLCECA30: 08/23/2006-16:37:41 CSSCEMerge::SingleRun id=0 hr=0 bread=8027251 bwritten=273 c_urows=0 c_drows=0 s_urows=5 s_drows=0 md_n=0 md_c=0 md_r=0 app=mpsim.exe
SQLCECA30: 08/23/2006-16:37:41 CSSCEMerge::Run totalsynctime=222 uploadtime=371 applytime=217185 id=0 publisher='se-cwo-ddb-1' publication='Software' initialsync=1 bread=8027251 bwritten=273 c_urows=0 c_drows=0 s_urows=5 s_drows=0 loops=1 app=mpsim.exe

|||

About the only thing you can do is to factor out the BLOB itself into a related table. For example, let's say right now you have a table called "Orders" which has 10 columns, one of which is a customer signature (image column in SQL Mobile). Every time any column in the orders table is updated, the whole row is replicated, including the image. It would be more efficient to have an Orders table and a separate OrderSignatures table, related by a foreign key.

Darren

|||

Yes, thank you for the suggestion.

I must say that I'm dissapointed in the performance. I mean 1.5 MB binary data is not alot in these days and it is almost impossible to handle for SQL Server Mobile in a merge replication. Can I expect the same bad performance when I replicate alot of rows (lets say 3000) with more ordinary data (say a few columns with ints and varchar(50)) or is this performance problem only related to dealing with BLOBs?

Johan

|||

Johan,

I was thinking about your post some more and it occurs to me that you are trying to solve the problem of automatically downloading CAB files

from the server to the CE5 device. I also frequently have this requirement when a mobile app must be automatically updating. There are some

great samples (they are not native code, but you can get the idea of how to do this) of auto-updating by downloading CABs to the device at:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnnetcomp/html/AutoUpdater.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnnetcomp/html/AUTD_Functionality_NETCF_WebServices.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnnetcomp/html/DeploymentPatterns.asp

Regards,

Darren

|||

Thank you Darren,

I will have a look at the documents before I decide how I'm going to implement this.

Best regards

Johan