Saturday, October 22. 2011
Problems with NULL Large Object Data Types and 64-bit Replication
I recently encountered an interesting problem where constraint checks on VARBINARY(MAX)
columns were failing during synchronization for merge-replicated clients. After a lot of investigation, I have found that when a 64-bit SQL Server 2008 R2 subscriber synchronizes a table which contains large object data types on an article published with @stream_blob_columns = N'true'
any constraint checks which depend on the values being NULL will fail. Note that 32-bit clients work properly and that without the constraint checks the final value would be NULL, but apparently during synchronization it is in a temporary state of being non-NULL and fails before reaching that point.
Although the release notes in KB2463333 do not mention a fix for this problem, it appears that Service Pack 1 for SQL Server 2008 R2 at least contains a workaround. When testing with SP1, the insert fails due to the constraint violation but the retry succeeds. However, even with the workaround, performance problems related to the retry are a legitimate concern. I would suggest considering disabling @stream_blob_columns
on the affected articles for anyone encountering this problem.