Single Database Vs Multiple Database on SQL Server

database-designmultitenancysqlsql server

we have web application which stores information in backend SQL 2014.
currently the way is set up that central database stores all key information for the customers and then we have seperate database for each customers.
Now tables structure for each customer's database is same for particular product.
e.g. if one customer1 DB has tables almonds, oranges and Customer2 DB has tables lemon, citrus the structure is same just table is different depends on what kind of famrs they have. This tables belongs to Product 1 they purchased. This tables doenst have any reference to key since it will have redundant data.

In future if they will buy second product, Table structure for the product will be different then product1 but it will be same along each customers database.

Currently we are small in size but expecting to grow very very big with all kind of spatial data and Raw data with images , GIS data etc..
Also with Different prodcuts offering like product1, product2, product3 etc..

question is ..
Is this good database design for future reference to hand heavy load?

Should move forward to have one big database contains all information?

Also keeping central Database on SQL and each customer DB keep on 'Not Only SQL' platfrom ( MongoDB, Cassendra) will be good approach?

Best Answer

How oranges are different from lemons? I mean, are they “structurally different” like a Product is structurally different from Person or from Comment, or they are just different forms of the same object, such as Smartphone and Tablet are simply different forms of a device?

In the first case, distinct tables make sense. In the second case, there would usually be one table ConsumerDevice which will store both forms of objects. Sometimes, the distinction wouldn't be clear, and the structure may change over time.

As for the one database per customer, I can see at least three reasons to use this approach:

  • If customer's data belongs to the customer, you may easily do a database dump, without having to carefully filter the data which belongs to other customers.

  • If you have a specific offer where a customer may host your service in-house, this is it: migrating the database from your servers to customers' becomes very straightforward, and doesn't affect in any way other customers.

  • You may upgrade the product selectively for some of your customers before pushing the new version to everyone.

However, if I were you, I would think twice before using this approach. Database maintenance may quickly become a nightmare. It could work if you have experienced DBAs who have already experienced the case where they had to maintain hundreds or thousands of similar databases. Otherwise, avoid this at all costs. A fully automated infrastructure is also a requirement here: “Remote Desktop/let's patch this quickly” approach is out of question here.

Related Topic