Dear Friends,
I wanted to Run my Application on cloud.Application will be for multiple clients . I wanted to know. which db architecture should i follow to handle multiple client and their data. I have browsed some sites. here i found there multiple way to handle this situation . we can use multi tenant DB architecture for the same.
- Catalog based multi tenant db architecture
- schema based multi tenant db architecture
- Table based multi tenant db architecture
so for cloud application which db architecture is preferable. ??
i am using SQL server 2012.
There will be huge data for each client.
Each is doable, and each has its pros and cons. you have to decide which pain you are willing to accept
I would say it depends. Is the application setup to handle multi-tenant dbs? How is that achieved? How big is each client? How volatile is the data? Does everything hit 1 or 2 tables or is it distributed? We ran a multi-tenant db for a SAAS company. We had 4 different AGs with multiple clients. We used a main database that the user would login to that had the database and AG name to connect to. The dbs ranged from 4Tb to 10Tb. The biggest issue we found was blocking, so we enable ReadOnly secondaries and directed all reads to those servers. Worked well and had no issues
Currently i am using one DB for one client and setup is on premise setup. Now wanted to shift it to cloud for multiple clients(same s/w and h/w will be in use for each client).Data for each client will be huge and gradually increase. My fear is that if i use single db/table architecture (client data segregation will be based on client id in each table) then there will be application performance issue. to dig in to huge data ,read write operation will be there on 2-3 tables, that surely impact performance