0

Within SQL Server's merge replication, I know how to make publication's article download-only (either with update restrictions on subscriber database or not) - @subscriber_upload_options parameter in sp_addmergearticle, or corresponding choices in the SSMS's GUI.

What I would like to achieve though is the reverse behavior on some articles. So that records that originate on the subscriber's side travel up to the publisher during syncronization, but not the other way around. And that updates made on the publisher are not sync'ed back to the subscriber, while updates made on subscriber do go up to the publisher. I.e. exactly reverse of the download-only articles.

Is this possible to achieve? Either with "standard" configuration options of the replication or with some manual hacks?

Improve this question
0

2 Answers 2

Reset to default
1

You can add a filter like: 0 = 1, and the article becomes upload only

Improve this answer
2
  • Tried this just now and looks like such setup deletes records from the Subscriber at the next synchronization instead of just leaving them alone. Which kind of makes sense, too, if I think about it: the table takes part in the replication, but the restrictive filter makes the Publisher's side look empty - and then the syncronization needs to make Subscriber's side empty...
    – Yuri Makassiouk
    Commented May 9, 2019 at 10:36
  • Could it be that attaching a custom conflict resolver of type "Upload Only Conflict Resolver" does the trick? At first I thought that it would only be involved if the actual conflict arises, but looks like it just actually blocks all downloads (which is exactly what I'm after)
    – Yuri Makassiouk
    Commented May 9, 2019 at 10:56
0

You can use a combination of settings. The key issue is at the time of synchronization, that the default setting is to drop and recreate your articles.

You can stop that by editing these variables: 

@pre_creation_cmd = ‘none’

You can also force a delete or truncate, too.

Then, because the snapshot did not drop any values, you may have to ensure the structure is correct before synchronization.

You can do this via the sp_changemergearticle:

@creation_script = ‘<yourpathtocreationscript.sql>’

This should reference the scripts you should have automated from publisher when you created the merge publication. There is an option to add post steps to the publication, where you can script out the ETL needed to keep the values in place.

You can do this two ways for the sp_addmergepublication procedure:

@pre_snapshot_script = ] 'pre_snapshot_script'

Or 

@post_snapshot_script = ] 'post_snapshot_script'

The scripts (file location of your script) can be used to first store the subscribers data onto a temp table and then reinsert the data into the merge table.

at this point, it will be apparent you do not have to use the @pre_command_cmd parameter at all if you like. I am just showing you all the options you have.

Also, since this is a merge replication, if you want the subscriber to alter the data but not to upload this to the publisher, use this option in sp_addmergearticle:

@subscriber_upload_options = 1

and 2 to prevent any changes.

sp_addmergearticle - Microsoft Docs sp_addmergepublication

Improve this answer
2
  • Thank you for the answer. I was actually asking about the opposite scenario, i.e. the subscriber is able to change (or create) the data and upload them to the publisher, but not download the changes that take place on the publisher (and instead, overwrite them at next syncronization). For example, records "owned" by the subscriber's side application, into which publisher wants to look, but has no business changing. I think I did solve it by using the Uploads Only conflict resolver, which provides exactly the described behavior. You did make me think though about applying new snapshot.
    – Yuri Makassiouk
    Commented May 13, 2019 at 13:09
  • @YuriMakassiouk Oh, I see. I can edit the answer so it fits better. Nice!
    – clifton_h
    Commented May 13, 2019 at 13:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.