Merge Replcation - Appears to break some queries RRS feed

  • Question

  • I have been using Merge Replication for 20 years and have come to expect that the RowGuid columns that are added to replicated tables will not cause any issues. Recently, I may have discovered at least one potential problem.

    There is some query text in some of my stored procedures, that were developed using the "Build Query" feature of SSMS. They are "Insert Results" queries. I have never taken much notice until now, but the query text, so generated, doesn't always have a specific list of columns in the INSERT statement.

    INSERT DestTable SELECT Col1 FROM SourceTable

    rather than

    INSERT DestTable (Col1) SELECT Col1 FROM SourceTable

    The first version worked before Replication was implemented but failed afterwards, due to the addition of a RowGuid column in the destination table (it originally had only one column, Col1). This is actually a temporary table (but not a "temp table") and should not have been replicated, but it was unintentionally added to the list of replication articles.

    Changing the query text to explicitly name the destination column fixed the problem. I assume that the issues is that, if the destination columns exactly match the source columns, they don't meed to be explicitly listed in the INSERT statement, but perhaps this is not best practice.

    Are there any other such issues to watch for when adding Merge Replication to database? In many years of using it, this is the first one that I have come across.

    R Campbell

    Monday, February 19, 2018 10:49 AM

All replies

  • Merge replication causes a performance hit. Expect slower DML performance. Also expect deadlocking during synchronizations.
    Monday, February 19, 2018 5:05 PM
  • Thanks, there have been no problems in that regard, possibly because database, while not small, is not particularly large. Also, there is normaly only one source of insert and update acivity and a low select query demand. The only problem has been that a few stored procedures have stopped working, for the reason already explained. Fixing any such issues has been a simple matter and I wouid be interested to hear of any other such issues that I should look for.

    R Campbell

    Monday, February 19, 2018 7:35 PM