Hi

I _might_ be stepping into a murky new world.  The world of  "web apps" (or
is it webapps - see I'm that ignorant!).

Scenario
======

We will (hopefully) have many customers running this new app.

Each customer has their own database with their own data but all with the
same database format.

Each customer will have one or more users who will connect to the customers
own database.

Each user will also have to access a common database to validate themselves,
find out what software options they have paid for etc. and direct them to
the correct customer database.

Software environment
===============

We anticipate writing the app using EWB, using EWBserver to serve the pages
and EDBserver to serve the data.  Not thought out yet, but probably have a
backup copy of the data in a separate location that automatically
replicates.

The tricky bit
=========

How do we design the database?

1.  Separate EDB database for each customer?

Does that mean a separate EDBServer  for each db.  Could have dozens of
users hooked up at one time, so maybe that isn't feasible.  (Especially if
the app is wildly successful and we have hundreds of users!)

It would be good for backups as you could presumably revert to backup for
just one customer quite easily.

2.  One EDB database for all customers.  Private tables for each customer -
maybe use a CustomerID as a prefix to the table name.

Reverting to backup would involve reverting just the tables for the given
customer.

3.   One EDB for all customers with every record it the database carrying
the CustomerID as part of it's Primary Key.

Reverting to backup for just one customer would have to be ... horrible.

4.  Something much better that I haven't thought of ....

I haven't thought this out obviously because, as I type this, I realise
there are two types of reverting to backup.  The first is recovery of the
last good version and the second is going back to "last Friday" before the
customer deleted some info accidently - or similar.

This all a bit much for a Sunday afternoon!

Anyway, any pointers to get me started in the right direction would be
appreciated.

Cheers

Jeff

--
Jeff Cook
Aspect Systems Ltd
www.aspect.co.nz

Jeff,

I would choose option 1, but not a separate EDB server for each DB.
You could start with ONE EDB server, one database for each customer, and then you can add servers as the number of customers grow, lets say each server would manage a set of 50 customers, but this is only an example, it would depend on the observed performance. Also, it would be easy to move an EDB server along with it's set of databases from one computer to another. With this design it would also be very easy to isolate problems in a customer's database without affecting other customers, as well as to do backup and restore operations for single customers.

--
Fernando Dias
[Team Elevate]

Hi Fernando

"Fernando Dias" <fernandodiasAremovthis.easygate.com.pt> wrote in message
news:A8800E36-AF1B-4E1B-84D7-C6EED96D155F@news.elevatesoft.com...
> Jeff,
>
> I would choose option 1, but not a separate EDB server for each DB.
> You could start with ONE EDB server, one database for each customer, and
> then you can add servers as the number of customers grow, lets say each
> server would manage a set of 50 customers, but this is only an example, it
> would depend on the observed performance. Also, it would be easy to move
> an EDB server along with it's set of databases from one computer to
> another. With this design it would also be very easy to isolate problems
> in a customer's database without affecting other customers, as well as to
> do backup and restore operations for single customers.
>
> --
> Fernando Dias
> [Team Elevate]

Thanks for that!  I've now read the book (in the right place) and thanks to
your input I have a better understanding of how the EDBServer works and how
I can set it up for multiple databases.

I'm coming from the world of DBISAM3 and an application that only ran one
database per PC, either file server or client server.  I had no idea that
one server could serve multiple databases - obvious when reading the manual
with no preconceived ideas and an open mind!

Cheers

Jeff

Jeff, I tend to agree with you. Keep it simple.

But I fear there is a problem with having a separate database for each customer. Maintenance.
He is going to have to have a plan in place when he changes the database schema, he is going to have to make the same changes to all 100+ databases at the same time.

Jeff, from your experience, have you ever had to do this on multiple live databases?

I suppose he could develop a script that will apply these changes to his list of databases automatically.
If the database structure becomes out of date with his updated application (webserver or client app), he's going to have a big problem sorting things out. So he will have to make sure all of the database objects have the correct version numbers etc..

I'm thinking off the top of my head:

