alter publication (subscription-value) SQL Remote

[Baron]

Is there a way to update the subscribe_by column in table SYSSUBSCRIPTION/S?

We are doing some changes in the infrastructure naming, so that I need to update the subscribe-by value for each remote on the Cons.

I could not find a statement like alter subscription in the docs.

If this is not possible, should I then use drop subscription + create subscription statements instead? I just wanted to make sure, that after these two statements the SQL-Remote offsets will stay healthy (I mean no side effects on SYSREMOTEUSERS)

[VolkerBarth]

Given Reg's answer, what are you trying to achieve? Not knowing that I wonder whether you want to temporarily remove data from remotes (by modifying the "subscription channel") and to later add them back? If so, territory realignment methods may help (unless they also fail based on the reserved words colum name issues...).

[Baron]

Remote DBs were grouped (according to market names), and the tables in question are price tables which are also grouped according according to market names, so the subscribe_by values are abbreviations of market names.

Now due to administrative changes the market names (and its abbreviations) are changed PERMANENTLY, and I need to take these changes in SYSSUBSCRIPTIONS table (so I will not change them later back).

Could you please tell me what you mean with TERRITORY REALIGNMENT METHODS?

BTW, my other question regarding reserved column names is another case and not relevant in this question.

Thanks

[VolkerBarth]

For territory realignment, see the docs - it's just a name for a common problem in any distributed database for the need to eventually change the "owner" (or responsible department etc.) for the relevant objects, so they have to be "moved" between different remote databases.

It's named after the need to "re-align" customers who have been assigned to, say, sales person S1 (using remote database R1) and are now assigned to S2 (using remote database R2), so customer data and any assigned entities have to be moved between remote databases.

---

That being said, your case is apparently different.

[regdomaratzki · Accepted solution]

There is no ALTER SUBSCRIPTION command. You would need to DROP, CREATE and START the subscription in question to change the subscribe by value. DO NOT STOP READING NOW.

These statements will have no effect on the rows in the SYSREMOTEUSER table, so you will likely be able to run dbremote again after executing these commands, and you will not likely get any errors about log offsets, BUT THAT IS 100% IRRELEVANT.

By executing these commands, and in particular changing the subscribe by value to a publication for a given subscription, you have created MASSIVELY CATASTROPHIC issues in the data integrity between the consolidated and the remote database.

1. The consolidated database now believes that the remote database has a different subset of data than currently exists in the remote database.
2. The DROP/CREATE/START SUBSCRIPTION commands will set the created and started log offsets in the SYSSUBSCRIPTION table for this subscription to the offsets when the CREATE and START SUBSCRIPTION command were executed. The confirm_sent value for the user in question in the SYSREMOTEUSER table will be less than started value in the SYSSUBSCRIPTION table, so any operations in the consolidated database's transaction log between these two values will NEVER BE SENT.

The FIRST thing you need to do after executing the DROP/CREATE/START SUBSCRIPTIONS commands is to either re-extract the remote user in question or execute a SYNCHRONIZE SUBSCRIPTION command (which isn't currently working for you) to make sure that the data between the consolidated and remote database are in synch.

These types of things you are asking about are things that should be verified in your test environment before being applied to your production environment.

Reg

[Baron · Accepted solution]

thank you for the clear and detailed answer.

1. Yes, the remote database should have now different subset of data, and this is the reason, why I wanted to change the subscribe_by value on Cons.

2. Thanks for the hint. But as long as I issue SYNCHRONIZE SUBSCRIPTION Command, then this will not be a problem, because it will delete the tables in question on each remote, and then will fill those tables on each remote with the correct subset of data (the new subscribe_by values will decide the subset for each romte).

[Baron · Accepted solution]

After following the steps (drop, create, start, synchronize subscription) I got not 100% correct results as expected.

The problem is that each remote has now double data rows as expected (i.e. the datarows from old subscribe_by value AND the datarows from the new subscribe_by value, here in my case the subscribe_by is ZIP)!!

This is because the SYNCHRONIZE SUBSCRIPTION statement on Cons. does the following on Remote:

1. delete the table in question where.....
2. synchronise the table with new rows

The problem here is with number 1 above, since here the delete statement is conditioned (delete from myprices where zip = 'new_zip_value').

Anyway, I solved it later as I delete all prices not belonging to current zip on each remote (delete from myprices where zip <> 'new_zip_value');

[VolkerBarth · Accepted solution]

I delete all prices not belonging to current zip on each remote

And that DELETE does not get replicated back to the cons...?

[Baron · Accepted solution]

No, the table myprices has one directional replication (only cons. -> remote)