Problems with NULL Large Object Data Types and 64-bit Replication

nospam@example.com (Kevin Locke)
Sat, 22 Oct 2011 13:13:54 -0600
SQL Server

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.