I am enjoying EDB. I am now using its replication capability, with a "host" server (amazon cloud) and each user having a local version. Every few minutes updates "chat" between each node and the central host to pass updates around.

It has been really liberating, users often away from offices more and more ... laptops, on the road etc., are now just connected ... brilliant.

BUT:

I now have to manage changes to the database.

In the past I could shut down the server, run UPDATE SQL to change tables, post a new version of the EXE on the server, with a mechanism in the local EXE to check for it and download it if the database change required the EXE to change.

NOW: I have to write a system to migrate the UPDATE SQL across all my client nodes - which are all sharing & swapping data updates live every few minutes - and EDB's UPDATE process can't easily work between databases with differing structures ... OUCH.

I kind of overlooked this bit of complexity at the outset when I thought about a cloud-based system ... please everyone laugh.

--

I can see that it is possible to build a fix, a friend who works with a big bank here in the city of London has pointed me towards the solution, he said:

"yes its not too easy but its possible, what you need are 2 concurrent versions of the database, you run updates on 1, test it then make it live, and include a flag on the server notifying new users as they log on. This triggers them to download the update SQL prior to logging on themselves. Then users can continue to use the old database with the new one live, and they migrate over to it gradually. You have to write code to funnel data between the 2 versions of the database so they are both capable of being the "live" database at any time."

... sounds scary but possible.

Does anyone else have experience with this they can share? ... a few wise words could save me a world of pain as I embark on making it work myself.

Adam

Adam,

> I now have to manage changes to the database.

I am just at the same point and asked Tim for a solution for this.

BTW: the combination of local databases / EDB server and replication
seems to work great.
I set up a testdatabase (in a LAN) using GUID's to avoid trouble with
index violations, but I'd prefer integers (like you).

Regards Uli

Adam Brett wrote:

I am enjoying EDB. I am now using its replication capability, with a "host" server (amazon cloud) and each user having a local version. Every few minutes updates "chat" between each node and the central host to pass updates around.

It has been really liberating, users often away from offices more and more ... laptops, on the road etc., are now just connected ... brilliant.

BUT:

I now have to manage changes to the database.

In the past I could shut down the server, run UPDATE SQL to change tables, post a new version of the EXE on the server, with a mechanism in the local EXE to check for it and download it if the database change required the EXE to change.

NOW: I have to write a system to migrate the UPDATE SQL across all my client nodes - which are all sharing & swapping data updates live every few minutes - and EDB's UPDATE process can't easily work between databases with differing structures ... OUCH.

I kind of overlooked this bit of complexity at the outset when I thought about a cloud-based system ... please everyone laugh.

--


I can see that it is possible to build a fix, a friend who works with a big bank here in the city of London has pointed me towards the solution, he said:

"yes its not too easy but its possible, what you need are 2 concurrent versions of the database, you run updates on 1, test it then make it live, and include a flag on the server notifying new users as they log on. This triggers them to download the update SQL prior to logging on themselves. Then users can continue to use the old database with the new one live, and they migrate over to it gradually. You have to write code to funnel data between the 2 versions of the database so they are both capable of being the "live" database at any time."

... sounds scary but possible.

Does anyone else have experience with this they can share? ... a few wise words could save me a world of pain as I embark on making it work myself.

Adam


Yes I can see that this would be a problem. On other software I have a table preferences that all users open when they open the app.  It has setup information. It has the version number of the software.  I email users that the software is going to be updated and to log off in the evening.  If they dont log off the server I kick them off by stopping the server and deleting the sessions. When they log back on they cannot access the data without upgrading because the version number in the prefernces table does not match the version they are running.  Works for me...

I havent done this in a replicationscenario but could be workable. The users have to download an update, during that update you can run a program that updates rthe database or runs the replication then drops the local database and creates an new one and replicates to that.

Peter

>>When they log back on they cannot access the data without upgrading because the version number in the >>prefernces table does not match the version they are running.  Works for me...

I can see that this works while all your data is in one place Peter, but once every user is carrying around a full version of the database which they update individually life gets more complicated.

The issue in a Replication scenerio is "floating updates".

Say on Monday I do a bunch of work, on the local copy of my database.

Unknown to me the SysAdmin alters the database structure on the server.

I come to log on to the main server, to upload / download all the new updates from myself & other users. I get a "please update now" message. I can update & change my local DB structure fine, so that it matches the server database structure ... However: I am left with a set of updates saved in the structure of the older database, which cannot be applied to the new database.

