I am really grateful to everyone for their feedback ... there are so may ways to do this sometimes we are talking slightly at cross-purposes, but even that is illuminating.

--

Uli is right. If the user Polls the server every time they start their program, then the server can say "no more updates today, you have to run a change-script first"

Thus there is no "old" update file generated from the old structure database.

This is an excellent solution _if_ users have access to the server all the time

Unfortunately my precise reason for having the "cloud" form for the database is because my users (mostly in remote areas in Africa) do not have continuous server access.

Thus they cannot always confirm that they are "allowed" to use the system by logging on to the server.

... however I might well be able to get around this. For example, if the change date was scheduled.

i.e. "you can continue to use the system and create updates up to date XXX, after that you must stop"

Then users could post their updates to the database up to a certain time & after that changes could be blocked.

In fact, the update script could be incorporated into a transfer of data prior to the date it was actually needed, then only used on that date, with an "old" update prior to that date, and "new" updates thereafter.

Phew ... moderately complex, but probably not impossible!

I've been following the thread with interest as i need to get into edb
replication later this year as well and this has been most useful.

I apologize for possibly silly question (i might be misunderstanding
this still) but it sounds like you don't need to be connected.

The concept i got is that you generate update file (save updates) only
when you're connected and checked the store for the stop file first.

Otherwise it's business as usual - meaning they can fully work with
local data.

If there is connection and update file then you run it first to update
table structures and then generate the update.

Would this work ?

Raul


On 4/11/2012 9:25 AM, Adam Brett wrote:
> I am really grateful to everyone for their feedback ... there are so may ways to do this sometimes we are talking slightly at cross-purposes, but even that is illuminating.
>
> --
>
> Uli is right. If the user Polls the server every time they start their program, then the server can say "no more updates today, you have to run a change-script first"
>
> Thus there is no "old" update file generated from the old structure database.
>
> This is an excellent solution _if_ users have access to the server all the time
>
> Unfortunately my precise reason for having the "cloud" form for the database is because my users (mostly in remote areas in Africa) do not have continuous server access.
>
> Thus they cannot always confirm that they are "allowed" to use the system by logging on to the server.
>
> .. however I might well be able to get around this. For example, if the change date was scheduled.
>
> i.e. "you can continue to use the system and create updates up to date XXX, after that you must stop"
>
> Then users could post their updates to the database up to a certain time&  after that changes could be blocked.
>
> In fact, the update script could be incorporated into a transfer of data prior to the date it was actually needed, then only used on that date, with an "old" update prior to that date, and "new" updates thereafter.
>
> Phew ... moderately complex, but probably not impossible!
>

Adam,

> This is an excellent solution _if_ users have access to the server all the time
>
> Unfortunately my precise reason for having the "cloud" form for the database is because my users (mostly in remote areas in Africa) do not have continuous server access.
>

As Raul indicated: you'll never run into this if updates are only
allowed when the user is online thus being able to query the existence
of any Change-script.
If a user is not online, he shouldn't be able to write any updates at all.

Maybe at the moment you allow users to write updates "offline" to a
local store and send them to the remote store later? Then you would be
right: that wouldn't work.

If you allow updates only for "online"-users, the concept should work.

Uli

Adam,

<< You cannot apply an update file to a table when the structure has
changed. >>

Just to clarify - you cannot apply an update file to a table when a column
exists in the update file whose data type is different.  The length/scale
can be different, and columns can also be missing (sorry Uli - told you this
wrong via email) in the target table.

So, the rule of thumb is to never alter a column's data type without
changing the column name also.

--
Tim Young
Elevate Software
www.elevatesoft.com

Peter,

<< Can you run the updates from a client twice? I would have thought this
would be possible because what if there was an error writing the updates, I
presume that they could be re-run. >>

This is true for two reasons:

1) The loading of an update file is transactional - if there's an exception,
the whole thing is rolled back.
2) When updates are loaded, they are "marked" as having been loaded by the
table, so they will ignored if you try to load them a second time.

--
Tim Young
Elevate Software
www.elevatesoft.com

"Tim Young [Elevate Software]" wrote:

Peter,

<< Can you run the updates from a client twice? I would have thought this
would be possible because what if there was an error writing the updates, I
presume that they could be re-run. >>

This is true for two reasons:

1) The loading of an update file is transactional - if there's an exception,
the whole thing is rolled back.
2) When updates are loaded, they are "marked" as having been loaded by the
table, so they will ignored if you try to load them a second time.

