Hi,

I wrote an application in DBISam some time ago that had a head office
database (primary), and staff in the fields (slaves).

It was pretty basic. All tables except for Invoices were copied / dumped
from head office onto each laptop for lookup data everytime the database
was 'synchronized'

Each laptop had 'unique' invoice number range allocated to them. During
syncronisation, any record flagged as new was inserted from the laptop
into the admin database, and the new flag removed. (Those records were
then readonly on the laptops).

It was simple, but it worked.

Now I'm venturing into designing another application that's very
similar, but using EDB and possibly replication. As a complete beginner
to this, I'm not sure if there are any pitfalls / traps I should be
looking out for, but have a few questions I was hoping people could
answer and/or shout out warnings or suggestions if I'm heading in the
wrong direction.

I'm not even sure if I should be using replication, or just creating a
CSV file of the invoices that need to be exported, and import them at
the head office.

I've had a look through the ElevateDB Manual but couldn't find much on
Replication. I found more in the SQL manual which is what I'm working
through at the moment.

For the sake of the discussion, let's call head office 'admin'.

1) Unlike DBISam, I know I can't just copy the table files from admin to
the laptops. Am I correct in understanding that most activities should
be done from the admin database. (ie, by creating a remote store am I
just creating a 'store' on the Admin computer that each laptop connects
to, and then the laptop copies data from the remote store to itself?)

Initially I would have thought that the laptop would just connect to the
ADMIN database, and initiate a COPY from ADMIN.DB to LOCAL.DB but after
reading the manual I think my understanding of replication is way off,
so I'm a little confused here as to whether I'm looking at it all upside
down. (I do live in the southern hemisphere though )

2) Am I correct (in my situation) that only the laptops need to PUBLISH
the database to track changes?

3) In regards to the PUBLISH statement tracking changes - is there a way
to only track for particular tables, or only put changes for particular
tables back on ADMIN.

4) Is there a way to take a sneak peek at the changes in the EDBUpd
files if I want to see what's actually in there?

5) Should I be looking at replication at all for my situation, or is it
for something totally different?

Sorry - there will most likely be additional questions, but considering
this is new ground for me, I want to get my foundation right before I
start building a new app on top of it.

Thanks & Regards

Adam.

Adam,

did you see the sample application, which is good tutorial?:

http://www.elevatesoft.com/articles?action=view&category=edb&article=building_sales_quote_replication_system_elevatedb

In addition I posted some scripts that might be useful for you to study
here:

http://www.elevatesoft.com/forums?action=view&category=edb&id=edb_binaries&msg=44&page=1

Generally replication works great with EDB. Of course you can replicate
just the tables you want and need.
Most important is to avoid key violations by assigning different ranges
of Primary Keys (integer) for each computer or using GUID's.

Good luck...

Uli

Hi Uli,

> did you see the sample application, which is good tutorial?:
>
> http://www.elevatesoft.com/articles?action=view&category=edb&article=building_sales_quote_replication_system_elevatedb

No - I missed that. Thanks for pointing it out. I'll check that out, as
it seems to have more information than what was in the manual. Thanks!


> In addition I posted some scripts that might be useful for you to study
> here:
>
> http://www.elevatesoft.com/forums?action=view&category=edb&id=edb_binaries&msg=44&page=1

Excellent - thanks for that. Looks like I've got a lot of learning to do
- but it is exciting!

Cheers

Adam.

Adam,

<< I'm not even sure if I should be using replication, or just creating a
CSV file of the invoices that need to be exported, and import them at the
head office. >>

You could continue to do things the old way, of course, but using the
built-in replication may be easier.

<< 1) Unlike DBISam, I know I can't just copy the table files from admin to
the laptops. Am I correct in understanding that most activities should be
done from the admin database. (ie, by creating a remote store am I just
creating a 'store' on the Admin computer that each laptop connects to, and
then the laptop copies data from the remote store to itself?) >>

