SQL Anywhere ultralite - Tricky replication problem in disaster recovery situation

Author

Tricky replication problem in disaster recovery situation

Tom

2005-09-05, 1:26 pm

I currently test the following situation using UltraLite 9 and an ASA 9
consolidated database:

Synchronisations are made between a UL database and an ASA database
Backups of the PDA are also made
For whatever reasons a user needs to make a hard-reset of the PDA

Situation:
==========

The user made a PDA backup at day X. (e.g. once a week)
The user made a replication at day X + 1 (daily)
The user made a hard-reset at day X + 2.

The user restored the PDA backup (eg from a SD card) and continues
working on the PDA. This means the user adds records to the UL database.
Back in the office the user makes a synch. This sync will ALWAYS fail
due to primary key conflicts.

All tables which are parts of publications use global autoincrement PKs.

Problem:
========

If the user adds records to a database, which was restored from a backup
made BEFORE a synch was made, the PKs of new records in the UL database
may have the same values as records on the database. Sample:

PK of table MyTable is 60 at the moment of the backup. The user adds
additional records and makes a synch with the server. The last PK of
MyTable is now 70. The PDA needs a hard-reset or has lost power (equal
to hard reset) and the user stores the PDA backup. The last PK of table
MyTable is again 60. The user addes one record which gets the PK 61. Now
the user performs a synch. Since the last synch was made AFTER the
backup the last ID (PK) on the ASA server is 70. The synchronisation
works on the mobilink side but fails with error -839 on the UltraLite
since there are duplicate Primary keys.

I set the following ul_synch_info properties:

pConnection->InitSynchInfo( &info );
info.user_name = (wchar_t*)MobiLinkUs
erName.data();
info.password = (wchar_t*)MobiLinkPa
ssword.data();
info.download_only = ul_true; // only download from server
info.observer = SynchObserverFunc;
info.publication = m_pConnection->GetPublicationMask
(ULValue(L"mTrackerULPub"));
info.disable_concurrency = ul_false ;

I tried the following before calling InitSynchInfo():

pConnection-> ResetLastDownloadTim
e( pConnection->GetPublicationMask(
ULValue(L"mTrackerULPub")) );

but unfortunately without success.

Is there any way to solve these kind of conflicts?

I must say that I experienced this situation during a test phase while
developing the software because the battery of my old iPAQ 5550 does not
survive a weekend without external power! So on monday all my data was lost!

Any hints would be greatly appreciated!

Thomas

Usmanov A.

2005-09-06, 3:23 am

Hi Tom.

In my application this problem partially solved:
1) Backup makes after each synchronization.
2) After restore from backup user must sync with Remote (before add new
data)
3) During synchronization Remote Database restore numeration for uploaded
tables PK (My uploaded tables have non-autoincrement PK)
4) User goes on work.
Shortcoming of this schema: User must successfully sync before work,
unfortunately they offer forget about this and then sync fails with
error -839

--

Alex Usmanov.

Greg Fenton

2005-09-06, 1:23 pm

Usmanov A. wrote:
> Shortcoming of this schema: User must successfully sync before work,
> unfortunately they offer forget about this and then sync fails with
> error -839

Some customers are able to enforce this. The "restore" capability is
built into their application (users do not copy files manually) and the
application sets a flag indicating that no update can occur until after
a sync. After each sync, they unset the flag.

It is a bit of work, but it solves the problem.

greg.fenton
--
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/

Tom

2005-09-07, 7:23 am

Thank you Guys for your feedback. Looks like no out of the box solution
of this problem is available. The data stored in my database is GPS data
and thats arriving as soon as the receiver is on.

I will find a 'programmatic' solution, since something else seems not to
be available.

Cheers

Tom

Greg Fenton wrote:
> Usmanov A. wrote:
>
>
>
> Some customers are able to enforce this. The "restore" capability is
> built into their application (users do not copy files manually) and the
> application sets a flag indicating that no update can occur until after
> a sync. After each sync, they unset the flag.
>
> It is a bit of work, but it solves the problem.
>
> greg.fenton

Sponsored Links