|
|
|
SSC-Addicted
 
Group: General Forum Members
Last Login: Tuesday, March 03, 2009 9:30 AM
Points: 489,
Visits: 489
|
|
Hi All,
I have recently been tasked with looking at migrating our product to SaaS, and the pheasability of hosting Multi Tenancy databases.
I have read up quite a bit on this, and have had the following thoughts towards a possible solution:
Use a single database to hold all customers data.
Use with shared tables with partitioning (creating a tenant id field); or create tables within a schema for each customer. (I would still possibly use schemas for the shared tables, by creating dbo tables and then schema views to ensure that customers only see their own data).
My question is, does anyone have experience of implemeting multi tenancy in the real world, and if so, do they have any comments on my proposed options?
Many thanks
|
|
|
|
|
One Orange Chip
Group: Administrators
Last Login: Today @ 6:59 AM
Points: 29,490,
Visits: 11,662
|
|
|
|
|
|
SSChampion
Group: General Forum Members
Last Login: Today @ 9:05 AM
Points: 13,518,
Visits: 8,078
|
|
Separate databases. Lots of reasons.
Makes it easy to split it up onto separate servers to scale out.
When a customer calls you and wants you to restore "their" database to "last night, before we accidentally deleted all of our records", it's a lot easier to do that if you don't have to worry about overwriting other customers' data.
More secure.
Legal compliance in some cases.
Easier to retire a database if you lose a customer than it is to clean all of their data out of a multi-tenant database.
You can offer more by way of customization, since table alterations don't break what other customers are doing.
Disaster recovery: If one database file gets corrupted, and you only have one database, you're in a lot more trouble than if one database file gets corrupted and you have one database per customer.
And so on.
- GSquared, RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
|
|
|
|
|
SSChampion
Group: General Forum Members
Last Login: Today @ 9:05 AM
Points: 13,518,
Visits: 8,078
|
|
Two things to add to that:
If you decide to go one-per-customer, make sure to set up your model database (system databases) so that it's got everything a new customer needs. Then, when you create a new database, it's ready to get started.
Second, create a "Common" or "Shared" database with static data that all the databases will need access to. Things like tables of US states, Zip codes, a Numbers table, possibly a basic calendar table. That way, you don't have to store that data dozens of times for dozens of customers. But make sure it's ONLY for common data that everyone needs access to, and make sure it's in read-only mode to simplify locks.
- GSquared, RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
|
|
|
|
|
SSC-Addicted
Group: General Forum Members
Last Login: Tuesday, March 03, 2009 9:30 AM
Points: 489,
Visits: 489
|
|
Many thanks for your replies. Lots of good advice. Never thought about restoring for different customers!!
|
|
|
|
|
SSCrazy
Group: General Forum Members
Last Login: Yesterday @ 4:51 AM
Points: 2,563,
Visits: 3,615
|
|
Rootman (9/15/2008)
My question is, does anyone have experience of implemeting multi tenancy in the real world, and if so, do they have any comments on my proposed options?
Yes, I did it using Oracle VPD which is designed specifically for that purpose.
My comment... I wouldn't do it again, in the Oracle world I would go with a private schema per "customer", on the SQL Server world I would go with a private database per "customer".
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
|
|
|
|