If the laptops only replicate invoice data to the admin (not the other way),
then your setup will be easy.  You'll just simply publish the invoice table
on each laptop, and then publish any tables that you want to replicate down
to the laptops on the admin.

The next steps are:

1) A job (or some other type of scheduled execution) on the admin that
executes a SAVE UPDATES TO.... statement on a regular basis to a local store
defined on the admin.  These update files will hang around for as long as
you need to keep them there.  I would create another job that deletes any
update files in the store that are older than X number of days, where X is a
safe number of days to ensure that all laptops have synchronized since that
time.  You'll also need a second store for incoming updates and another job
(or include this functionality in the same job) that iterates all incoming
update files and loads them, deleting them after a successful load (or
moving them into an archival store and then deleting them from the active
store).

2) Each laptop should have two local stores defined, one for loading updates
from the admin and one for saving invoice updates, as well as two remote
stores defined that point to the admin's local store where it saves all
update files (see 1) and the admin's local store where incoming invoice
update files are copied.  If you don't want to use direct connections to the
admin server for moving around the update files, then things get a little
more complicated because you need a way of easily retrieving the most recent
update files.

3) Each laptop should keep track of the timestamp of the last update file
that was loaded from the admin.  This can be retrieved from each update file
via this system information table:

http://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=Updates_Table

You want the "CreatedOn" column.  This value will be used when querying this
same table in the remote store on the admin.  All update files with a value
that is greater than the last loaded update file should be copied down to
the local store (the first in 2) on the laptop and loaded from there.  Once
each update file is successfully loaded, it should be deleted from the local
store.

4) The synch process is just a matter of having the laptop do a SAVE UPDATES
for the invoice data into a local store (the second in 2), copy the update
file to the remote admin store (the second in 1)

5) You probably want to keep all invoice update files generated by the
laptop in the local store (the second in 2) on the laptop.  That way you can
re-load the data on the admin side in the case of some disaster where all of
your data and backups are gone.  After that, you can kill whomever came up
with the disaster recovery plan on the admin side.  On a serious note,
though, it's good to keep them around for audit purposes since you can load
them into temporary tables and query them after the fact (CREATE TEMPORARY
TABLE...FROM UPDATES....).

So, you need:

Admin:   Incoming Updates Store (Local)
            Outgoing Updates Store (Local)
            Outgoing Updates Archival Store (Local)

            Incoming Updates Scanning Job
            Outgoing Updates Creation Job
            Outgoing Updates Cleanup/Archival Job

Laptop:  Incoming Updates Store (Local)
            Outgoing Updates Store (Local)
            Outgoing Updates Archival Store (Local)
            Admin Incoming Store (Remote)
            Admin Outgoing Store (Remote)

            One columns in a system table:  last update file loaded
(CreatedOn timestamp)

<< 2) Am I correct (in my situation) that only the laptops need to PUBLISH
the database to track changes? >>

Well, if you want to synch changes to the admin side, then you would want to
publish all relevant tables there also.

<< 3) In regards to the PUBLISH statement tracking changes - is there a way
to only track for particular tables, or only put changes for particular
tables back on ADMIN. >>

Sure, that's what the TABLES clause does:

http://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=PUBLISH_DATABASE

<< 4) Is there a way to take a sneak peek at the changes in the EDBUpd files
if I want to see what's actually in there? >>

Yep, as mentioned above, CREATE [TEMPORARY] TABLE ..... FROM UPDATES:

http://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=CREATE_TABLE

If you have any other questions, please let me know.

Tim Young
Elevate Software
www.elevatesoft.com

Hi Tim,

Thank you for the very detailed response. (Also - I wasn't expecting a
reply that late - isn't it some ridiculous time in the evening in your
part of the world?)

I do have a couple of follow up questions I hope won't take up as much
of your time...