He is going to need 2 sets of everything: Database server, Database directory (and customer directories below it), and web server.  The first set could be called "Live" and the second is "Maintenance".

He can replicate all of the "live" databases to a separate set of databases in the "Maintenance" directory. Since each customer has a separate database, he can start changing the databases for the customers that are NOT logged in so the currently connected customers won't be impacted for that long a period. Before he does this he will have to make the database for the not logged in customers, read-only in order to prevent changes to the live database while its schema is being updated.

The updated database (maintenance) is of course using an EDBServer on a different port. He then needs to change the web application so new connections for this customer will use the new database server. Probably a central EDB database with a CustDb table that has the columns Cust#, Database Version, EDBServer port, Db Directory will tell the EDB Webserver which database to use when the customer connects.

He can then continue to convert over the rest of the databases one at a time and update CustDb table.

So the old Live database is archived and the Maintenance database then becomes the Live database. A new Maintenance directory is created and the Live database gets replicated here. (Or he can have a separate replicated database directory and create the Maintenance directory only when he is about to update the schema. He will still need to blow away the old replication directory and start fresh when the maintenance db goes live).

I've only scratched the surface here, but these are some of the things he's going to have to implement when dealing with multiple databases.


Barry

Hi Barry
<Barry> wrote in message
news:AB549D33-0363-4FD0-89FC-135052E410D9@news.elevatesoft.com...
> Jeff, I tend to agree with you. Keep it simple.
>
> But I fear there is a problem with having a separate database for each
> customer. Maintenance.
> He is going to have to have a plan in place when he changes the database
> schema, he is going to have to make the same changes to all 100+ databases
> at the same time.
>
Actually "he" is me!  We are planning on hosting separate "databases" for
each customer of our new application.
>
> Jeff, from your experience, have you ever had to do this on multiple live
> databases?
>

Yes but in an arguably more difficult situation.  We have 60+ databases with
several hundred users but running on separate machines out of our direct
control.  And yes, we have to handle schema updates to match our EXE
versions.

> I suppose he could develop a script that will apply these changes to his
> list of databases automatically.
> If the database structure becomes out of date with his updated application
> (webserver or client app), he's going to have a big problem sorting things
> out. So he will have to make sure all of the database objects have the
> correct version numbers etc..

Taking Fernando's suggestion this should not be a problem.  All the
databases are under our control so we can apply the updates to our own
schedule.  If we know the version of the database then we will know which
version of our webapp to serve to the user.  In fact we will probably always
need this ability as I expect we will, as now, have "tame users" who are
only too willing to try out a new version (beta testers!) especially if it
involves a new feature that they requested.

Once we release a new database version, we can work to a simple schedule and
for each database:-
1.  Is there a schema update waiting to run
2.  Is there a user logged on?  If yes, then leave this database for next
time round.
3.  Flag the database to prevent login with message on attempted logins,
"Half a mo', we are busy, try again in 5 minutes"
4.  Update the schema
5.  Change the version number of the application to match the new schema
6.  Flag the database to allow login again.

>
> I'm thinking off the top of my head:
>
> He is going to need 2 sets of everything: Database server, Database
> directory (and customer directories below it), and web server.  The first
> set could be called "Live" and the second is "Maintenance".
>

Yes, I can see we need two versions of the database as we are planning to
replicate for security purposes.

Not sure why you'd need two servers apart from load handling.
<snip>

One thing that we won't be doing is having difference schemas apart from
version to version.  The application itself will "one size fits all" albeit
with the possibility of different options being available.

Thanks for your input Barry.  It is appreciated and has helped clarify my
thinking.

Cheers

Jeff

"Jeff Cook" wrote:

>Thanks for your input Barry.  It is appreciated and has helped clarify my
>thinking.

No problem.

>Not sure why you'd need two servers apart from load handling.
After some rethinking, a single EDBSrv should be able to handle it.

BTW, since Tim has made C/S memory tables shareable a couple of months ago, you can throw often used readonly or intermediate tables in memory to save wear and tear on the disk drive and it offers some additional security for session data.

Barry