Jump to content

Multiple databases or one big database?


Skepsis IT

Recommended Posts

Hi everybody,

 

I post the following topic because I implement a "framework" that our applications will use an application server containing all objects and business logic that serves rest/json data and use unigui as frontend without much business logic at it (I try to have none). The question is that if it's better to have a model that uses one db per customer, or one db for all customers (just have a foreign key to a table containg customers). I've read a lot of advantages and disadvantages on books and web, but I'll like to here the opinions of all unigiuers because I respect all of them a lot. Moreover if someone has a real case study and wants to share it, that will be great.

 

Moreover another topic is how easy and if is achievable to have multiple dll's running on different subdomains on IIS or Apache? Just for scalability and performance issues.

 

Regards,

Skepsis IT

Link to comment
Share on other sites

Hi and thanks for your answer,

 

How many users are you expecting to have?
Which database are you planning to use?

 

The problem is that we don't know exactly, we hope a lot :) , if I go for one database I will choose SQL Server, but If the solution is for multiples I have a thought of firebird.

 

 

The smaller the database , the faster it responds.

I would go for multiple databases.

There is also the issue of not "mixing" the clients data in case of a single database.

 

I agree with you adragan for faster response. With multiple databases it is also easy to scale out, with one db only scale up is an option. The disadvantage is that processes such backup or upgrade or more time consuming and perhaps they hide problems... Furthermore multiple database take more space on hard disk (of course the costs nowadays are low for disk space).

 

Link to comment
Share on other sites

using one database, you will certainly have to make adjustments in performance, because in all the consultations must be applied by the filters connected enterprise ID.

on the other hand it may be that in your database you have tables that can be shared between companies, thus avoiding data redundancy.

 

I have something similar, and I chose to have separate banks for each client.

my routine updating applies scripts when necessary on all seats attached in SQL Server server and has worked very well, especially in relation to performance. I have a server with 8GB with 45 banks sql server, and I did not have slowness of complaints.

I have some tables that are shared among customers and that are my responsibility, these tables I chose to leave in a single separate basis, as are tables where customers only have access to reading.

 

 

this brings a problem because every new customer, you should attach a new bank.

If you foresees few records for each customer, and you want new customers entering their much simpler system, the use of one bank only would be ideal. then you should analyze all this to see what best suits your needs.

Link to comment
Share on other sites

Wilton_rad thanks for your answer, and for sharing your experience.

I tend to go for multiple databases.

 

Could you share the technical view of having multiple databases and unigui. What I meant is that you have one dll and one db for each customer, or one dll and multiple dbs?

 

Thanks again

Link to comment
Share on other sites

Hi.

 

I strongly recomend to use one DB. Why:

- many structures of data are common for all customers, it's no use to replicate it. One job for many future customers. Every common structure with "customer_id" field, and index for this, no efficiency problem

- future changes for structures - one change vs many the same changes for all customers

- new customer = new customer_id, it's no use to create next db

- one DB = easy backup

Link to comment
Share on other sites

Piotr thanks for the reply.

 

Of course one db is the easy way and the most straight forward. You mentioned already some of the advantages.

 

The only fear that I have is that if the database get corrupted nobody can access it and work. The loss of data perhaps is for everyone.

 

I have seen occasions ant troubleshoot corrupted databases and it is a nightmare.

 

Do you have a personal experience with a system that uses one database for hundreds of companies?

Link to comment
Share on other sites

Hello Sekpsis,

 

I think that there is no ONE good answer.  It depends on your database itself (working with several databases is easier with Firebird or Interbase than with Oracle or DB2, and I don't mention MS Access), on your DB components (BDE, ADO or FireDAC doesn't allow you the same things), on your drivers (OLE-DB, ODBC, Native?), on your web server (Apache OR IIS?)...

 

And overall, it depends on your skills, habits, previous experiences and preferences...

Link to comment
Share on other sites

Hi Zizzig and Andy.

 

Of course there is no correct answer because both have advantages and disadvantages. The question basically is for sharing experiences from other developers like Andy did.

 

I conclude that all developers that have personal experience used multiple databases and this has it's meaning.

 

Thanks again for all the replies

Link to comment
Share on other sites

I developed many DB projects. Some for 10.000 users. Safety is of course base problem. But there are diffrents ways to increase safety, separating BD's is not:

1. DB replication online

2. Hot backups

3. DB access protection

I work with PostgreSQL, that is quite advanced DB, like Oracle etc. What DB engine are'u using ?

Link to comment
Share on other sites

I can talk with my experiences on this kind of issue.

 

If you use Firebird is not the same than SQL Server, PostgreSQL or something else.

 

I have a lot of experience on SQL Server and all is simply pleasure an pleasure. For the me is the best Database I that has never used. All is easy to do. And with this database I will take the risk of merging all customer data in one database for sure.

 

But is not my reallity of the day to day. I in most cases use Firebird because my customers don't want to buy database licences. But this comment does not do less of Firebird that is a very very good free database.

 

Firebird has a stone in the shoes that is when lock stored procedures when a user is using it or to change foreign keys. You make to expluse all users from database to make a change on it.

 