> So, you need:
>
> Admin:   Incoming Updates Store (Local)
>              Outgoing Updates Store (Local)
>              Outgoing Updates Archival Store (Local)
>
>              Incoming Updates Scanning Job
>              Outgoing Updates Creation Job
>              Outgoing Updates Cleanup/Archival Job
>
> Laptop:  Incoming Updates Store (Local)
>              Outgoing Updates Store (Local)
>              Outgoing Updates Archival Store (Local)
>              Admin Incoming Store (Remote)
>              Admin Outgoing Store (Remote)
>
>              One columns in a system table:  last update file loaded
> (CreatedOn timestamp)

So, from this I take it one wouldn't allow the Laptops to connect direct
to the database on the sever for updating / adding the new invoice
records, but rather you copy the updated files between the computers? Or
is it also possible for the Laptop to connect to the Admin database on a
separate session component, and execute the update directly so no
scheduled tasks are required?

> << 2) Am I correct (in my situation) that only the laptops need to
> PUBLISH the database to track changes? >>
>
> Well, if you want to synch changes to the admin side, then you would
> want to publish all relevant tables there also.

I was thinking of just completely overwriting the other tables on the
Laptop with the latest data on the server. (Just a complete 'dump' so to
speak).

My thoughts were for the Laptop to connect directly to the Admin
database again, and just retrieve / dump all the data across (excluding
the invoice table) instead of using replication files for that procedure
- but I don't know whether there's any traps involved?

I figure this would eliminate the laptops needing to keep any timestamp
of the last update, or avoid a 'late' laptop missing out on some
information before those files are cleaned up.

I'm going to go through the sample application that Uli referenced to
try and get a better understanding before I proceed much further. It
seems as though manually doing this would be easier from my current
perspective, but I think that's only because I don't have an
understanding of data replication, and once I get my head around it -
it'll probably be much better to use the replication features you've
already designed.

Thanks again for your help!

Adam.

Adam

<< Thank you for the very detailed response. (Also - I wasn't expecting a
reply that late - isn't it some ridiculous time in the evening in your part
of the world?) >>

Yeah, I've been pulling late-nighters getting EDB and EWB to settle back
down after the two new releases, and working on EWB 2.x.  It's quiet at
night.

<< So, from this I take it one wouldn't allow the Laptops to connect direct
to the database on the sever for updating / adding the new invoice records,
but rather you copy the updated files between the computers? >>

Yes, which I thought was the goal since I assumed that the laptops may or
may not be connected at all times.

<< Or is it also possible for the Laptop to connect to the Admin database on
a separate session component, and execute the update directly so no
scheduled tasks are required? >>

Sure, you can do that also.  You just have to make sure to do some extra
work to make sure that the EDB Server that is facing the Internet is secure
(get rid of default admin password, change encryption password, require
encrypted connections).

<< I was thinking of just completely overwriting the other tables on the
Laptop with the latest data on the server. (Just a complete 'dump' so to
speak). >>

Not a good idea, in general, but you can do it if you want to.  The best way
to do so would be via a backup/restore of the selected tables without the
database catalog, provided that the tables being backed up/restored are
structurally equivalent on both ends.  The beauty of replication is that it
automatically ignores columns that aren't present in the target system, so
you don't have to worry about mismatched table structures.

<< My thoughts were for the Laptop to connect directly to the Admin database
again, and just retrieve / dump all the data across (excluding the invoice
table) instead of using replication files for that procedure - but I don't
know whether there's any traps involved? >>

No, just time involved, depending upon the size of the source table data.
Of course, the backup/restore would use compression, so that's less of an
issue.

Tim Young
Elevate Software
www.elevatesoft.com

> Yeah, I've been pulling late-nighters getting EDB and EWB to settle back
> down after the two new releases, and working on EWB 2.x.  It's quiet at
> night.

Except when some turkey from the other side of the world keeps asking
you new questions.

> << So, from this I take it one wouldn't allow the Laptops to connect
> direct to the database on the sever for updating / adding the new
> invoice records, but rather you copy the updated files between the
> computers? >>
>
> Yes, which I thought was the goal since I assumed that the laptops may
> or may not be connected at all times.

Sorry - I should clarify.

