Question Re: manipulating sysremoteusers.confirm_received in Technology Q&A

manipulating sysremoteusers.confirm_received

Baron — Tue, 17 Dec 2019 05:04:01 GMT

I have an installation of 2 DBs replicating using DBRemote.

I lost the productive Cons. DB, but I have a backup of before 2 days (so now Remote DB. is dated 10.12.2019 and Cons. DB is dated 08.12.2019).

Now I want to use this 2 days old Cons. DB, but it misses all the transactions of Remote DB since 2 days (between 08.12.2019 - 10.12.2019).

What can I do in order to enforce the remote DB to resend all the transactions up 08.12.2012 (knowing that the Remote DB. was replicating against the lost Cons. till 10.12.2019, and knowing that the transaction logs of Remote DB still has the transactions of more than 2 days ago)?

I tried to manipulate the SYSREMOTEUSERS: I changed log_send, log_sent, confirm_sent values on Remote DB and set it to the values of log_receieved, confirm_received on the backup Cons. DB (i.e. -2 days), and, additionally I changed the log_received, confirm_received values on Remote DB and set it to the values of log_send, log_sent, confirm_sent on the backup Cons.

When I read the table SYSREMOTEUSERS then I see all the values are correct as I want, and when I start DBREMOTE then I see that the remote DB is generating the wanted packets (between 8.12.2019 - 10.12.2019) (The amount of generated packets tell me that they contain 2 days transactions), but the problem is that DBREMOTE additionaly (and unintendently) changes the value of confirm_received (on Remote DB) to the value of 10.12.2019!

In other words, after starting DBREMOTE the value of confirm_received is set back to the last value!! and I dont know from where it could remember this offset (having that I have overritten SYSREMOTEUSERS)

As a result, the Cons. DB does not accept those generated packets, because the confirmation offset is bigger than its current offset (the error message says, packets dont belong me!!).

I know that manipulating SYSREMOTEUSERS is not documented, but how is considered to solve such situations? (Replicated against backed up cons.).

Re: manipulating sysremoteusers.confirm_received

VolkerBarth — Tue, 17 Dec 2019 06:57:05 GMT

The need to restore the consolidated from an old backup is always a severe problem with SQL Remote and usually will require that the remotes that have replicated after the cons's backup was taken need to be re-extracted. At least that's the way to go unless you want to mess around with SYSREMOTEUSER settings... - That's why the docs tell the consolidated logs are crucial in a SQL Remote setup...

Before you re-extract those remotes, you could usually try to get the active translog from the remotes and DBTRANslate it to get changes that have been made there after the last replication with the cons (as of its backup) has taken place, and check the according statements and apply them at the cons, if published data is affected. That way, one would hope that remotes could be re-extracted based on their current data.

Re: manipulating sysremoteusers.confirm_received

Baron — Tue, 17 Dec 2019 07:05:12 GMT

Translating the transaction logs of Remotes and apply them on Cons. is not that easy, because some transactions from Remotes are not applied 1:1 on the Cons. (Conflict triggers, and another triggers written for our special cases, that distinguishes whether the transcation is being applied over replication or as local).

My question is, when I change the value of confirm_received & log_received, and later I can see it changed in SYSREMOTEUSERS, how can DBREMOTE ignore my changes on confirm_received and bring it again to its last value? How can i prevent this happening?

Re: manipulating sysremoteusers.confirm_received

VolkerBarth — Tue, 17 Dec 2019 07:39:53 GMT

That's probably a question for @Reg... - I can't tell that, except to ask whether you "cleaned" the message stores so no unwanted messages from before the cons's crash are sitting somewhere?

Re: manipulating sysremoteusers.confirm_received

VolkerBarth — Tue, 17 Dec 2019 07:43:11 GMT

Translating the transaction logs of Remotes and apply them on Cons. is not that easy.

Nobody said it would be... Therefore log backup is so crucial for consolidated databases. Are there no incremental log backups for the last two days? (I'm aware that it would still be a problem if the remote has replicated after the last log backup and has no backup itself, but at least the amount of data to be salvaged from the remote's log should be much smaller.)

Re: manipulating sysremoteusers.confirm_received

Baron — Tue, 17 Dec 2019 07:51:46 GMT

Do you mean whether during starting DBREMOTE some messages are read inside Remote (from Cons.).

Here I can say no, the inbox folder for the Remote (select publisher_address from sysremotetype where type_name = 'file') was empty!

Re: manipulating sysremoteusers.confirm_received

Baron — Tue, 17 Dec 2019 07:53:59 GMT

Moreover, after starting DBREMOTE, only the value of confirm_received was changed, and the value of log_received was still the same as my manipulation.

So, the value of confirm_received is restored from somewhere in DB (ISYSREMOTEUSER?)

Re: manipulating sysremoteusers.confirm_received

VolkerBarth — Tue, 17 Dec 2019 08:27:58 GMT

Well, I meant inboxes for both databases (and "outboxes", if these are different folders, aka if the inbox of the remote is not also the outbox of the cons).