When you make complex queries, you must know wath are you doing, need tools like IBExperts to help you to tunning your queries. this is mandatory. On SQL Server you can be drugged when you make SELECTs, I don't know how it makes allways work. Even on a big database with millons of records.

 

Firebird Databases crashes are more habitual, because of this is mandatory a backup couple of a day for sure. Model of restore backup are more complex on firebird, specially when use IBExperts to manage it that when you make a change on the metadata it is does "by under" modfying metadata tables directly that make your table rows inconsistent. And this sittuation make you suffer on restarting a backup, is needed to deactivate validations on restore and then update metadata again using Database Comparer.

 

Last complain of Firebird this stored procedures and triggers executes so slow comparing to others databases. Sometimes if better to get all records do yo need on a Delphi ClientDataSet and process him than execute a stored procedure.

 

Out of this highlights Firebird is a very good database to use.

 

But of this issues if I need to manage very costumers data on Firebird I will use a distributed model (one database per customer).

 

And the most important with Firebird.....if you can....always use it on Linux with multiple processor support enabled.

Link to comment
Share on other sites

Oscar Flor thanks for the answer. This is exactly the dilemma multiple firebird dbs or one big sql server. Furthermore after piotre post a thought of postgres wake up because of the free situation. But this means that you must rent 2 vps one for the db (linux because as I read performance on windows it very bad) and one windows vps for unigui server.

Link to comment
Share on other sites

Or wait for Farshad to make the changes to allow for a UniGui app work as console app on Linux, via wineconsole or the Technology preview of Embarcadero...:)

 

But for now You can use only one VPS because latest versions of Firebird with classic server implementation works well on Windows.

Link to comment
Share on other sites

Oscar unfortunately, there is no time to wait for linux support, and wine console not so stable for production environment.

 

Moreover I tend to select an one database solution so firebird is not an option. Do you have an opinion about postgres?

Link to comment
Share on other sites

As other said, there's no right answer, but it's depend on you sitution, you should decide based on some factors for example:

 

1. Will you host the application for all users one same physical server.?

 

2. Will you have a replication server for the database?

 

3. Does clients will be able to have their backup from their database?

 

4. How many clients and tables you think you will have?

 

5. If you have changes to database, do you consider how much effort to updates all your clients database?

 

6. Calculate how much data will be after a year for all users, and will one database will work with this large data or it should be separated.

 

7. Finally the backup, one backup is much easier than having hundreds backups, specially if you have a backup plan when you have full back each week and incremental back each day.

 

Also if you consider Oracle database, it has great feature called "Virtual database", which you can have one database for all clients, but each clients will see his data only even if he uses "Select * from table"., I don't know if MS SQL server has this feature or not.

  • Upvote 1
Link to comment
Share on other sites

  • 2 weeks later...

I can tell you what I do, with my online appointment scheduling/invoicing app.

 

To keep the costs down, I run several clients on a single VPS, but if they want to be separate, they can, but at higher cost.

 

On a VPS with several clients, I run a single MySQL db instance, with several db's - one for each client.

 

Backup is easy as I can backup the whole MySQL instance at once, and same with replication to a local db server at the client location.

 

Setting up a new client takes me 5 minutes for the clean db, 5 minutes for file copy, 2 minutes for apache httpd.conf editing

and 30 mins for app basic setup like company info, daytime schedule etc.

 

All apps on a single VPS run on a single domain with different aliases, but the client can also get his own domain mapped to

that first domain/alias.

 

On a single VPS I can run maybe 20 clients with enough RAM. Each client is a small business with up to 5 concurrent internal users

and up to around 20-30 online customer concurrent users, but then they use a smaller app not taking up so much RAM.

 

I used Oracle earlier, and it was great, but the last 10 years I have used MySQL, and it is extremely stable,

never ever had a db issue, and very simple to use. And free of course.

Link to comment
Share on other sites

Thanks for sharing your experience delphidude. So you use a multidatabase model.

 

I assume that your unigui dll is common for all customers and you change db connection according domain alias. Correct me if I am wrong.

 

I didn't know that mysql can perform a backup to all database under a common instance. Moreover I think that mysql is free only for use with freeware applications, this changed since oracle bought it.

 

Do you implemented also a mechanism for updating automatically all of your clients dbs?

Link to comment
Share on other sites

MySQL comes in two versions, one community server which is completely free, as far as I know, and one paid version.

 

Each customer has its own DLL, in a separate directory, and in that folder there is a text file telling the DLL which db to load.

 

The text file is loaded by both the servermodule and the mainmodule.

 

The alias defined in the Apache httpd.conf loads the dll in each directory.

 

The VPS and db is backed up hourly by Idera Backup running a client instance on the VPS.

 

In addition there is a full db replication going to a local machine at one of the clients' office, as mentioned earlier.

 

During 9 months now, I've only had one issue where the VPS froze, only the db worked, but after a restart all was ok.

Link to comment
Share on other sites

Very well delphidude.

 

The solution that I think to deploy is to have one common dll and multiple databases.

 

Every customer will have a subdomain that will be used for pointing which database to use.

 

I'll come up when I have something ready but if someone has something to suggest I will be glad.

Link to comment
Share on other sites

Please sign in to comment

You will be able to leave a comment after signing in



Sign In Now
×
×
  • Create New...