|
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) |
|
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.
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 thank you for the clear and detailed answer.
(12 Mar '20, 12:01)
Baron
Replies hidden
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:
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');
(18 Mar '20, 05:51)
Baron
1
And that DELETE does not get replicated back to the cons...?
(18 Mar '20, 05:58)
Volker Barth
1
No, the table myprices has one directional replication (only cons. -> remote)
(18 Mar '20, 06:36)
Baron
|
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...).
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
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.