none
Schema-only Replication Problem

    Question

  • Hi all,

    The background = sql2014 publisher, merge replication , 500 SQL Express 2014 subscribers (user laptops).

    One publication with roughly 150 articles. The subscription is filtered dynamically by the login of the subscriber, which together with merge filters defines the data partition for each subscriber.

    One of the articles includes document data in a filestream column. The requirement is to take this article out of merege replication and have it managed by an alternative mechanism, taking an 'eventual consistency' approach.  However we would like to continue managing the schema via merge replication.

    I have come across a few posts here and elsewhere that briefly discuss schema-only replication, and based on those I tried :

    1) drop the article in question (which also drops a mergefilter definition to this article from its parent table in the hierarchy)
    2) readd the article with @subset_filterclase = '0=1' (no rows will qualify at the publisher) and set @subscriber_upload_options = 1 (rows can be changed at subscriber but aren't uploaded)
    3) run static snapshot job and sync

    There were additional considerations to deal with identity columns and their range management (basically setting the seed to the highest in the system at all nodes)

    This seemed to work well initially, but we exposed a problem that occurred when data was moved from one data partition to another, triggering an error :
    "Could not find stored procedure 'dbo.MSmerge_expand_sp_4013124408D64B36AA2A6B2E04924D98'"

    This missing procedure is actually for the parent table my new schema-only table. I suspect it's a 'precompute' SP.
    It seems that in making my config changes, the static snapshot job decides to remove that SP (that's when it disappears), but other 'cogs in the system' seem to expect it to still exist.

    So my question is whether it is possible to use merge replication to deliver a schema-only article, if so what am I missing in my actions, what are the definitive steps.
    Or is it a case that merge rep is simply not designed to do this.

    All insights , suggestions welcome;  thanks for reading and for any help!
    BTTF

    Friday, October 19, 2018 10:07 AM

Answers

  • I would use the post snapshot script to deploy it. You can use sp_addscriptexec to deploy schema changes.

    Schema changes in merge publications, especially with large numbers of subscribers are often problematic. Removing this article from your merge publication will make them less problematic.

    Friday, October 19, 2018 12:36 PM
    Moderator

All replies

  • I am struggling to try to understand what you are trying to do.

    It sounds like you want to deploy a table without its data to all subscribers. Data entered on the publisher stays on the publisher and does not move to any subscriber. Data on the subscriber stays on the subscriber and does not move to the publisher.

    It further sounds like this article is somehow part of a filter. Is it a leaf level article or higher up the hierarchy.

    If it is a leaf level article, I would simply make this article/table part of a pre-snapshot or post-snapshot script which will just deploy the schema to all subscribers. Then all you need to worry about is handling schema changes - which could be done via sp_addscriptexec.

    Now if it is somewhere in the middle of your hierarchy you are making filtering decisions based on data which will be present on the publisher but not on the subscriber. I would guess that keep partition changes here would be your friend.

    But it is hard to understand exactly what you are doing based on what you have described.

    Friday, October 19, 2018 12:10 PM
    Moderator
  • Hi Hilary, Thanks for your reply.

    "...you want to deploy a table without its data to all subscribers. Data entered on the publisher stays on the publisher and does not move to any subscriber. Data on the subscriber stays on the subscriber and does not move to the publisher."   That's exactly it. 

    Difficult to explain succinctly what we are trying to do, but ultimately it relates to getting filestream out of the publisher DB and pout of merge rep.

    The article in question is currently a leaf level article, and I had thought that removing the article and adding it with the 0=1 filter, and without adding a mergefilter for it, would accomplish my goal.  But, as I say this seems to result in an inconsistency regarding the 'expand' procedure.

    I liked the idea of having schema changes handled elegantly by sync, but if I can't get it to work , the postscript/scriptexec approach might be next best thing. 

    Have also seen the idea of putting the article in another publication and using the /NoBcpData switch , but not investigated in any detail yet if that is a viable option also ?

    BTTF

    Friday, October 19, 2018 12:29 PM
  • I would use the post snapshot script to deploy it. You can use sp_addscriptexec to deploy schema changes.

    Schema changes in merge publications, especially with large numbers of subscribers are often problematic. Removing this article from your merge publication will make them less problematic.

    Friday, October 19, 2018 12:36 PM
    Moderator
  • Thanks for your input, Hilary.

    We are going down the post-snapshot/scriptexec route as suggested.

    regards!

    Monday, October 22, 2018 3:26 PM