Distinguishing Cons. and Remote in DBREMOTE

[Baron]

Through the answer from @Reg Domaratzki to my Question, I came to another question!

What are the main differences between Remote and Consolidate databases in case of DBRemote, except the fact that the Consolidate is also responsible for resolving conflicts!

Can we say that in case of Remote, then DBRemote must:

• send the before and after values with each update statement
• and may not apply any resolve conflicts (even if they exist in Remote)

And in case of Consolidate, then DBRemote must:

• consider all the before, after and current values, and apply the triggers in order to resolve any conflict

One more question, how can DBREMOTE decide whether this is a consolidate database?

In case of Remote, it is clear:

if exists (select 1 from sysremoteusers where consolidate = 'Y') then --Remote

select 'I am a remote because my rmote is cons., Correct!!!';

end if;

But in case of Cons., this can't work, having that we are using a multitier installation, in which the consolidate is in turn a remote for another consolidate:

if NOT exists (select 1 from sysremoteusers where consolidate = 'Y') then --Cons.

select 'I am Cons. but you couldn't recognize me because I am Remote for another Remote too, False!!!';

end if;

[regdomaratzki · Accepted solution]

My previous answer assumed that you were only using a two-tier SQL Remote system and you were trying to find the single database that was the consolidated database in a two-tier system.

When you have a multi-tiered SQL Remote system, the question isn't "am I consolidated database", it's more along the lines of "I am applying messages for user X. Am I the consolidated for this database, or am I a remote user of this database?".

Key points :

1. SQL Remote ALWAYS puts the before and after values for an update in the message.
2. Conflict resolution (either default conflict resolution or the firing of RESOLVE triggers) ONLY occurs when applying messages from remote users that are defined with GRANT REMOTE as opposed to GRANT CONSOLIDATE. In other words, conflict resolution only occurs when applying messages from remote users for which this database is the consolidated database.

So, the exitance of rows in the SYSREMOTEUSER table where consolidate='N' means the database is a consolidated database, but not necessarily THE consolidated database (i.e. the database at the top of the hierarchy).

Reg

[Baron · Accepted solution]

Thank you very much for the explanation.

I can say now, when DBREMOTE generates messages it flags them whether they are generated of a remote, so that applying this message may try to resolve any caused conflict, whereas if it is not flagged (it is generated not from a remote), then the message should be applied as it is.

Yes it was my mistake to take your previous query as it is (as I was concentrating on my main problem), but @Volker Barth solved it (as usual).

[VolkerBarth · Accepted solution]

I can say now, when DBREMOTE generates messages it flags...

In my understanding, it's not DBREMOTE that "flags" messages when generating them, it's DBREMOTE when applying messages that knows whether it operates as consolidated or not for a particular message and therefore knows whether it has to apply conflict resolution.

[Baron · Accepted solution]

OK, clear!!

[VolkerBarth]

Hm, apparently a database is a SQL Remote consolidated if the query for the contrary value returns at least one rows...:)

select 1 from sysremoteusers where consolidate = 'N'

[Baron]

Oh, you have right!!

And what you think for the begin of the question? Can we say that the distinguishing between Cons. and Remote is only for the sake of resolving conflicts?

In other words, can I define the statement grant remote to user on both Remote and Cons. in case we know that no any conflicts will arise?Actually I found that this was the case in some old installtion in which the replication was so far functioning!

[VolkerBarth]

Hm, in our case the roles of consolidated and remotes have always been clear (and we have never used multi-tier setups with SQL Remote) - headquarter vs. remotes offices/sales reps, so the question what remotes MUST do compared to consolidated has never been an actual question - simply because the organizational roles are different. Remotes have always just contained subsets of the data the consolidated contains, usually via subscriptions with particular values. And of course there are some functions only the consolidated can do, such as resolving conflicts or creating new remotes/extracting data for remotes. - But as stated, in our case the roles are so different that there is no question what makes them (technically) different...

[regdomaratzki]

Can we say that the distinguishing between Cons. and Remote is only for the sake of resolving conflicts?

Yes. See my answer for more details.

In other words, can I define the statement grant remote to user on both Remote and Cons. in case we know that no any conflicts will arise? Actually I found that this was the case in some old installation in which the replication was so far functioning!

While I don't remember the details, I recall that if both sides are doing conflict resolution (because you used GRANT REMOTE when you should have used GRANT CONSOLIDATE), it is possible to get in a loop where conflicts never resolve. You'll never see errors, but the data involved in the conflict will be constantly changing, because the conflict never resolves.

Reg