Replicating/coordinating/integrating local area databases to national database

Hi.

I am interested in the issue of connecting local databases (eg in local
sales offices, on salespeoples laptops, etc) with a central database.
Specifically I have in mind an Access database or SQL Server database
(serving clients in the office) in the branch offices and an SQL Server
database or Oracle database for the national office.

The issues involved are primarily replication of data and the transport
proprocol and messaging between the two systems.

I am interested in how easily this is handled within the Delphi 6
MIDAS/Datasnap framework, and how this compares with third party tools such
as ASTA (http://www.astatech.com, "The easiest way to multi-tier data access
over the web ").

I have been discussing this third party component set with the developers of
it in the third party tools forum, and have been encouraged by the
developers to get some comments from people here about using Delphi
Enterprise to complete these and similar development tasks. Some excerpts
from the conversation follow - any comments re working on these tasks in
Delphi Enterprise with datasnap versus using these third-party tools are
appreciated.

Lauchlan Mackinnon.

**************************

Quote
> Very interesting. So you could write a desktop database application in
> Delphi, associate an ASTA client with it, and use it to replicate in a
> secure format to a central database/server application?

Ah - you're in my backyard now :)  I wrote the white paper on
Synchronization
and Replication for Asta and the product is under development now.  It will
do
exactly as you state and a bit more besides :)

Quote
> This is interesting to me because I know of several situations where the
> need is to have a central database ...

That's exactly right. That's what it is for. You may have salesmen using
laptops that contain the section of data that is of interest to them, in one
database format (let's say Access) and a central database that uses another
database format (let's say MS SQL Server).  The salesman can connect to the
main server at any time (via the Internet, or direct hookup in the office)
and
synchronize their data.

Some database vendors offer this kind of capabaility for their own
databases.
What we will do is provide it for all databases that Delphi can utilize!! :)

Quote
> What advantages does ASTA have over Delphi Ent for these sorts of tasks

Our main product line is components that give you FAST database connectivity
over the Internet. Your data is transported within a TCP/IP packet - we take
care of all the clever stuff for you. It's small footprint and as fast as it
can be. The closest comparable that Delphi has is MIDAS (or DataSnap as it
is
now known). I would encourage you to ask questions in that forum to see the
applicability of MIDAS to your task. It's not that I can't tell you, it's
just
that I am, of course, biased :)

<<What advantages does ASTA have over Delphi Ent for these sorts of tasks
(essentially database replication) apart from those mentioned in the second
paragraph below?

Delphi Ent doesn't really give you too much to do this, it certainly has the
core tools with Midas but ASTA has a messaging layer and AstaPosBus and soon
a full featured Sync and Replication Application. PosBus is $249 and
requires the appopriate ASTA server licenses.  Sync and Repli will be more
expensive, as it will bascially be cross platform hotsync for any database,
OS or handheld.
http://www.astawireless.com/products/skysync/

ASTA Database Replication Engine

There is a Demo available using Dbisam (www.elevatesoft.com) available at

www.astatech.com/files/posbus/AstaPosBusDemo.zip

 ASTA allows remote users to connect to databases across the Internet and
efficiently communicate with a remote server. When deploying applications
over WAN's and the Internet, developers must be very conscious of the cost
to and from remote servers. In this brave new world the most precious
resource is now bandwidth, not cpu speed, memory or database speed.

Disconnected Users

Oftentimes users work remotely or connect to a server for a limited period
of time and then want to be able to work "offline". With the rise in
wireless communication and the spread of reasonably priced connectivity,
more and more employees will be given the opportunity to work remotely but
will not always be able to be connected to a remote server 24 hours a day, 7
days a week.

ASTA Suitcase Model

ASTA supports a suitcase model where users can fetch data from an ASTA
server and then disconnect from the server, modify the data, save it to a
file, and then come back at a later date, reconnect to the server and apply
any inserts, updates or deletes. This suitcase model can be complicated by
the fact that data on the server may have changed during the time the user
was disconnected.

Disconnected Groups of Users

 Sometimes groups of users may work remotely and be connected by a local
area network or run their own ASTA server. These users may need to share
data and that data may need to be synchronized with a remote Database of
other users also sharing Data.

Fairly Static Data

 Sometimes data that a remote client selects from a server does not change
too often. Some ASTA users have taken to performing some queries when an
ASTA server starts up or at fixed intervals and then compressing that data
and streaming it down to remote ASTA clients using ASTA messaging.  This
data needs to be efficiently "refreshed" and synchronized between host and
remote sites.

The Solution

 To deal with all the above issues, ASTA is developing an Add On that uses
the internal code name of AstaPosBus. (PosBus is Afrikaans for Post Office
Box and was coined by the PosBus project leader Stephan Marais as Stephan
had experience on a simlar project in his native South Africa).

 ASTAPosBus will allow table by table and field by field replication and
syncronization. Replication can be very complicated subject but ASTAPosBus
will attempt to provide base methods to allow for ASTA developers to easily

synchronize data between remote databases using ASTA servers.

Purpose
To provide a basic process to sync remote databases with a server database.
This does not apply to database schema changes, only data changes.

There are 3 different actions that can be applied to a table, and depending
which kind of action, it must be handled differently.

Insert
Can be handled in two ways:
1. The table must contain an update_date/insert (datetime) field
2. An audit table can be used to record the insert. The audit table must
also contain an "action" field

Update
Can be handled in two ways:
1. The table must contain an update_date/insert (datetime) field
2. An audit table can be used to record the insert. The audit table must
also contain an "action" field

Delete
1. An audit table can be used to record the insert. The audit table must
also contain an "action" field

Assumptions
1. Primary keys are not allowed to be changed. If needed, delete the record
and make a new entry
2. The tables on the server must have at least all the fields as the tables
on the client
3. If audit tables are used, each audit table must have a field called
action_indicator (char) and audit_date (datetime)

<<I can see the importance of the messaging. If one client makes a change,
and
a second client later makes a change, the second change may need to be
rolled back or redone. This could involve some quite complicated logic. For
a simple case, for example, the first change could be a sale which happens
to be the last unit of a Chinese antique vase from the national catalogue,
as is the second. But the second would have to be rolled back from a sale
and converted to possibly  a request for items of similar quality, style and
price, or let go of, depending on what the customer wants. So you need a
whole system to resolve the request as some sort of logic or dialog between
the client and the server systems.

There also could be different logics for preference for the transaction. The
sale could go to the first customer who requested the Ming vase, by the
timestamp recorded in the database there, or to the first client database
that logs in to process transactions.>>

we will give you events or scripting options to handle conflicts.