Re: manipulating sysremoteusers.confirm_received

regdomaratzki — Tue, 17 Dec 2019 08:35:27 GMT

In other words, after starting DBREMOTE the value of confirm_received is set back to the last value!! and I dont know from where it could remember this offset (having that I have overritten SYSREMOTEUSERS)

Were there messages in the message system applied by the remote database when you ran dbremote?

Reg

Re: manipulating sysremoteusers.confirm_received

Baron — Tue, 17 Dec 2019 08:37:21 GMT

No, moreover, I started DBREmote with -S (only send messages)! to make sure that no messages will be applied, but only send messages.

Re: manipulating sysremoteusers.confirm_received

Baron — Thu, 19 Dec 2019 05:27:32 GMT

I do the following:

execute remote reset cons_user (on Remote).
Then I have sysremoteusers.log_received=0 && sysremoteusers.confirm_received=0 && syssubscriptions.created = syssubscriptions.started = current offset of DB.
I change the values of syssubscriptions.created && syssubscriptions.started to its same values before executing step 1.
I change the values of sysremoteusers.log_sent = sysremoteusers.confirm_sent=X, where X is my desired values (the last offset replicated to the backed up Cons.)
Now Sysremoteusers has the values which I want (log_sent =confirm_sent=X; log_send = log_received = confirm_received = send_count = resend_count = receive_count = rereceive_count = 0; time_received = time_sent = null).
Now I start DBREMOTE with -S, and then it generates the messages starting from X (exactly as I want), but the only problem is that now sysremoteusers.confirm_received is changed to the value before executing step 1, and of course this value is written als header in the messages, so that the remote will reject those messages!

Could you please help anyhow? @reg

Re: manipulating sysremoteusers.confirm_received

Baron — Thu, 19 Dec 2019 05:33:44 GMT

Actually I did more, so I unloaded the database after STEP 5, and I started looking for the value of sysremoteusers.confirm_received, to see where is this value saved in DB, but I didnt find it neither in the unload.sql nor in the .dat files?

So for me is a something mysterious and very magic!!

Re: manipulating sysremoteusers.confirm_received

VolkerBarth — Thu, 19 Dec 2019 05:40:34 GMT

Hm, if you do not understand those values and their calculation and meaning, then I doubt it is a good idea to use undocumented functions to manipulate them... (Note, I do not claim to understand them fully, either...)

Re: manipulating sysremoteusers.confirm_received

Baron — Thu, 19 Dec 2019 05:48:29 GMT

I work with SQL Remote since more than 2 years, and I think I understand the meaning of the mentioned fields but I was always watching the values to check the health of the Replication, but this is the first time I manipulate its values (I must do it to restore my system)

Re: manipulating sysremoteusers.confirm_received

VolkerBarth — Thu, 19 Dec 2019 06:17:58 GMT

That certainly should be contained in the reload.sql file in the context of the remote user creation...

Re: manipulating sysremoteusers.confirm_received

VolkerBarth — Thu, 19 Dec 2019 06:47:51 GMT

I can't tell whether Reg has more support to offer, but in my understanding the settings of the remote's SYSREMOTEUSER table should mirror those of the consolidated's backup (with mirrored roles, apparently), particularly if you want to have the statements in the remote that have been made after the last replication before the consolidated's backup time to be replicated to the cons, again.

There should neither be a need to call REMOTE RESET nor to reload the remote.

Re: manipulating sysremoteusers.confirm_received

Baron — Thu, 19 Dec 2019 06:59:20 GMT

I swear that it is not contained in reload.sql in the Create SQL Remote definitions section.

Should I copy the whole section here?

Note, I am unloading the DB before Step 6 and after Step 5.

If you mean unloading the DB after Step 6, then yes, I should drop my swear.

Re: manipulating sysremoteusers.confirm_received

VolkerBarth — Thu, 19 Dec 2019 07:33:30 GMT

There should be a call to the undocumented function you are dealing with, at least that's my experience when using DBUNLOAD with according options... - But as stated, you should not need to reload the remote AFAICT.

Re: manipulating sysremoteusers.confirm_received

VolkerBarth — Thu, 19 Dec 2019 07:35:54 GMT

In addition, the database property "RemoteTrunc" might interfere with your manipulations.

Re: manipulating sysremoteusers.confirm_received

Baron — Thu, 19 Dec 2019 08:12:29 GMT

Yes, there is a call to the undocumented function, but in the value of '0' was passed to the confirm_receieved parameter. The parameters are exactly so:

( 108, 2217130948 , 2217130948,0,0,0,0,0,0);

Where, 108 is the User of Cons, 2217130948 is my X, and all other parameters are 0.

Once again, the problem is that when I run DBREMOTE then sysremoteusers.confirm_received gets the value of 19142614683, and the current offset on Cons (restored copy) is something like 19120000000 (i.e. < 19142614683), so that the Cons. will reject any messages coming from this (manipulated) Remote.