The laptops will have their own seperate / isolated database. However I
was thinking of dropping a second session component on the laptop's
application that could connect to the admin computer / database when
they are on the network during the synchronization so there's no need to
transfer files back and forth between the machines - I could use EDB
exclusively.

> Sure, you can do that also.  You just have to make sure to do some extra
> work to make sure that the EDB Server that is facing the Internet is
> secure (get rid of default admin password, change encryption password,
> require encrypted connections).

I think this answers my question above.


> << I was thinking of just completely overwriting the other tables on the
> Laptop with the latest data on the server. (Just a complete 'dump' so to
> speak). >>
>
> Not a good idea, in general, but you can do it if you want to.  The best
> way to do so would be via a backup/restore of the selected tables
> without the database catalog, provided that the tables being backed
> up/restored are structurally equivalent on both ends.  The beauty of
> replication is that it automatically ignores columns that aren't present
> in the target system, so you don't have to worry about mismatched table
> structures.

Ah - understood. OK - replication it will be. Thanks.


> << My thoughts were for the Laptop to connect directly to the Admin
> database again, and just retrieve / dump all the data across (excluding
> the invoice table) instead of using replication files for that procedure
> - but I don't know whether there's any traps involved? >>
>
> No, just time involved, depending upon the size of the source table
> data. Of course, the backup/restore would use compression, so that's
> less of an issue.

Thanks Tim, I think I've got enough to start work with. Appreciate the
help once again.

Have a good evening.

Adam.

Adam H.

I haven't been on the groups for a couple of days, so I have missed this thread.

I have a decent system using EDB's replication. I think a lot of what I'm about to write might have been written above, so sorry if its repetitive.

* Use unique keys on all tables that will be replicated so that whenever a new record is created on any computer you can be sure it has a unique key. The GUID datatype is the obvious one to use for this.

* Every machine needs its own copy of the database, and a set of STORES to hold the various update files every machine may also need to have EDBSRVR running if you want to use EDB JOBs to run the replication process. This is a much "heftier" install than non-replication based systems. You will probably need to build a simple scripting install-tool to ensure every laptop has all the files, folders etc. This was quite a big step for me moving from "normal" C/S EDB to replication, as previously I basically copied an EXE onto the machine and that was the installation process finished!

* For every new machine in the replication "loop" you will need an "UPDATES STORE" on the server i.e. UpdatesUser01, UpdatesUser02, etc. When you SAVE UPDATES on the server you will then have to COPY FILE into _every_ user update store. These updates files must then be applied in order, and none must be lost. If the machines are accessing the server remotely this process can be a bit dodgy, and you need to write false-safe routines into JOBs to check things are OK.

* Once you have a replication system up and running be aware from the outset that upgrades and changes to database structure will become far more complex to manage than they are with a "simple" C/S system. Remember, if you want to change the structure of a table you can only do this if there are no Update Files "live" flying around between machines. An update file will not apply itself to a table if the table structure does not match the update file's table structure (understandably!) so this update file can become orphaned.

--

It sounds like a lot of negatives, but it is really just a lot of things to think about and get right. Once it is working it is magic. You get super-fast operation (every user becomes a local user, so all database operations are near instant).

I am not sure I would recommend it for systems where the database structure is likely to change AND there are a really large number of users.

Also, you have to get used to the fact that different users will all be carrying different versions of data, depending on which updates they have incorporated into their data.

Finally, we had some serious problems with people taking machines, going off into the field & doing large quantities of work, and not connecting for long periods or even losing the machines (!!). This causes situations where data has been captured, but management can't yet see it, which is a pain. On one occasion a guy returned to the office having "lost" his laptop, manually re-keyed thousands of records, then found the laptop, switched it on & it automatically ran its UPDATE JOB ... bang we suddenly had thousands of double-entered records ... ouch.

Good luck with it & I would be happy to share a few hundred lines of SQL code which I use for my updates process (and for my "create new user on server" process) if you were interested.

Hi Adam B,

Thanks for the information. Good thought about people losing machines,
doubling up, etc. There's always something unforseen that the users are
able to do to find a way to get us.

Cheers

Adam.