--
Tim Young
Elevate Software
www.elevatesoft.com

Very interesting thread. I'm getting a good insight into how this will work.

Thanks

Peter

I have had replication running for a couple of years and have endured the pain of controlling the update/replication issue.
I have now developed a plan that seems to be working for my customers.
The issues to be considered are:
 1. Making sure that each replication server is running the same version when synching takes place.
 2. The Remote Servers should not Update Versions if the Main Server hasn't.
 3. That Updates do not occur if the Customer's registration is out of date.
 4. To be able to archive synch files leaving and arriving the servers.
 5. Be able to suspend synching on one or more of the servers without logging onto the machines.
 6. Ensure that client machines have the right site ID for their site. I use a primary Key scheme that prefixes the siteID on a sequential number for each table. Each site is totally independent when creating primary keys and it is easy to determine which site created any given record.

The heart of the plan is a Customer Database, of course using EDB, and located on a hosting site. I use DYNDNS to establish a dns host name. Each Server has that host name stored in a Site Info in their DB.

Each Server has a AppUpdate.exe that is scheduled during the night with the Remotes scheduled about 1/2 hour after the Main. The Updater checks for valid Customer number and registration and the newest Version. If a new version is determined, new files are downloaded from the Update host site to a Local_NewVersion store. Then those same files are copied to the application folder. Client machines will copy their new files from the server's New_LocalVersion also. Next the database DBUpdate exe is executed and the Updater is terminated. The Main's version is also posted on the Customer database.

When the Remote(s) AppUpdater runs, it checks the Remote version against the Main Version on the Customer DB and only runs if the Main has updated. Then it basically does the same procedure posting its new Version to the Customer DB.

Client machines do a version check when they log on and if necessary run a ClientUpdate that copies new files from the Server.

The Customer database has properties to enable/disable Synch and Archiving on each Site Server. The synch routine (which is scheduled) checks:
 1. That server version matches the Main server version.
 2. That Synch is on. Otherwise it aborts.
 3. Whether to archive the update files.
 4. A Clear flag allows control of deleting old archive files.

When Client machines log on they look up the SiteID using the Remote HostName/IP to find the correct ID. This prevents them from creating primary keys that would conflict with another site.

This plan has been in operation for about a month on a limited number of customers and so far is meeting the objectives. I am deploying it to all other customers slowly.

I have also added to the Customer DB standard report temples (Report Builder) that the Application can access and download as the customer requires. I can also create custom reports and mark them for download by specific customers.

Since my application serves the hearing aid industry, customers can download vendor products. We keep this list update to date in the Customer DB.



Adam Brett wrote:

>>How often do your users send updates to the server?

Usually hourly

>>If the space of time between two updates is large enough, it should be
>>possible to say "No further updates" by placing a file in the user's
>>remote store.

>>You can stop him from sending updates just at the moment, the file is
>>present in the store. Just query the store every time *before* an update
>>is even stored into the client's local store.

The problem is rather that once I have forced 1 user to stop updating "i.e. "System change pending, please wait to use the system until update is complete" I then have to wait until the very last user has logged on and confirmed their update is loaded before I can restart.

If this user is off the system for many days everyone else has to wait.

Of course if I am co-ordinating users it is OK, I can say "hey guys tomorrow all log on so I can do an update over the weekend" ... however you can see that that this requires quite a lot of co-ordination. I would have loved something I could just set up and "leave", since many of my systems are in remote locations, and I can't easily co-ordinate the users.

I don't think that that is possible.

Sincere thanks for this really useful post Jim.

I have actually got quite a distance with the replication process. The main news is that EDB replication is really workable ... which is kind of miraculous & wonderful ... but that some processes (particularly updating & changing structures of the database) become massively more complex when you have multiple disconnected users, as I have with my replication scenario.

The key issue is to ensure that you don't have UPDATE files for a redundant version of your database which have not been applied, as they cannot be applied, so data is lost, and (this is really a corollary problem) users do not add UPDATE data to an update file before and after a database restructure has occurred, as this results in an UPDATE file which is unusable, since some of its update elements are for "before" table structures, while others are for "after".

I think it would be really useful to post up some example replication projects over and above Tim's useful "sales replication" example. However there are a lot of different use-cases ... which all require different programming ....

If I get a bit further with my own systems I will try to post them up here.