exclude transactions of a single connection in Replication

[Baron]

Can somebody advise me for such a problem?

A remote Database (sql Anywhere 17) replicating to another cons. DB, has two connections A & B. for some reasons we need to exclude the transactions of connection B on this remote DB from being replicated to the cons., whereas we need to keep transactions of connection A to replicate to cons.

The simplest way is to add an extra column in each replicating table, and let each connection write its own value, so that the publication can distinguish where the trnasactions are coming from. This would not help for our case.

What we need is to use alter publication P1 delete Table T1 which affects only the transactions of Connection B (without affecting Connection A).

[regdomaratzki]

Both Volker and I have assumed that when you say "replication" that you mean you are using SQL Remote, as opposed to synchronizing with dbmlsync and MobiLink.

Can you please confirm that assumption correct?

Thanks, Reg

[VolkerBarth]

Well, this assumption is also based on the "dbremote" tag in my case...

[VolkerBarth · Accepted solution]

How do you distinguish these transactions? Are they done by different users? And are these "differences" noticeable in the data?

AFAIK, SQL Remote does replicate all transactions of all published articles, no matter what user/transaction has run those. However, in case you can somehow distinguish the transactions you might be able to add logic at the cons (say, by using INSERT/UPDATE/DELETE triggers) that could handle incoming data differently - say, by ignoring insert/updates for a certain user. - Note, that's just a very wild guess, and I would suspect that this could easily lead to data inconsistency...

That being said, an easier approach would be to add a "DoNotReplicate" flag in the according table(s) and use an article with an according WHERE DoNotReplicate = 0 clause or the like. Even here, you need to assure that no foreign key relationships are broken for published data.

[Baron · Accepted solution]

I can define a new user, and can always execute those "unwanted" transactions in the name of this user.

It is difficult to handle it on the cons. because all those "unwanted" transactions are delete transactions.

My explanation of the problem above was not complete, so all the transactions coming of Connection B (unwanted transactions) are delete transactions, and they are executed from a daily triggered event (different connection)

[VolkerBarth · Accepted solution]

Hm, what's the actual requirement? Are you locally deleting data on the remote(s) that should stay permanently at the cons?

[Baron · Accepted solution]

Yes, almost. But can't simply go on Remote and say ignore all delete statements coming from remotes, since some of them are from Connection A and should be deleted on Cons.

[VolkerBarth · Accepted solution]

A very different approach: Could you decide which rows to delete from within the consolidated?

Because you could use a PASSTHROUGH ONLY session to send delete statements to the remote that would only delete there "locally", i.e. without being replicated back to the cons.

That could be either via an explicit list of rows to delete or say, by calling a stored procedure that would then delete the according rows.

So basically you would trigger your event not locally but via the cons.

[Baron · Accepted solution]

This is a super good idea, despite it changes my whole plan, but never thought of it.

Thank you.

[Baron · Accepted solution]

One more question, what is the best way to identify the Cons. database?

Usually in Cons. there are >1 rows in SYSREMOTEUSERS but, we have some installations with only one remote 😞

[regdomaratzki · Accepted solution]

At the consolidated database, you execute the GRANT REMOTE command, but at the remote database, you execute GRANT CONSOLIDATE, to identify the database immediately above the current database in a SQL Remote hierarchy.

Find the database where the SQL below returns zero rows to find the consolidated database

select count(*) from SYS.SYSREMOTEUSER where consolidate='Y'

Reg

[Baron · Accepted solution]

and in case of mobilink, how can I tell that the Database is a consolidate?

if exists select 1 from ml_subscription

Or, is there another way?

[VolkerBarth]

Baron asked:

and in case of mobilink, how can I tell that the Database is a consolidate? if exists select 1 from ml_subscription Or, is there another way?

If you are using MobiLink and want to delete data on a remote database without synchronizing these deletes, it's way easier: You can embrace the delete statement with a pair of STOP SYNCHRONIZATION DELETE and START SYNCHRONIZATION DELETE statements, see the sample here.

This has only effect on the current transaction, so it might just be exactly what you are looking for - in case you actually are using MobiLink and not SQL Remote.

[Baron]

Thanks a lot.

We are using both mobilink and SQL Remote, and sometimes standalone installations.

[Baron]

According to the documentation I can understand that after executing STOP SYNCHRONIZATION DELETE all delete statements will not be logged (in transaction log files), and so they would not be sent to cons.

I did a small test:

1. STOP SYNCHRONIZATION DELETE
2. delete from table1

Then I translated the transaction log file, and I still see the delete statement in it! Do I understand it false?

[regdomaratzki]

The deletes are still logged into the transaction log, as they will be needed in case of recovery, but they are ignored by dbmlsync when scanning the transaction log and will not be uploaded.

[Baron]

Then I would say the explanation in the documentation is misleading:

"You can temporarily suspend automatic logging of delete operations using the STOP SYNCHRONIZATION DELETE statement"

[VolkerBarth]

That's why it is called "DocCommentXchange" - feel free to leave a clarifying comment there...