exclude a table from publication SQL REMOTE

[Baron]

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).

• With: ALTER PUBLICATION pub1 DELETE/DROP TABLE table1;
  This ignores only the changes after executing this statement (so the past changes will still be replicated).

• With: STOP SUBSCRIPTION TO pub1 FOR remote1;
  This ignores all changes as I want (i.e. ignores even the changes before executing this statement), but the problem here I cant define a specific table, but I should define a whole pbulication (but I want only one specific table)!!!

Is there any way to stop the subscription for a specific table?

[VolkerBarth]

Well, I wildly guess the following approach could give you what you want:

1. drop that table from the publication (both in the cons and via passthrough only mode within the remotes)
2. synchronize subscription for all subscribers (or re-extract them...)

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.

[VolkerBarth]

This ignores only the changes after executing this statement (so the past changes will still be replicated).

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.

[Baron]

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

[VolkerBarth]

I assume (but have not tested!!!) that the synchronize subscription statement will skip the pending changes. To cite the docs:

After you complete significant maintenance to the consolidated database
For example, you make changes to the consolidated database, which updates every row in the database. By default, SQL Remote creates and sends update messages to each subscribed remote. These update messages could include the UPDATE, DELETE, and INSERT statements for each row.

If you chose to synchronize the subscription using a SYNCHRONIZE SUBSCRIPTION statement, you only send the statements required to delete all the rows in the subscribed tables and the INSERT statements to insert all new rows.

The "DELETE TABLE" or "DROP TABLE" clauses of the ALTER PUBLICATION statements are synonyms.

[Baron]

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?

[VolkerBarth]

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?

[Baron]

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.

[regdomaratzki]

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

[VolkerBarth]

if it's in the transaction log, it replicates

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?

[regdomaratzki]

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.

[Baron]

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)

[Breck_Carter · Accepted solution]

Perhaps you should seriously consider MobiLink.

[regdomaratzki]

No, this is not possible.

[former_SQLA_member1694885]

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.
Due to the way SQLRemote replication control is stored - inside the xact.log, together with the logged row operation(s) - I'm quite confident that this is not possible on SQLRemote level. What has been written to the xact.log must not be changed any more. If you manage to create a set of INSTEAD OF triggers against the table on each receiving side, that might do the job. But then, this also has to be arranged outside SQLRemote, and depending on how tightly or loosely the nodes are coupled. manually corrupting the continuity of replication messages by deleting the oldest message not received yet may buy you the time required to get this organized.
All these actions massively affect the consistency of replication (on a conceptual level, not necessarily on the level of your setup), so I guess you might be better off by letting the replication run dry and fix the results afterwards with e.g. passthrough operations.

HTH

[Baron]

I am not thinking of a workaround. I am trying to find some already existing statement to solve an already happened error in DB.

[Baron]

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.

[former_SQLA_member1694885]

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.

[Baron]

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.!

[VolkerBarth]

Have you considered to re-extract the remotes after the publication has been changed?