Replication: Watch out for STREAM_BLOB_COLUMNS when setting up replication on your SQL 2008 + server

When setting up merge replication on your SQL 2008 + server, please be careful with the parameter STREAM_BLOB_COLUMN.

By default, this option is set to FALSE, as explained here in BOL:

  [ @stream_blob_columns= ] 'stream_blob_columns'
Specifies that a data stream optimization be used when replicating binary large object columns. stream_blob_columns is nvarchar(5), with a default of FALSE. true means that the optimization will be attempted.

However one should be careful with the NOTE information (see below):

When filestream is enabled - @schema_option = 0x000000010C034FD1 (100000000 --> Use this option to replicate the FILESTREAM attribute if it is specified on varbinary(max) columns. Do not specify this option if you are replicating tables to SQL Server 2005 Subscribers. Replicating tables that have FILESTREAM columns to SQL Server 2000 Subscribers is not supported, regardless of how this schema option is set. See related option 0x800000000) - the stream_blob_column option is also enabled and a warning is generated:

Warning: To allow replication of FILESTREAM data to perform optimally and reduce memory utilization, the 'stream_blob_columns' property has been set to 'true'. To force FILESTREAM table articles to not use blob streaming, use sp_changemergearticle to set 'stream_blob_columns' to 'false'.

Should this option NOT be necessary (you will not be transfering data greater than 16K) than you should disable the option in order to avoid possible performance issues.

Important
Enabling this memory optimization may reduce the performance of the Merge Agent during synchronization. This option should only be used when replicating columns that contain megabytes of data.

Note
Certain merge replication functionalities, such as logical records, can still prevent the stream optimization from being used when replicating binary large objects even with stream_blob_columns set to true.