The solution (which is making my head spin) seems to be to save all the "old structure" updates somewhere sensible, apply them to some "holder database", then migrate this holder database to the new structure and (somehow) pass the floating updates back into the new database ... making this work is a really big job.

--

Right now, I am just soldiering on, making very limited changes to the databases where I am testing replication, to avoid this type of problem.

However, my commercial systems have the capacity for users to alter the database built in ... so I need to make this smooth if I am going to roll it out.

Adam,

> I come to log on to the main server, to upload / download all the new updates from myself&  other users. I get a "please update now" message. I can update&  change my local DB structure fine, so that it matches the server database structure ... However: I am left with a set of updates saved in the structure of the older database, which cannot be applied to the new database.

Are you using different stores for each user?

Tim mentioned a way to manage that:

<<
....In general, you have to properly manage any updates to the database
structure to ensure that you don't break the replication.  You can use
any method that you want in order to do this, but even something as
simple as a script sitting in a  special "DDL changes" store that you
check before copying any update files would work.
If a script exists (and it hasn't been run for that user yet), then you
need to run the script to modify the database structure before loading
any updates.
>>

So I am thinking about creating such a "DDL changes" store for each
user. If there is a script (or any notification) in this store, no
updates should be loaded or *sent*.
Thus a short query *before* creating updates should work.

IOW: for each update a kind of "permission" should be queried from the
server.

This should ensure that no updates with the old table structure can be
sent to the remote store. After executing the script that alters the
database, the script could be deleted thus enabling new updates.

I'll play with this a bit today... Hope it'll work.

Uli

Uli Becker wrote:

>>I'll play with this a bit today... Hope it'll work.

... I'm playing a bit myself ... nice to have holiday time to just try to get it to work. I'm finding it hard though.

Main issue:

If you have a "live" system (i.e. with large-ish numbers of users 24-7) it is really, really difficult to update as you have to start an update somewhere and then "migrate" the change across all the users as they log-in.

However, once the change is started it is very hard to call LOAD UPDATES in a meaningful way for the users with update files in the "old" database structure.

If the table structure of an UPDATE file does not exactly match the table onto which it is being applied it simply fails. This is fair enough ... but does make migration / database structure changes more complicated in a replication scenario.

--

More or less the only way I can see to work it is to actually call "STOP" ... i.e. get ALL users to supply their updates, apply all these updates at the centre, then run the change at the centre, then run the change on all users, then RESTART ... so there is a proper pause during which no additional updates are made to the data which might not be reconciled after the data-change.

Doing it this way is a bit ugly ... but at least it would work.

I can't see a more elegant way to do it easily.

It would be _possible_ to LOAD UPDATES to some secondary database (with the old structure), change it & save updates ... but that seems technically very nasty/complicated to me, and the automated systems of Elevate could not be used, as UPDATES don't survive across a structure change in a table. You would have to write a manual mechanism to recognise the changes.

Adam,

> If you have a "live" system (i.e. with large-ish numbers of users 24-7) it is really, really difficult to update as you have to start an update somewhere and then "migrate" the change across all the users as they log-in.

But if every user has his own store, you can say "STOP" by putting a
file in there. That ensures no further updates until the restructuring
has been done.
You mean, you have too many users to give each of them his own store?

> nice to have holiday time to just try to get it to work.

Yeah. And I like the bad weather in this case.

Regards Uli

>>But if every user has his own store, you can say "STOP" by putting a
>>file in there. That ensures no further updates until the restructuring
>>has been done.
>>You mean, you have too many users to give each of them his own store?

No, I am giving each user their own stores, this is necessary & not too hard.

However there is a fundamental problem. If any user adds any data to their local copy of the database the update file this generates will not be compatible with the new version of the database.

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

I attach a script which demonstrates this.

To run it open it in EDBMgr, and mark the "EXECUTE IMMEDIATE" elements with break-points so you can trace through the process ...

You will see EDB can cope with a lot & keep the update / replication process working ... but at a certain point it (quite understandably!) barfs.

Adam,

> You cannot apply an update file to a table when the structure has changed.
> I attach a script which demonstrates this.

Yeah, I know that.

How often do your users send updates to the server?
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.

Thanks though for the script: I'll go through it.

Regards Uli

>>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.