|
Is there any way to exclude a specific table from replication (SQL Remote) (in a way to ignore the past and current changes on this table).
Is there any way to stop the subscription for a specific table? |
|
Perhaps you should seriously consider MobiLink. |
|
No, this is not possible. |
|
Before trying to think of a workaround for your problem, I'd like to understand it. If the table is intended to be no longer replicated, but still locally used, you're violating the idea of occasionally connected replication by creating a dependency upon the time an operation was replicated. HTH I am not thinking of a workaround. I am trying to find some already existing statement to solve an already happened error in DB.
(03 May '19, 08:19)
Baron
Replies hidden
The goal is not finding a way how I can ignore the changes on the receiver, but how can I tell the sender to ignore these changes. So I dont care anymore about the consistency/correctnes of the information on this specific table, but I am thinking of the health of my PC because it does now nothing rather than allocating resources for DBREMOTE which in turns does nothing rather than collecting garbages in DB.
(03 May '19, 08:51)
Baron
Ok, I understand your problem. And, trusting Reg's answer, I'm afraid your only options are to go through it or to stop and restart replication. BTW, are you using the default message size? Depending on message type and infrastructure, you're probably better off by massively (1 or 2 orders of magnitude) increasing it.
(03 May '19, 14:02)
Volker DB-TecKy
Stop and restart replication will from one side solve this particular problem, but from the other side, will mean that I should go in all the 100 remotes and search for the necessary changes, and then apply them manually on Cons.!
(06 May '19, 03:35)
Baron
Have you considered to re-extract the remotes after the publication has been changed?
(06 May '19, 06:01)
Volker Barth
|
Well, I wildly guess the following approach could give you what you want:
However, that would overwrite all rows on all published tables in all remotes, so modifications in the remotes after their last successful replication would be lost.
As for each change in a SQL Remote setup, I would very strongly recommend to test that in depth in a test environment. And dropping a table from a publication is certainly a major change.
This is a very true statement. What is in the transaction log and belongs to a particular publication when the according transaction takes place, will be sent by SQL Remote. It does not matter whether the publication is altered afterwards. Sometimes it's helpful to study the "DBTRAN -sr" output of transaction log to understand how operations are "marked" for replication.
what you mean exactly with 1. drop the table from the publication?
Do you mean ALTER PUBLICATION pub1 DROP TABLE table1?
But this will still result in that the pending changes will be replicated, and what I want is to avoid replicating the pending changes!
Is there any difference between:
ALTER PUBLICATION pub1 DROP TABLE table1
ALTER PUBLICATION pub1 DELETE TABLE table1
I assume (but have not tested!!!) that the synchronize subscription statement will skip the pending changes. To cite the docs:
The "DELETE TABLE" or "DROP TABLE" clauses of the ALTER PUBLICATION statements are synonyms.
I cant execute SYNCHRONIZE SUBSCRIPTION because I want to exclude the table on remote side not on cons. side!
SYNCHRONIZE SUBSCRIPTION is only allowed on cons. Am I right?
Ah, I see. - Well, technically, I'm not sure whether it is only allowed on the cons (subscriptions are generelly bi-directional with SQL Remote, and AFAIK PASSTHROUGH MODE can be started by remotes, too, although I guess that is very uncommon, and I remember that I was really surprised to learn that...) but of course I would not recommend that.
Publications are usually "mirrored" between cons and remotes, so what exactly is your goal?
The goal is: I have about 100 remotes trying to replicate unnecessary update statements to cons. These unnecessary statements are several millions on each remote, and I know they came because of an error in our application (caused some echo updates), so I want to stop those updates from being 'unnecessarly' sent to Cons.
Of course between those unnecessary updates there are necessary updates regarding other tables, and so I cant say REMOTE RESET.
I tried alter publication delete table, but this considers only the upcoming updates, and the millions of old updates are trying since several days to be sent to cons.
Maybe the only solution to wait some days, and then take those millions of updates on cons. and throw them in bin.
As Volker points out, if it's in the transaction log, it replicates, so there is nothing you can do at the time you run dbremote if a operation is already in the transaction log.
Perhaps you could get the behaviour you are looking for by adding a WHERE clause to the table in the publication.
CREATE PUBLICATION p1 ( TABLE t1, TABLE t2(pk,c1,c2) WHERE replicate=1, TABLE t3 );
If you later decide there are rows in t2 you don't want to send, set replicate to zero, and deletes will sent rows for rows where replcaite changes from 1 to another value.
If your goal is to prevent operations from being sent after they are put into the transaction log (I suspect it is), I'll refer you back to the first point in my post. If your goal is to limit which rows in a table will replicate to remote users, the WHERE clause on the publication might be what you're looking for.
Reg
unless you do a re-extract or re-synchronize, or am I mistaken?
Reg, can you clarify whether a SYNCHRONIZATION SUBSCRIPTION statement skips "pending changes" for the according subscription within the log or not?
It does not.
When you execute a SYNCHRONIZE SUBSCRIPTION, it simply places a marker in the transaction log. When SQL Remote scans this entry, it initiates a process to generate messages that truncate all the tables and then generate inserts for all the rows that currently exist. SQL Remote still needs to scan and generate messages for all the entries in the transaction log before the SYNCHRONIZE SUBSCRIPTION.
Dont you think that the other statement is also very true? With: STOP SUBSCRIPTION TO pub1 FOR remote1; This ignores all changes (i.e. ignores even the changes before executing this statement)