A bug in merge replication with FILESTREAM data


I wish I could say that every DBA has a love/hate relationship with Replication, but, let’s face it, it’s only hate. But it could get worse: it could be Merge Replication. Or even worse: Merge Replication with FILESTREAM.

What could possibly top all this hatred and despair if not a bug? Well, I happened to find one, that I will describe here.

The scenario

I published tables with FILESTREAM data before, but it seems like there is a particular planetary alignment that triggers an error during the execution of the snapshot agent.

This unlikely combination consists in a merge article with a FILESTREAM column and two UNIQUE indexes on the ROWGUIDCOL column. Yes, I know that generally it does not make sense to have two indexes on the same column, but this happened to be one of the cases where it did, so we had a CLUSTERED PRIMARY KEY on the uniqueidentifier column decorated with the ROWGUIDCOL attribute and, on top, one more NONCLUSTERED UNIQUE index on the same column, backed by a UNIQUE constraint.

Setting up the publication does not throw any error, but generating the initial snapshot for the publication does:

Cannot create, drop, enable, or disable more than one constraint,
column, index, or trigger named 'ncMSmerge_conflict_TestMergeRep_DataStream'
in this context. Duplicate names are not allowed.

Basically, the snapshot agent is complaining about the uniqueness of the name of one of the indexes it is trying to create on the conflict table. The interesting fact about this bug is that it doesn’t appear when the table has no FILESTREAM column and it doesn’t appear when the table doesn’t have the redundant UNIQUE constraint on the ROWGUID column: both conditions need to be met.

The script

Here is the full script to reproduce the bug.

Before you run it, make sure that:

  1. FILESTREAM is enabled
  2. Distribution is configured

After running the script, start the snapshot agent and you’ll see the error appearing:

snapshot

Workaround

One way to get rid of the bug is to enforce the uniqueness of the data by using a UNIQUE index instead of a UNIQUE constraint:

CREATE UNIQUE NONCLUSTERED INDEX UQ_MESL_DataStreamPK
ON [DataStream] ([DataStreamGUID]);

With this index, the snapshot agent completes correctly. Please note that the index would have been UNIQUE anyway, because its key is a superset of the primary key.

Hope this helps!

Please Vote!

This bug has been filed on UserVoice and can be found here: https://feedback.azure.com/forums/908035-sql-server/suggestions/34735489-bug-in-merge-replication-snapshot-agent-with-files

Please upvote it!

Advertisements

Posted on July 3, 2018, in SQL Server and tagged , , , . Bookmark the permalink. 4 Comments.

  1. patchy_drizzle

    From the name, it looks like the error occurs creating an index twice on the conflicts table with same name.

    So there must be two triggers within your ddl that cause that to be.

    The conflict table index includes ROWGUID; I suppose the act of publishing
    the table creates this conflict table and index.

    It looks as if this bit
    ‘ALTER TABLE [DataStream] ADD CONSTRAINT UQ_MESL_DataStreamPK UNIQUE ([DataStreamGUID’

    must also be picked up by the publication process and cause it to try and add the same unique index on the conflict table with the same system generated name.

    Why add another unique index to that GUID column anyway ? Perhaps you don’t need…

    Also You might want to check the guid is defined with ‘newsequentialid’ to avoid lots of page
    splitting. Especially as it’s your clustered index …

    • Thanks, I appreciate the tips, but they have nothing to do with the bug itself. It’s the constraint combined with the filestream column that causes the unwanted behavior

  2. The error message appears to be similar, but it’s a default constraint in that case. However, there seems to be a bond between the FILESTREAM attribute on the column and the bug: if you remove it from the script, everything works just fine. I have absolutely no idea why FILESTREAM could cause a problem like that, but turns out it does.

  1. Pingback: Problem With Merge Replication And FILESTREAM – Curated SQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: